user1673665 user1673665 - 9 days ago 7
SQL Question

The DELETE statement conflict REFERENCE constraint

I get the error


The DELETE statement conflicted with the REFERENCE constraint
FK_DocYDocument1 - table DocYDocument, column SubDocID - Statement:
DELETE FROM DOCUMENT WHERE (ID=?) Parameter: 'D7FAA3CF...'


Table DocYDocument has columns

PK ID, FK DocID and FK SubDocID.
And the keys PK_DocYDocument, FK_DocYDocument and FK_DocYDocument1.


FK_DocYDocument1 foreign key column is SubDocID and FK_DocYDocument foreign key column is DocID.

Is there a problem with database design or do I've to look for the error in the program?

Answer

You have rows in DocYDocument that are referring to the DOCUMENT you are trying to delete.

You have to either delete the rows from DocYDocument with matching DocID

DELETE FROM DocYDocument WHHERE DocID = ?
DELETE FROM DOCUMENT WHERE ID = ?

or change the constraint to do this automatically

ALTER TABLE DocYDocument
  DROP CONTRAINT FK_DocYDocument;
ALTER TABLE DocYDocument
  ADD CONSTRAINT FK_DocYDocument
    FOREIGN KEY ( DocID )
    REFERENCES DOCUMENT ( ID )
    ON DELETE CASCADE;

If DocID is nullable, you could instead do ON DELETE SET NULL, if you wish.