truncate table with foreign key reference. Strange behavior.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: truncate table with foreign key reference. Strange behavior.

  1. #1
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758

    truncate table with foreign key reference. Strange behavior.

    Why the truncate should give error while checking referencial constraint?
    Code:
    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?
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  2. #2
    Join Date
    May 2001
    Posts
    736
    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.

  3. #3
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jan 2001
    Posts
    3,131
    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
    I remember when this place was cool.

  6. #6
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    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.
    -- Dilip

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width