truncate table with foreign key reference. Strange behavior.

[SANJAY_G]

Why the truncate should give error while checking referencial constraint?

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

sanjay@ORAM> 
sanjay@ORAM> 
sanjay@ORAM> create table blah1 (c1 varchar2(4), c2 varchar2(4), constraint blah1_pk primary key (c1) );

Table created.

sanjay@ORAM> create table blah2 (c1 varchar2(4), c2 varchar2(4), constraint blah2_fk foreign key (c1)
  2  references blah1 (c1) );

Table created.

sanjay@ORAM> truncate table blah1;
truncate table blah1
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


sanjay@ORAM> delete from blah1;

0 rows deleted.

sanjay@ORAM>

Is it a bug or I am missing something?

[akhadar]

From 9i fundamentals a table which is referenced by a foreign key can't be truncated.That is the reason why u are getting the error.

[SANJAY_G]

Doesn't make sense to me. If child table is empty the parent should be allowed to be truncated.

Metalink says it is filed a bug (No. 949289). But it is not fixed till version 9.2.0.3
More interesting, they say the enhancement request is to be approved by user group and that user group consists of Oracle Empolyees and people from some other user groups..
Don't know what does that mean ???

Doc ID:175430.995

[jmodic]

If it is a bug then it must be one with the longest beard - it behaves like this since TRUNCATE was first introduced in Oracle7 (not sure in which release of Oracle7) - about 10 years ago, I'd say.

[Mr.Hanky]

Weird, does it do that if you disable the constraints.
I'm lucky in that we do not use RI here, so my exposure is limited.

MH

[patel_dil]

Originally posted by Mr.Hanky
Weird, does it do that if you disable the constraints.

Yupe, it works if you disable or drop the constraints. Make sure you apply the constraints back to the tables once you are done truncating.