Use Triggers to Maintain Referential Integrity

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.

• Test Data Modifications Against the Trigger Test Tables
  SAP ASE uses two special tables in trigger statements: the deleted table and the inserted table. These are temporary tables used in trigger tests. When you write triggers, you can use these tables to test the effects of a data modification and to set conditions for trigger actions.
• Insert Trigger Example
  When you insert a new foreign-key row, make sure the foreign key matches a primary key. The trigger should check for joins between the inserted rows (using the inserted table) and the rows in the primary-key table, and then roll back any inserts of foreign keys that do not match a key in the primary-key table.
• Delete Trigger Examples
  When you delete a primary-key row, also delete corresponding foreign-key rows in dependent tables. This preserves referential integrity by ensuring that detail rows are removed when their master row is deleted.
• Update Trigger Examples
  A primary key is the unique identifier for its row and for foreign-key rows in other tables. Generally, you should not allow updates to primary keys. A change or an update to a foreign key by itself is probably an error. A foreign key is a copy of the primary key. Never design the two to be independent.