使用相同INSERT模板中途出现父表约束错误的原因排查

表结构 #

nm_order表 #

CREATE TABLE nm_order (
  orderID INT PRIMARY KEY,
  customerID INT REFERENCES nm_customer(customerID) NOT NULL,
  orderDate DATE NOT NULL,
  orderStatus VARCHAR2(2) NOT NULL,
  orderShippedDate DATE ) ;

nm_orderLine表 #

CREATE TABLE nm_orderLine (
  orderLineID INT PRIMARY KEY,
  orderID INT REFERENCES nm_order(orderID),
  productCode VARCHAR2(15) REFERENCES nm_product(productCode),
  quantity NUMBER(3) NOT NULL,
  unitPrice NUMBER(5,2) NOT NULL ) ;

插入操作记录 #

-- 成功插入
INSERT INTO nm_orderLine (
orderLineID, orderID, productCode, quantity, unitPrice)
VALUES (5, 4, 'R03515', 1, 5.99) ;
-- 输出：1 row created.

-- 成功插入
INSERT INTO nm_orderLine (
orderLineID, orderID, productCode, quantity, unitPrice)
VALUES (6, 5, 'R03669', 1, 8.99) ;
-- 输出：1 row created.

-- 插入失败
INSERT INTO nm_orderLine (
orderLineID, orderID, productCode, quantity, unitPrice)
VALUES (7, 6, 'IM657', 1, 12.99) ;
-- 错误：ORA-02291: integrity constraint (BISUSER.SYS_C009576) violated - parent key not found

错误原因分析 #

ORA-02291错误表示你尝试插入的子表数据，对应的外键字段在父表中没有匹配的主键/唯一键值。

nm_orderLine有两个外键约束：

• orderID 关联父表 nm_order 的主键 orderID
• productCode 关联父表 nm_product 的主键/唯一键 productCode

前两条插入成功，说明：

• nm_order 中存在 orderID=4 和 orderID=5 的记录
• nm_product 中存在 productCode='R03515' 和 productCode='R03669' 的记录

第三条插入失败，必然是以下两种情况之一：

1. nm_order 中不存在 orderID=6 的记录
2. nm_product 中不存在 productCode='IM657' 的记录

排查验证步骤 #

1. 确认约束对应的外键字段 #

执行以下SQL查询，定位错误关联的是哪个外键：

SELECT ucc.constraint_name, ucc.table_name, ucc.column_name, uc.r_constraint_name
FROM user_cons_columns ucc
JOIN user_constraints uc ON ucc.constraint_name = uc.constraint_name
WHERE ucc.constraint_name = 'SYS_C009576';

然后根据返回的r_constraint_name查询父表信息：

SELECT table_name, constraint_type
FROM user_constraints
WHERE constraint_name = '<上面查询得到的r_constraint_name>';

2. 验证父表是否存在对应记录 #

如果约束关联的是orderID，执行：

SELECT * FROM nm_order WHERE orderID = 6;

如果约束关联的是productCode，执行：

SELECT * FROM nm_product WHERE productCode = 'IM657';

查询结果为空的话，就是缺失的父记录导致的错误。

解决方法 #

• 如果是orderID=6不存在，先在nm_order表中插入对应订单记录，再插入nm_orderLine数据
• 如果是productCode='IM657'不存在，先在nm_product表中插入对应产品记录，再插入nm_orderLine数据

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