1

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?

Community
  • 1
Alexei
  • 1,261
  • 1
  • 13
  • 35

1 Answers1

1

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:

Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
  • Thanks for the quick answer. So, if I understood correctly, in SQL Server 2014 the only MERGE related bugs appear when using table variables and/or OUTPUT clause. Personally, I have never used MERGE with these, but it is good to know about them. – Alexei Mar 09 '17 at 13:12
  • @Alexei well, that list in the article I wrote three and a half years ago - before 2014 was released - was the list of bugs I knew about at the time. Most of them still haven't been addressed. And if you use MERGE in your codebase, there's nothing stopping someone from later adding things like table variables or output clauses, unaware of the potential issues. Personally, I don't think it's safe to make excuses to use something with known issues unless there is a really good reason. I've converted dozens of customer MERGE statements to separate ins/upd/del and they sleep better. – Aaron Bertrand Mar 09 '17 at 14:08
  • @Aaron Bertrand, Sorry to ask the below question here. I have no idea to design the tables in a better manner. Kindly have a look. https://stackoverflow.com/questions/46285924/which-kind-of-table-design-best-suitable-for-normalization-in-sql-server. Please suggest me the better solution to handle that scenario. – RGS Sep 21 '17 at 10:37