I am in the middle of building a utility (for work, and for my next SQLBLOG post), that will help when you need to drop the foreign key constraints on a table if you want to truncate the tables, but holds the script in a table to replace the script. The first thing though, is to make sure I have all of the scripting possibilities understood.
When I started hunting around to remember how to create a disabled constraint, I couldn’t easily find anything, so I figures I would make this a two-parter. (My blogging rule is if I look for something and find a good article about it, reference it, then tweet the article out. If it is too hard to find, blog about it!) So today I will review how to create a FOREIGN KEY constraint in three ways:
Enabled, and Trusted – Just as you would normally create one
Enabled, Not Trusted – The “quick” way, not checking data to see if any wrong data already exists, but not allowing new, bad data in
Disabled, Not Trusted – The constraint is basically documentation of the relationship, but you are on your own to make sure the data matches the constraint
In an ideal world, all of your constraints are enabled and trusted, but when you’re building a general-purpose script, you can’t always assume that will be the case. Click through for examples on how to create foreign key constraints fitting each of these scenarios.