Foreign Key Reference Check operator - false report
I have a database with a table "note" which has more then 253 incoming references.
Every entry of the "node" table is references only 1 time.
Some of those references are coming from a table "doc", some from a table "docPos" and some from other tables.
The "docPos" has a reference on "doc" which is defined as cascading delete, so if a doc is deleted, all it's position are deleted too.
Now I tryed to delete a "doc" and a referenced "node" like this:
BEGIN TRAN T1
DELETE FROM doc WHERE id=1
DELETE FROM note WHERE id=1
COMMIT TRAN T1
However the statment fails with "The DELETE statement conflicted with the REFERENCE constraint".
The reported "note" is referenced only by the (deleted) "doc", the reported field is one in the "docPos"-table, but it is always empty.
So the statements should have been executed without any problem and the "note" should have been deleted.
The same 2 statements can be executed independently (without a transaction) without problems, the following statements work:
DELETE FROM doc WHERE id=1
DELETE FROM note WHERE id=1
Since the issue started with SQL Server 2016, I was looking for changes in that version and found the "Foreign Key Reference Check operator".
So I tryed to execute the statement without using the "Foreign Key Reference Check operator" by:
a) Removing some of the incoming references from the "note" table
b) using a compatibility mode < 130
and in both cases the statement executed correctly.
Thats why I guess it is an issue with the new Foreign Key Reference Check operator in SQL Server 2016 and 2017.
During my research, I found this article (https://support.microsoft.com/en-us/help/4503379/fix-referential-integrity-constraints-are-not-evaluated-correctly-when) but unfortunately the referenced update did not solve my issue.
I also tested this with the SQL Server 2019 Preview but the bug persists.
