Archive for February 20th, 2008

At times, there is a need to disable/enable the Foreign keys and the check constraints in the development/qa environment. The need arises from the requirement to have bulk data copy movement done from one environment to the other. In order to move the data fast between databases which have identical schema and object definitions, it is fast to disable the constraints, truncate/delete the data out and bulk insert the data from the source to the destination. This can be done by making use of T-SQL scripts or DTS/SSIS packages. It should not be done in a production environment though.

Also, please note that only the foreign keys and check constraints are disabled/enabled using these scripts. If you are looking for disabling of the indexes that are used to enforce your uniqueness/PK constraints, read the blog post here. Again, this should be done only in the load testing/development/QA environment and only for the sake of faster bulk copy data and other admin tasks.

The attached script has the script for disabling all the constraints in the schema. This script will work in both SQL Server 2000 as well as SQL Server 2005. In order to change it for enabling the constraints, just change “NOCHECK” with “CHECK” in the ALTER command and run it.

Also, please note that enabling the constraints this way only enables the constraints for future data integrity violations. If bad data has been put into the system between the disabling and enabling of the FK constraints, you can check those by running the DBCC CHECKCONSTRAINTS command. Here is an example:

We created a parent table t1 and a child table t2 above and put the FK in place. Now, using the above script, we disable the FK and check and default constraints. After that, we insert this data record into it:

insert into t2 values (1, 2)

And you will see that it will go through. After that, change the above script by replacing “NOCHECK” with “CHECK” and re-run the script to enable the constraints. Once the script completes, you will notice that it does not report back to you that there was a data record that violated the constraint. You can now run “DBCC CHECKCONSTRAINTS” to find out the data records that violate the constraints:

This is the output of that execution and as you can see, it will show you the table, the name of the constraint that was violated as well as the where condition that shows the filter criteria to help identify the data record in question.

And in order to look at the FK constraints and their status, you can use this simple script: