Non-Trusted Constraints

Question

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?

Answer 1

select *
    from sys.check_constraints
    where is_not_trusted = 1

select * 
    from sys.foreign_keys
    where is_not_trusted = 1

Answer 2

Yes there is a property on the constraint accessible through the OBJECTPROPERTY function.

SELECT 
    CASE WHEN OBJECTPROPERTY(OBJECT_ID('FK_TIMECARD_EMPLOYEEID'), 'CnstIsNotTrusted') = 1
        THEN 'NO' 
        ELSE 'YES' 
        END AS 'IsTrustWorthy?'