Temporary disable foreignkeys?

问题:

I saw thisone in MSDN magazine.i wanted to Temporary disable Foreignkeys,Can i do using following code.. Not needed to delete but temporary disable because i wanted to enable again

ALTER TABLE Orders
NOCHECK CONSTRAINT 
    FK_Orders_Customers

回答1:

-- Disable the constraint.

ALTER TABLE Orders NOCHECK CONSTRAINT FK_Orders_Customers

-- Reenable the constraint.

ALTER TABLE Orders WITH CHECK CHECK CONSTRAINT FK_Orders_Customers

回答2:

Yes, as you've suspected ALTER TABLE [table] CHECK / NOCHECK CONSTRAINT *FK_Name* enables and disables foreign key constraint checking. It also proves why it is a good idea to explicitly name your constraints i.e. to avoid names like FK__TABLE__A2A64E930CBAE877.

One point to note that after inserting / changing data with disabled foreign key constraints is that SQL won't trust your constraint if you simply enable it with CHECK CONSTRAINT. You will need to do the following to get SQL to recheck the constraint during re enable:

ALTER TABLE [table] WITH CHECK CHECK CONSTRAINT *FK_Name*

You can check for violations with

DBCC CHECKCONSTRAINTS ([table])

Ref : http://msdn.microsoft.com/en-us/library/ms177456(v=sql.90).aspx (Disabling Constraints) and also see http://geekswithblogs.net/dturner/archive/2011/01/31/sql-constraints-check-and-nocheck.aspx