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.
