Is there another source for untrusted foreign keys beside FK disable and BULK inserts? This question and its answer explain how bulk inserts will render FK as untrusted, as bulk inserts are not completely checked (only PK and UNIQUEs) . I also know that temporarily disable of a FK may lead to untrusted status.

My current project uses a rather small database (&lt; 3GB) and typical table count is less than 200K rows. However, integrity is very important, so I rely heavily on FK constraints (integrity is much more important than speed).

Question: is there any other mechanism (besides disable and BULK INSERT) that can produce untrusted foreign keys?
Another source is a bug in MERGE that allows foreign key constraints to be ignored. This was reported by Paul White, closed as Won't Fix, and has been ignored ever since. It's one of the main reasons I've long been vocal against using MERGE at all.


MERGE query plans allow FK and CHECK constraint violations (also reported here)
MERGE with caution


There are other possible scenarios too - I haven't thoroughly checked out this one about check constraints to see if it could also affect FKs, and another one I didn't read is this one...

While she focused on the performance aspect, my co-worker Melissa Connors wrote a great post on finding and fixing untrusted foreign keys:


Trust issues hurting database performance?