ppshein ppshein - 5 months ago 15
SQL Question

Truncate table in Oracle getting errors

I got the problem is when I run following command in Oracle, I encounter the error.

Truncate table mytable;


Errors:

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


I found that, this mytable has relationship with other tables. That's why Truncate command cannot proceed anymore. How to delete data from myTable with the SQL scripts using Truncate command?

Answer

You have to swap the TRUNCATE statement to DELETE statements, slower and logged but that's the way to do it when constraints are in place.

DELETE mytablename;

Either that or you can find the foreign keys that are referencing the table in question and disable them temporarily.

select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINTS '||CONSTRAINT_NAME||';'
from user_constraints
where R_CONSTRAINT_NAME='<pk-of-table>';

Where pk-of-table is the name of the primary key of the table being truncated
Run the output of the above query. When done, remember to enable them again, just change DISABLE CONSTRAINTS to ENABLE CONSTRAINTS