Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: Truncate Table

  1. #1
    Join Date
    Nov 2003
    Location
    Israel
    Posts
    41

    Unanswered: Truncate Table

    I have several very large tables and sometimes I need to clean them.
    It's known that TRUNCATE TABLE works much faster than DELETE, but impossible to use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint... Is it possible temporary disable (not delete) constraints and after complition of TRUNCATE enable them ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't know if it is possible to disable the foreign key constraint

    but i do know that you would never be able to turn it back on

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Location
    Israel
    Posts
    41
    Quote Originally Posted by r937
    i don't know if it is possible to disable the foreign key constraint

    but i do know that you would never be able to turn it back on

    Is it a good idea to delete constraint and after truncate table to create it again? I have to clear tables with millions rows and DELETE works very slow...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that will work, but only if you do something to the dependent table before attempting to create the constraint again, because otherwise it will fail
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Location
    Israel
    Posts
    41
    Quote Originally Posted by r937
    that will work, but only if you do something to the dependent table before attempting to create the constraint again, because otherwise it will fail

    I want to truncate these tables, so when I'll ceate constraints tables will be empty...

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you want to truncate the dependent table as well? your first message did not suggest this

    there is no need to mess with the constraints

    just truncate the dependent table first!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2003
    Location
    Israel
    Posts
    41
    Quote Originally Posted by r937
    you want to truncate the dependent table as well? your first message did not suggest this

    there is no need to mess with the constraints

    just truncate the dependent table first!!!

    I tried to do so, but I got error 'Cannot truncate table 'Table1' because it is being referenced by a FOREIGN KEY constraint.'

    Also, MSDN says: You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you can drop foriegn key constraints by using alter table and then after you do your import you can recreate it.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    import? where did that come from?

    yurich, you have to truncate the table that references (contains the foreign key) FIRST

    THEN you can truncate the table that is referenced (contains the primary key) SECOND
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    whatever. I just assumed.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Nov 2003
    Location
    Israel
    Posts
    41
    Quote Originally Posted by r937
    import? where did that come from?

    yurich, you have to truncate the table that references (contains the foreign key) FIRST

    THEN you can truncate the table that is referenced (contains the primary key) SECOND
    Foreign key table is empty already, but I get the same error when I trying to truncate primary key table...

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh

    okay, i guess you'll have to alter/drop the keys

    (can you tell i have never done this before?)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You can not use TRUNCATE on tables that have RI
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you can if you use ALTER TABLE to remove your RI like so

    ALTER TABLE SALES DROP CONSTRAINT FK__sales__stor_id__0AD2A005
    before you do your truncate.

    EXTRA BOLDS FOR EXTRA SNIDENESS
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Cute...but, if you alter the table, then the table then has no RI, Soooo...you can then use TRUNCATE...so the statement stands

    You can not use TRUNCATE on tables that have RI
    Besides, doing the ALTER means you'd have to makes sure you rebuild the RI later.

    If this doesn't have to be automated (I did that once...I'd have to dig up the code...and it's fairly involved) The easiest way to do this is to Script the tables you want from your existing database. Create 2 files. 1 With the CREATE TABLE statements, and the other with all the other constraints.

    Then DROP The tables

    Run the create table scripts

    Do what you have to do.

    Then rerun the ALTERs to recreate the constraints

    That's what I'd do...

    MOO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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