Search code examples
sqlsql-serversql-server-2005constraintsentity-relationship

Turn off constraints temporarily (MS SQL)

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 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?

Solution

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

    ALTER TABLE foo NOCHECK CONSTRAINT ALL

    or

    ALTER TABLE foo 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.