In this post, I demonstrate 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 require to enable again, but during this exercise make sure that all your data changes are correct and as per the constraint rule otherwise you cannot enable those constraints.
You can perform disable / enable action only on Foreign Key and Check Constraint.
You cannot perform disable / enable on Primary Key and Unique Key.
Disable All Foreign key and Check Constraint:
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"
Enable All Foreign key and Check Constraint:
EXEC sp_msforeachtable @command1="print '?'",
@command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"
Disable all table constraints:
ALTER TABLE table_name NOCHECK CONSTRAINT ALL
Enable all table constraints:
ALTER TABLE table_name CHECK CONSTRAINT ALL
Disable single constraint:
ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name
Enable single constraint:
ALTER TABLE table_name CHECK CONSTRAINT constraint_name
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.