Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2005
    Posts
    7

    Unanswered: TRUNCATE table that has referential constraints

    Hi

    I have a TABLE_A with primary key of A1
    Another TABLE_B is linked to this TABLE_A by a foreign key constraint.

    I try to truncate TABLE_A using the following statement:
    (Please note, there is no data in TABLE_B, that is truncated earlier itsef)

    TRUNCATE TABLE TABLE_A IGNORE DELETE TRIGGERS IMMEDIATE;

    This throws the following exception:

    The TRUNCATE statement could not be processed for one of the following
    reasons:

    * The TRUNCATE statement would result in the activation of DELETE
    triggers. However, the statement specifies RESTRICT WHEN DELETE
    TRIGGERS (implicitly or explicitly). This can occur when DELETE
    triggers exist for the table to be truncated, and the result of the
    TRUNCATE statement would activate the triggers.
    * The table to be truncated is the parent table in a referential
    constraint.


    It is true that the table to be truncted is the parent table in a referential constraint.

    In such a case, what is the way to truncate to the data?

    I tried the following too:
    alter table TABLE_A activate not logged initially with empty table;

    IMPORT FROM NUL OF DEL REPLACE INTO TABLE_A.

    None of these worked.
    Can anyone guide me through this?

    Thanks in advance.
    Last edited by sriramkanala; 05-05-10 at 06:57.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about ALTER TABLE command?

    ALTER TABLE table_b ALTER FOREIGN KEY constraint-name NOT ENFORCED

Tags for this Thread

Posting Permissions

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