ORA-02449: Solving the DROP TABLE Dilemma

May 2, 2013

in DBA, DDL, Dependencies, Exceptions, ORA Errors

Drop table error ORA-02449 keys in table referenced by foreign keys

Sometimes you want to drop and recreate an Oracle table, and are in a fix because when you issue the DROP command Oracle responds with ORA-02449:

 SQL> drop table agreement;
drop table agreement
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

Disabling constraints has no effect on ORA-02449, the only resolution is to drop constraints. Which presents another dilemma: the error does not specify which foreign keys are causing this problem, and from which tables.

Luckily, there is a quick workaround to ORA-02449, provided you are ready to heed the risks of the approach along with it.

How do you get past ORA-02449?

The shortest way out of this problem is the CASCADE CONSTRAINTS option (read the note “Cascade Constraints  with Caution” listed below, before you try it!)

SQL> drop table agreement cascade constraints;

Table dropped.

CASCADE CONSTRAINTS silently forces all related Foreign Key constraints to be dropped at the time of dropping a table.

Cascade Constraints with Caution

When you drop a table with CASCADE CONSTRAINTS, all related Foreign Key constraints get dropped. When you recreate the dropped table, the Foreign Key constraints do not reappear and must be recreated. There is a high risk of messing up referential integrity if the dropped and recreated table has many dependent tables.

To retain the Foreign Key details as-is when the referenced table is dropped and recreated, you will have to do a bit more along with CASCADE CONSTRAINTS to ascertain all the Foreign Keys getting impacted due to DROP TABLE, and recreate them later.

In the next post, we will see how to get a list of Foreign Keys referencing a specified table and how to easily recreate the dropped Foreign Keys.

{ 2 comments… read them below or add one }

Mohiadeen Jamil December 12, 2013 at 9:49 am

Hi Oratable,
Nice post:)

marge August 16, 2014 at 12:46 am

The sql you show does not take into account all of the possible parameters (ie, RELY DISABLE NOVALIDATE, DELETE NO ACTION, etc.) We have all different paramaters in use, not just the default rules. It would be able to gen the add sql with all possible parameter combos…..

Leave a Comment

Previous post:

Next post: