Search code examples
sql-serverdata-integrity

Non-Trusted Constraints

I just learned, to my amazement, that foreign key constraints can be non-trusted. This occurs when a constraint is added using WITH NOCHECK. When a constraint is non-trusted, it isn't used by the query analyzer for query plan generation.

See:
https://sqlserverfast.com/blog/hugo/2007/03/can-you-trust-your-constraints/

My question is this. When a constraint is non-trusted, is there a property on the constraint I can inspect that tells me this? I guess I could check to see if the constraint was added using WITH NOCHECK, but are there other ways a constraint can get flagged as non-trusted?

Solution 
  • select *
    from sys.check_constraints
    where is_not_trusted = 1

select * 
    from sys.foreign_keys
    where is_not_trusted = 1