ORA-02273: this unique/primary key is referenced by some foreign keys

If you tried to drop the primary key of a table which having foreign keys with another table then you received the following error:

ORA-02273: this unique/primary key is referenced by some foreign keys
SQL> alter table hr.employees drop primary key;
alter table hr.employees drop primary key
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys


1. Check the constraint of other table which using the PRimary key of primary table as Reference constraint.

col constraint_name for a20
col owner for a6
col table_name for a20
col r_owner for a6
col r_constraint_name for a20
select constraint_name,owner,constraint_type,table_name,
r_owner,r_constraint_name
from dba_constraints a where constraint_type='P' and table_name = 'EMPLOYEES';

CONSTRAINT_NAME      OWNER  C TABLE_NAME
-------------------- ------ - --------------------
EMP_EMP_ID_PK        HR     P EMPLOYEES

2. Find other table associated with this primary key so that you can drop the constraints

select constraint_name,owner,constraint_type,table_name,r_constraint_name from dba_constraints where r_constraint_name in (
select constraint_name from dba_constraints a where constraint_type='P' and table_name = 'EMPLOYEES')

CONSTRAINT_NAME      OWNER  C TABLE_NAME
-------------------- ------ - ---------------
DEPT_MGR_FK          HR     R DEPARTMENTS
EMP_MANAGER_FK       HR     R EMPLOYEES
JHIST_EMP_FK         HR     R JOB_HISTORY

3. Drop the constraint associated with Employee table primary key.
Note: I tried by disable the constraint but its not work again giving same error ORA-02273.
ORA-02273: this unique/primary key is referenced by some foreign keys.

Alter table hr.DEPARTMENTS drop constraints DEPT_MGR_FK;

ALTER table hr.employees drop constraints EMP_MANAGER_FK;


4. Now it successfully run after dropping associated foreign keys tables constraint with primark key of employee table.

SQL> alter table hr.employees drop primary key;
Table altered.


Check more detail of foreign constraints with column name

col P_Table for a30
col r_table for a30
col P_constraint for a16
col r_constraint for a16
SELECT c.owner||'-'||a.table_name||'-'|| a.column_name "P_table",
a.constraint_name "P_constraint",
c.r_constraint_name "R_constraint", c.r_owner||'-'||f.table_name||'-'||f.column_name "R_table"
FROM DBA_CONS_COLUMNS A, DBA_CONSTRAINTS C , DBA_CONS_COLUMNS F
where A.CONSTRAINT_NAME = C.CONSTRAINT_NAME and F.CONSTRAINT_NAME = C.r_CONSTRAINT_NAME
and a.table_name= 'EMPLOYEES' and c.owner ='HR'
and C.CONSTRAINT_TYPE = 'R'