January 12, 2011

Find out child table constraint --- Full solving steps for ORA-02266

Some DBA solving the ORA-02266 by doing these steps :
▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼
SQL> truncate table table_name;
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
SQL> alter table table_name disable primary key cascade;
Table altered.

SQL> truncate table table_name;
Table truncated.

SQL> alter table table_name enable primary key;
Table altered.

▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲


But till now work is still not finished yet.
Pay attention to this: 
"alter table table_name disable primary key cascade"  disable the primary key and the foreign key.
"alter table table_name enable primary key "               only enable the primary key.
You can't add " cascade "  to the enable command.
And you need to find out the diabled constraint and enable it again.
See following:

▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼
oracle $ sqlplus kevin/oracle   SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 12 04:01:35 2011                                       
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

duck_11 > create table a(a1 int primary key, a2 int);
Table created.
duck_11 > insert into a values (1,1);
1 row created.
duck_11 > insert into a values (2,2);
1 row created.

duck_11 >  create table b(b1 int,b2 int,constraint fk_supplier foreign key(b1) references a(a1));
Table created.
duck_11 > insert into b values (2,2);
1 row created.
duck_11 > commit;
Commit complete.

duck_11 >  truncate table a;
 truncate table a
                *ERROR at line 1:ORA-02266: unique/primary keys in table referenced by enabled foreign keys
duck_11 >  select CONSTRAINT_NAME from user_constraints  where table_name='A' and CONSTRAINT_TYPE='P';
CONSTRAINT_NAME
------------------------------
SYS_C005896

duck_11 >  col owner format a8
duck_11 > select owner,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME  from user_constraints where R_CONSTRAINT_NAME='SYS_C005896';
OWNER  CONSTRAINT_NAME       C TABLE_NAME
--------    ------------------------------ -  ------------------------------
KEVIN    FK_SUPPLIER                     R B

duck_11 > alter table kevin.b disable constraint FK_supplier;
Table altered.
duck_11 > truncate table a;
Table truncated.

duck_11 > alter table kevin.b enable constraint FK_supplier;
alter table kevin.b enable constraint FK_supplier
                                      *
ERROR at line 1:
ORA-02298: cannot validate (KEVIN.FK_SUPPLIER) - parent keys not found

duck_11 > truncate table b;
Table truncated.
duck_11 >  alter table kevin.b enable constraint FK_supplier;
Table altered.
duck_11 >  truncate table a;
 truncate table a
                *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

duck_11 >  alter table kevin.a disable primary key cascade;
Table altered.
duck_11 > truncate table a;
Table truncated.
duck_11 >  alter table kevin.a enable  primary key;
Table altered.

duck_11 > select CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where CONSTRAINT_NAME='FK_SUPPLIER';
CONSTRAINT_NAME                C  STATUS
-----------------------------------  -  --------
FK_SUPPLIER                             R  DISABLED

duck_11 > alter table kevin.b enable constraint FK_SUPPLIER;
Table altered.
duck_11 > truncate table a;
truncate table a
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

0 Comments:

Post a Comment