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

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

Error
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
Following error occurred on execution of truncate command:

SQL> create table hr.emp2 as select * from hr.employees;
Table created.
SQL> create table hr.dept2 as select * from hr.departments;
Table created.
SQL> alter table hr.dept2 add primary key(department_id);
Table altered.
SQL>alter table hr.emp2 add constraint emp2fk foreign key(department_id) references hr.dept2(department_id);
Table altered.

SQL> truncate table hr.dept2;
truncate table hr.dept2
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Solution
1. If you try delete command it will give you this error with constraint name.

SQL> delete from hr.dept2;
delete from hr.dept2
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.EMP2FK) violated - child record found

Note: HR.EMP2FK – Constraint Name.

2. If you got error in 1 steps or not want to run delete then you have two options:
1. Option to disable this constraint and run the truncate command.
2. Option is truncate parent table (if data is not needed) and then delete from child table.

Example of first option
Disable this constraint and run the truncate command.

-- IF delete statement is not working you can also disable the Constraint.
SQL> alter table hr.EMP2 disable constraint EMP2FK;
Table altered.

-- Truncate the table
SQL> Truncate table hr.dept2;
Table truncated.

Example of second option
If parent table data is also not needed then you can only use second option truncate the parent table first and then you can use delete from child table.

-- truncate or delete from primary table
SQL> truncate table hr.emp2;
Table truncated.

-- Delete from child table.
SQL> delete from hr.dept2;
27 rows deleted.

Reason
TRUNCATE statement is that you can’t truncate a parent table that has a primary key defined that is referenced by an enabled foreign-key constraint in a child table—even if the child table contains zero rows. In this scenario, Oracle will throw this error when attempting to truncate the parent table:

ORA-02266: unique/primary keys in table referenced by enabled foreign keys
Oracle prevents you from truncating the parent table because in a multiuser system, there is a possibility that another session can populate the child table with rows in between the time you truncate the child table and the time you subsequently truncate the parent table.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.