Creating and Modifying FOREIGN KEY Constraints

FOREIGN KEY constraints can be:

• Created when the table is created, as part of the table definition.
• Added to an existing table provided that the FOREIGN KEY constraint is linked to an existing PRIMARY KEY constraint or UNIQUE constraint in another (or the same) table. A table can contain multiple FOREIGN KEY constraints.
• Modified or deleted if FOREIGN KEY constraints already exist. For example, you may want the table’s FOREIGN KEY constraint to reference other columns. It is not possible to change the length of a column defined with a FOREIGN KEY constraint.

  ---

  Note To modify a FOREIGN KEY constraint using Transact-SQL or SQL-DMO, you must first delete the existing FOREIGN KEY constraint and then re-create it with the new definition.

  ---

When a FOREIGN KEY constraint is added to an existing column or columns in the table, Microsoft® SQL Server™ by default checks the existing data in the columns to ensure that all values, except NULL, exist in the column(s) of the referenced PRIMARY KEY or UNIQUE constraint. However, SQL Server can be prevented from checking the data in the column against the new constraint and made to add the new constraint regardless of the data in the column. This option  is useful when the existing data already meets the new FOREIGN KEY constraint, or when a business rule requires the constraint to be enforced only from this point forward.

However, you should be careful when adding a constraint without checking existing data because this bypasses the controls in SQL Server that enforce the data integrity of the table.

Disabling FOREIGN KEY Constraints

Existing FOREIGN KEY constraints can be disabled for:

• INSERT and UPDATE statements, thereby allowing data in the table to be modified without being validated by the constraints. Disable a FOREIGN KEY constraint during INSERT and UPDATE statements if new data will violate the constraint or if the constraint should apply only to the data already in the database.
• Replication processing. Disable a FOREIGN KEY constraint during replication if the constraint is specific to the source database. When a table is replicated, the table definition and data are copied from the source database to a destination database. These two databases are usually (but not necessarily) on separate servers. If the FOREIGN KEY constraints are specific to the source database but are not disabled during replication, they may unnecessarily prevent new data from being entered in the destination database.

Delete a FOREIGN KEY constraint, thus removing the requirement, to enforce referential integrity between the foreign key columns and the related primary key (or UNIQUE constraint) columns in another table.

To create a FOREIGN KEY constraint when creating a table

To create a FOREIGN KEY constraint on an existing table

To prevent checking of existing data when creating a FOREIGN KEY constraint

To modify a FOREIGN KEY constraint

To disable a FOREIGN KEY constraint for INSERT and UPDATE statements

To disable a FOREIGN KEY constraint for replication

To delete a FOREIGN KEY constraint

See Also

Foreign Key Constraints