ORA-02266: unique/primary keys in table referenced by enabled foreign keys这篇博客是很早之前总结的一篇文章，最近导数时使用TRUNCATE清理主表数据又遇到了这个错误，发现还有其它解决方案：

 

 

 

a) 禁用与主表相关的外键约束

 

b) TRUNCATE TABLE

 

c) 启用那些外键约束。

 

 

在实际操作中，发现使用上面的流程操作虽然正确，但是要写很多脚本，有些主表中的字段可能是多个表的外键约束。那么我们必须写多个脚本，那么我们必须使用脚本批量生成。

 

快速解决问题，具体如下所示：。

 

 

--生成禁用约束的脚本,解决ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SELECT DC.OWNER           AS "PARENT_TABLE_OWNER",
       DC.TABLE_NAME      AS "PARENT_TABLE_NAME",
       DC.CONSTRAINT_NAME AS "PRIMARY CONSTRAINT NAME",
       DF.CONSTRAINT_NAME AS "REFERENCED CONSTRAINT NAME",
       DF.OWNER           AS "CHILD_TABLE_OWNER",
       DF.TABLE_NAME      AS "CHILD_TABLE_NAME" ,
       'ALTER TABLE ' || DF.OWNER  || '.' || DF.TABLE_NAME || ' DISABLE CONSTRAINT ' || DF.CONSTRAINT_NAME || ';'
FROM   DBA_CONSTRAINTS DC,
       (SELECT C.OWNER,
               C.CONSTRAINT_NAME,
               C.R_CONSTRAINT_NAME,
               C.TABLE_NAME
        FROM   DBA_CONSTRAINTS C
        WHERE  CONSTRAINT_TYPE = 'R') DF
WHERE  DC.CONSTRAINT_NAME = DF.R_CONSTRAINT_NAME
       AND DC.OWNER =UPPER('&OWNER')
       AND DC.TABLE_NAME=UPPER('&TABLE_NAME');

 

 

 

--生成启用约束的脚本,解决ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SELECT DC.OWNER           AS "PARENT_TABLE_OWNER", 

       DC.TABLE_NAME      AS "PARENT_TABLE_NAME", 

       DC.CONSTRAINT_NAME AS "PRIMARY CONSTRAINT NAME", 

       DF.CONSTRAINT_NAME AS "REFERENCED CONSTRAINT NAME", 

       DF.OWNER           AS "CHILD_TABLE_OWNER", 

       DF.TABLE_NAME      AS "CHILD_TABLE_NAME" ,

       'ALTER TABLE ' || DF.OWNER  || '.' || DF.TABLE_NAME || ' ENABLE CONSTRAINT ' || DF.CONSTRAINT_NAME || ';'

FROM   DBA_CONSTRAINTS DC, 

       (SELECT C.OWNER, 

       &nbs