In SQL Server, a foreign key constraint (and a CHECK constraint) can be either trusted or not trusted.

When a constraint is trusted, this means that the constraint has been verified by the system. When it’s not trusted, the constraint has not been verified by the system.

Basically, when you have an untrusted constraint, you could also have invalid data in your database. By this I mean you could have data that violates the constraint.

This means that you’re no longer maintaining referential integrity within your relationships, which is not normally good practice when looking after a relational database in production.

In this article I’ll check my existing constraints for their “trustworthiness”, and then I’ll update them to become trustworthy once again.

Example 1 – Review Existing Constraints

You can find out whether a constraint is trusted or not by querying the sys.foreign_keys system view.

Like this:

SELECT 
  name AS 'Constraint',
  is_disabled,
  is_not_trusted
FROM sys.foreign_keys;

Result:

+-------------------+---------------+------------------+
| Constraint        | is_disabled   | is_not_trusted   |
|-------------------+---------------+------------------|
| FK_Albums_Artists | 1             | 1                |
| FK_Albums_Genres  | 0             | 1                |
+-------------------+---------------+------------------+

OK, so this tells me I have two foreign key constraints and both of them are untrusted.

One of the constraints is disabled, so it makes sense that it’s not trusted (bad data can enter the database whenever the constraint is disabled).

But the other constraint is enabled, so it really shouldn’t be untrusted. Being untrusted means that there could be invalid data in the database. It doesn’t mean that there is invalid data, just that there could be.

Basically, by being enabled, it will check future data, but it can’t vouch for existing data. If a constraint is trusted, then you can be sure that all existing data is valid.

Return Only Untrusted Constraints

You might find prefer to use a WHERE clause to return only the untrusted constraints. Like this:

SELECT 
  name AS 'Constraint',
  is_disabled,
  is_not_trusted
FROM sys.foreign_keys
WHERE is_not_trusted = 1;

Result:

+-------------------+---------------+------------------+
| Constraint        | is_disabled   | is_not_trusted   |
|-------------------+---------------+------------------|
| FK_Albums_Artists | 1             | 1                |
| FK_Albums_Genres  | 0             | 1                |
+-------------------+---------------+------------------+

So in this case the result is the same (because all current constraints are untrusted).

Example 2 – Restore Trust

To restore trust to your enabled constraint, simply re-enable it while using the WITH CHECK option.

Like this:

ALTER TABLE Albums 
WITH CHECK CHECK CONSTRAINT FK_Albums_Genres;

Now when we query sys.foreign_keys we get a different result:

SELECT 
  name AS 'Constraint',
  is_disabled,
  is_not_trusted
FROM sys.foreign_keys;

Result:

+-------------------+---------------+------------------+
| Constraint        | is_disabled   | is_not_trusted   |
|-------------------+---------------+------------------|
| FK_Albums_Artists | 1             | 1                |
| FK_Albums_Genres  | 0             | 0                |
+-------------------+---------------+------------------+

We can see that the constraint is now trusted, because the is_not_trusted flag is set to 0.

Example 3 – How did the Constraint become Untrusted?

When you disable a foreign key constraint, it automatically becomes untrusted. When you re-enable the same constraint, you have the opportunity to restore its trust. If you don’t do this, it will remain untrusted.

When you enable a foreign key constraint, you have the option of specifying WITH CHECK or WITH NOCHECK. If you specify the later, your constraint will remain untrusted once it has been enabled.

It’s important to note that WITH NOCHECK is the default option, so if you don’t explicitly specify that it should be trusted, the constraint will be enabled as untrusted.

However, it’s the opposite when you create a foreign key constraint. When you first create the constraint, the default option is WITH CHECK. So if you omit this setting, it will be trusted by default (unless you have invalid data, in which case it won’t be enabled). However, you can override this setting by explicitly specifying WITH NOCHECK when you create the constraint.

To demonstrate how your enabled constraints can easily remain untrusted, I’ll re-enable the other key (the disabled one), but I’ll use the default setting:

ALTER TABLE Albums 
CHECK CONSTRAINT FK_Albums_Artists;

SELECT 
  name AS 'Constraint',
  is_disabled,
  is_not_trusted
FROM sys.foreign_keys;

Result:

+-------------------+---------------+------------------+
| Constraint        | is_disabled   | is_not_trusted   |
|-------------------+---------------+------------------|
| FK_Albums_Artists | 0             | 1                |
| FK_Albums_Genres  | 0             | 0                |
+-------------------+---------------+------------------+

So by being lazy (or forgetful) and not explicitly specifying WITH CHECK, I successfully managed to enable a constraint while keeping its “not trusted” status intact.

The key takeaway from this is: if you want your re-enabled constraints to be trusted, you should always enable them using WITH CHECK.