Truncate table error: foreign key reference that is enabled for unique/primary keys in the ORA-02266 table

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. Alternatively, you must cancel the foreign key before deleting the data.

Yesterday, we used to delete the child table first, then the parent table, and truncate to delete the child table. The following message is displayed when we delete the parent table:

"

An error occurred when running the command on Line 1:
Truncate table ep_point
Error Report:
SQL error: ORA-02266: Unique/foreign key reference enabled in the table
02266. 00000-"unique/primary keys in Table referenced by enabled foreign keys"
* Cause: an attempt was made to truncate a table with unique or
Primary keys referenced by foreign keys enabled in another table.
Other operations not allowed are dropping/truncating a partition of
Partitioned table or an alter table exchange partition.
* Action: Before grouping Ming the above operations the table, disable
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 ";

".

If it is changed to delete, the deletion is successful.

The explanation provided on the Internet is the difference between DDL and DML (because truncate isn' t going to verify the constraint, truncate is DDL .).

 

Solution:

SQL> ALTER TABLE ep_pointdisable primary key cascade;
The table has been changed.

SQL> truncate table ep_point;
The table has been truncated.

SQL> ALTER TABLE ep_point enable primary key;
The table has been changed.

SQL> ALTER TABLE 'subtable' enable constraint' foreign key constraint name ';

* SpecialNote that the foreign key will not be automatically restored after the enable primary key (no Cascade option), so you need to manually enable the constraints that reference this key.

 

This solution disable the foreign key associated with the sub-table, so use it with caution.