Skip to content

Disabling Constraints

2009 April 13
by Shannon Lowder

Constraints are great for maintaining the integrity of the data in your database. I’m not a big fan of running your database without them. It just opens up the door for too many problems. But there are a few times where you’re going to need to be able to disable them.

Let’s say you’re loading a tremendous amount of data, and you have a highly normalized database. If all these tables are related, and you need to insert all the data more quickly… you could drop the constraints, insert the data, check the constraints, then re-create them.

This would be as simple as running

ALTER TABLE tableName DROP CONSTRAINT constraintName

You would then run all your INSERT or UPDATE statements.

You then need to check the data to make sure that re-creating the CONSTRAINT won’t result in an error. If it’s a unique constraint, do a group by count, with a HAVING COUNT(*) > 1. That will show duplicates very quickly. Once you’re sure the CONSTRAINT won’t be violated. RUN the ALTER statement to CREATE the CONSTRAINT.

But there is a less drastic method. You can set the CONSTRAINT to NOCHECK.


Then run all your INSERT or UPDATES.

Next, check the CONSTRAINT conditions, make sure you won’t have an error when you re-enable the CONSTRAINT. Then run


This can be a useful technique, but be careful with it. Never leave the CONSTRAINT turned off for very long… you may have other users in your database making INSERTS… You’d have to clean up after them as well.

Perhaps you would want to consider dumping the new data into a copy of your database, then moving over the data one table at a time, start with the referenced data, then add the referencing data.

If you have any questions, let me know. I’m here to help you learn all you can about SQL!

No comments yet

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS