Foreign Key Check Options

Louis Davidson shows how to create a foreign key constraint which is enabled or disabled, trusted or untrusted:

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.

Related Posts

Discovering Composite Keys

John Morehouse shares some good information on composite keys, including a few scripts: As I started to work on this, my first thought was that it would be helpful to know how many tables had a composite primary key.  This would give me an idea on how many tables I was dealing with.  Thankfully, SQL […]

Read More

Cleaning Up Foreign Keys

Adrian Buckman looks at how to clean up untrusted foreign key constraints: SQL Server tracks untrusted Foreign keys in sys.Foreign keys with a column called is_not_trusted, there may be a number of reasons why a Foreign key may have become untrusted below are a couple of examples: Foreign key was disabled using the ‘NOCHECK’ option then re-enabled using […]

Read More