ORA-02297: cannot disable constraint ( .. ) - dependencies exist

SQL> alter table scott.employee disable constraint employee_pk ;

ORA-02297: cannot disable constraint (SCOTT.EMPLOYEE_PK) - dependencies exist

    

    

Problem:

Disable constraint command fails as the table is parent table and it has foreign

key that are dependent on this constraint.

Fix:

There are two things we can do here.

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.

SELECT p.table_name "Parent Table", c.table_name "Child Table",

p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"

FROM user_constraints p

JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)

WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')

AND c.constraint_type = 'R' AND p.table_name = UPPER('&table_name')

/

The following query will generate a script to drop the child constraints

select 'alter table '||c.table_name||' disable constraint '||c.constraint_name||' ;'

FROM user_constraints p

JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)

WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')

AND c.constraint_type = 'R' AND p.table_name = UPPER('&table_name')

/

2)Disable the constraint with cascade option.

SQL> alter table transaction disable constraint EMPLOYEE_PK cascade;</b>

Handy one more Query ^^^

SELECT p.owner, p.table_name "Parent Table", c.table_name "Child Table",

p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"

FROM all_constraints p

JOIN all_constraints c ON(p.constraint_name=c.r_constraint_name)

WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')

AND c.constraint_type = 'R'

--AND p.OWNER = ''

AND p.table_name = UPPER('&table_name');