Constraint Disabling–sometimes it needs to be done

Recently I was trying to reproduce an error in a test environment and I needed to insert data that didn’t fit into the current constraints.  Long story on how it got there, but it was something I had to deal with.  The question someone asked me was could we disable a constraint – instead of dropping it, adding the data, then adding the constraint back without checking the existing data.  It’s pretty simple to do using the NOCHECK clause.  Some legit reasons to use this would be to:

1. Bulk loaddata
2. Reproducing errors
3. Send a team of DBAs on a wild goose chase

To disable a constraint, the TSQL is:

ALTER TABLE dbo.<<TableName>> NOCHECK CONSTRAINT <<ConstraintName>>;

To enable it

ALTER TABLE dbo.<<TableName>> CHECK CONSTRAINT <<ConstraintName>>;

Here’s where I got my info from…
http://msdn.microsoft.com/en-us/library/ms190273.aspx