时间戳

首页 > 解决方案 > 无法验证 - 未找到父键

oracle - 无法验证 - 未找到父键

问题描述

我的数据库中有以下表格：

CREATE TABLE "ALMAT"."ORDER" 
   (    "ID" NUMBER(*,0) NOT NULL ENABLE, 
    "CUSTOMER_ID" NUMBER(*,0), 
    "CREATE_DATE" DATE, 
    "UPDATE_DATE" DATE, 
    "STATUS_ID" NUMBER(*,0), 
     CONSTRAINT "order_PK" PRIMARY KEY ("ID")
CONSTRAINT "FK_ORDER" FOREIGN KEY ("STATUS_ID")
      REFERENCES "ALMAT"."ORDER_STATUS" ("ID") ENABLE
   )

CREATE TABLE "ALMAT"."ORDER_ITEM" 
   (    "ID" NUMBER(*,0) NOT NULL ENABLE, 
    "ORDER_ID" NUMBER(*,0), 
    "PRODUCT_ID" NUMBER(*,0), 
     CONSTRAINT "ORDER_ITEM_PK" PRIMARY KEY ("ID"))


CREATE TABLE "ALMAT"."PRODUCT" 
   (    "ID" NUMBER(*,0) NOT NULL ENABLE, 
    "NAME" VARCHAR2(50 BYTE), 
    "PRICE" NUMBER(*,0), 
    "DESCRIPTION" VARCHAR2(180 BYTE), 
    "CREATE_DATE" DATE, 
    "UPDATE_DATE" DATE, 
    "CATEGORY_ID" NUMBER(*,0), 
    "STATUS_ID" NUMBER(*,0), 
    "DISCOUNT_ID" NUMBER(*,0), 
     CONSTRAINT "PRODUCT_PK" PRIMARY KEY ("ID")
CONSTRAINT "FK_STATUS" FOREIGN KEY ("STATUS_ID")
      REFERENCES "ALMAT"."PRODUCT_STATUS" ("ID") ENABLE, 
     CONSTRAINT "FK_DISCOUNT" FOREIGN KEY ("DISCOUNT_ID")
      REFERENCES "ALMAT"."DISCOUNT" ("ID") ENABLE
   )

当我尝试向 ORDER_ITEM 添加约束时抛出错误：“由于表有子记录，更改表验证约束失败。”

alter table order_item
    add constraint fk_orderitem_productid
        foreign key (product_id)
        references product(id);
    
alter table order_item
    add constraint fk_orderitem_orderid
        foreign key (order_id)
        references "ORDER"(id);

除此之外，我尝试删除与父表不匹配的记录，但这不起作用：

delete from order_item where exists(
    SELECT DISTINCT order_id FROM order_item WHERE order_id NOT IN (
    SELECT id FROM "ORDER")
)

标签： oracleforeign-keys

解决方案

我们不要假设以下情况

alter table order_item
    add constraint fk_orderitem_orderid
        foreign key (order_id)
        references "ORDER"(id);  


ORA-02298: cannot validate (ALMAT.FK_ORDERITEM_ORDERID) - parent keys not found
*Cause:    an alter table validating constraint failed because the table has
           child records.

这意味着：
您有ORDER_ITEM一个在“ORDER”中不存在的 ORDER_ID。您可以通过以下查询发现这些 ID

SELECT ORDER_ID from "ORDER_ITEM" 
MINUS
SELECT ID from "ORDER"

要摆脱这些行，请执行以下操作

DELETE from  order_item
WHERE ORDER_ID in 
(SELECT ORDER_ID from "ORDER_ITEM" 
MINUS
SELECT ID from "ORDER"
);

1 row deleted.

现在您可以安全地添加约束

alter table order_item
    add constraint fk_orderitem_orderid
        foreign key (order_id)
        references "ORDER"(id);
        
Table ORDER_ITEM altered.

推荐阅读