Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Truncating a table with foreign keys

    Hi, I'm trying to truncate a table, but I'm getting the following error
    Code:
    Cannot truncate table 'authors' because it is being referenced by a FOREIGN KEY constraint.
    Is there a way to determine WHICH table is referencing it?
    Last edited by dbguyfh; 07-03-08 at 09:23.

  2. #2
    Join Date
    Jan 2008
    Posts
    186
    ...because I'm looking at my create table script, and there is only ONE table that references a key from the table I'm trying to delete (I've verified this with a search)

    Okay, so table "books" references the pk from table "authors" and that is the ONLY table that references from table "authors"

    I do:

    TRUNCATE TABLE books;

    And that works fine. I run a SELECT query on the books table, and no rows are returned (b/c they were truncated!)

    Next I try:

    TRUNCATE TABLE authors

    And it gives me the error shown in my previous post -- even though no rows are found in the books table... What's up here?

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    It is the constraint, not the data that prevents the truncate.

  4. #4
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by pootle flump
    It is the constraint, not the data that prevents the truncate.
    Ahhh so how do I get around that?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Don't use truncate (use delete) or drop and recreate the constraint. You could try enabling and re-enabling the constraint first - I don't know if that works, but it would be easier.

  6. #6
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by pootle flump
    Don't use truncate (use delete) or drop and recreate the constraint. You could try enabling and re-enabling the constraint first - I don't know if that works, but it would be easier.
    I was afraid of t hat... I've read someplace that using truncate is more efficient than using delete (something about not logging every row-delete in the trans-log).

    I guess I have no other choice.

    Regarding disabling the constraints, is this somethign that can be done universally (for all tables) or will I have to do it for each and every individual table? Because I have quite a few.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,634
    Disabling a constraint only allows you to delete from a parent table while the child table contains rows related to it. It will not allow you to truncate the parent table.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by rdjabarov
    Disabling a constraint only allows you to delete from a parent table while the child table contains rows related to it. It will not allow you to truncate the parent table.
    The thing is... I have this as part of a "CleanDatabase" stored proc. And this stored proc is run before each test case (I have several dozen test cases).

    It proves to be too time-consuming to drop and re-create the schema upon each test case, which is why I was looking into truncate table (which works fine in MySql).

    Does this mean my only option is DELETE FROM ?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    For clean installs I just drop the database and run a script to recreate everything. How on earth can that be too time consuming?

  10. #10
    Join Date
    Jan 2008
    Posts
    186
    Well, we have a 3-tier architecture, and I'm writing test cases for the methods in my middle-layer DLL.

    Prior to each test case, I wipe the database, and insert some dummy data, so that it is in a known state prior to running each test case.

    Problem is that I have several dozen of these test cases, so I'll be re-creating the schema everytime, which seems to take a lot more time. The deleting is fine -- very quick -- but the re-creating of the tables is what seems to take the most time

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,634
    Quote Originally Posted by dbguyfh
    ... which is why I was looking into truncate table (which works fine in MySql).
    That's why we're in SQL forum, not MySql
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Jan 2008
    Posts
    186
    Yeah, I'm from a MySql background.... I just thought there's gotta be a better way than having to use DELETE FROM (w/ all those records in trans log) And there's gotta be a quicker way than dropping & re-creating the schema.

    If only truncate table worked on the data

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Quote Originally Posted by dbguyfh
    And there's gotta be a quicker way than dropping & re-creating the schema.
    Are you really saying that DROP DATABASE myDB crawls and that running N TRUNCATE statements is fast?

  14. #14
    Join Date
    Jan 2008
    Posts
    186
    No no, you're right the DROP statement is fine, and I'd imagine runs substantially quicker than running "n TRUNCATE"

    My problem is with the re-creating of the tables -- this is the part that hogs the time

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Are you using standard CREATE TABLE....syntax?
    Are you pre-populating selective tables with lots of data?

Posting Permissions

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