Creating and Modifying FOREIGN KEY Constraints
You can create a FOREIGN KEY constraint as part of the table definition when you create a table. If a table already exists, you can add a FOREIGN KEY constraint, provided that the FOREIGN KEY constraint is linked to an existing PRIMARY KEY constraints or UNIQUE constraint in another, or the same, table. A table can contain multiple FOREIGN KEY constraints.
If a FOREIGN KEY constraint already exists, you can modify or delete it. For example, you may want the FOREIGN KEY constraint of the table to reference other columns. However, you cannot change the length of a column that is defined with a FOREIGN KEY constraint.
Note
To modify a FOREIGN KEY constraint, you must first delete the existing FOREIGN KEY constraint and then re-create it with the new definition.
Delete a FOREIGN KEY constraint to remove the requirement for 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 you create a table
To create a FOREIGN KEY constraint on an existing table
How to: Create Relationships Between Tables (Visual Database Tools)
To delete a FOREIGN KEY constraint
Forcing a FOREIGN KEY Constraint by Using WITH NOCHECK
When a FOREIGN KEY constraint is added to an existing column or columns in the table, by default, the SQL Server 2005 Database Engine examines the existing data in the columns to make sure that all values, except NULL, exist in the columns of the referenced PRIMARY KEY or UNIQUE constraint. However, by specifying WITH NOCHECK, the Database Engine 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. The WITH NOCHECK 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 you add a constraint without checking existing data because this bypasses the controls in the Database Engine that enforce the data integrity of the table.
To prevent checking of existing data when you create a FOREIGN KEY constraint
Disabling FOREIGN KEY Constraints
You can disable existing FOREIGN KEY constraints forspecific operations, such as INSERT operations, UPDATE operations, and replication processing.
INSERT and UPDATE statements
Disabling a FOREIGN KEY constraint enables 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.Note
Any cascading actions defined on a related primary key will not be performed on rows that contain foreign keys that are disabled.
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 is copied from the source database to a destination database. 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. For more information, see Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION.
To disable a FOREIGN KEY constraint for INSERT and UPDATE statements
How to: Disable Foreign Key Constraints with INSERT and UPDATE Statements (Visual Database Tools)
To disable a FOREIGN KEY constraint for replication
How to: Disable Foreign Key Constraints for Replication (Visual Database Tools)
To obtain information about FOREIGN KEY constraints
sys.foreign_keys (Transact-SQL)
To obtain information about columns that make up a FOREIGN KEY constraint
sys.foreign_key_columns (Transact-SQL)