在数据库里面使用TRUNCATE命令截断一个表的数据时，遇到如下错误
SQL >TRUNCATE TABLE ESCMOWNER.SUBX_ITEM
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
有时候对应的中文错误提示为：ORA-02266: 表中的唯一/主键被启用的外部关键字引用，一般出现这个错误，是因为表中的主键被其它表的外键所引用，导致删除数据时出错。
此时，你可以通过下面脚本查看一下涉及该表主键的外键约束信息。
1: 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 = 'OWNER_NAME'
14: and c1.table_name = 'TABLE_NAME'
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;
查询结果如下所示：
1: SQL> select c1.table_name as org_table_name,
2: 2 c1.constraint_name as org_constraint_name,
3: 3 c1.constraint_type as org_constriant_type,
4: 4 n1.column_name as org_colun_name,
5: 5 c2.table_name as ref_table_name,
6: 6 c2.constraint_type as ref_constraint_type,
7: 7 c2.constraint_name as ref_constraint_name,
8: 8 n2.column_name as ref_column_name
9: 9 from dba_constraints c1,
10: 10 dba_constraints c2,
11: 11 dba_cons_columns n1,
12: 12 dba_cons_columns n2
13: 13 where c1.owner = 'ESCMOWNER'
14: 14 and c1.table_name = 'SUBX_ITEM'
15: 15 and n1.constraint_name = c1.constraint_name
16: 16 and n1.owner = c1.owner
17: 17 and c2.constraint_type = 'R'
18: 18 and c2.r_constraint_name = c1.constraint_name
19: 19 and n2.owner = c2.owner
20: 20 and n2.constraint_name = c2.constraint_name;
21:
22: ORG_TABLE_NAME ORG_CONSTRAINT_NAME ORG_CONSTRIANT_TYPE ORG_COLUN_NAME REF_TABLE_NAME REF_CONSTRAINT_TYPE REF_CONSTRAINT_NAME REF_COLUMN_NAME
23: -------------- ------------------- ------------------- ---------------- -------------- ------------------- -------------------
24: SUBX_ITEM PK_SUBX_ITEM P ITEM_ID SUBX_DIMM R FK_SUBX_DIMM ITEM_ID
25:
26: SQL>
解决方法：先禁用表的主键约束，等截断后再启用
1: SQL> ALTER TABLE ESCMOWNER.SUBX_ITEM DISABLE PRIMARY KEY CASCADE;
2:
3:
4: SQL>TRUNCATE TABLE ESCMOWNER.SUBX_ITEM
5:
6: SQL>ALTER TABLE ESCMOWNER.SUBX_ITEM ENABLE PRIMARY KEY;
7:
8: SQL>ALTER TABLE ESCMOWNER.SUBX_DIMM ENABLE CONSTRAINT FK_SUBX_DIMM;
9:
注意事项：在ENABLE主键后不会自动恢复外键（没有cascade选项），因此需要手工对引用该键的约束进行ENABLE。
