ORA-02266: unique/primary keys in table referenced by enabled foreign keys

在数据库里面使用TRUNCATE命令截断一个表的数据时，遇到如下错误

SQL >TRUNCATE TABLE ESCMOWNER.SUBX_ITEM

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

有时候对应的中文错误提示为：ORA-02266: 表中的唯一/主键被启用的外部关键字引用，一般出现这个错误，是因为表中的主键被其它表的外键所引用，导致删除数据时出错。

此时，你可以通过下面脚本查看一下涉及该表主键的外键约束信息。

select c1.table_name      as org_table_name,
       c1.constraint_name as org_constraint_name,
       c1.constraint_type as org_constriant_type,
       n1.column_name     as org_colun_name,
       c2.table_name      as ref_table_name,
       c2.constraint_type as ref_constraint_type,
       c2.constraint_name as ref_constraint_name,
       n2.column_name     as ref_column_name
  from dba_constraints  c1,
       dba_constraints  c2,
       dba_cons_columns n1,
       dba_cons_columns n2
 where c1.owner = 'OWNER_NAME'
   and c1.table_name = 'TABLE_NAME'
   and n1.constraint_name = c1.constraint_name
   and n1.owner = c1.owner
   and c2.constraint_type = 'R'
   and c2.r_constraint_name = c1.constraint_name
   and n2.owner = c2.owner
   and n2.constraint_name = c2.constraint_name;

查询结果如下所示：

SQL> select c1.table_name      as org_table_name,
  2         c1.constraint_name as org_constraint_name,
  3         c1.constraint_type as org_constriant_type,
  4         n1.column_name     as org_colun_name,
  5         c2.table_name      as ref_table_name,
  6         c2.constraint_type as ref_constraint_type,
  7         c2.constraint_name as ref_constraint_name,
  8         n2.column_name     as ref_column_name
  9    from dba_constraints  c1,
 10         dba_constraints  c2,
 11         dba_cons_columns n1,
 12         dba_cons_columns n2
 13   where c1.owner = 'ESCMOWNER'
 14     and c1.table_name = 'SUBX_ITEM'
 15     and n1.constraint_name = c1.constraint_name
 16     and n1.owner = c1.owner
 17     and c2.constraint_type = 'R'
 18     and c2.r_constraint_name = c1.constraint_name
 19     and n2.owner = c2.owner
 20     and n2.constraint_name = c2.constraint_name;

ORG_TABLE_NAME   ORG_CONSTRAINT_NAME  ORG_CONSTRIANT_TYPE ORG_COLUN_NAME  REF_TABLE_NAME  REF_CONSTRAINT_TYPE REF_CONSTRAINT_NAME REF_COLUMN_NAME
--------------   ------------------- ------------------- ---------------- --------------  ------------------- -------------------
SUBX_ITEM             PK_SUBX_ITEM           P               ITEM_ID         SUBX_DIMM            R                   FK_SUBX_DIMM                   ITEM_ID

SQL>

解决方法：先禁用表的主键约束，等截断后再启用

SQL> ALTER TABLE ESCMOWNER.SUBX_ITEM DISABLE PRIMARY KEY CASCADE;


SQL>TRUNCATE TABLE ESCMOWNER.SUBX_ITEM

SQL>ALTER TABLE ESCMOWNER.SUBX_ITEM ENABLE PRIMARY KEY;

SQL>ALTER TABLE ESCMOWNER.SUBX_DIMM ENABLE CONSTRAINT FK_SUBX_DIMM;

注意事项：在ENABLE主键后不会自动恢复外键（没有cascade选项），因此需要手工对引用该键的约束进行ENABLE。