sql - DELETE 语句冲突 REFERENCE 约束

Question

我收到错误

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

表 DocYDocument 有列

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

FK_DocYDocument1 外键列是 SubDocID，FK_DocYDocument 外键列是 DocID。

数据库设计有问题还是我必须在程序中查找错误？

Answer 1

您在 DocYDocument 中有行指的是DOCUMENT您正在尝试删除。

您必须从 DocYDocument 中删除行与匹配 DocID

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

或更改约束以自动执行此操作

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

如 DocID可以为空，您可以改为执行 ON DELETE SET NULL ， 如果你希望。