Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Truncate table in Oracle getting errors

Tags:

sql

oracle

plsql

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?

like image 982
PPShein Avatar asked Jan 27 '11 02:01

PPShein


People also ask

Is there any error if we try to TRUNCATE empty table?

Truncate does not fire any triggers, does not validate any constraints. It does not care of the child table is empty or not -- in this case the child table might actually not be empty. You would have to disable or drop the foreign keys that reference this table.

What happens when you TRUNCATE a table in Oracle?

When you truncate a table, Oracle Database automatically removes all data in the table's indexes and any materialized view direct-path INSERT information held in association with the table. This information is independent of any materialized view log.


1 Answers

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 CONSTRAINT '||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 this has been done, remember to enable them again, just change DISABLE CONSTRAINT into ENABLE CONSTRAINT

like image 103
RichardTheKiwi Avatar answered Oct 19 '22 04:10

RichardTheKiwi