Say you are copying data to one table from another, and you need to work around a particular FOREIGN KEY constraint. Or, maybe you you need to work around ALL FK constraints on that table. We'll often need to work around FK constraints in situations like these. This post is just a quick how-to, for a single table constraint, or all constraints for a table.
For all constraints on a table, replace 'TableName' with your table name:
-- Disable all table
constraints, whole database
ALTER TABLE TableName NOCHECK CONSTRAINT ALL
------ do your table insert here
-- Enable all table
constraints, whole database
ALTER TABLE TableName CHECK CONSTRAINT ALL
For a single constraint on a table, replace 'TableName' and 'ConstraintName' with your specific object names:
-- Disable single constraint,
single table
ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName
----- do your table insert
here
-- Enable single constraint,
single table
ALTER TABLE TableName CHECK CONSTRAINT ConstraintName
There you have it.
Actually you when re-enabling the check constraint you will need one of my favourite TSQL syntaxes - WITH CHECK CHECK CONSTRAINT - if you want to mark the constraint as trusted so the optimizer can use it.
ReplyDeletesee
https://dba.stackexchange.com/questions/167861/what-is-a-with-check-check-constraint
This is really nice post, I love this content also visit maison chic. Thanks for sharing.
ReplyDelete