Re: Problems with Truncate Table

Sorry for the corrections, but see below.

--
Bob Fazio
Remove no.spam from my email to reply

<markp7832_at_my-deja.com> wrote in message news:7qofse$aht$1_at_nnrp1.deja.com...
> In article <37CF8E89.ACE033BC_at_synergy-infotech.com>,
> Anurag Minocha <anurag_at_synergy-infotech.com> wrote:
> > Hi,
> > I am using the Truncate command but it doesnt work, whereas delete
> works
> > fine for the same table.
> >
> > Here is what I am doing
> >
> > SQL>Truncate table test;
> > Error at line 1:
> > ORA-02266:Unique/Primary Keys in table refernced by enabled foreign
> key.
> >
> > But when i issue the following command it executes file
> >
> > sql>delete from test;
> > 100 rows deleted.
> >
> > Can somebody point out the problem please.
> >
> > Thanks
> > anurag
> >
> The problem is identifed by the error message: > ORA-
> 02266:Unique/Primary Keys in table referenced by enabled foreign key.
> You have a foreign key constraint defined from another table to this
> one.

Correct.

> Since the delete worked it must be a 'delete cascade' rather than
> a delete restrict.
>

It doesn't matter if it is delete cascade or not. Truncate doesn't even check.

> To get around this you drop the FK constraint, Truncate the table, and
> then put the FK constraint back.

This will work fine, and is actually the only way to get around the problem.

> If you want to also remove the
> related rows then truncate the other table also before re-creating the
> constraint.

If delete worked this won't be necessary, and in fact it must have been empty, or you have rows that have a null value for the foreign key. If they were null, you can still re-enable, without deleting them.

> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Fri Sep 03 1999 - 10:56:22 CDT