Tuesday, March 31, 2009

ORA-02449: unique/primary keys in table referenced by foreign keys

When you hit this error, it means that you can not drop(modify) current table (or index) because there are some foreign keys defined on some other tables referencing unique/primary keys of the current table. You should drop these foreign keys in order to drop/modify current table/index.

SQL script to find out these foreign keys:

select * from dba_constraints where R_CONSTRAINT_NAME in (select CONSTRAINT_NAME from dba_constraints where owner='MYOWNER' and table_name='MYTABLE') and CONSTRAINT_TYPE='R';

1 comment:

Leslie Lim said...

Thank you for posting some kind of information. It was really helpful since I am doing some research now.