PL/SQL: ORA-02266 with truncate, but with DELETE; it is OK... why?

Posted on 2014-10-15
Last Modified: 2014-10-15
Hi experts:

i have this error
02266, 00000, "unique/primary keys in table referenced by enabled foreign keys"
when try: TRUNCATE TABLE ta_cuenta_contable;

i solution with
DELETE FROM ta_cuenta_contable;

Question by:enrique_aeo
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 40382140
The docs are your friend:

Restrictions on Truncating Tables This statement is subject to the following restrictions:

•You cannot truncate the parent table of an enabled foreign key constraint. You must disable the constraint before truncating the table. An exception is that you can truncate the table if the integrity constraint is self-referential.
LVL 22

Accepted Solution

Steve Wales earned 250 total points
ID: 40382141
It's the way Oracle implements truncate.

Refer to the Online Documentation:

Notably; You cannot truncate the parent table of an enabled foreign key constraint. You must disable the constraint before truncating the table.

Even if the enabled foreign key constraint has no data it's referencing, because of the way truncate works, it just won't let you do it - where as the delete will work.

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now