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.

score 1 · Answer 1 · edited May 23 '17 at 12:03

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.

Our comment table has lots of different types of comments. Some tables uses Guids, some ints for the PK. Also, one bag may have many comments. Sounds like I need to pick either guids or ints and have the FK go directly from my bag table. — Vaccano, Mar 18 '10 at 15:49

score 1 · Answer 2 · answered Mar 18 '10 at 15:36

1

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.

answered Mar 18 '10 at 15:36

devio

36,858
7
80
143

A comment can only be applied to one item (bag, user whatever). We may have used the wrong table structure for this. If we have a one to many (bag to comment) relation then do we just have a CommentID in the bag table and then a FK From bag to comment? (and ditch the Relation table?) – Vaccano Mar 18 '10 at 15:50
@Vaccano yes, you don't need the BagCommentRelation. Just adding a BagID to BagComment should be enough (if you need more than 1 comment for a Bag) – devio Mar 18 '10 at 15:58

score 1 · Answer 3 · answered Mar 18 '10 at 17:14

1

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.

answered Mar 18 '10 at 17:14

HLGEM

94,695
15
113
186

Cascading Deletes in SQL Sever 2008 not working

3 Answers3