disabling, enabling FOREIGN KEY constraints and triggers

References: http://huangqiqing123.iteye.com/blog/1420465

"One, SQL command"
---Disable all foreign KEY constraints for the specified table
ALTER TABLE [table name] NOCHECK constraint all

---Enable all foreign key constraints for the specified table
ALTER TABLE [table name] CHECK constraint all

"Two, assemble SQL statement"
--Disables SQL for specifying table FOREIGN KEY constraints
Select ' ALTER TABLE ' +b.name+ ' NOCHECK CONSTRAINT ' +a.name+ ';
From sysobjects a
INNER JOIN sysobjects b onb.id=a.parent_obj
Where a.xtype= ' F ' andb.name= ' table name '

--Enables the SQL of the specified table foreign KEY constraint
Select ' ALTER TABLE ' +b.name+ ' CHECK CONSTRAINT ' +a.name+ ';
From sysobjects a
INNER JOIN sysobjects b onb.id=a.parent_obj
Where a.xtype= ' F ' andb.name= ' table name '

"Three, Query dictionary table Sys.foreign_keys"
View constraint status (query dictionary table Sys.foreign_keys, which starts appearing in sqlserver2005 and above):
Select Name, is_disabled from Sys.foreign_keys order by name

which
Name: FOREIGN KEY constraint names
is_disabled : Is disabled (this value is important, as shown below).

"Use case"
Requirements:
We're going to do some document records (including single and document details) are imported into the history table, and records of the current table are deleted when the import is finished.

Problem encountered:
Because a single header table is bound to a document schedule, the problem of constraints is involved regardless of the derivative to the history table or the records that delete the current table. We are very aware of the record dependencies of each table, you must pilot a single header record, and then guide the document detail record, otherwise it will violate the constraint cannot continue; When you delete the record for the current table, you delete the document detail record and then delete the header record, otherwise the constraint cannot continue.

Workaround:
Deactivate the constraint before the derivative and delete the record, and reopen the constraint after the derivative and delete the record. (Of course triggers have to do the same)

--sql script: Use [database] go SET xact_abort on begin Tran-1. Disable the foreign KEY constraint (that is, disable the relationship that requires the Force foreign KEY constraint) DECLARE @TableName sysname, @  ConstraintName sysname, @SQLString nvarchar (+) SELECT object_name (parent_obj) as TableName, name as ConstraintName into
[_myforeignkey] From sysobjects f where exists (SELECT * from Sys.foreign_keys t where t.[is_disabled]=0 and T.[name]=f.name and T. [object_id]=f.id)--has "force foreign KEY constraint" and F.xtype=n ' F ' DECLARE cTmp CURSOR local static read_only forward_only for Sele CT TableName, constraintname from [_myforeignkey] OPEN cTmp FETCH cTmp to @TableName, @ConstraintName while (@ @FETCH _status= 0) BEGIN SET @SQLString = ' ALTER TABLE ' + @TableName + ' NOCHECK CONSTRAINT ' + @ConstraintName EXEC sp_execut ESQL @SQLString--Next FETCH cTmp into the @TableName, @ConstraintName end close CTmp deallocate cTmp-2. Turn off Trigger DE CLARE cTmp CURSOR Local static read_only forward_only for SELECT name from sysobjects WHERE xtype= N ' U ' OPEN cTmp FE TCH cTmP into @TableName while (@ @FETCH_STATUS = 0) BEGIN SET @SQLString = ' ALTER TABLE ' + @TableName + ' DISABLE TRIGGER all ' EXEC sp_executesql @SQLString--Next FETCH cTmp to @TableName end close CTMP deallocate cTmp--3. Derivative and delete records (note Cannot use Trucate table, only delete)--dangerous operation, temporarily commenting--declare cTmp CURSOR local static read_only forward_only for    -     SELECT name from sysobjects --    WHERE xtype= N ' U ' and name <> ' _myforeignkey '--note : You must exclude the [_myforeignkey] table, or you cannot recover the foreign KEY constraint    --    ORDER by name--open cTmp  --fetch cTmp into @TableNa Me  --while (@ @FETCH_STATUS = 0)  --begin  --    SET @SQLString = ' Delete ' + @TableName + ' &nbs
P
--    Print @SQLString    --    EXEC sp_executesql @SQLString  --     --   --Next  --    FETCH cTmp into @TableName  --end  --close cTmp  --deallo CATE CTMP    --4. Recovery triggers and FOREIGN KEY constraints DECLARE cTmp CURSOR local static read_only forward_only for SELECT name from sysobjects WHERE xtype= N ' U ' OPEN cTmp FETCH cTmp into @TableName while (@ @FETCH_STATUS = 0) BEGIN SET @SQLString = ' ALTER TABLE ' + @TableName + ' E Nable TRIGGER all ' EXEC sp_executesql @SQLString--Next FETCH cTmp to @TableName end close CTmp deallocate CTMP-5. Restore the FOREIGN KEY constraint (that is, the relationship that requires the "Force foreign KEY constraint" to be restored) DECLARE cTmp CURSOR Local static read_only for SELECT forward_only  Ename,constraintname from [_myforeignkey] OPEN cTmp FETCH cTmp to @TableName, @ConstraintName while (@ @FETCH_STATUS = 0 BEGIN SET @SQLString = ' ALTER TABLE ' + @TableName + ' CHECK CONSTRAINT ' + @ConstraintName EXEC sp_executesql @SQLS Tring--Next FETCH cTmp into @TableName, @ConstraintName end close CTmp deallocate cTmp-6. Delete temporary table Drop table[_ Myforeignkey] Commit Tran SET xact_abort off