This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing the script to enable and disable foreign key and check constraint in SQL Server.
During data migration and testing purpose, Database Developer requires to disable Foreign key constraint or Check constraint.
Once you disable constraint, then later you might need enabling again, but during this exercise make sure that all your data changes are correct, and as per the constraint rule otherwise, you should not enable those constraints.
You can disable/enable action only on Foreign Key and Check Constraint.
You cannot perform disable/enable on Primary Key and Unique Key.
Disable All Foreign keys and Check Constraints:
|
1 |
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL" |
Enable All Foreign keys and Check Constraints:
|
1 2 |
EXEC sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL" |
Disable all the table constraints:
|
1 |
ALTER TABLE table_name NOCHECK CONSTRAINT ALL |
Enable all the table constraints:
|
1 |
ALTER TABLE table_name CHECK CONSTRAINT ALL |
Disable a single constraint:
|
1 |
ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name |
Enable a single constraint:
|
1 |
ALTER TABLE table_name CHECK CONSTRAINT constraint_name |
