To return a list of all untrusted foreign key constraints in a SQL Server database, you can run the T-SQL code below.

An untrusted foreign key is one that has its is_not_trusted flag set to 1.

Example 1 – Return Only Untrusted Foreign Key Constraints

This query returns only the untrusted foreign key constraints in the current database. For this example, I only return the name of the constraint, its trusted status, along with its enabled/disabled status.

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

Result:

+------------------------+------------------+---------------+
| Constraint             | is_not_trusted   | is_disabled   |
|------------------------+------------------+---------------|
| FK_BandMember_Band     | 1                | 1             |
| FK_BandMember_Musician | 1                | 0             |
+------------------------+------------------+---------------+

This queries the sys.foreign_keys system view. We know it only returns untrusted constraints because the WHERE clause specifies only rows that have the is_not_trusted column set to 1.

If you want to return only trusted foreign key constraints, simply change the 1 to 0.

I’ve also included the is_disabled flag, because it shows us whether the constraint is currently enabled or not. We can see that one of the constraints is enabled and the other is not.

This demonstrates the fact that a constraint can be untrusted even when it’s enabled. This is because, when you enable (or create) a constraint, you have the option of getting it to check any existing data before it’s enabled. If you choose not to check existing data, the constraint will remain untrusted once it’s enabled.

Here’s the same query again, but this time I include the table and the referenced table for each constraint:

SELECT 
  name AS 'Constraint',
  OBJECT_NAME(parent_object_id) AS 'Table',
  OBJECT_NAME(referenced_object_id) AS 'Referenced Table',
  is_disabled,
  is_not_trusted
FROM sys.foreign_keys
WHERE is_not_trusted = 1;

Result:

+------------------------+------------+--------------------+---------------+------------------+
| Constraint             | Table      | Referenced Table   | is_disabled   | is_not_trusted   |
|------------------------+------------+--------------------+---------------+------------------|
| FK_BandMember_Band     | BandMember | Band               | 1             | 1                |
| FK_BandMember_Musician | BandMember | Musician           | 0             | 1                |
+------------------------+------------+--------------------+---------------+------------------+

Example 2 – Return All Foreign Key Constraints

The following query returns all foreign key constraints for the current database (not just the untrusted ones):

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

Result:

+--------------------------------+------------------+---------------+
| Constraint                     | is_not_trusted   | is_disabled   |
|--------------------------------+------------------+---------------|
| FK_BandMember_Band             | 1                | 1             |
| FK_BandMember_Musician         | 1                | 0             |
| FK_MembershipPeriod_BandMember | 0                | 0             |
+--------------------------------+------------------+---------------+