Thursday, January 29, 2009

ORA-02297: cannot disable constraint -dependencies exist

Problem Description
Whenever you try to disable a constraint of a table it fails with error message ORA-02297: cannot disable constraint -dependencies exist as below.

SQL> alter table transaction disable constraint TRANSACTION_PK;
alter table transaction disable constraint TRANSACTION_PK
*
ERROR at line 1:
ORA-02297: cannot disable constraint (OMS.TRANSACTION_PK) - dependencies exist

Cause of the Problem
Disable constraint command fails as the table is parent table and it has foreign key that are dependent on this constraint.

Solution of the Problem
Two solutions exist for this problem.
1)Find foreign key constraints on the table and disable those foreign key constraints and then disable this table constraint.

Following query will check dependent table and the dependent constraint name. After that disable child first and then parent constraint.

SQL> SELECT p.table_name "Parent Table", c.table_name "Child Table",
2 p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
3 FROM user_constraints p
4 JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)
5 WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
6 AND c.constraint_type = 'R'
7 AND p.table_name = UPPER('&table_name');

Enter value for table_name: transaction
old 7: AND p.table_name = UPPER('&table_name')
new 7: AND p.table_name = UPPER('transaction')

Parent Table Child Table Parent Constraint Child Constraint
------------------------------ ------------------------------ ------------------------------ ------------------------------
TRANSACTION USER_SALARY_RECORD TRANSACTION_PK SYS_C005564
TRANSACTION TRANSACTION_DETAIL TRANSACTION_PK TRNSCTN_DTL_TRNSCTN_FK

SQL> alter table USER_SALARY_RECORD disable constraint SYS_C005564;
Table altered.

SQL> alter table TRANSACTION_DETAIL disable constraint TRNSCTN_DTL_TRNSCTN_FK;
Table altered.

SQL> alter table transaction disable constraint TRANSACTION_PK;
Table altered.

2)Disable the constraint with cascade option.
SQL> alter table transaction disable constraint TRANSACTION_PK cascade;
Table altered.

No comments:

Post a Comment