| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Truncate Qs
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01BFDAF9.54AFE06C
Content-Type: text/plain;
charset="windows-1252"
Taken from Oracle8i SQL Reference Release 8.1.5 A67779-01 at http://sitelicense.arizona.edu/~oradoc/DOC/server.815/a67779/ch4m.htm#13607
Restrictions:
You cannot truncate the parent table of an enabled
referential integrity constraint. You must disable the
constraint before truncating the table. (An exception
is that you may truncate the table if the integrity constraint is
self-referential.)
Brian P. Mac Lean
Senior Oracle Database Administrator
OCPv8/Oracle Master
HomeBid.Com
8700 N. Gainey Center Drive
Scottsdale, AZ 85258
Tel:480.609.4624
Cel:602.617.6075
Fax:480.609.4646
Net:brian.maclean_at_homebid.com
-----Original Message-----
From: Prabal Biswas [mailto:willsm_at_hotmail.com]
Sent: Tuesday, June 20, 2000 1:13 PM
To: Multiple recipients of list ORACLE-L
Subject: Truncate Qs
Hello,
Can someone please explain me this:
I tried to truncate a table, it gave ORA-2266.
But when I deleted the rows, it did not show the error. And after deleting
the rown I tried to truncate the table again, but it still gives ORA-2266
TIA
Prabal
SQL> truncate table supplier_order;
truncate table supplier_order
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
SQL> delete supplier_order;
97 rows deleted.
SQL> truncate table supplier_order;
truncate table supplier_order
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
-- Author: Prabal Biswas INET: willsm_at_hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists ------_=_NextPart_001_01BFDAF9.54AFE06C Content-Type: text/html; charset="windows-1252" Content-Transfer-Encoding: quoted-printableReceived on Tue Jun 20 2000 - 15:51:38 CDT
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dwindows-1252">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2448.0">
<TITLE>RE: Truncate Qs</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=3D2 FACE=3D"Arial">Taken from Oracle8i SQL Reference =
Release 8.1.5 A67779-01 at</FONT> <FONT SIZE=3D2 FACE=3D"Arial"><A = HREF=3D"http://sitelicense.arizona.edu/~oradoc/DOC/server.815/a67779/ch4= m.htm#13607" = TARGET=3D"_blank">http://sitelicense.arizona.edu/~oradoc/DOC/server.815/= a67779/ch4m.htm#13607</A></FONT></P>
<P><FONT SIZE=3D2 FACE=3D"Arial">Restrictions: </FONT>
<BR><FONT SIZE=3D2 =
FACE=3D"Arial"> &nb= sp; &nb= sp; You cannot truncate the parent table of an enabled referential = integrity constraint. You must disable the</FONT></P>
<P><FONT SIZE=3D2 =
FACE=3D"Arial"> &nb= sp; &nb= sp; constraint before truncating the table. (An exception is that you = may truncate the table if the integrity constraint is</FONT></P>
<P><FONT SIZE=3D2 =
FACE=3D"Arial"> &nb= sp; &nb= sp; self-referential.) </FONT>
</P>
<BR>
<BR>
<P><B><FONT COLOR=3D"#0000FF" FACE=3D"Comic Sans MS">Brian P. Mac =
Lean</FONT></B>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans MS">Senior =
Oracle Database Administrator</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans =
MS">OCPv8/Oracle Master</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans =
MS">HomeBid.Com</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans MS">8700 N. =
Gainey Center Drive</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans MS">Scottsdale, =
AZ 85258</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans =
MS">Tel:480.609.4624</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans =
MS">Cel:602.617.6075</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans =
MS">Fax:480.609.4646</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans =
MS">Net:brian.maclean_at_homebid.com</FONT>
</P>
<BR>
<P><FONT SIZE=3D2 =
FACE=3D"Arial"> &nb= sp; </FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">-----Original Message-----</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">From: Prabal Biswas [<A =
HREF=3D"mailto:willsm_at_hotmail.com">mailto:willsm_at_hotmail.com</A>]</FONT>=
<BR><FONT SIZE=3D2 FACE=3D"Arial">Sent: Tuesday, June 20, 2000 1:13 =
PM</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">To: Multiple recipients of list =
ORACLE-L</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Subject: Truncate Qs</FONT>
</P>
<BR>
<P><FONT SIZE=3D2 FACE=3D"Arial">Hello,</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Can someone please explain me =
this:</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">I tried to truncate a table, it gave =
ORA-2266.</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">But when I deleted the rows, it did =
not show the error. And after deleting </FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">the rown I tried to truncate the =
table again, but it still gives ORA-2266</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">TIA</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Prabal</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">SQL> truncate table =
supplier_order;</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">truncate table supplier_order</FONT>
<BR><FONT SIZE=3D2 =
FACE=3D"Arial"> &nb= sp; *</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">ERROR at line 1:</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">ORA-02266: unique/primary keys in =
table referenced by enabled foreign keys</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">SQL> delete supplier_order;</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">97 rows deleted.</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">SQL> truncate table =
supplier_order;</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">truncate table supplier_order</FONT>
<BR><FONT SIZE=3D2 =
FACE=3D"Arial"> &nb= sp; *</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">ERROR at line 1:</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">ORA-02266: unique/primary keys in =
table referenced by enabled foreign keys</FONT>
</P>
<P><FONT SIZE=3D2 =
FACE=3D"Arial">_________________________________________________________= _______________</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Get Your Private, Free E-mail from =
MSN Hotmail at <A HREF=3D"http://www.hotmail.com" = TARGET=3D"_blank">http://www.hotmail.com</A></FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">-- </FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Author: Prabal Biswas</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> INET: =
willsm_at_hotmail.com</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">Fat City Network =
Services -- (858) 538-5051 FAX: (858) = 538-5051</FONT>
 |
 |