SQL server

Disabling constraints.

There are times when under the order of management, I had to do bulk inserts of data. Data that violated the underlying referential integrity of the stored data. The issue was that there were constraints on the tables that prohibited me from doing a bulk insert. If you drop the index first, you will not be able to use it again, until the index is recreated. For any reason, if you do not have a copy of the code to recreate the index, then that would be the start of the problem. What you can do instead is to disable the enforcement of the constraint:
ALTER TABLE name_of_the_table NOCHECK CONSTRAINT name_of_the_constraint;
After this script is run, the constraint will not be enforced, and you can enter data without worrying about the constraint checks. You can alter the table again, to re-enable the constraints.
ALTER TABLE name_of_the_table CHECK CONSTRAINT name_of_the_constraint;
Any previous violations will not be checked or corrected, but any entries after the constraint has been enforced, will be checked.

The same can be done for triggers:

DISABLE TRIGGER name_of_the_trigger ON name_of_the_table;

ENABLE TRIGGER name_of_the_trigger ON name_of_the_table;

This saves the worry about storing the index creation code and archiving it for when you need it later.


Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s