ORA-02291外键约束报错：父键不存在问题排查求助

咱们来一步步分析你遇到的ORA-02291外键约束错误，核心原因有两个，都是设计和执行顺序上的问题：

1. 外键约束逻辑完全不符合业务场景 #

你给REQUISITION表同时加了三个针对不同供应表的ITEMNO外键：

• ITEMNO_PHAR_FK（关联药品表）
• ITEMNO_SURG_FK（关联外科用品表）
• ITEMNO_NONSURG_FK（关联非外科用品表）

这就意味着每一条申请记录的ITEMNO必须同时存在于这三个供应表中，但实际业务里，一个申请的物品肯定只属于其中一类啊！比如你第一个申请的888520是非外科用品，只在SUPPLIES_NONSURGICAL里，自然找不到药品表和外科用品表的对应记录，直接触发了外键不存在的错误。

2. 数据插入顺序颠倒 #

你的SQL里先插了REQUISITION的数据，再插各个供应表的内容——虽然你把外键设成了DEFERRABLE INITIALLY DEFERRED（延迟检查到COMMIT时），但因为上面的约束逻辑错误，就算顺序对了，COMMIT的时候还是会报错。

我给你两种可行的修复思路，你可以根据业务需求选：

方案一：用条件外键区分物品类型（Oracle 12c及以上支持） #

给REQUISITION表加一个ITEM_TYPE字段，用来标记物品属于哪一类（比如用'P'代表药品、'S'代表外科用品、'N'代表非外科用品），然后给每个外键加条件，让不同类型的ITEMNO只关联对应的供应表：

步骤1：删除错误的外键并新增字段

-- 先删掉原来的三个外键
ALTER TABLE REQUISITION DROP CONSTRAINT ITEMNO_PHAR_FK;
ALTER TABLE REQUISITION DROP CONSTRAINT ITEMNO_SURG_FK;
ALTER TABLE REQUISITION DROP CONSTRAINT ITEMNO_NONSURG_FK;

-- 新增物品类型字段
ALTER TABLE REQUISITION ADD ITEM_TYPE CHAR(1) NOT NULL;

步骤2：创建条件外键

-- 药品类外键：只有ITEM_TYPE='P'时检查
ALTER TABLE REQUISITION ADD CONSTRAINT ITEMNO_PHAR_FK FOREIGN KEY(ITEMNO) 
REFERENCES SUPPLIES_PHARMACEUTICAL(ITEMNO)
DEFERRABLE INITIALLY DEFERRED
WHERE ITEM_TYPE = 'P';

-- 外科用品外键：只有ITEM_TYPE='S'时检查
ALTER TABLE REQUISITION ADD CONSTRAINT ITEMNO_SURG_FK FOREIGN KEY(ITEMNO) 
REFERENCES SUPPLIES_SURGICAL(ITEMNO)
DEFERRABLE INITIALLY DEFERRED
WHERE ITEM_TYPE = 'S';

-- 非外科用品外键：只有ITEM_TYPE='N'时检查
ALTER TABLE REQUISITION ADD CONSTRAINT ITEMNO_NONSURG_FK FOREIGN KEY(ITEMNO) 
REFERENCES SUPPLIES_NONSURGICAL(ITEMNO)
DEFERRABLE INITIALLY DEFERRED
WHERE ITEM_TYPE = 'N';

步骤3：修改插入语句，补充类型标记

INSERT INTO REQUISITION VALUES('000001', '345000', 'Julie Wood', '8', '888520', 2, '27-FEB-2018', '15-MAR-2018', 'N'); -- 非外科用品
INSERT INTO REQUISITION VALUES('000002', '345000', 'Julie Wood', '8', '923956', 1, '25-FEB-2018', '28-FEB-2018', 'P'); -- 药品
INSERT INTO REQUISITION VALUES('000003', '345000', 'Julie Wood', '8', '054802', 3, '20-FEB-2018', '22-FEB-2018', 'S'); -- 外科用品

方案二：合并三个供应表（更简单直观） #

如果业务允许，把三个供应表合并成一个通用的SUPPLIES表，用ITEM_TYPE区分类型，这样只需要一个外键关联REQUISITION：

步骤1：创建合并后的供应表

CREATE TABLE SUPPLIES (
    ITEMNO CHAR(6),
    ITEM_TYPE CHAR(1) NOT NULL, -- 'P'=药品, 'S'=外科用品, 'N'=非外科用品
    SUPPLIERNO CHAR(6),
    NAME VARCHAR2(25),
    DESCRIPTION VARCHAR2(25),
    QUANTITYINSTOCK INT,
    REORDERLEVEL INT,
    COSTPERUNIT DECIMAL(6,2),
    DOSAGE VARCHAR2(12), -- 只有药品需要，设为NULL即可
    CONSTRAINT ITEMNO_SUPP_PK PRIMARY KEY(ITEMNO),
    CONSTRAINT SUPPLIERNO_SUPP_FK FOREIGN KEY(SUPPLIERNO) REFERENCES SUPPLIER(SUPPLIERNO) DEFERRABLE INITIALLY DEFERRED
);

步骤2：迁移原供应表数据到新表

INSERT INTO SUPPLIES VALUES ('823456', 'P', '100001', 'Zanax', 'Anti Depressant', 8, 2, 100.50, '50mg');
INSERT INTO SUPPLIES VALUES ('923956', 'P', '100001', 'Zupridol', 'Blood Pressure Treatment', 12, 5, 50, '20mg');
INSERT INTO SUPPLIES VALUES ('003952', 'P', '200001', 'Amibreezax', 'Antifungal Ear Wax', 2, 1, 200, '5g');
INSERT INTO SUPPLIES VALUES ('004955', 'P', '200001', 'Ambridax', 'Blood Fungus Treatment', 5, 10, 20, '2mg');
INSERT INTO SUPPLIES VALUES ('054802', 'S', '100001', 'Scalpel', 'Scalping Tool', 20, 10, 200.42, NULL);
INSERT INTO SUPPLIES VALUES ('634520', 'S', '200001', 'Stitches', 'Suture Tool', 100, 10, 2.50, NULL);
INSERT INTO SUPPLIES VALUES ('888520', 'N', '100001', 'Cart', '5ftx2ftx3ft', 2, 0, 200.00, NULL);
INSERT INTO SUPPLIES VALUES ('000423', 'N', '100001', 'Tool Holder', 'Holds Inspection Equip.', 4, 2, 50.00, NULL);

步骤3：修改REQUISITION的外键

-- 删除原来的三个错误外键
ALTER TABLE REQUISITION DROP CONSTRAINT ITEMNO_PHAR_FK;
ALTER TABLE REQUISITION DROP CONSTRAINT ITEMNO_SURG_FK;
ALTER TABLE REQUISITION DROP CONSTRAINT ITEMNO_NONSURG_FK;

-- 添加一个关联合并后供应表的外键
ALTER TABLE REQUISITION ADD CONSTRAINT ITEMNO_SUPP_FK FOREIGN KEY(ITEMNO) REFERENCES SUPPLIES(ITEMNO) DEFERRABLE INITIALLY DEFERRED;

最后：调整插入顺序 #

不管选哪个方案，都要把INSERT INTO REQUISITION的语句移到所有父表数据插入之后——也就是先插供应商、供应表、员工表的数据，最后插申请数据，这样逻辑更清晰，也能避免不必要的延迟约束触发问题。

CREATE TABLE SUPPLIER (SUPPLIERNO CHAR(6), SUPPLIERNAME VARCHAR2(100), PHONENO VARCHAR2(12), ADDRESS VARCHAR(100), FAXNO VARCHAR(12), CONSTRAINT SUPPLIERNO_SSPL_PK PRIMARY KEY(SUPPLIERNO)); 
CREATE TABLE SUPPLIES_PHARMACEUTICAL (ITEMNO CHAR(6), SUPPLIERNO CHAR(6), NAME VARCHAR2(25), DESCRIPTION VARCHAR2(25), QUANTITYINSTOCK INT, REORDERLEVEL INT, COSTPERUNIT DECIMAL(6,2), DOSAGE VARCHAR2(12), CONSTRAINT ITEMNO_PHAR_PK PRIMARY KEY(ITEMNO)); 
CREATE TABLE SUPPLIES_SURGICAL (ITEMNO CHAR(6), NAME VARCHAR2(25), DESCRIPTION VARCHAR2(25), QUANTITYINSTOCK INT, REORDERLEVEL INT, COSTPERUNIT DECIMAL(6,2), SUPPLIERNO CHAR(6), CONSTRAINT ITEMNO_SUP_PK PRIMARY KEY(ITEMNO)); 
CREATE TABLE SUPPLIES_NONSURGICAL (ITEMNO CHAR(6), NAME VARCHAR2(25), DESCRIPTION VARCHAR2(25), QUANTITYINSTOCK INT, REORDERLEVEL INT, COSTPERUNIT DECIMAL(6,2), SUPPLIERNO CHAR(6), CONSTRAINT ITEMNO_NONSURG_PK PRIMARY KEY(ITEMNO)); 
CREATE TABLE STAFF_CHARGENURSE (STAFFNO CHAR(6), ADDRESS VARCHAR2(25), POSITION VARCHAR2(12), BUDGET DECIMAL(6,2), SPECIALTY VARCHAR2(12), CONSTRAINT STAFFNO_CHNURSE_PK PRIMARY KEY(STAFFNO)); 
CREATE TABLE REQUISITION (REQNO CHAR(6), STAFFNO CHAR(6), STAFFNAME VARCHAR2(25), WARDNO CHAR(6), ITEMNO CHAR(6), QUANTITY INT, DATEORDERED DATE, DATERECIEVED DATE, ITEM_TYPE CHAR(1) NOT NULL, CONSTRAINT REQ_PK PRIMARY KEY(REQNO)); 

ALTER TABLE SUPPLIES_PHARMACEUTICAL ADD CONSTRAINT SUPPLIERNO_PHA_FK FOREIGN KEY(SUPPLIERNO) REFERENCES SUPPLIER(SUPPLIERNO) DEFERRABLE INITIALLY DEFERRED; 
ALTER TABLE SUPPLIES_SURGICAL ADD CONSTRAINT SUPPLIERNO_SURG_FK FOREIGN KEY(SUPPLIERNO) REFERENCES SUPPLIER(SUPPLIERNO) DEFERRABLE INITIALLY DEFERRED; 
ALTER TABLE SUPPLIES_NONSURGICAL ADD CONSTRAINT SUPPLIERNO_NONSURG_FK FOREIGN KEY(SUPPLIERNO) REFERENCES SUPPLIER(SUPPLIERNO) DEFERRABLE INITIALLY DEFERRED; 
ALTER TABLE REQUISITION ADD CONSTRAINT STAFFNO_REQ_FK FOREIGN KEY(STAFFNO) REFERENCES STAFF_CHARGENURSE(STAFFNO) DEFERRABLE INITIALLY DEFERRED; 

-- 添加条件外键
ALTER TABLE REQUISITION ADD CONSTRAINT ITEMNO_PHAR_FK FOREIGN KEY(ITEMNO) 
REFERENCES SUPPLIES_PHARMACEUTICAL(ITEMNO)
DEFERRABLE INITIALLY DEFERRED
WHERE ITEM_TYPE = 'P';

ALTER TABLE REQUISITION ADD CONSTRAINT ITEMNO_SURG_FK FOREIGN KEY(ITEMNO) 
REFERENCES SUPPLIES_SURGICAL(ITEMNO)
DEFERRABLE INITIALLY DEFERRED
WHERE ITEM_TYPE = 'S';

ALTER TABLE REQUISITION ADD CONSTRAINT ITEMNO_NONSURG_FK FOREIGN KEY(ITEMNO) 
REFERENCES SUPPLIES_NONSURGICAL(ITEMNO)
DEFERRABLE INITIALLY DEFERRED
WHERE ITEM_TYPE = 'N';

-- 先插入父表数据
INSERT INTO SUPPLIER VALUES ('100001','Company A', '503-222-3333', '100 SE Stark Rd Portland, OR', '503-666-4444'); 
INSERT INTO SUPPLIER VALUES ('200001','Company B', '666-333-4444', '500 SE Bilerica Rd Akron, OH', '666-444-3333'); 

INSERT INTO SUPPLIES_PHARMACEUTICAL VALUES ('823456', '100001', 'Zanax', 'Anti Depressant', 8, 2, 100.50, '50mg'); 
INSERT INTO SUPPLIES_PHARMACEUTICAL VALUES ('923956', '100001', 'Zupridol', 'Blood Pressure Treatment', 12, 5, 50, '20mg'); 
INSERT INTO SUPPLIES_PHARMACEUTICAL VALUES ('003952', '200001', 'Amibreezax', 'Antifungal Ear Wax', 2, 1, 200, '5g'); 
INSERT INTO SUPPLIES_PHARMACEUTICAL VALUES ('004955', '200001', 'Ambridax', 'Blood Fungus Treatment', 5, 10, 20, '2mg'); 

INSERT INTO SUPPLIES_SURGICAL VALUES ('054802', 'Scalpel', 'Scalping Tool', 20, 10, 200.42, '100001'); 
INSERT INTO SUPPLIES_SURGICAL VALUES ('634520', 'Stitches', 'Suture Tool', 100, 10, 2.50, '200001'); 

INSERT INTO SUPPLIES_NONSURGICAL VALUES ('888520', 'Cart', '5ftx2ftx3ft', 2, 0, 200.00, '100001'); 
INSERT INTO SUPPLIES_NONSURGICAL VALUES ('000423', 'Tool Holder', 'Holds Inspection Equip.', 4, 2, 50.00, '100001'); 

INSERT INTO STAFF_CHARGENURSE VALUES('345000', '32 Stark St. Portland, OR', 'Charge Nurse', 8000.99, 'Head Trauma'); 
INSERT INTO STAFF_CHARGENURSE VALUES('246000', '18 Wilson Rd Portland, OR', 'Charge Nurse', 6000, 'Epidermus'); 

-- 最后插入申请数据
INSERT INTO REQUISITION VALUES('000001', '345000', 'Julie Wood', '8', '888520', 2, '27-FEB-2018', '15-MAR-2018', 'N'); 
INSERT INTO REQUISITION VALUES('000002', '345000', 'Julie Wood', '8', '923956', 1, '25-FEB-2018', '28-FEB-2018', 'P'); 
INSERT INTO REQUISITION VALUES('000003', '345000', 'Julie Wood', '8', '054802', 3, '20-FEB-2018', '22-FEB-2018', 'S'); 

COMMIT;

这个版本执行后应该就能顺利通过，不会再触发外键约束错误了。

内容的提问来源于stack exchange，提问作者Mark McGown