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?
You can disable FK and CHECK constraints
only in SQL 2005+. See ALTER TABLE
ALTER TABLE foo NOCHECK CONSTRAINT ALL
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.
-- 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' ---------------------------------------------------------
Read more... Read less...
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
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.
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
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.