Friday, April 10, 2009

Disable constraints

I've looked the way to temporarily turn off all DB's constraints (eg table relationships)

I needed to copy (using INSERTs) one DBs tables to another DB I know I can archive that by executing commands in proper order (to do not brake relationships)

But it would be easier if I could turn off checking constraints temporarily and turn on it back after operation finish.

So, you can disable FK and CHECK constraints only in SQL 2005+ using below command

ALTER TABLE foo WITH NOCHECK CONSTRAINT ALL

or

ALTER TABLE foo WITH NOCHECK CONSTRAINT CK_foo_column

Note: Primary keys and unique constraints can not be disabled.

Labels: