Cannot truncate a table which is being referenced by other table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Cannot truncate a table which is being referenced by other table

  1. #1
    Join Date
    Oct 2003
    Location
    New Delhi
    Posts
    20

    Smile Cannot truncate a table which is being referenced by other table

    Why can't I truncate a table which is being referenced by other table?
    This happens even if the child table does not have any records.
    I get ORA-02266.
    ------------------------------------------------------------------
    SQL> create table T1 ( col1 number);

    Table created.

    SQL> create table T2(col2 number);

    Table created.

    SQL> alter table T1 add constraint PK1 primary key (col1)
    2 /

    Table altered.

    SQL> alter table t2 add constraint fk2 foreign key (col2) references T1
    2 /

    Table altered.

    SQL> truncate table t1 reuse storage;
    truncate table t1 reuse storage
    *
    ERROR at line 1:
    ORA-02266: unique/primary keys in table referenced by enabled foreign keys
    When everything is lost, future still remains.

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: Cannot truncate a table which is being referenced by other table

    You can't truncate a table that has a primary key which is a foreign key in a child table. Drop the relationship first.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No need to drop the foreign key constraint. Just disabling it for the duration of truncate operation and enabling it afterwards will be enough. So in your case:
    Code:
    ALTER TABLE t2 DISABLE CONSTRAINT fk2;
    
    TRUNCATE TABLE t1 REUSE STORAGE;  
    
    ALTER TABLE t2 ENABLE CONSTRAINT fk2;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by jmodic
    No need to drop the foreign key constraint. Just disabling it for the duration of truncate operation and enabling it afterwards will be enough. So in your case:
    Code:
    ALTER TABLE t2 DISABLE CONSTRAINT fk2;
    
    TRUNCATE TABLE t1 REUSE STORAGE;  
    
    ALTER TABLE t2 ENABLE CONSTRAINT fk2;
    Yes, that's the way it should be done...
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

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