0

I keep getting the error when running the code below:

Cannot truncate table 'Entry' because it is being referenced by a FOREIGN KEY constraint.

enter image description here

    --ALTER TABLE [dbo].[Entry] DROP CONSTRAINT [PK_Entry_Id] 
    ALTER TABLE [dbo].[Entry] DROP CONSTRAINT [DF_Entry_HideChrome]
    ALTER TABLE [dbo].[Entry] DROP CONSTRAINT [DF_Entry_IsDiscussionEnabled]
    ALTER TABLE [dbo].[Entry] DROP CONSTRAINT [DF_Entry_MetaDescription]
    ALTER TABLE [dbo].[Entry] DROP CONSTRAINT [DF_Entry_MetaTitle]

    Truncate table [Entry]

    ALTER TABLE [dbo].[Entry] ADD  CONSTRAINT [DF_Entry_HideChrome]  DEFAULT ((0)) FOR [HideChrome]
    GO

    ALTER TABLE [dbo].[Entry] ADD  CONSTRAINT [DF_Entry_IsDiscussionEnabled]  DEFAULT ((1)) FOR [IsDiscussionEnabled]
    GO

    ALTER TABLE [dbo].[Entry] ADD  CONSTRAINT [DF_Entry_MetaDescription]  DEFAULT ('') FOR [MetaDescription]
    GO

ALTER TABLE [dbo].[Entry] ADD  CONSTRAINT [DF_Entry_MetaTitle]  DEFAULT ('') FOR [MetaTitle]
GO

ALTER TABLE [dbo].[Entry] ADD  CONSTRAINT [DF_EntryStatus]  DEFAULT ('Public-Page') FOR [Status]
GO

Ok so I dropped all other constraints from any tables referencing this table but I still get one more error saying there's still a PK constraint referencing my Entry table.

I go and check out the dependencies on Entry (view dependencies) and see that the comment table is still dependent on it:

enter image description here

Then I see there's a FK as one of the main fields of the comment table but you can't drop that I guess.

So I don't see what other dependencies are referencing this Entry table when View Dependencies says the only table left after I dropped constraints on the other tables is from the Comment table? I don't see it.

PositiveGuy
  • 46,620
  • 110
  • 305
  • 471
  • Check the *other* tables to see what constraint is referencing `Entry`. (A right-click and view-dependencies on the table in SSMS should show you what you need) – lc. Jan 18 '13 at 05:33
  • so I have to disable those also or drop them? I assume I need to disable all other tables referencing this table as a FK? What a pain in the ass – PositiveGuy Jan 18 '13 at 05:40
  • 1
    Disabling won't be enough. You must drop the FKs. – bobs Jan 18 '13 at 05:40
  • ahhhh! you have to even drop the FKs from other tables referencing this one. Ok, yea brb – PositiveGuy Jan 18 '13 at 05:44
  • well there aren't constraints referencing this table from other tables but there are Keys (FK) referencing this table. So I have to drop the FKs (under the keys section) AND any constraints of the other tables (if there were constraints from other tables to this one) and recreate them after the truncate? – PositiveGuy Jan 18 '13 at 05:51
  • Any reason why a delete won't suffice? – Kevin Dahl Jan 18 '13 at 06:07
  • Yes @CoffeeAddict you have to first drop those constraint then truncate your table and then again add your constraints. similar question and you have also commented on the answer there :P http://stackoverflow.com/questions/253849/cannot-truncate-table-because-it-is-being-referenced-by-a-foreign-key-constraint – Meherzad Jan 18 '13 at 06:09
  • @Kevin...well delete doesn't reseed. I guess it doesn't matter if I'm doing an IDENTITY INSERT ON whereas I'm porting over IDs from another table that skip numbers? But if I reseed, and then turn identity insert on, insert my records..then the new records added from that point on will have a new number that's not so much higher. Lets say the last ID I port over is 400. Lets say the last ID in the table before the delete was 1000. Now after I port over 400, my next inserted record would be 1001? That's a huge jump. This is my thinking about that. – PositiveGuy Jan 18 '13 at 06:10
  • @Meherzad, yea but now I have a new issue, see my update in the post. I can't find that last FK constraint that it's complaining about even though it says the remaining dependency on the Entry table is only from the Comment table but I see no constraint there to drop! – PositiveGuy Jan 18 '13 at 06:12
  • and when they say you have to drop the constraints all in one transaction I assume that means you put GO after all the statements. GO represents ONE transaction set/scope right? – PositiveGuy Jan 18 '13 at 06:17
  • ok for some reason I had to drop the key FK_Comment_Comment. I don't get how that's tied back to Entry! But this solved it. – PositiveGuy Jan 18 '13 at 06:37
  • If you're just using `TRUNCATE` because it reseeds the identity column, don't. Use `DELETE` and then manually reseed the identity column. And `GO` does *not* represent a transaction. `GO` separates *batches*. Batches and transactions are orthogonal - one batch may contain multiple transactions. One transaction may span multiple batches. – Damien_The_Unbeliever Jan 18 '13 at 07:04
  • Also, re: identity. "That's a huge jump" - you really ought to learn to *not care* about what actual values are being used. As much as possible, you should treat identity values as opaque blobs. The fact that they happen to resemble (okay, actually are) `int`s (or similar types) shouldn't matter, and shouldn't be relied upon. – Damien_The_Unbeliever Jan 18 '13 at 07:49

2 Answers2

0

In the code of dropping the constraint that you have provide you are not dropping the DF_EntryStatus key. You have only dropped these 4 keys.

ALTER TABLE [dbo].[Entry] DROP CONSTRAINT [DF_Entry_HideChrome]
ALTER TABLE [dbo].[Entry] DROP CONSTRAINT [DF_Entry_IsDiscussionEnabled]
ALTER TABLE [dbo].[Entry] DROP CONSTRAINT [DF_Entry_MetaDescription]
ALTER TABLE [dbo].[Entry] DROP CONSTRAINT [DF_Entry_MetaTitle]

As this DF_EntryStatus still referencing you are not able to truncate the table.

Hope this solves our problem.

Meherzad
  • 8,433
  • 1
  • 30
  • 40
0

For some odd reason, I had to drop the key FK_Comment_Comment. Don't know how it's related to the Entry table though...weird.

PositiveGuy
  • 46,620
  • 110
  • 305
  • 471