SQL Server: Disable Table Constraints (all or some)

Constraints let you define a way to automatically enforce the integrity of a database. A table constraint is declared independently from a column and can be applied to more than one column in a table. Sometimes you may need to disable one or all table constraints, in order to import data, truncate tables etc.

You can use the following methods:

Consider these tables:

Suppose you want to import employee details to the table emp_details without having any corresponding data in emp_master:

INSERT INTO emp_details(emp_id) SELECT 34

This statement will throw an error as the value 34 in not available in the table emp_master

Msg 547, Level 16, State 0, Line 10
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__emp_detai__emp_i__1ED998B2". The conflict occurred in database "master", table "dbo.emp_master", column 'emp_id'. The statement has been terminated.

A Table constraint is in effect! Here to know the constraint name, run this code:

EXEC sp_help emp_details

and see the sixth resultset which shows the constraint name.



Using this name, you can now disable the constraint

ALTER TABLE emp_details
NOCHECK CONSTRAINT FK__emp_detai__emp_i__1ED998B2

Now if you re-run the code,

INSERT INTO emp_details(emp_id) SELECT 34

you will see that it gets executed successfully

To enable the constraint again, use the following code:

ALTER TABLE emp_details
CHECK CONSTRAINT FK__emp_detai__emp_i__1ED998B2

Disable All Constraints in a Table

To disable all constraints at a time, use the following code:

EXEC sp_msforeachtable 'alter table ? nocheck constraint all'

To enable all constraints at a time, use the following code

EXEC sp_msforeachtable 'alter table ? check constraint all'

Note that sp_msforeachtable is undocumented and alternatively you can use a cursor or a while loop to get the constraints and disable or enable them.

Some Points to be considered:

1. If you disable a constraint for some reasons, make sure to enable them later
2. Primary key constraint can not be disabled
3. Foreign key and Check constraint will be disabled using the nocheck command

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan