Disabling Constraints

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.

ALTER TABLE tableName NOCHECK CONSTRAINT constraintName

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

ALTER TABLE tableName CHECK CONSTRAINT constraintName

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!

Leave a Reply

Your email address will not be published. Required fields are marked *