How can foreign key constraints be temporarily disabled using T-SQL

Question

Are disabling and enabling foreign key constraints supported in SQL Server  Or is my only option to drop and then re-create the constraints

User · Answer

http   www sqljunkies com WebLog roman archive 2005 01 30 7037 aspx  -- Disable all table constraints  ALTER TABLE MyTable NOCHECK CONSTRAINT ALL  -- Enable all table constraints  ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT ALL  -- Disable single constraint  ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint  -- Enable single constraint  ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint

User · Answer

Find the constraint  SELECT    FROM sys foreign keys WHERE referenced object id   object id  TABLE NAME     Execute the SQL generated by this SQL  SELECT       ALTER TABLE      OBJECT SCHEMA NAME parent object id               OBJECT NAME parent object id            DROP CONSTRAINT     name FROM sys foreign keys WHERE referenced object id   object id  TABLE NAME     Safeway   Note  Added solution for droping the constraint so that table can be dropped or modified without any constraint error

User · Answer

SET NOCOUNT ON  DECLARE  table TABLE     RowId INT PRIMARY KEY IDENTITY 1  1      ForeignKeyConstraintName NVARCHAR 200      ForeignKeyConstraintTableSchema NVARCHAR 200      ForeignKeyConstraintTableName NVARCHAR 200      ForeignKeyConstraintColumnName NVARCHAR 200      PrimaryKeyConstraintName NVARCHAR 200      PrimaryKeyConstraintTableSchema NVARCHAR 200      PrimaryKeyConstraintTableName NVARCHAR 200      PrimaryKeyConstraintColumnName NVARCHAR 200      UpdateRule NVARCHAR 100      DeleteRule NVARCHAR 100        INSERT INTO  table ForeignKeyConstraintName  ForeignKeyConstraintTableSchema  ForeignKeyConstraintTableName  ForeignKeyConstraintColumnName  SELECT     U CONSTRAINT NAME      U TABLE SCHEMA      U TABLE NAME      U COLUMN NAME FROM     INFORMATION SCHEMA KEY COLUMN USAGE U       INNER JOIN INFORMATION SCHEMA TABLE CONSTRAINTS C          ON U CONSTRAINT NAME   C CONSTRAINT NAME WHERE    C CONSTRAINT TYPE    FOREIGN KEY   UPDATE  table SET    T PrimaryKeyConstraintName   R UNIQUE CONSTRAINT NAME     T UpdateRule   R UPDATE RULE     T DeleteRule   R DELETE RULE FROM      table T       INNER JOIN INFORMATION SCHEMA REFERENTIAL CONSTRAINTS R          ON T ForeignKeyConstraintName   R CONSTRAINT NAME  UPDATE  table SET    PrimaryKeyConstraintTableSchema    TABLE SCHEMA     PrimaryKeyConstraintTableName    TABLE NAME FROM  table T    INNER JOIN INFORMATION SCHEMA TABLE CONSTRAINTS C       ON T PrimaryKeyConstraintName   C CONSTRAINT NAME  UPDATE  table SET    PrimaryKeyConstraintColumnName   COLUMN NAME FROM  table T    INNER JOIN INFORMATION SCHEMA KEY COLUMN USAGE U       ON T PrimaryKeyConstraintName   U CONSTRAINT NAME  --SELECT   FROM  table  SELECT   BEGIN TRANSACTION BEGIN TRY   --DROP CONSTRAINT  SELECT       ALTER TABLE      ForeignKeyConstraintTableSchema           ForeignKeyConstraintTableName        DROP CONSTRAINT     ForeignKeyConstraintName          FROM     table  SELECT   END TRY  BEGIN CATCH    ROLLBACK TRANSACTION    RAISERROR   Operation failed     16  1  END CATCH  IF   TRANCOUNT    0  BEGIN    COMMIT TRANSACTION    RAISERROR   Operation completed successfully     10  1  END    --ADD CONSTRAINT  SELECT   BEGIN TRANSACTION BEGIN TRY   SELECT         ALTER TABLE      ForeignKeyConstraintTableSchema           ForeignKeyConstraintTableName          ADD CONSTRAINT     ForeignKeyConstraintName     FOREIGN KEY     ForeignKeyConstraintColumnName      REFERENCES      PrimaryKeyConstraintTableSchema           PrimaryKeyConstraintTableName          PrimaryKeyConstraintColumnName      ON UPDATE     UpdateRule     ON DELETE     DeleteRule          FROM     table  SELECT   END TRY  BEGIN CATCH    ROLLBACK TRANSACTION    RAISERROR   Operation failed     16  1  END CATCH  IF   TRANCOUNT    0  BEGIN    COMMIT TRANSACTION    RAISERROR   Operation completed successfully     10  1  END   GO

User · Answer

Answer marked  905  looks good but does not work   Following worked for me  Any Primary Key  Unique Key  or Default constraints CAN NOT be disabled  In fact  if  sp helpconstraint    shows  n a  in status enabled - Means it can NOT be enabled disabled   -- To generate script to DISABLE  select  ALTER TABLE     object name id      NOCHECK CONSTRAINT      object name constid        from sys sysconstraints  where status  amp  0x4813   0x813 order by object name id    -- To generate script to ENABLE  select  ALTER TABLE     object name id      CHECK CONSTRAINT      object name constid        from sys sysconstraints  where status  amp  0x4813   0x813 order by object name id

User · Answer

To disable the constraint you have ALTER the table using NOCHECK  ALTER TABLE  TABLE NAME  NOCHECK CONSTRAINT  ALL CONSTRAINT NAME    To enable you to have to use double CHECK   ALTER TABLE  TABLE NAME  WITH CHECK CHECK CONSTRAINT  ALL CONSTRAINT NAME     Pay attention to the double CHECK CHECK when enabling    ALL means for all constraints in the table    Once completed  if you need to check the status  use this script to list the constraint status  Will be very helpfull       SELECT  CASE          WHEN OBJECTPROPERTY CONSTID   CNSTISDISABLED     0 THEN  ENABLED          ELSE  DISABLED          END  AS STATUS          OBJECT NAME CONSTID  AS CONSTRAINT NAME          OBJECT NAME FKEYID  AS TABLE NAME          COL NAME FKEYID  FKEY  AS COLUMN NAME          OBJECT NAME RKEYID  AS REFERENCED TABLE NAME          COL NAME RKEYID  RKEY  AS REFERENCED COLUMN NAME    FROM SYSFOREIGNKEYS ORDER BY TABLE NAME  CONSTRAINT NAME REFERENCED TABLE NAME  KEYNO

User · Answer

I have a more useful version if you are interested  I lifted a bit of code from here a website where the link is no longer active  I modifyied it to allow for an array of tables into the stored procedure and it populates the drop  truncate  add statements before executing all of them  This gives you control to decide which tables need truncating           Object   UserDefinedTableType  util   typ objects for managing     Script Date  03 04 2016 16 42 55         CREATE TYPE  util   typ objects for managing  AS TABLE       schema   sysname  NOT NULL       object   sysname  NOT NULL   GO  create procedure  util   truncate table with constraints   objects for managing util typ objects for managing readonly  -- schema sysname --  table sysname  as  --select --     table    TABLE   --     schema    SCHEMA   declare  exec table as table  ordinal int identity  1 1   statement nvarchar 4000   primary key  ordinal     --print    Drop Foreign Key Statements for     schema        table        insert into  exec table  statement  select            ALTER TABLE    SCHEMA NAME o schema id         o name    DROP CONSTRAINT    fk name     from sys foreign keys fk inner join sys objects o           on fk parent object id   o object id where  exists    select   from  objects for managing chk  where  chk  schema    SCHEMA NAME o schema id    and  chk  object    o name                --o name    table and           --SCHEMA NAME o schema id      schema  insert into  exec table  statement   select  TRUNCATE TABLE     src  schema          src  object   from  objects for managing src     --print    Create Foreign Key Statements for     schema        table       insert into  exec table  statement  select  ALTER TABLE    SCHEMA NAME o schema id        o name    ADD CONSTRAINT    fk name    FOREIGN KEY     c name      REFERENCES    SCHEMA NAME refob schema id        refob name       refcol name      from sys foreign key columns fkc inner join sys foreign keys fk           on fkc constraint object id   fk object id inner join sys objects o           on fk parent object id   o object id inner join sys columns c           on      fkc parent column id   c column id and                    o object id   c object id inner join sys objects refob           on fkc referenced object id   refob object id inner join sys columns refcol           on fkc referenced column id   refcol column id and                    fkc referenced object id   refcol object id where  exists    select   from  objects for managing chk  where  chk  schema    SCHEMA NAME o schema id    and  chk  object    o name                 --o name    table and           --SCHEMA NAME o schema id      schema    declare  looper int    total records int   sql exec nvarchar 4000   select  looper   1   total records   count    from  exec table    while  looper  lt    total records  begin  select  sql exec    select statement from  exec table where ordinal   looper  exec sp executesql  sql exec  print  sql exec  set  looper    looper   1 end

User · Answer

If you want to disable all constraints in the database just run this code   -- disable all constraints EXEC sp MSforeachtable  ALTER TABLE   NOCHECK CONSTRAINT all    To switch them back on  run   the print is optional of course and it is just listing the tables   -- enable all constraints exec sp MSforeachtable  command1  print        command2  ALTER TABLE   WITH CHECK CHECK CONSTRAINT all    I find it useful when populating data from one database to another  It is much better approach than dropping constraints  As you mentioned it comes handy when dropping all the data in the database and repopulating it  say in test environment     If you are deleting all the data you may find this solution to be helpful   Also sometimes it is handy to disable all triggers as well  you can see the complete solution here

User · Answer

--Drop and Recreate Foreign Key Constraints  SET NOCOUNT ON  DECLARE  table TABLE     RowId INT PRIMARY KEY IDENTITY 1  1      ForeignKeyConstraintName NVARCHAR 200      ForeignKeyConstraintTableSchema NVARCHAR 200      ForeignKeyConstraintTableName NVARCHAR 200      ForeignKeyConstraintColumnName NVARCHAR 200      PrimaryKeyConstraintName NVARCHAR 200      PrimaryKeyConstraintTableSchema NVARCHAR 200      PrimaryKeyConstraintTableName NVARCHAR 200      PrimaryKeyConstraintColumnName NVARCHAR 200         INSERT INTO  table ForeignKeyConstraintName  ForeignKeyConstraintTableSchema  ForeignKeyConstraintTableName  ForeignKeyConstraintColumnName  SELECT     U CONSTRAINT NAME      U TABLE SCHEMA      U TABLE NAME      U COLUMN NAME  FROM     INFORMATION SCHEMA KEY COLUMN USAGE U       INNER JOIN INFORMATION SCHEMA TABLE CONSTRAINTS C          ON U CONSTRAINT NAME   C CONSTRAINT NAME WHERE    C CONSTRAINT TYPE    FOREIGN KEY   UPDATE  table SET    PrimaryKeyConstraintName   UNIQUE CONSTRAINT NAME FROM      table T       INNER JOIN INFORMATION SCHEMA REFERENTIAL CONSTRAINTS R          ON T ForeignKeyConstraintName   R CONSTRAINT NAME  UPDATE  table SET    PrimaryKeyConstraintTableSchema    TABLE SCHEMA     PrimaryKeyConstraintTableName    TABLE NAME FROM  table T    INNER JOIN INFORMATION SCHEMA TABLE CONSTRAINTS C       ON T PrimaryKeyConstraintName   C CONSTRAINT NAME  UPDATE  table SET    PrimaryKeyConstraintColumnName   COLUMN NAME FROM  table T    INNER JOIN INFORMATION SCHEMA KEY COLUMN USAGE U       ON T PrimaryKeyConstraintName   U CONSTRAINT NAME  --SELECT   FROM  table  --DROP CONSTRAINT  SELECT         ALTER TABLE      ForeignKeyConstraintTableSchema           ForeignKeyConstraintTableName          DROP CONSTRAINT     ForeignKeyConstraintName         GO  FROM     table  --ADD CONSTRAINT  SELECT         ALTER TABLE      ForeignKeyConstraintTableSchema           ForeignKeyConstraintTableName          ADD CONSTRAINT     ForeignKeyConstraintName     FOREIGN KEY     ForeignKeyConstraintColumnName      REFERENCES      PrimaryKeyConstraintTableSchema           PrimaryKeyConstraintTableName          PrimaryKeyConstraintColumnName          GO  FROM     table  GO     I do agree with you  Hamlin  When you are transfer data using SSIS or when want to replicate data  it seems quite necessary to temporarily disable or drop foreign key constraints and then re-enable or recreate them  In these cases  referential integrity is not an issue  because it is already maintained in the source database  Therefore  you can rest assured regarding this matter

User · Answer

Your best option is to DROP and CREATE foreign key constraints   I didn t find examples in this post that would work for me  as-is   one would not work if foreign keys reference different schemas  the other would not work if foreign key references multiple columns  This script considers both  multiple schemas and multiple columns per foreign key   Here is the script that generates  ADD CONSTRAINT  statements  for multiple columns it will separate them by comma  be sure to save this output before executing DROP statements    PRINT N -- CREATE FOREIGN KEY CONSTRAINTS --    SET NOCOUNT ON  SELECT   PRINT N  Creating    const const name         GO ALTER TABLE     const parent obj         ADD CONSTRAINT     const const name     FOREIGN KEY                   const parent col csv                   REFERENCES     const ref obj         const ref col csv      GO  FROM       SELECT QUOTENAME fk NAME  AS  const name           QUOTENAME schParent NAME          QUOTENAME OBJECT name fkc parent object id   AS  parent obj           STUFF                   SELECT       QUOTENAME COL NAME fcP parent object id  fcp parent column id                   FROM sys foreign key columns AS fcP                 WHERE fcp constraint object id   fk object id                 FOR XML path                        1  1      AS  parent col csv           QUOTENAME schRef NAME          QUOTENAME OBJECT NAME fkc referenced object id   AS  ref obj           STUFF                   SELECT       QUOTENAME COL NAME fcR referenced object id  fcR referenced column id                   FROM sys foreign key columns AS fcR                 WHERE fcR constraint object id   fk object id                 FOR XML path                        1  1      AS  ref col csv      FROM sys foreign key columns AS fkc     INNER JOIN sys foreign keys AS fk ON fk object id   fkc constraint object id     INNER JOIN sys objects AS oParent ON oParent object id   fkc parent object id     INNER JOIN sys schemas AS schParent ON schParent schema id   oParent schema id     INNER JOIN sys objects AS oRef ON oRef object id   fkc referenced object id     INNER JOIN sys schemas AS schRef ON schRef schema id   oRef schema id     GROUP BY fkc parent object id          fkc referenced object id          fk NAME          fk object id          schParent NAME          schRef NAME       AS const ORDER BY const const name   Here is the script that generates  DROP CONSTRAINT  statements   PRINT N -- DROP FOREIGN KEY CONSTRAINTS --    SET NOCOUNT ON   SELECT   PRINT N  Dropping     fk NAME          GO ALTER TABLE      sch NAME           OBJECT NAME fk parent object id            DROP  CONSTRAINT           fk NAME      GO  FROM sys foreign keys AS fk INNER JOIN sys schemas AS sch ON sch schema id   fk schema id ORDER BY fk NAME

User · Answer

The SQL-92 standard allows for a constaint to be declared as DEFERRABLE so that it can be deferred  implicitly or explicitly  within the scope of a transaction  Sadly  SQL Server is still missing this SQL-92 functionality   For me  changing a constraint to NOCHECK is akin to changing the database structure on the fly -- dropping constraints certainly is -- and something to be avoided  e g  users require increased privileges

User · Answer

First post     For the OP  kristof s solution will work  unless there are issues with massive data and transaction log balloon issues on big deletes   Also  even with tlog storage to spare  since deletes write to the tlog  the operation can take a VERY long time for tables with hundreds of millions of rows   I use a series of cursors to truncate and reload large copies of one of our huge production databases frequently   The solution engineered accounts for multiple schemas  multiple foreign key columns  and best of all can be sproc d out for use in SSIS   It involves creation of three staging tables  real tables  to house the DROP  CREATE  and CHECK FK scripts  creation and insertion of those scripts into the tables  and then looping over the tables and executing them   The attached script is four parts  1   creation and storage of the scripts in the three staging  real  tables  2   execution of the drop FK scripts via a cursor one by one  3   Using sp MSforeachtable to truncate all the tables in the database other than our three staging tables and 4   execution of the create FK and check FK scripts at the end of your ETL SSIS package     Run the script creation portion in an Execute SQL task in SSIS   Run the  execute Drop FK Scripts  portion in a second Execute SQL task  Put the truncation script in a third Execute SQL task  then perform whatever other ETL processes you need to do prior to attaching the CREATE and CHECK scripts in a final Execute SQL task  or two if desired  at the end of your control flow   Storage of the scripts in real tables has proven invaluable when the re-application of the foreign keys fails as you can select   from sync CreateFK  copy paste into your query window  run them one at a time  and fix the data issues once you find ones that failed are still failing to re-apply     Do not re-run the script again if it fails without making sure that you re-apply all of the foreign keys checks prior to doing so  or you will most likely lose some creation and check fk scripting as our staging tables are dropped and recreated prior to the creation of the scripts to execute   ---------------------------------------------------------------------------- 1     Author          Denmach DateCreated     2014-04-23 Purpose         Generates SQL statements to DROP  ADD  and CHECK existing constraints for a                  database   Stores scripts in tables on target database for execution   Executes                 those stored scripts via independent cursors   DateModified  ModifiedBy Comments        This will eliminate deletes and the T-log ballooning associated with it      DECLARE  schema name SYSNAME   DECLARE  table name SYSNAME   DECLARE  constraint name SYSNAME   DECLARE  constraint object id INT   DECLARE  referenced object name SYSNAME   DECLARE  is disabled BIT   DECLARE  is not for replication BIT   DECLARE  is not trusted BIT   DECLARE  delete referential action TINYINT   DECLARE  update referential action TINYINT   DECLARE  tsql NVARCHAR 4000    DECLARE  tsql2 NVARCHAR 4000    DECLARE  fkCol SYSNAME   DECLARE  pkCol SYSNAME   DECLARE  col1 BIT   DECLARE  action CHAR 6     DECLARE  referenced schema name SYSNAME     --------------------------------Generate scripts to drop all foreign keys in a database --------------------------------  IF OBJECT ID  dbo sync dropFK   IS NOT NULL DROP TABLE sync dropFK  CREATE TABLE sync dropFK           ID INT IDENTITY  1 1  NOT NULL       Script NVARCHAR 4000         DECLARE FKcursor CURSOR FOR      SELECT          OBJECT SCHEMA NAME parent object id            OBJECT NAME parent object id            name     FROM          sys foreign keys WITH  NOLOCK      ORDER BY          1 2   OPEN FKcursor   FETCH NEXT FROM FKcursor INTO       schema name        table name        constraint name  WHILE   FETCH STATUS   0  BEGIN         SET  tsql    ALTER TABLE                     QUOTENAME  schema name                                            QUOTENAME  table name                      DROP CONSTRAINT                      QUOTENAME  constraint name                              --PRINT  tsql      INSERT sync dropFK                            Script                                                   VALUES                                    tsql                                           FETCH NEXT FROM FKcursor INTO       schema name        table name        constraint name        END   CLOSE FKcursor   DEALLOCATE FKcursor    ---------------Generate scripts to create all existing foreign keys in a database -------------------------------- ---------------------------------------------------------------------------------------------------------- IF OBJECT ID  dbo sync createFK   IS NOT NULL DROP TABLE sync createFK  CREATE TABLE sync createFK           ID INT IDENTITY  1 1  NOT NULL       Script NVARCHAR 4000         IF OBJECT ID  dbo sync createCHECK   IS NOT NULL DROP TABLE sync createCHECK  CREATE TABLE sync createCHECK           ID INT IDENTITY  1 1  NOT NULL       Script NVARCHAR 4000            DECLARE FKcursor CURSOR FOR       SELECT          OBJECT SCHEMA NAME parent object id            OBJECT NAME parent object id            name           OBJECT NAME referenced object id            OBJECT ID           is disabled           is not for replication           is not trusted           delete referential action           update referential action           OBJECT SCHEMA NAME referenced object id       FROM          sys foreign keys WITH  NOLOCK       ORDER BY          1 2   OPEN FKcursor   FETCH NEXT FROM FKcursor INTO       schema name        table name        constraint name        referenced object name        constraint object id        is disabled        is not for replication        is not trusted        delete referential action        update referential action        referenced schema name   WHILE   FETCH STATUS   0  BEGIN          BEGIN             SET  tsql    ALTER TABLE                             QUOTENAME  schema name                                                            QUOTENAME  table name                              CASE                                   is not trusted                                 WHEN 0 THEN   WITH CHECK                                   ELSE   WITH NOCHECK                               END                             ADD CONSTRAINT                              QUOTENAME  constraint name                              FOREIGN KEY              SET  tsql2                DECLARE ColumnCursor CURSOR FOR               SELECT                  COL NAME fk parent object id                   fkc parent column id                    COL NAME fk referenced object id                   fkc referenced column id               FROM                  sys foreign keys fk WITH  NOLOCK                  INNER JOIN sys foreign key columns fkc WITH  NOLOCK  ON fk  object id    fkc constraint object id              WHERE                  fkc constraint object id    constraint object id              ORDER BY                  fkc constraint column id           OPEN ColumnCursor           SET  col1   1           FETCH NEXT FROM ColumnCursor INTO  fkCol   pkCol           WHILE   FETCH STATUS   0          BEGIN             IF   col1   1                  SET  col1   0              ELSE             BEGIN                 SET  tsql    tsql                        SET  tsql2    tsql2                    END               SET  tsql    tsql   QUOTENAME  fkCol               SET  tsql2    tsql2   QUOTENAME  pkCol               --PRINT   tsql        tsql              --PRINT   tsql2        tsql2             FETCH NEXT FROM ColumnCursor INTO  fkCol   pkCol              --PRINT  FK Column      fkCol             --PRINT  PK Column      pkCol          END           CLOSE ColumnCursor          DEALLOCATE ColumnCursor           SET  tsql    tsql       REFERENCES                          QUOTENAME  referenced schema name                                                    QUOTENAME  referenced object name                                                     tsql2                 SET  tsql    tsql                         ON UPDATE                                                   CASE  update referential action                             WHEN 0 THEN  NO ACTION                               WHEN 1 THEN  CASCADE                               WHEN 2 THEN  SET NULL                                   ELSE  SET DEFAULT                           END                          ON DELETE                                                   CASE  delete referential action                             WHEN 0 THEN  NO ACTION                               WHEN 1 THEN  CASCADE                               WHEN 2 THEN  SET NULL                                   ELSE  SET DEFAULT                           END                                             CASE  is not for replication                         WHEN 1 THEN   NOT FOR REPLICATION                               ELSE                        END                                     END       --  PRINT  tsql         INSERT sync createFK                                                       Script                                                   VALUES                                    tsql                                    -------------------Generate CHECK CONSTRAINT scripts for a database ------------------------------ ----------------------------------------------------------------------------------------------------------          BEGIN          SET  tsql    ALTER TABLE                         QUOTENAME  schema name                                                    QUOTENAME  table name                                                 CASE  is disabled                             WHEN 0 THEN   CHECK                                   ELSE   NOCHECK                           END                        CONSTRAINT                          QUOTENAME  constraint name                                     --PRINT  tsql          INSERT sync createCHECK                                                    Script                                                   VALUES                                    tsql                                              END       FETCH NEXT FROM FKcursor INTO       schema name        table name        constraint name        referenced object name        constraint object id        is disabled        is not for replication        is not trusted        delete referential action        update referential action        referenced schema name   END   CLOSE FKcursor   DEALLOCATE FKcursor   --SELECT   FROM sync DropFK --SELECT   FROM sync CreateFK --SELECT   FROM sync CreateCHECK --------------------------------------------------------------------------- 2   ----------------------------------------------------------------------------------------------------------------- ----------------------------execute Drop FK Scripts --------------------------------------------------  DECLARE  scriptD NVARCHAR 4000   DECLARE DropFKCursor CURSOR FOR     SELECT Script      FROM sync dropFK WITH  NOLOCK   OPEN DropFKCursor  FETCH NEXT FROM DropFKCursor INTO  scriptD  WHILE   FETCH STATUS   0 BEGIN --PRINT  scriptD EXEC   scriptD  FETCH NEXT FROM DropFKCursor INTO  scriptD END CLOSE DropFKCursor DEALLOCATE DropFKCursor -------------------------------------------------------------------------------- 3     ------------------------------------------------------------------------------------------------------------------ ----------------------------Truncate all tables in the database other than our staging tables -------------------- ------------------------------------------------------------------------------------------------------------------   EXEC sp MSforeachtable  IF OBJECT ID        NOT IN    ISNULL OBJECT ID   dbo sync createCHECK    0   ISNULL OBJECT ID   dbo sync createFK    0   ISNULL OBJECT ID   dbo sync dropFK    0    BEGIN TRY  TRUNCATE TABLE   END TRY BEGIN CATCH  PRINT   Truncation failed on          END CATCH    GO ------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------- ----------------------------execute Create FK Scripts and CHECK CONSTRAINT Scripts--------------- ----------------------------tack me at the end of the ETL in a SQL task------------------------- ------------------------------------------------------------------------------------------------- DECLARE  scriptC NVARCHAR 4000   DECLARE CreateFKCursor CURSOR FOR     SELECT Script      FROM sync createFK WITH  NOLOCK   OPEN CreateFKCursor  FETCH NEXT FROM CreateFKCursor INTO  scriptC  WHILE   FETCH STATUS   0 BEGIN --PRINT  scriptC EXEC   scriptC  FETCH NEXT FROM CreateFKCursor INTO  scriptC END CLOSE CreateFKCursor DEALLOCATE CreateFKCursor ------------------------------------------------------------------------------------------------- DECLARE  scriptCh NVARCHAR 4000   DECLARE CreateCHECKCursor CURSOR FOR     SELECT Script      FROM sync createCHECK WITH  NOLOCK   OPEN CreateCHECKCursor  FETCH NEXT FROM CreateCHECKCursor INTO  scriptCh  WHILE   FETCH STATUS   0 BEGIN --PRINT  scriptCh EXEC   scriptCh  FETCH NEXT FROM CreateCHECKCursor INTO  scriptCh END CLOSE CreateCHECKCursor DEALLOCATE CreateCHECKCursor

User · Answer

Right click the table design and go to Relationships and choose the foreign key on the left-side pane and in the right-side pane  set Enforce foreign key constraint to  Yes   to enable foreign key constraints  or  No   to disable it

User · Answer

You should actually be able to disable foreign key constraints the same way you temporarily disable other constraints   Alter table MyTable nocheck constraint FK ForeignKeyConstraintName   Just make sure you re disabling the constraint on the first table listed in the constraint name  For example  if my foreign key constraint was FK LocationsEmployeesLocationIdEmployeeId  I would want to use the following   Alter table Locations nocheck constraint FK LocationsEmployeesLocationIdEmployeeId   even though violating this constraint will produce an error that doesn t necessarily state that table as the source of the conflict

User · Answer

WITH CHECK CHECK is almost certainly required   This point was raised in some of the answers and comments but I feel that it is important enough to call it out again   Re-enabling a constraint using the following command  no WITH CHECK  will have some serious drawbacks   ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint       WITH CHECK   WITH NOCHECK       Specifies whether the data in the table is or is not validated against   a newly added or re-enabled FOREIGN KEY or CHECK constraint  If not   specified  WITH CHECK is assumed for new constraints  and WITH NOCHECK   is assumed for re-enabled constraints       If you do not want to verify new CHECK or FOREIGN KEY constraints   against existing data  use WITH NOCHECK  We do not recommend doing   this  except in rare cases  The new constraint will be evaluated in   all later data updates  Any constraint violations that are suppressed   by WITH NOCHECK when the constraint is added may cause future updates   to fail if they update rows with data that does not comply with the   constraint        The query optimizer does not consider constraints that are defined   WITH NOCHECK  Such constraints are ignored until they are re-enabled   by using ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL    Note  WITH NOCHECK is the default for re-enabling constraints  I have to wonder why      No existing data in the table will be evaluated during the execution of this command - successful completion is no guarantee that the data in the table is valid according to the constraint  During the next update of the invalid records  the constraint will be evaluated and will fail - resulting in errors that may be unrelated to the actual update that is made  Application logic that relies on the constraint to ensure that data is valid may fail  The query optimizer will not make use of any constraint that is enabled in this way    The sys foreign keys system view provides some visibility into the issue  Note that it has both an is disabled and an is not trusted column  is disabled indicates whether future data manipulation operations will be validated against the constraint  is not trusted indicates whether all of the data currently in the table has been validated against the constraint   ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint    Are your constraints to be trusted  Find out     SELECT   FROM sys foreign keys WHERE is not trusted   1

User · Answer

One script to rule them all  this combines truncate and delete commands with sp MSforeachtable so that you can avoid dropping and recreating constraints - just specify the tables that need to be deleted rather than truncated and for my purposes I have included an extra schema filter for good measure  tested in 2008r2   declare  schema nvarchar max     and Schema Id Schema id   Value     declare  deletiontables nvarchar max        TableA     TableB     declare  truncateclause nvarchar max     schema     and o Name not in         deletiontables  declare  deleteclause nvarchar max     schema     and o Name in      deletiontables           exec sp MSforeachtable  alter table   nocheck constraint all    whereand  schema exec sp MSforeachtable  truncate table      whereand  truncateclause exec sp MSforeachtable  delete from      whereand  deleteclause exec sp MSforeachtable  alter table   with check check constraint all    whereand  schema

User · Answer

You can temporarily disable constraints on your tables  do work  then rebuild them    Here is an easy way to do it     Disable all indexes  including the primary keys  which will disable all foreign keys  then re-enable just the primary keys so you can work with them     DECLARE  sql AS NVARCHAR max     select  sql    sql        ALTER INDEX ALL ON      t  name       DISABLE   CHAR 13  from       sys tables t where type  u   select  sql    sql        ALTER INDEX     i  name      ON      t  name       REBUILD   CHAR 13  from       sys key constraints i join     sys tables t on i parent object id t object id where     i type  PK    exec dbo sp executesql  sql  go    Do something  like loading data   Then re-enable and rebuild the indexes     DECLARE  sql AS NVARCHAR max     select  sql    sql        ALTER INDEX ALL ON      t  name       REBUILD   CHAR 13  from       sys tables t where type  u   exec dbo sp executesql  sql  go

[sql-server] How can foreign key constraints be temporarily disabled using T-SQL?

Examples related to sql-server

Examples related to tsql

Examples related to foreign-keys

Examples related to constraints