Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Aug 7, 2001

Quickly Disable Constraint and Triggers

By Brian Knight

In a previous article, I covered how to use the sp_msforeachtable system stored procedure to look through a list of tables and perform an action on them. You may want to read that article before continuing.

When loading data, nothing can be more frustrating than having to deal with stubborn constraints and triggers. This is especially true when you know your data is fine but the constraints are too prohibitive for a mass initial load. Here's a technique I use to disable the constraints and triggers temporarily during a mass load.

To do this, we can use the sp_msforeachtable system stored procedure to loop through each table and issue an ALTER TABLE statement to dsable the constraint and trigger. The ALL clause at the end of the command will disable all constraints and triggers. There is no need to drop referential integrity to load data. If you turn on NOCHECK, constraints will not be checked. Here's the code to perform this:

sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER  all"

To enable them back, you can reverse the function. Let's get a little more creative this time. We'll explicitly set the parameters and list each table before enabling them so we can verify any errors easily.

sp_msforeachtable @command1="print '?'", 
@command2="ALTER TABLE ? CHECK CONSTRAINT all"
sp_msforeachtable @command1="print '?'", 
@command2="ALTER TABLE ? ENABLE TRIGGER  all"

As I mentioned before, you never want to drop constraints during development. Instead disable them temporarily until the problem is debugged.



SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date