Re: Problems with Truncate Table

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. Since the delete worked it must be a 'delete cascade' rather than a delete restrict.

To get around this you drop the FK constraint, Truncate the table, and then put the FK constraint back. If you want to also remove the related rows then truncate the other table also before re-creating the constraint.
--
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 - 07:45:05 CDT