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

User · Answer

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

User · Answer

You can actually disable all database constraints in a single SQL command and the re-enable them calling another single command  See    Can foreign key constraints be temporarily disabled using TSQL    I am currently working with SQL Server 2005 but I am almost sure that this approach worked with SQL 2000 as well

User · Answer

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

User · Answer

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    see Disabling Constraints and Triggers from MSDN Magazine

User · Answer

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

[sql] Turn off constraints temporarily (MS SQL)

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to constraints

Examples related to entity-relationship