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

1 Flares Twitter 0 Facebook 0 Google+ 1 LinkedIn 0 Buffer 0 Email -- Filament.io 1 Flares ×

ORA-02449: unique/primary keys in table referenced by foreign keys‘ error occurs when we try to drop a parent table which is having child tables/table (foreign key relations).
Let us create a scenario which causes ORA-02449 error.

Let us create an EMPLOYEE table which holds the employee information like name ,id and salary.
Here emp_id is the primary key column.

SQL> create table EMPLOYEE(
name VARCHAR2(50),
emp_id NUMBER,
salary NUMBER(8,2),
CONSTRAINT EMPLOYEE_ID_PK PRIMARY KEY (emp_id));

Table created

Now create an Employee address table which holds the address details of the employees.The address table is linked to EMPLOYEE table through the foreign key emp_id;

SQL> create table EMPLOYEE_ADDRESS(
address varchar2(200),
emp_id number,
CONSTRAINT EMP_ADD_FK FOREIGN KEY (emp_id) REFERENCES EMPLOYEE(emp_id));

Table created

Now let us insert one record in EMPLOYEE table.

SQL> insert into EMPLOYEE values('ABCD',1,12345);

1 row inserted

Now let us add address for this particular employee in EMPLOYEE_ADDRESS table.

SQL> insert into EMPLOYEE_ADDRESS values('abcd efgh',1);
1 row inserted

Now let us try to delete the Parent table ‘EMPLOYEE’.

SQL> drop table EMPLOYEE;
ORA-02449: unique/primary keys in table referenced by foreign keys

Now drop the EMPLOYEE_ADDRESS table first.

SQL> drop table EMPLOYEE_ADDRESS;
Table dropped

Now drop the EMPLOYEE table.

SQL> drop table EMPLOYEE;
Table dropped

The ORA-02449 will occur even if there is no records in related tables.

Related Articles,ORA-02449: unique/primary keys in table referenced by foreign
ORA-02292: integrity constraint violated – child record found
ORA-02291: integrity constraint violated – parent key not Found
Oracle/PLSQL: Foreign Keys | Oracle Referential Integrity
Oracle/PLSQL: Composite Primary Key
Oracle/PLSQL: Primary Key and Composite Primary Key

Technorati Tags:
,

1 Flares Twitter 0 Facebook 0 Google+ 1 LinkedIn 0 Buffer 0 Email -- Filament.io 1 Flares ×

2 thoughts on “Oracle: ORA-02449: unique/primary keys in table referenced by foreign keys

  1. Pingback: Oracle: ORA-02292: integrity constraint violated - child record found | SQL and PLSQL

  2. Luis

    just in case someone is seeking crazily for help, i fixed it by altering the table (deleting the constrains of those tables) with the following command
    ALTER TABLE DROP CONSTRAINT ;
    deleted all constraints just to be sure, so i could drop table later

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Paged comment generated by AJAX Comment Page