ANBOB™

今天开发的问我说有几个表清空truncate 不可以，出于对HWM的影响，清空表想到的是truncate 而不是delete这点很好，但是有启用了外键参照的表还只能delete不能truncate.

for example:

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


SQL> !oerr ora 2266                                                                                                                                                              
02266, 00000, "unique/primary keys in table referenced by enabled foreign keys"
// *Cause: An attempt was made to truncate a table with unique or 
//         primary keys referenced by foreign keys enabled in another table.
//         Other operations not allowed are dropping/truncating a partition of a
//         partitioned table or an ALTER TABLE EXCHANGE PARTITION.
// *Action: Before performing the above operations the table, disable the 
//          foreign key constraints in other tables. You can see what 
//          constraints are referencing a table by issuing the following 
//          command:
//          SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

SQL> desc ICME_AWARD_SCORE_STANDARD                                                                                                                                              
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 AWARD_SCORE_STANDARD_ID                               NOT NULL NUMBER(38)
 AWARD_SCORE_STANDARD_NAME                             NOT NULL VARCHAR2(500)
 SCORE_TYPE_ID                                         NOT NULL NUMBER(38)
 AWARD_SCORE_STANDARD_REMARK                                    VARCHAR2(200)
 ORG_ID                                                NOT NULL NUMBER(38)
 ADMIN_ID                                              NOT NULL NUMBER(38)
 YEAR_ID                                               NOT NULL NUMBER(38)
 AWARD_SCORE_STANDARD_TYPE                                      NUMBER(38)


SQL> select constraint_name,constraint_type from user_constraints where table_name='ICME_AWARD_SCORE_STANDARD';                                                                  

CONSTRAINT_NAME                C
------------------------------ -
PK_ICME_AWARD_SCORE_STANDARD   P
SYS_C00197937                  C
SYS_C00197938                  C
SYS_C00197939                  C
SYS_C00197940                  C
SYS_C00197941                  C
SYS_C00197942                  C

7 rows selected.

SQL> delete ICME_AWARD_SCORE_STANDARD;                                                                                                                                           

0 rows deleted.

SQL> select table_name,constraint_name,constraint_type from user_constraints where r_constraint_name='PK_ICME_AWARD_SCORE_STANDARD';                                             

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
ICME_SCORE_AWARD_STANDARD      FK_ICME_SCO_REFERENCE_ICME_AWA R

SQL> truncate table ICME_SCORE_AWARD_STANDARD;                                                                                                                                   

Table truncated.

SQL> truncate table ICME_AWARD_score_STANDARD;                                                                                                                                   
truncate table ICME_AWARD_score_STANDARD
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
                                                                                                                                                                           
SQL> alter table ICME_SCORE_AWARD_STANDARD  disable constraint FK_ICME_SCO_REFERENCE_ICME_AWA;                                                                                   

Table altered.

SQL> truncate table ICME_AWARD_score_STANDARD;                                                                                                                                   

Table truncated.

SQL> alter table ICME_SCORE_AWARD_STANDARD  enable constraint FK_ICME_SCO_REFERENCE_ICME_AWA;                                                                                    

Table altered.

或者
SQL> alter table ICME_AWARD_score_STANDARD disable primary key cascade;                                                                                                          

Table altered.

SQL> truncate table ICME_AWARD_score_STANDARD;                                                                                                                                   

Table truncated.


SQL> select table_name,constraint_name,constraint_type,status from user_constraints where r_constraint_name='PK_ICME_AWARD_SCORE_STANDARD';                                      

TABLE_NAME                     CONSTRAINT_NAME                C STATUS
------------------------------ ------------------------------ - --------
ICME_SCORE_AWARD_STANDARD      FK_ICME_SCO_REFERENCE_ICME_AWA R DISABLED


SQL> alter table ICME_AWARD_score_STANDARD enable primary key ;                                                                                                                  

Table altered.

SQL> select table_name,constraint_name,constraint_type,status from user_constraints where r_constraint_name='PK_ICME_AWARD_SCORE_STANDARD';                                      

TABLE_NAME                     CONSTRAINT_NAME                C STATUS
------------------------------ ------------------------------ - --------
ICME_SCORE_AWARD_STANDARD      FK_ICME_SCO_REFERENCE_ICME_AWA R DISABLED

SQL> alter table ICME_SCORE_AWARD_STANDARD enable constraint FK_ICME_SCO_REFERENCE_ICME_AWA;                                                                                     

Table altered.

SQL> select table_name,constraint_name,constraint_type,status from user_constraints where r_constraint_name='PK_ICME_AWARD_SCORE_STANDARD';                                      

TABLE_NAME                     CONSTRAINT_NAME                C STATUS
------------------------------ ------------------------------ - --------
ICME_SCORE_AWARD_STANDARD      FK_ICME_SCO_REFERENCE_ICME_AWA R ENABLED

note:
oracle 不允许truncate 一个启用外键引用的父表，因为truncate 不会触发任何trigger和任何constraint验证，当然它也不会关心子表是不是真的为空或是不是没有参照。

第二种方法网上有部分教程是只写到enable PK,不要只启用了primary key就不管了，级连禁用了外键也不要忘了再启用回来.

打赏

微信扫一扫，打赏作者吧～