Untrusted foreign keys and constraints can degrade your SQL Server performance

Disable keys and constraints to improve the performance of load data is a common practice in large data warehouses and other environments with big tables.

This technique is relatively more natural and safer than dropping and recreating all the keys and constraints if you don’t forget to enable them after the work has been done.

Having untrusted or disabled FOREIGN KEY or CHECK constraints in your database may cause query performance problems with inadequate query plans and degrade data consistency and integrity.

How to check for Untrusted Foreign Keys and Constraints?

Use the script below to find untrsted untrusted foreign Keys:

  1. SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS KeyName
  2. from sys.foreign_keys i
  3. INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
  4. INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
  5. WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0;

Use the  following  script to get the list of  untrusted constraints:

  1. SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS KeyName
  2. from sys.check_constraints i
  3. INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
  4. INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
  5. WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0;

How to restore the trust for Foreign Keys and Constraints?

Once you have that result set above, you can use the alter table statement to re-establish the trust for each result in the list. Just replace “MyTableName” and “MyConstraintName” to your table name and Foreign Keys/Constraints name in the code below.

  1. ALTER TABLE MyTableName WITH CHECK CHECK CONSTRAINT MyConstraintName

Alternatively, you can also run the script below that will generate the necessary T-SQL to recheck all the Untrusted constraints:

  1. SELECT N'ALTER TABLE ' 
  2.     + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) 
  3.     + N' WITH CHECK CHECK CONSTRAINT ' 
  4.     + QUOTENAME(c.name) + N';' AS ConstraintsToFix
  5. FROM sys.check_constraints c
  6. INNER JOIN 
  7. sys.objects o ON c.parent_object_id = o.object_id
  8. INNER JOIN 
  9. sys.schemas s ON o.schema_id = s.schema_id
  10. WHERE c.is_not_trusted = 1 
  11. AND c.is_not_for_replication = 0 
  12. AND c.is_disabled = 0

The following script will generate the necessary T-SQL to recheck the untrusted foreign keys:

  1. SELECT N'ALTER TABLE ' + QUOTENAME(s.name) 
  2.   + N'.' + QUOTENAME(o.name) + N' WITH CHECK CHECK CONSTRAINT ' 
  3.   + QUOTENAME(f.name) + N';' AS ForeignKeystoFix
  4. FROM    sys.foreign_keys f
  5. INNER JOIN 
  6.  sys.objects o ON f.parent_object_id = o.object_id
  7. INNER JOIN 
  8.  sys.schemas s ON o.schema_id = s.schema_id
  9. WHERE f.is_not_trusted = 1 
  10.  AND f.is_not_for_replication = 0

More information:

Microsoft – Unique Constraints and Check Constraints
Managing untrusted foreign keys, Jefferson Elias – SQLShack
Avoid Untrusted Constraints in SQL Server, Armando Prato – MSSQLTips

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.