ORA-02266: unique/primary keys in table referenced by enabled foreign keys and ORA-02297: cannot disable constraint (MIS_OWNER.USR_PK) - dependencies exist

Alert/error :- When I tried to truncate the table/clear the table data, Oracle not allowed me to 
                      cleared the table data, because whenever we creates primary/unique key constraint, 
                      oracle internally linked column data with ROWID, hence oracle not allow to  
                     remove  table data. however if we disable link between column data and ROWID, then
                    it will allow us to truncate the table.




Workaround :-


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


SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,status ,TABLE_NAME from dba_constraints where TABLE_NAME='MIS_USERS' and owner='MIS_OWNER';

CONSTRAINT_NAME                C STATUS   TABLE_NAME
------------------------------ - -------- ------------------------------
SYS_C0024627                   C ENABLED  MIS_USERS
SYS_C0024626                   C ENABLED  MIS_USERS
SYS_C0024625                   C ENABLED  MIS_USERS
SYS_C0024624                   C ENABLED  MIS_USERS
SYS_C0024623                   C ENABLED  MIS_USERS
SYS_C0024622                   C ENABLED  MIS_USERS
SYS_C0024621                   C ENABLED  MIS_USERS
SYS_C0024620                   C ENABLED  MIS_USERS
AVCON_MIS_E_NEW_F_000          C ENABLED  MIS_USERS
USR_UK                         U ENABLED  MIS_USERS
USR_PK                         P ENABLED  MIS_USERS

11 rows selected.

SQL> select ' alter table ' ||owner||'.'||TABLE_NAME|| ' disable constraint '||CONSTRAINT_NAME || ';' from dba_constraints where TABLE_NAME='MIS_USERS' and owner='MIS_OWNER';

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'
--------------------------------------------------------------------------------
 alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024627;
 alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024626;
 alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024625;
 alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024624;
 alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024623;
 alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024622;
 alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024621;
 alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024620;
 alter table MIS_OWNER.MIS_USERS disable constraint AVCON_MIS_E_NEW_F_000;
 alter table MIS_OWNER.MIS_USERS disable constraint USR_UK;
 alter table MIS_OWNER.MIS_USERS disable constraint USR_PK;

11 rows selected.


 SQL> alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024627;
 SQL> alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024626;
 SQL> alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024625;
 SQL> alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024624;
 SQL> alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024623;
 SQL> alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024622;
 SQL> alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024621;
 SQL> alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024620;
 SQL> alter table MIS_OWNER.MIS_USERS disable constraint AVCON_MIS_E_NEW_F_000;
 SQL> alter table MIS_OWNER.MIS_USERS disable constraint USR_UK;

 SQL>  alter table MIS_OWNER.MIS_USERS disable constraint USR_PK
*
ERROR at line 1:
ORA-02297: cannot disable constraint (MIS_OWNER.USR_PK) - dependencies exist

SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,status ,TABLE_NAME from dba_constraints where TABLE_NAME='MIS_USERS' and owner='MIS_OWNER';

CONSTRAINT_NAME                C STATUS   TABLE_NAME
------------------------------ - -------- ------------------------------
SYS_C0024627                   C DISABLED MIS_USERS
SYS_C0024626                   C DISABLED MIS_USERS
SYS_C0024625                   C DISABLED MIS_USERS
SYS_C0024624                   C DISABLED MIS_USERS
SYS_C0024623                   C DISABLED MIS_USERS
SYS_C0024622                   C DISABLED MIS_USERS
SYS_C0024621                   C DISABLED MIS_USERS
SYS_C0024620                   C DISABLED MIS_USERS
AVCON_MIS_E_NEW_F_000          C DISABLED MIS_USERS
USR_UK                         U DISABLED MIS_USERS
USR_PK                         P ENABLED  MIS_USERS

11 rows selected.

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

SQL> alter table MIS_OWNER.MIS_USERS disable constraint USR_PK cascade;

Table altered.


SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,status ,TABLE_NAME from dba_constraints where TABLE_NAME='MIS_USERS' and owner='MIS_OWNER';

CONSTRAINT_NAME                C STATUS   TABLE_NAME
------------------------------ - -------- ------------------------------
SYS_C0024627                   C DISABLED MIS_USERS
SYS_C0024626                   C DISABLED MIS_USERS
SYS_C0024625                   C DISABLED MIS_USERS
SYS_C0024624                   C DISABLED MIS_USERS
SYS_C0024623                   C DISABLED MIS_USERS
SYS_C0024622                   C DISABLED MIS_USERS
SYS_C0024621                   C DISABLED MIS_USERS
SYS_C0024620                   C DISABLED MIS_USERS
AVCON_MIS_E_NEW_F_000          C DISABLED MIS_USERS
USR_UK                         U DISABLED MIS_USERS
USR_PK                         P DISABLED MIS_USERS

11 rows selected.

SQL> truncate table MIS_OWNER.MIS_USERS;

Table truncated.

========================= THANK YOU  ===================================