Professional SQL Server™ 2005 Programming

5.6. Disabling Constraints

Sometimes you want to eliminate the constraint checking, either just for a time or permanently. It probably doesn't take much thought to realize that SQL Server must give you some way of deleting constraints, but SQL Server also enables you to deactivate a FOREIGN KEY or CHECK constraint while otherwise leaving it intact.

The concept of turning off a data integrity rule might seem rather ludicrous at first. I mean, why would you want to turn off the thing that makes sure you don't have bad data? The usual reason is the situation where you already have bad data. This data usually falls into two categories:

• Data that's already in your database when you create the constraint

• Data that you want to add after the constraint is already built

NOTE

You cannot disable PRIMARY KEY or UNIQUE constraints.

5.6.1. Ignoring Bad Data When You Create the Constraint

All this syntax has been just fine for use when you create the constraint at the same time as you create the table. Quite often, however, data rules are established after the fact. Say, for instance, that you missed something when you were designing your database, and you now have some records in an Invoicing table that show a negative invoice amount. You might want to add a rule that won't let any more negative invoice amounts into the database, but at the same time, you want to preserve the existing records in their original state.

To add a constraint but have it not apply to existing data, you make use ...