Question

如何解决Ora-02298 错误

Answer 1

我是在完成impdp 导入后遇到的这个问题，但是使用很多导入工具后也会出现这个问题。
ORA-02298: 未找到父项关键字
ORA 02291: 违反完整约束条件 (SCOTT.SYS_C005172) 未找到父项关键字
ORA-02298: 未找到父项关键字

IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "EMPLOYEE_SCHED_DTL" ENABLE CONSTRAINT "FK_ESCHD_EMPSKD_ID""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (WORKBRAIN.FK_ESCHD_EMPSKD_ID) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "EMP_SCHD_DTL_LAYER" ENABLE CONSTRAINT "FK_ESCHDL_EMPSKDID""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (WORKBRAIN.FK_ESCHDL_EMPSKDID) - parent keys not found
Import terminated successfully with warnings.
“
原因:

你要插入的表A里，有外键连接到另一个表B的主键， 你在表A的外键列插入的值 在表B的主键列找不到就不能插入。
这往往由于表在不同的时间点导出，主表中有新的业务数据。

解决方法:

如果可以删除主表中的多余记录，保证主表和子表一致

1: 下面的语句根据索引关联信息表生成删除语句

SELECT ' delete from '
||a.table_name
||' a where not exists ( select 1 from '
||c_pk.table_name
|| ' b where b.'
|| b.column_name
||'=a.'
||a.column_name
||');'
FROM user_cons_columns a
JOIN user_constraints c
ON a.constraint_name = c.constraint_name
JOIN user_constraints c_pk
ON c.r_constraint_name = c_pk.constraint_name
JOIN user_cons_columns b
ON c_pk.constraint_name = b.constraint_name
WHERE c.constraint_type = 'R'
AND a.table_name = '&Table_Name'
AND a.constraint_name ='&FK_NAME';

There are two “Parent keys not found error” , so we generate two causes :

delete from EMPLOYEE_SCHED_DTL a where not exists ( select 1 from EMPLOYEE_SCHEDULE b where b.EMPSKD_ID=a.EMPSKD_ID);
delete from EMP_SCHD_DTL_LAYER a where not exists ( select 1 from EMPLOYEE_SCHEDULE b where b.EMPSKD_ID=a.EMPSKD_ID);

2: 重新Enable约束:

ALTER TABLE "EMPLOYEE_SCHED_DTL" ENABLE CONSTRAINT "FK_ESCHD_EMPSKD_ID";
ALTER TABLE "EMP_SCHD_DTL_LAYER" ENABLE CONSTRAINT "FK_ESCHDL_EMPSKDID”;

3:如果错误如下（约束没有建立 ）需要先创建约束但是不要Enable，然后再做步骤1和2

ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (WORKBRAIN.FK_RCPMSG_MSG_ID) - parent keys not found
Failing sql is:
ALTER TABLE "WORKBRAIN"."RECIPIENT_MESSAGE" ADD CONSTRAINT "FK_RCPMSG_MSG_ID" FOREIGN KEY ("MSG_ID") REFERENCES "WORKBRAIN"."MESSAGE" ("MSG_ID") ON DELETE CASCADE ENABLE

create this constraint with DISABLE option and then execute step 1 and 2 :
ALTER TABLE "WORKBRAIN"."RECIPIENT_MESSAGE" ADD CONSTRAINT "FK_RCPMSG_MSG_ID" FOREIGN KEY ("MSG_ID") REFERENCES "WORKBRAIN"."MESSAGE" ("MSG_ID") ON DELETE CASCADE DISABLE;

转载

Answer 2

你要插入的表A里，有外键连接到另一个表B的主键， 你在表A的外键列插入的值 在表B的主键列找不到就不能插入。
改表A里面有外间约束那个栏位的值，或者改表B（新增一条你要插入的那个外键的值的记录），或者直接删除你的外键约束，根据你的业务需求来做