Constraints and how to disable or drop all the constraints on a specific table?

Sometimes constrains in the table can get in the way, like for instance when one needs to load initial values into a database one table at a time, without worrying with foreign key constraints and checks until all of the tables have finished loading. In that case one might want to delete or better, disable, all the constraints before performing the task, and then re-enabling them.

Although there is one catch with disabling constraints - you can only disable FOREIGN KEY constraint and the CHECK constraint. PRIMARY KEY, UNIQUE, and DEFAULT constraints are always active.


Below are two examples of how to delete and disable all the contstraints on a specific table respectively. I used Northwind database as an example.

There are numbers of ways to delete all the constraints, including deleting them from the system tables, but it's better to create a script that will get all the constraints from INFORMATION_SCHEMA view and then dynamically delete them, rather then deleting them directly from the system tables.

DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

SET @database = 'Northwind'
SET @table = 'Products'

DECLARE @sql nvarchar(255)
WHILE EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog = @database AND table_name = @table)
BEGIN
    SELECT @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE constraint_catalog = @database and table_name AND @table
    EXEC sp_executesql @sql
END

The above will delete all the constraints for Products table in the Northwind database.

And here's how one can disable all the constraints on a particular table:

DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

SET @database = 'Northwind'
SET @table = 'Products'

DECLARE @sql nvarchar(255)
WHILE EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog = @database AND table_name = @table)
BEGIN
    SELECT @sql = 'ALTER TABLE ' + @table + ' NOCHECK CONSTRAINT ' + CONSTRAINT_NAME 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE constraint_catalog = @database AND table_name = @table
    EXEC sp_executesql @sql
END

For disabling all constraints at once there is a simpler way without going through constraints one by one:

DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

SET @database = 'Northwind'
SET @table = 'Products'

DECLARE @sql nvarchar(255)

SELECT @sql = 'ALTER TABLE ' + @table + ' NOCHECK CONSTRAINT ALL'
EXEC sp_executesql @sql