ORA-02266: A unique/primary key-enabled foreign key reference in the table

• Summary

When deleting data in a table with a parent-child relationship, we all know that you must delete the child table before deleting the data in the parent table.

Yesterday we used to delete the child table first, then delete the parent table, delete the use of truncate, the results in the deletion of the parent table, given the prompt: ORA-02266 this error message.

When the data in the parent table is deleted using the delete statement, the operation is successful.

The fundamental problem is: difference between DDL and DML (because truncate isn' t going to verify the constraint, truncate is DDL .)

The following operations are required:

Cancel the foreign key constraint of the child table foreign key, delete the parent table data, and enable the foreign key constraint again.

• Prepare the Parent and Child tables

 --  Create a person table  Create     Table  Person (PID  Integer   Primary   Key , Pname  Varchar2 ( 10 ) Not   Null  ) Tablespace Cici;  --  Create Department  Create   Table  Department (depid  Integer   Primary   Key  , Deptname Varchar2 ( 10 ) Not   Null  ) Tablespace Cici;  --  Add Field  Alter   Table Person Add Depid Integer  ;  --  Add a foreign key constraint person --> Department  -- Deptid --> deptid  Alter   Table  Person  Add   Constraint  Fk_person_dept  Foreign   Key (Depid) References Department (depid );

• Run the following statement when the problem occurs:

Truncate TablePerson;
-- Execution successful
Truncate TableDepartment;
-- Encountered error message: ORA-02266: Unique/foreign key reference enabled in the table
 

• Problem Solving

The FK foreign key constraint of the distable sub-Table. After the data is deleted, entable again;

1. query foreign key constraints

Select * fromAll_constraints
Where constraint_type = 'r' and owner = 'ss _ hr ';

The constraint_name is fk_person_dept.

2. The foreign key constraint of the invalid sub-table

Alter table person disable ConstraintFk_person_dept;

3. Delete the data in the parent table again.

 
Truncate TableDepartment;
 

4. Foreign key constraints

 
Alter TablePerson enableConstraintFk_person_dept