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.
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
disable_fk_constraints.txt
/************************** DISABLE ALL TABLE CONSTRAINTS **********************************
This script will disable all constraints on all tables within the database
that it is run in.
********************************************************************************************/
SET NOCOUNT ON
SET ROWCOUNT 0
DECLARE @Count int
DECLARE @String nvarchar (1000)
DECLARE @ConstraintName varchar(128)
DECLARE @TableName varchar(128)
--Find all constraints and their respective tables from the sysobjects table and place into a temp table.
--Primary Key and Unique Constraints via Unique Indexes are not disabled through this command
--You should use the ALTER INDEX...DISABLE command in SQL Server 2005
SELECT
name AS constraintname,
object_name(parent_obj) AS tablename
INTO #Const_Table
FROM sysobjects s
where xtype in ('F')
SELECT @Count = Count(*) FROM #Const_Table
--Setting the rowcount to one allows for one row from the temp table to be picked off at a time.
--Used as an alternative to a cursor.
SET ROWCOUNT 1
--Loop until all rows in temp table have been processed.
WHILE @Count > 0
BEGIN
--The rowcount of one ensures that only one tablename and constraint name is picked.
SELECT @TableName = TableName, @ConstraintName = ConstraintName
FROM #Const_Table
--Build execution string to disable constraint.
SET @String = 'ALTER TABLE ['+ @tablename + '] NOCHECK CONSTRAINT [' + @constraintname +']'
--Execute the SQL
exec sp_executesql @string
--Remove this row from the temp table, since it has now been processed.
DELETE FROM #Const_Table WHERE ConstraintName = @ConstraintName and TableName = @TableName
SET @Count = @Count - 1
END -- Loop
set rowcount 0
Revision 2 (by another user – in 2010)
/************************** DISABLE ALL TABLE CONSTRAINTS **********************************
This script will enable or disable all constraints on all tables within the database
that it is run in.
********************************************************************************************/
SET NOCOUNT ON
SET ROWCOUNT 0
DECLARE @Count int
DECLARE @String nvarchar (1000)
DECLARE @ConstraintName varchar(128)
DECLARE @TableName varchar(128)
DECLARE @SchemaName varchar(128)
--Find all constraints and their respective tables from the sysobjects table and place into a temp table.
--Primary Key and Unique Constraints via Unique Indexes are not disabled through this command
--You should use the ALTER INDEX...DISABLE command in SQL Server 2005
SELECT
o.name AS ConstraintName,
sc.name AS SchemaName,
object_name(o.parent_obj) AS TableName
INTO #Const_Table
FROM sysobjects o, sys.schemas sc
WHERE xtype in ('F') AND o.uid = sc.schema_id
SELECT @Count = Count(*) FROM #Const_Table
--Setting the rowcount to one allows for one row from the temp table to be picked off at a time.
--Used as an alternative to a cursor.
SET ROWCOUNT 1
--Loop until all rows in temp table have been processed.
WHILE @Count > 0
BEGIN
--The rowcount of one ensures that only one tablename and constraint name is picked.
SELECT @TableName = TableName, @SchemaName = SchemaName, @ConstraintName = ConstraintName
FROM #Const_Table
--Comment or uncomment the following statements according to your needs
--Build execution string to enable constraint.
SET @String = 'ALTER TABLE ['+ @SchemaName + '].['+ @TableName + '] WITH CHECK CHECK CONSTRAINT [' + @ConstraintName +']'
--Build execution string to disable constraint.
--SET @String = 'ALTER TABLE ['+ @SchemaName + '].['+ @TableName + '] NOCHECK CONSTRAINT [' + @ConstraintName +']'
--Execute the SQL
EXEC sp_executesql @string
--Remove this row from the temp table, since it has now been processed.
DELETE FROM #Const_Table
WHERE ConstraintName = @ConstraintName AND TableName = @TableName AND SchemaName = @SchemaName
SET @Count = @Count - 1
END -- Loop
DROP TABLE #Const_Table
SET ROWCOUNT 0
