How to resolve ORA-02449 Error Message in Oracle database

Learn the reason and how to unravel the ORA-02449 error message in Oracle.

Description

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

ORA-02449: unique/primary keys in desk referenced by way of foreign keys

Cause

You tried to drop a desk that is referenced as a guardian desk by a overseas key.

Resolution

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

Option #1

This error often occurs when you have a parent-child relationship hooked up between two tables thru a overseas key. You then have tried to drop the parent desk besides doing away with the overseas key.

To right this problem, you need to drop the overseas key first and then you can drop the father or mother table.

For example, if you had created the following overseas 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 guardian desk as follows:

DROP TABLE supplier;

You would receive the following error message:

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

ALTER TABLE products
  DROP CONSTRAINT fk_supplier;

Then you can drop the parent table:

DROP TABLE supplier;