Disable/Enable Foreign Key and Check constraints in SQL Server

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.

disable_fk_constraints.txt

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:

create table t1 (col1 int primary key, col2 int)
create table t2 (col3 int primary key, col4 int, constraint fk_t2_t1 foreign key (col4) references t1 (col1))

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:

Table Constraint Where
———– ———– ————-
[dbo].[t2] [fk_t2_t1] [col4] = ‘2’
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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:

SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, ‘CNSTISDISABLED’) = 0 THEN ‘ENABLED’
ELSE ‘DISABLED’
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO