Oracle / PLSQL: ORA-02449 Error Message

Learn the cause and how to resolve the ORA-02449 error message in Oracle.

Description

When you encounter an ORA-02449 error, the following error message will appear:

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

Cause

You tried to drop a table that is referenced as a parent table by a foreign key.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

This error commonly occurs when you have a parent-child relationship established between two tables through a foreign key. You then have tried to drop the parent table without removing the foreign key.

To correct this problem, you need to drop the foreign key first and then you can drop the parent table.

For example, if you had created the following foreign key (parent-child relationship).

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier (supplier_id)
);

Then you tried to drop the parent table as follows:

DROP TABLE supplier;

You would receive the following error message:

Since the foreign key named fk_supplier exists, you need to first drop this constraint as follows:

ALTER TABLE products
  DROP CONSTRAINT fk_supplier;

Then you can drop the parent table:

DROP TABLE supplier;