Select the option to disable a foreign key constraint during INSERT and UPDATE transactions if you know that new data will violate the constraint or if the constraint applies only to the data already in the database.
- In the Database window, click Database Diagrams
under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar. - In your database diagram, right-click the table containing the foreign key, then select Relationships from the shortcut menu.
- Select the relationship from the Selected relationship list.
- Clear the Enforce relationship for INSERTs and UPDATEs check box.
After you add or modify data, you should select this option if you want to ensure the constraint applies to subsequent data modifications.
Note If you plan to use triggers to implement database operations, you must disable foreign key constraints in order for the trigger to run.
Disable a check constraint with INSERT or UPDATE Statements
You can disable a check constraint when data is added to, updated in, or deleted from a table. Disabling a constraint enables you to perform the following transactions:
- Add a new row of data to a table (using the INSERT statement) where the existing rows were required to meet specific business rules that no longer apply. For example, you may have required postal codes to be limited to five digits in the past, but now want new data to allow nine-digit postal codes. Old data with five-digit postal codes will coexist with new data that contains nine-digit postal codes.
- Modify existing rows (using the UPDATE statement) where the existing rows were required to meet specific business rules that no longer apply. For example, you may want to update all existing five-digit postal codes to nine-digit postal codes.
Select the option to disable a check constraint during INSERT and UPDATE transactions if you know that new data will violate the constraint, or if the constraint applies only to the data already in the database.
Disable a check constraint with INSERT and UPDATE statements
- In the Database window, click Database Diagrams
under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.
- In your database diagram, right-click the table containing the constraint, then select Properties from the shortcut menu.
- Choose the Check Constraints tab.
- Select the constraint from the Selected constraint list.
- Clear the Enforce constraint for INSERTs and UPDATEs check box.
You can select this option after you add or modify data to guarantee that the constraint applies to subsequent data modifications.
Disable a foreign key constraint for replication
Microsoft SQL Server supports replication. Select the option to disable a foreign key constraint during replication if the constraint is specific to the source database and may unnecessarily prevent new data from being entered into the destination database.
- In the Database window, click Database Diagrams
under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.
- In your database diagram, right-click the table containing the foreign key, then select Properties from the shortcut menu.
- Choose the Relationships tab.
- Select the relationship from the Selected relationship list.
- Clear the Enforce relationship for replication check box.
Disable a check constraint for replication
You can disable a check constraint when your table is replicated in another database. When you replicate a table, 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 check constraints are specific to the source database, they may unnecessarily prevent new data from being entered in the destination database. When you replicate a database at a remote site, you should not reapply check constraints because:
- The integrity of the data was checked when it was entered into the original database.
- The replication will fail if data violates the check constraints.
- In the Database window, click Database Diagrams
under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.
- In your database diagram, right-click the table containing the constraint, then select Properties from the shortcut menu.
- Choose the Check Constraints tab.
- Select the constraint from the Selected constraint list.
- Clear the Enforce constraint for replication check box.