Invisible Indexes and its impact on Foreign Keys

Invisible Indexes on Foreign Keys can still be used by Oracle to prevent locking and performance
related issues when delete/update operations are performed on the parent records.

for more information read a very nice article by Richard Foote.
http://richardfoote.wordpress.com/category/invisible-indexes/

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;