On Wed, 19 May 1999, Tarver, Steven wrote:
> Hi Ari,
>
> First time asker, long time reader! My question:
>
> I wish to delete all records from a parent table and all records from a
> child table.
>
> If I delete from from the child table then delete from the parent,
> everything works fine. But if I truncate the child table, then try to
> truncate the parent, I get an error:
>
> ERROR at line 1:
> ORA-02266: unique/primary keys in table referenced by enabled foreign keys
>
> Do you have insight into this?
> Thanks and best regards,
> Steve Tarver
>
Steve,
Glad that you enjoy my web page!
As for your question, I can hopefully shed some insight, which lies in the
differences between the DELETE command and the TRUNCATE command.
DELETE removes one or more records in a table, checking referential
constraints (to see if there are dependent child records) and firing any
DELETE triggers. In the order you are deleting (child first then parent)
there will be no problems.
TRUNCATE removes ALL records in a table. It does not execute any triggers.
Also, it only checks for the existance (and status) of another foreign key
pointing to the table. If one exists and is enabled, then you will get
your error. This is true even if you do the child tables first.
You should disable the foreign key constraints in the child tables before
issuing the TRUNCATE command, then re-enable them afterwards.
Best regards,
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 295+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page