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

ORA-02449: unique/primary keys in table referenced by foreign keys error occurs when you try to drop the parent table without removing the foreign key in a parent-child relationship established between two tables. Two tables are created with a parent-child relationship through a foreign key. You try to drop the parent table without removing the foreign key.

When two tables create a parent-child relationship, a constraint is created and enforced. If the parent table is dropped, the constraint prevents the table from being dropped. The parent table is required for the existence of the child table. Before removing the parent table, the parent-child relationship should be removed from the constraint.

As long as the child table exists in the parent-child relationship, the parent table cannot be removed. The foreign key column in the child table is reliant on the parent table’s parent key. The child table should be deleted, or the parent-child relation in the parent table should be removed. The parent table cannot be dropped if this condition is not met.

Cause

An attempt was made to drop a table with unique or primary keys referenced by foreign keys in another table.

Action

Before performing the above operations the table, drop the foreign key constraints in other tables. You can see what constraints are referencing a table by issuing the following
command:
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = “tabnam”;



The Problem

A parent-child relationship is established between two tables in the Oracle database using a foreign key. The reference constraint enforces the parent-child relationship between the two tables. The child table is dependent on the parent table. If the parent table is dropped from a parent-child relationship, the child table cannot exist. The parent table cannot be dropped until the foreign key constraint is removed.

create table dept
(
 id numeric(5) primary key,
 name varchar2(100)
);

create table employee
(
  id numeric(5) primary key,
  name varchar2(100),
  deptid numeric(5) references dept(id)
);

drop table dept;

Error

Error starting at line : 2 in command -
drop table dept
Error report -
ORA-02449: unique/primary keys in table referenced by foreign keys
02449. 00000 -  "unique/primary keys in table referenced by foreign keys"
*Cause:    An attempt was made to drop a table with unique or
           primary keys referenced by foreign keys in another table.
*Action:   Before performing the above operations the table, drop the
           foreign key constraints in other tables. You can see what
           constraints are referencing a table by issuing the following
           command:
           SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";


Solution 1

The two tables, as well as the constraint name, must be known before dropping the parent table. The SQL below displays the names of the tables involved in the parent-child relationship as well as the names of the foreign key constraints. If you know the name of the foreign key constraint, you may delete it.

select r.constraint_name Foreign_key_constraint,
    p.owner parent_owner, p.table_name parent_table, pc.column_name parent_column_name, 
    r.owner child_owner, r.table_name child_table, rc.column_name child_colum_name
from user_constraints p
join user_cons_columns pc on p.owner=pc.owner 
        and p.table_name=pc.table_name and p.constraint_name = pc.constraint_name
        and p.constraint_type='P'
join user_constraints r on p.constraint_name=r.r_constraint_name and r.constraint_type='R'
join user_cons_columns rc on r.owner=rc.owner 
        and r.table_name=rc.table_name and r.constraint_name = rc.constraint_name
        and r.constraint_type='R'
where p.table_name='DEPT' 
order by p.owner, p.table_name, pc.column_name, rc.position;
Foreign_key_constraint | parent_owner |parent_table | parent_column_name |child_owner | child_table | child_colum_name
SYS_C0012548	HR	DEPT	ID	HR	EMPLOYEE	DEPTID


Solution 2

The foreign-key constraint is used to establish the parent-child relationship. The foreign key constraint is used to enforce the relationship. The parent-child connection will not exist if the foreign key constraint is removed. The parent table may be dropped.

ALTER TABLE employee DROP CONSTRAINT SYS_C0012548;

drop table dept;

Output

Table DEPT created.


Solution 3

If the child table in the parent-child relation is not necessary, it can be removed before the parent table. To begin, all of the child tables should be deleted. The parent tables can be deleted if the child tables do not exist.

drop table employee;
drop table dept;

Output

Table EMPLOYEE dropped.
Table DEPT dropped.


Solution 4

Oracle will allow you to cascade drop tables in a parent-child relationship. When the parent table is dropped through the cascade option, all of the child tables are also dropped. This command will not be used unless you are well familiar with the database table foreign key relationship. If the constraint are dropped, they can no longer be retrieved from the Oracle database.

drop table dept cascade constraints;

Output

Table DEPT dropped.



Leave a Reply