DROP TABLE QUERY ORA -02449

Question

DROP TABLE Errors

results in

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

How to drop the table?

Answer 1

Handle referential integrity first.

What will you do with data that references values in a table you'd want to drop?

• if you don't care, then drop foreign key constraint(s), then drop the errors table
• if you do care, then - obviously - you can't drop it

If you don't know how to find foreign keys, have a look here.

Answer 2

ALTER TABLE tablename
DROP PRIMARY KEY CASCADE;

it drops your foreign key or any dependencies in other tables. Only use it if you know you don't need this relation. Once committed it won't rollback.

If you don't want to drop them you can disable them.

ALTER TABLE tablename
DISABLE CONSTRAINT constraintname CASCADE;

You can find the names of constraints by:

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'tablename';

Answer 3

Find out the table where the table you are dropping(Let's say XYZ) is referenced.

You can use the following query to find such tables and constraint name.

select * from user_constraints u
 where u.constraint_type = 'R'
   and u.constraint_name = (select p.constraint_name from user_constraints p
                             where p.table_name = 'XYZ' and p.constraint_type = 'P')