Question:
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
also reply at
[email protected]
Answer1:
The table that you are trying to truncate is the parent to a referencial
constraint.
The reason that truncate is so fast is that it doesn't check anything,
it
just marks all rows as deleted and moves the high watermark.
This leads to
your problem. If it doesn't check to see if you can delete the
rows, the
only alternative is to not let you do it, because you may violate the
constraint that you have on the table. Even if there are no rows
in the
other table.
--
Bob Fazio
Answer 2:
It could be that the table is referenced with on delete cascade. In
that
case the delete works, but the truncate does not.
SQL> create table z(a int primary key);
Tabel er oprettet.
SQL> create table x(b int primary key, c int references z on delete
cascade);
Tabel er oprettet.
SQL> truncate table z;
truncate table z
*
FEJL i linie 1:
ORA-02266: tabel har unikke/primære nøgler, der refereres
til af aktiverede
fremmede nøgler
SQL> delete from z;
0 rækker er slettet.
Sorry for the danish text
Peter Laursen
Answer 3:
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.