If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Truncating a table with foreign keys

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #3 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
It is the constraint, not the data that prevents the truncate.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #5 (permalink)  
Old
King of Understatement
 
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.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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."
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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 ?
Reply With Quote
  #9 (permalink)  
Old
King of Understatement
 
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?
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
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."
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #13 (permalink)  
Old
King of Understatement
 
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?
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #15 (permalink)  
Old
King of Understatement
 
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?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On