Turn off constraints temporarily (MS SQL)

sql sql-server sql-server-2005 constraints entity-relationship
Maciej picture Maciej · Apr 10, 2009 · Viewed 318.7k times · Source

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?

Answer

Donal picture Donal · Oct 24, 2014 
-- Disable the constraints on a table called tableName:
ALTER TABLE tableName NOCHECK CONSTRAINT ALL

-- Re-enable the constraints on a table called tableName:
ALTER TABLE tableName WITH CHECK CHECK CONSTRAINT ALL
---------------------------------------------------------

-- Disable constraints for all tables:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

-- Re-enable constraints for all tables:
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'
---------------------------------------------------------

Related questions

Can there be constraints with the same name in a DB?

This is a follow-on question from the one I asked here. Can constraints in a DB have the same name? Say I have: CREATE TABLE Employer ( EmployerCode VARCHAR(20) PRIMARY KEY, Address VARCHAR(100) NULL ) CREATE TABLE Employee ( EmployeeID INT PRIMARY KEY, …

Read More
Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More