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].[B开发者_如何学Goag_CommentRelation] CHECK CONSTRAINT [FK_Bag_CommentRelation_Comment]
GO
The row in this table deletes but the row in the comment table does not.
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.
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.
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.
精彩评论