General Rules for Creating Referential Integrity Constraints

Make sure you have references permission on the referenced table. See, Managing User Permissions, in the Security Administration.

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.