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

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;
    COMMIT;  

why?
enrique_aeoAsked:
Who is Participating?
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
It's the way Oracle implements truncate.

Refer to the Online Documentation: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10007.htm#SQLRF01707

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.
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
The docs are your friend:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10007.htm

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.
0
All Courses

From novice to tech pro — start learning today.