Non-Trusted Foreign Keys

Daniel Janik explains what happens when you don’t have trusted foreign key constraints:

Why is it untrusted? Perhaps we disabled the check to load data and neglected to re-enable it?

No matter what the reason is the next part is not as simple. This is for two reasons.

  1. The data in the child table may not be valid. Since the key was not being checked I may have data in my table that isn’t represented in the parent.

  2. The syntax is a bit silly. As Mike Byrd in Austin, TX says, Microsoft studders. The syntax to reenable is “CHECK CHECK”. Let’s look at how we reenable the Address key check.

Read on for pros and cons of disabling (or not trusting) foreign key constraints.

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


January 2017
« Dec Feb »