ORA-02449 Oracle Drop Table Error

Sometimes when dropping a table in Oracle database by executing DROP TABLE SQL statement, Oracle may return the error ORA-02449 as below:

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

The Oracle error is caused by the attempt to drop a table with unique or primary keys referenced by foreign keys in another table, or in other word, the table that is referenced as a parent table by a foreign key constraint in a parent-child relationship that established between two tables through a foreign key. Oracle does not allow to drop tables referenced by foreign keys of other tables without specifying the CASCADE CONSTRAINTS option in the DROP TABLE statement, or to drop the parent table without first removing the foreign key.

The solution and workaround for the error when you want to drop tables referenced by child tables, is to use the CASCADE CONSTRAINTS option in the DROP TABLE statement. For example:

DROP TABLE table_name CASCADE CONSTRAINTS;

The CASCADE CONSTRAINTS option in the DROP TABLE SQL statement will drop the FOREIGN KEY constraints of the child tables referenced.

Alternatively, you can manually drop and remove the foreign key key constraints in other tables before performing the DROP TABLE operations on the parent table, drop the foreign key constraints in other tables. To check what constraints are referencing a table in Oracle, use the following command:

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = “table_name“;

To drop and delete the user constraints in Oracle use the following command in SQL*Plus, Toad or other SQL tools:

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Share and contribute or get technical support and help at My Digital Life Forums.

Related Articles

• ORA-00942 Table or View Does Not Exist Oracle Error
• Create, Add or Split Oracle Database Partition Fails with ORA-14080 Error
• Oracle Database Import Error 3113/3114
• Oracle ORA-14074 Create or Add New Partition Fails Error
• ORA-01502 Oracle Index in Unusable State
• Oracle PL/SQL ORA-00947 Not Enough Values Error
• Oracle ORA-01658 Unable to Create INITIAL Extent for Segment in Tablespace Error
• ORA-25153 Temporary Tablespace is Empty Error in Oracle
• Manual and Clean Uninstall Oracle for Windows
• Oracle EXP-00091 Error When Export Database

This entry was posted on Wednesday, August 23rd, 2006 at 5:35 pm and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.