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

      这两天在做一个feature正在进行测试阶段，结果run了 一个简单的altertable的脚本后竟然出现一下错误：

[/usr1/arbor/idesign_site/install_scripts]

(145)yyang@dstibm07#./altertable.sh.yy RDCO1104 roadmap1 arbor123
SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 31 04:10:05 2014
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL>
Table truncated.

SQL> SQL>
Table truncated.

SQL> SQL> TRUNCATE table        bill_expression_ref
                *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


SQL> SQL>
Table truncated.

SQL> SQL> TRUNCATE table bill_expression_group_ref
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

脚本里的内容十分简单：

#!/bin/sh
# For DR-2-024-394
DB=$1
USER=$2
PASS=$3

if [ $# -ne 3 ]
then
        echo "\nusage : <DB> <USER> <PASS>\n"
        echo " run this script against each of your databases.\n"
        exit
fi

if [ "$DBMS" =  "oracle" ]
then
sqlplus $USER/$PASS@$DB << THEEND
<pre name="code" class="plain">TRUNCATE table <span style="font-family: Arial, Helvetica, sans-serif;">bill_expression_values;</span>

TRUNCATE table bill_expression_groups;TRUNCATE bill_expression_ref ;TRUNCATE table bill_expression_group_values ;TRUNCATE table bill_expression_group_ref ;quitTHEENDelse        echo "The environment variable DBMS must be set to oracle\n"        exit 1fi

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

delete 操作不会改变表的高水标记，因此如果我们对一个表插入1000万条数据，然后再回滚（对insert操作做回滚相当于相应地做delete操作），会使表的高水标记增长得很高，这时虽然我们操作的表依然是一个空表，但是查询它却会读惊人数量的内存块，实验如下：ETL@RACTEST> create table test_table (a number);Table created.Elapsed: 00:00:00.01ETL@RACTEST> set autotrace traceonly statistics;ETL@RACTEST> select * from test_table;no rows selectedElapsed: 00:00:00.00Statistics----------------------------------------------------------         24  recursive calls          0  db block gets          7  consistent gets          0  physical reads          0  redo size        318  bytes sent via SQL*Net to client        453  bytes received via SQL*Net from client          1  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          0  rows processed可以看到查询这个空表读了7个内存块。然后我们向表中插入1000万条数据后再回滚：insert into test_table select level from dual connect by level<=10000000;10000000 rows created.Elapsed: 00:00:58.38ETL@RACTEST> rollback;Rollback complete.Elapsed: 00:00:01.15ETL@RACTEST> set autotrace traceonly statistics;ETL@RACTEST> select * from test_table;no rows selectedElapsed: 00:00:00.14Statistics----------------------------------------------------------        283  recursive calls          1  db block gets      15463  consistent gets          0  physical reads        176  redo size        318  bytes sent via SQL*Net to client        453  bytes received via SQL*Net from client          1  SQL*Net roundtrips to/from client          4  sorts (memory)          0  sorts (disk)          0  rows processed可以看到，同样是读一个空表，但是却做了15463(consistent gets)+1(db block gets)个逻辑读，可见delete操作不会修改表的高水标记。并且delete操作也很耗费时间，因此我们通常在想清空一个表的数据时用truncate来替代delete。truncate会以一种快速的方式清空表，只产生很少的日志信息，并且会将高水标记清零。例如：ETL@RACTEST> insert into test_table select level from dual connect by level<=10000000;10000000 rows created.Elapsed: 00:00:32.45ETL@RACTEST> commit;Commit complete.Elapsed: 00:00:00.02ETL@RACTEST> truncate table test_table;Table truncated.Elapsed: 00:00:29.52ETL@RACTEST> set autotrace traceonly statistics;ETL@RACTEST> select * from test_table;no rows selectedElapsed: 00:00:00.00Statistics----------------------------------------------------------          1  recursive calls          1  db block gets          6  consistent gets          0  physical reads         96  redo size        318  bytes sent via SQL*Net to client        453  bytes received via SQL*Net from client          1  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          0  rows processedtruncate后再查询只做了7个逻辑读，也就是读了7个内存块。但是truncate操作有一个限制，例如B表有一个外键参照A表的一个字段，那么truncate A表Oracle就会报错，实验如下：create table ttt1 (a number,primary key(a));Table created.Elapsed: 00:00:00.02ETL@RACTEST> insert into ttt1 select level from dual connect by level<=10;10 rows created.Elapsed: 00:00:00.02ETL@RACTEST> commit;Commit complete.Elapsed: 00:00:00.00ETL@RACTEST> select * from ttt1;         A----------         1         2         3         4         5         6         7         8         9        1010 rows selected.Elapsed: 00:00:00.00ETL@RACTEST> create table ttt2 (a number,constraint fk_ttt2_a foreign key(a) references ttt1(a));Table created.这时ttt2表的a字段参照了ttt1表的a字段，我如果用delete操作清空ttt1表的值是可以的，因为ttt2表没有数据，删除ttt1的数据不会违背外键参照完整性。ETL@RACTEST> delete from ttt1;10 rows deleted.但是如果我们使用truncate,oracle则要报错：ETL@RACTEST> truncate table ttt1;truncate table ttt1               *ERROR at line 1:ORA-02266: unique/primary keys in table referenced by enabled foreign keys因为truncate实现的是一种快速删除，因此它无法检验参照完整性，即使删除的数据不违反参照完整性，Oracle也不允许做truncate操作，也就是我们说的宁可错杀一千，不可放过一个，呵呵。我在培训的时候人说外键如果设置了级联删除就可以truncate，但实际上是不行的，实验如下：ETL@RACTEST> drop table ttt2 purge;Table dropped.Elapsed: 00:00:00.03ETL@RACTEST> create table ttt2 (a number,constraint fk_ttt2_a foreign key(a) references ttt1(a) on delete cascade);Table created.Elapsed: 00:00:00.01ETL@RACTEST> truncate table ttt1;truncate table ttt1               *ERROR at line 1:ORA-02266: unique/primary keys in table referenced by enabled foreign keysElapsed: 00:00:00.00这是一种方式的级联删除，trucate仍然报错，下面是另一种级联：ETL@RACTEST> drop table ttt2 purge;Table dropped.Elapsed: 00:00:00.01ETL@RACTEST> create table ttt2 (a number,constraint fk_ttt2_a foreign key(a) references ttt1(a) on delete set null);Table created.Elapsed: 00:00:00.01ETL@RACTEST> truncate table ttt1;truncate table ttt1               *ERROR at line 1:ORA-02266: unique/primary keys in table referenced by enabled foreign keys同样不行，因此得出结论，只要外键参照A表的字段，那么就不能对A表使用truncate操作！

最后必须要改为：

delete from bill_expression_values;

delete from bill_expression_groups;

delete from bill_expression_ref ;

delete from bill_expression_group_values ;

delete from bill_expression_group_ref ;

你可以通过下面脚本查看一下涉及该表主键的外键约束信息。
 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;

另外你也可以用truncate，解决方法是：先禁用表的主键约束，等截断后再启用
1SQL> 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;   9:  
注意事项：在ENABLE主键后不会自动恢复外键（没有cascade选项），因此需要手工对引用该键的约束进行ENABLE。