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:
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx
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?