Truncate table in Oracle getting errors

sql oracle plsql
PPShein picture PPShein · Jan 27, 2011 · Viewed 58.7k times · Source

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

RichardTheKiwi picture RichardTheKiwi · Jan 27, 2011

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

Related questions

SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and while), I always get the error at the end... undefine numero set serveroutput on accept numero …

Read More