Turn off constraints temporarily

Question

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

I need 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 break relationships)

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

Is this possible?

Answer 1

You can disable FK and CHECK constraints only in SQL 2005+. See ALTER TABLE

ALTER TABLE foo WITH NOCHECK CONSTRAINT ALL

or

ALTER TABLE foo WITH NOCHECK CONSTRAINT CK_foo_column

Primary keys and unique constraints can not be disabled, but this should be OK if I've understood you correctly.

Answer 2

And, if you want to verify that you HAVEN'T broken your relationships and introduced orphans, once you have re-armed your checks, i.e.

ALTER TABLE foo WITH CHECK CONSTRAINT ALL

or

ALTER TABLE foo WITH CHECK CONSTRAINT FK_something

then you can run back in and do an update against any checked columns like so:

UPDATE myUpdatedTable SET someCol = someCol, fkCol = fkCol, etc = etc

And any errors at that point will be due to failure to meet constraints.

Answer 3

My concern about doing this is that this turns off the constraints for everyone not just you. If you must do this, put the database in single user mode first. Otherwise you can end up data integrity problems.

Answer 4

You can actually disable all database constraints in a single SQL command and the re-enable them calling another single command. See:

• Can foreign key constraints be temporarily disabled using TSQL?

I am currently working with SQL Server 2005 but I am almost sure that this approach worked with SQL 2000 as well

Answer 5

see Disabling Constraints and Triggers from MSDN Magazine