When you define referential integrity constraints in a table:
Make sure you have references permission on the referenced table. See Chapter 17, “Managing User Permissions,” in the System Administration Guide: Volume 1.
Make sure that the referenced columns are constrained by a unique index in the referenced table. You can create that unique index using either the unique or primary key constraint, or the create index statement. For example, the referenced column in the stores table is defined as:
stor_id char(4) primary key
Make sure the columns used in the references definition have matching datatypes. For example, the stor_id columns in both my_stores and store_employees were created using the char(4) datatype. The mgr_id and emp_id columns in store_employees were created with the id datatype.
You can omit column names in the references clause only if the columns in the referenced table are designated as a primary key through a primary key constraint.
You cannot delete rows or update column values from a referenced table that match values in a referencing table. Delete or update from the referencing table first, and then delete from the referenced table.
Similarly, you cannot use truncate table on a referenced table. Truncate the referencing table first, then truncate the referenced table.
You must drop the referencing table before you drop the referenced table; otherwise, a constraint violation occurs.
Use sp_helpconstraint to find a table’s referenced tables.
Referential integrity constraints provide a simpler way to enforce data integrity than triggers. However, triggers provide additional capabilities to enforce referential integrity between tables. See Chapter 20, “Triggers: Enforcing Referential Integrity.”
