truncat table 清空表时ORA-02266表中的唯一/主键被启用的外键引用

使用delete 时并没有报错，使用truncate 清空重置表时会报错。

SQL>  truncate table te_equip;

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

一般出现这个错误，是因为表中的主键被其它表的外键所引用，导致删除数据时出错

SQL> alter table te_equip disable primary key cascade;

表已更改。

SQL> truncate table te_equip;

表已截掉。

SQL> alter table te_equip enable primary key;

表已更改。
 

SQL> alter table TH_TARGETONLINE enable constraint FK_TH_TARGETONLINE1;

表已更改。

注意外键与约束的区别。

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

select c1.table_name,
       c1.constraint_name,
       dcc1.column_name,
       c2.table_name,
       c2.constraint_name,
       dcc2.column_name
from dba_constraints c1,
     dba_constraints c2,
     dba_cons_columns dcc1,
     dba_cons_columns dcc2
where c1.owner = 'NEUDDC'
  and c1.table_name = 'TE_EQUIP'
  and dcc1.constraint_name = c1.constraint_name
  and dcc1.owner = c1.owner
  and c2.constraint_type = 'R'
  and c2.r_constraint_name = c1.constraint_name
  and dcc2.owner = c2.owner
  and dcc2.constraint_name = c2.constraint_name;

TABLE_NAME                CONSTRAINT_NAME           COLUMN_NAME                   TABLE_NAME                           CONSTRAINT_NAME                COLUMN_NAME
--------------------------- - ------------------------------ ---------------------------------------- ------------------------------ --------------------- --------------------------    ----------------------------------------------
TE_EQUIP                       SYS_C0016598                   SIM                                           TH_TARGETONLINE                FK_TH_TARGETONLINE1            SIM

或是下面的脚本：

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 = 'NEUDDC'
      
   and c1.table_name = 'TE_EQUIP'
      
   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;