oracle 无法drop,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;