Cascading Deletes in SQL Sever 2008 not working.

Question

I have the following table setup.

Bag
  |
  +-> BagID (Guid)
  +-> BagNumber (Int)

BagCommentRelation
  |
  +-> BagID (Int)
  +-> CommentID (Guid)

BagComment
  |
  +-> CommentID (Guid)
  +-> Text (varchar(200))

BagCommentRelation has Foreign Keys to Bag and BagComment.

So, I turned on cascading deletes for both those Foreign Keys, but when I delete a bag, it does not delete the Comment row.

Do need to break out a trigger for this? Or am I missing something?

(I am using SQL Server 2008)

---

Note: Posting requested SQL. This is the defintion of the BagCommentRelation table. (I had the type of the bagID wrong (I thought it was a guid but it is an int).)

CREATE TABLE [dbo].[Bag_CommentRelation](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [BagId] [int] NOT NULL,
    [Sequence] [int] NOT NULL,
    [CommentId] [int] NOT NULL,
 CONSTRAINT [PK_Bag_CommentRelation] PRIMARY KEY CLUSTERED 
(
    [BagId] ASC,
    [Sequence] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Bag_CommentRelation]  WITH CHECK ADD  CONSTRAINT [FK_Bag_CommentRelation_Bag] FOREIGN KEY([BagId])
REFERENCES [dbo].[Bag] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Bag_CommentRelation] CHECK CONSTRAINT [FK_Bag_CommentRelation_Bag]
GO

ALTER TABLE [dbo].[Bag_CommentRelation]  WITH CHECK ADD  CONSTRAINT [FK_Bag_CommentRelation_Comment] FOREIGN KEY([CommentId])
REFERENCES [dbo].[Comment] ([CommentId])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Bag_CommentRelation] CHECK CONSTRAINT [FK_Bag_CommentRelation_Comment]
GO

The row in this table deletes but the row in the comment table does not.

Answer 1

If you have the FK created correctly, with the Cascade Delete option set correctly, it will work as it should. Can you show us the FK definition?

Answer 2

With your current structrue, the BagComment record wouldn't be deleted when you delete a Bag, only the BagCommentRelation record. The delete cascades from Bag to BagCommentRelation, but stops there. The structure you have looks like a Many-to-Many relationship between Bag and BagCommentRelation. Why do you need BagCommentRelation?

Edit: It sounds like the easiest thing to do would be to make your structure like this:

Bag 
  | 
  +-> BagID (Guid)
  +-> BagNumber (Int)

BagComment 
  | 
  +-> BagID (Guid)
  +-> Text (varchar(200))

but using the BagComment (or Comment) table to refer to multiple objects add some complexity. If that's what you need to do, this question should provide assistance.

Answer 3

Your table BagCommentRelation in as n:m relation between Bag and BagComments, so it is a detail table to both other tables.

A DELETE CASCADE constraint will only work towards the detail table, so clearly a BagComment cannot be deleted if a Bag is deleted.

Which makes me wonder how a bag comment can be applied to several bags.

If you really need to have the same BagComment used for different Bag records, I suggest a DELETE trigger on Bag_CommentRelation which deletes all BagComments that are no longer referenced by the relation table.

Answer 4

Personally I would not use cascading delete at all. What if the need came to a large group of bags? Cascade delete could tie up your tables for hours. It is a better practice to specifically write the deletes you need.