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.



3 Responses to “ORA-02449 Oracle Drop Table Error”

  1. vipin
    February 8th, 2007 18:51
    1

    what if after dropping the table using the query blow you want again all those constraints.

    DROP TABLE table_name CASCADE CONSTRAINTS;

    please tell the query if u knows.

  2. f
    July 3rd, 2008 06:30
    2

    Constrangimentos? Orra, meu revisa essa tradução aí e testa antes de postar!!! Se não seja honesto e indica o link de onde pegou!!!

  3. sangam
    July 24th, 2008 13:21
    3

    Great! The information provided is very helpful. It seems to be simple but really halps an lot. Thanks again.

Leave a Reply

You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Subscribe without commenting


Custom Search

New Articles

Incoming Search Terms for the Article

ORA-02449 - ORA-02449: unique/primary keys in table referenced by foreign keys - oracle drop table - drop table oracle - ORA-02449: unique/primary keys in table referenced by foreign keys - ORA-02449: - drop table cascade constraints - oracle drop statement - ora 02449 - oracle delete table - oracle drop table cascade - oracle ORA-02449 - oracle drop - oracle drop all tables - oracle drop table cascade constraints - all - drop table in oracle - oracle drop table force - delete cascade Oracle - drop table cascade constraints oracle - SQL Error: ORA-02449: unique/primary keys in table referenced by foreign keys - drop table toad - cascade constraints in oracle - oracle delete cascade constraint - error DROP TABLE - oracle delete from cascade constraints - ORA-02449 + oracle - drop table cascade oracle - ORA-02449: unique/primary keys in table referenced by foreign key - drop table + oracle - drop table with cascade option - oracle cascade constraints - oracle drop tables - 02449 - delete table entry Oracle - oracle drop all table ora-02449 - ORA-02449: - removing CONSTRAINT IN ORACLE - oracle drop table with foreign keys - oracle exp drop - drop table and constraints - drop table cascade + Ora - oracle drop table constraints - drop table with foreign keys in Oracle - drop table ORA-02449 - ora-02449 drop table - Drop table in Oracle - oracle drop index before drop table - oracle drop table ORA-02449 - delete oracle cascade -