SQL Server 2014 – Sources for Untrusted Foreign Keys Besides FK Disable and BULK Inserts

foreign keyreferential-integritysql serversql server 2014

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 (< 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?

Best Answer

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.

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:

Related Question