Triggers maintain referential integrity, which assures that vital data in your database—such as the unique identifier for a given piece of data—remains accurate and can be used consistently as other data in the database changes. Referential integrity is coordinated through the use of primary and foreign keys.
The primary key is a column or combination of columns with values that uniquely identify a row. The value cannot be null and must have a unique index. A table with a primary key is eligible for joins with foreign keys in other tables. Think of the primary-key table as the master table in a master-detail relationship. There can be many such master-detail groups in a database.
You can use sp_primarykey to mark a primary key for use with sp_helpjoins to add it to the syskeys table.
For example, the title_id column is the primary key of titles. It uniquely identifies the books in titles and joins with title_id in titleauthor, salesdetail, and roysched. The titles table is the master table in relation to titleauthor, salesdetail, and roysched.
The foreign key is a column, or combination of columns, match the primary key. A foreign key does not have to be unique. It is often in a many-to-one relationship to a primary key. Foreign-key values should be copies of the primary-key values. That means no value in the foreign key should exist unless the same value exists in the primary key. A foreign key may be null; if any part of a composite foreign key is null, the entire foreign key must be null. Tables with foreign keys are often called detail tables or dependent tables to the master table.
You can use sp_foreignkey to mark foreign keys in your database. This flags them for use with sp_helpjoins and other procedures that reference the syskeys table. The title_id columns in titleauthor, salesdetail, and roysched are foreign keys; the tables are detail tables. In most cases, you can enforce referential integrity between tables using referential constraints (constraints that ensure the data inserted into a particular column has matching values in another table), because the maximum number of references allowed for a single table is 200. If a table exceeds that limit, or has special referential integrity needs, use referential integrity triggers.
Referential integrity triggers keep the values of foreign keys in sync with those in primary keys. When a data modification affects a key column, triggers compare the new column values to related keys by using temporary worktables called trigger test tables. When you write your triggers, base your comparisons on the data that is temporarily stored in the trigger test tables.
