Turn off constraints temporarily (MS SQL)
SqlSql ServerSql Server-2005ConstraintsEntity RelationshipSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
-- 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 in the database:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
-- Re-enable constraints for all tables in the database:
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
---------------------------------------------------------
Solution 2 - Sql
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.
Solution 3 - Sql
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 CHECK CONSTRAINT ALL
or
ALTER TABLE foo 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.
Solution 4 - Sql
You can actually disable all database constraints in a single SQL command and the re-enable them calling another single command. See:
I am currently working with SQL Server 2005 but I am almost sure that this approach worked with SQL 2000 as well
Solution 5 - Sql
Disabling and Enabling All Foreign Keys
CREATE PROCEDURE pr_Disable_Triggers_v2
@disable BIT = 1
AS
DECLARE @sql VARCHAR(500)
, @tableName VARCHAR(128)
, @tableSchema VARCHAR(128)
-- List of all tables
DECLARE triggerCursor CURSOR FOR
SELECT t.TABLE_NAME AS TableName
, t.TABLE_SCHEMA AS TableSchema
FROM INFORMATION_SCHEMA.TABLES t
ORDER BY t.TABLE_NAME, t.TABLE_SCHEMA
OPEN triggerCursor
FETCH NEXT FROM triggerCursor INTO @tableName, @tableSchema
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @sql = 'ALTER TABLE ' + @tableSchema + '.[' + @tableName + '] '
IF @disable = 1
SET @sql = @sql + ' DISABLE TRIGGER ALL'
ELSE
SET @sql = @sql + ' ENABLE TRIGGER ALL'
PRINT 'Executing Statement - ' + @sql
EXECUTE ( @sql )
FETCH NEXT FROM triggerCursor INTO @tableName, @tableSchema
END
CLOSE triggerCursor
DEALLOCATE triggerCursor
>
>
> First, the foreignKeyCursor cursor is declared as the SELECT statement
> that gathers the list of foreign keys and their table names. Next, the
> cursor is opened and the initial FETCH statement is executed. This
> FETCH statement will read the first row's data into the local
> variables @foreignKeyName and @tableName. When looping through a
> cursor, you can check the @@FETCH_STATUS for a value of 0, which
> indicates that the fetch was successful. This means the loop will
> continue to move forward so it can get each successive foreign key
> from the rowset. @@FETCH_STATUS is available to all cursors on the
> connection. So if you are looping through multiple cursors, it is
> important to check the value of @@FETCH_STATUS in the statement
> immediately following the FETCH statement. @@FETCH_STATUS will reflect
> the status for the most recent FETCH operation on the connection.
> Valid values for @@FETCH_STATUS are:
>
> 0 = FETCH was successful
> -1 = FETCH was unsuccessful
> -2 = the row that was fetched is missing
>
> Inside the loop, the code builds the ALTER TABLE command differently
> depending on whether the intention is to disable or enable the foreign
> key constraint (using the CHECK or NOCHECK keyword). The statement is
> then printed as a message so its progress can be observed and then the
> statement is executed. Finally, when all rows have been iterated
> through, the stored procedure closes and deallocates the cursor.