Turn off constraints temporarily (MS SQL)

Question

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

I need to copy (using INSERTs) one DB's tables to another DB. I know I can achieve that by executing commands in the proper order (to not break relationships).

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

Is this possible?

Answer 1

You can try disabling FK and CHECK constraints by using ALTER TABLE

ALTER TABLE foo NOCHECK CONSTRAINT ALL

Or else you can use the below code:

ALTER TABLE foo NOCHECK CONSTRAINT CK_foo_column

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

For more, information you can refer to ALTER TABLE.