ORA-02297: cannot disable constraint -dependencies exist

I got ” ORA-02297: cannot disable constraint -dependencies exist ”  error in Oracle database.

ORA-02297: cannot disable constraint -dependencies exist

Details of error are as follows.

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

*Cause: an alter table disable constraint failed becuase the table has foriegn keys that are dependent on this constraint.

*Action: Either disable the foreign key constraints or use disable cascade

cannot disable constraint -dependencies exist

This ORA-02297 error is related with the alter table disable constraint failed becuase the table has foriegn keys that are dependent on this constraint.

You can use the Following query to find dependent table and the dependent constraint name.

If you find it,  disable child constraint firstly and then disable 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')
/

Disable Constraint with Cascade option

To solve this error, Either disable the foreign key constraints or use disable cascade as follows.

SQL> alter table TABLE_NAME disable constraint CONSTRAINT_NAME;
Table altered.



SQL> alter table TABLE_NAME disable constraint CONSTRAINT_NAME cascade;
Table altered.

Do you want to learn Oracle Database for Beginners, then read the following articles.