We have one parent table:
Document (GID int identity(1,1),ParentName)
and two child tables, both having FOREIGN KEY(Document_GID) REFERENCES Document (GID)
1. Lets say DocumentChild1 table has 'n' documents.
2. Then in a transaction we try to insert entries into DocumentChild2 from DocumentChild1.
Here, we get foreign key violation :
The INSERT statement conflicted with the FOREIGN KEY constraint "DocumentChild2_DOC_GID_FK".
The conflict occurred in database "VOL_ABC", table "Document", column 'GID'.
Also, we don't have any delete operation in Document table. But many insert operation into this table can be going on in parallel.
We saw this error once. Everything works fine when we reran the insert sql.
I am wondering, what could have caused this behavior. Definitely data exists in the Document table else insert into DocumentChild1 would have failed.
Let me know if you need more details.