SQL SERVER – Find Untrusted Foreign Key

An untrusted foreign key has not validated all existing rows. The question arose during a customer health check.

SELECT s.name AS SchemaName,t.name AS TableName,f.name AS KeyName,
       f.is_not_trusted,f.is_disabled,f.is_not_for_replication
FROM sys.foreign_keys f JOIN sys.tables t ON t.object_id=f.parent_object_id
JOIN sys.schemas s ON s.schema_id=t.schema_id
WHERE f.is_not_trusted=1 ORDER BY s.name,t.name,f.name;
-- After fixing violations and reviewing locking/validation cost:
-- ALTER TABLE dbo.YourTable WITH CHECK CHECK CONSTRAINT YourForeignKey;

My original filter selected enabled, untrusted keys without NOT FOR REPLICATION. This inventory exposes all three flags. Deliberately narrow it when that older scope is required. Excluded cases are not proof of absent problems.

An enabled untrusted key can enforce new changes despite unvalidated existing data. NOCHECK has statement-specific effects on validation and enforcement. Untrusted and disabled describe different states.

Trusted relationships can assist optimization. Check violations and ownership before WITH CHECK CHECK CONSTRAINT. Validation can fail, lock data and consume resources. Test and schedule it instead of enabling every discovered key.

A missing relationship deserves a business-rule and data-integrity review. Catalog presence alone does not establish correctness. Assess the actual rule and data.

Reference: Foreign-key enforcement and trust.

Related reading

• Comprehensive Database Performance Health Check
• LinkedIn

An untrusted constraint is not necessarily disabled, it is a relationship whose existing-data validation state differs from enforcement.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.