时间戳

首页 > 解决方案 > DELETE 语句与 REFERENCE 约束“FK_BlogCommentReply_BlogCommentId”冲突

sql - DELETE 语句与 REFERENCE 约束“FK_BlogCommentReply_BlogCommentId”冲突

问题描述

我有一个博客并保持简单讨论，主要表格是博客、博客评论、博客评论回复和用户。

博客属于用户。评论属于博客/用户。评论回复属于博客/评论/用户。

我想删除一个用户 - UserId = 5。

我的删除用户存储过程按以下顺序删除（包装在事务中）：

它删除用户的 BlogCommentReply 中的条目，然后尝试删除用户的 BlogComment 中的条目。

然后我得到错误：

   The DELETE statement conflicted with the REFERENCE constraint "FK_BlogCommentReply_BlogCommentId". 
   The conflict occurred in database "DBGbngDev", table "dbo.BlogCommentReply", column 
   'BlogCommentId'.

它仍然认为 BlogCommentReply 表中有一个条目。

这是存储过程的执行，其中包含调试选择，以表明它删除了 BlogCommentReply 条目，但随后在删除 BlogComment 时失败。

在此处输入图像描述 在此处输入图像描述

表定义：

----------------------- BlogComment table (parent to blogCommentReply)

CREATE TABLE [dbo].[BlogComment](
    [BlogCommentId] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,
    [BlogId] [int] NOT NULL,
    [BlogCommentContent] [varchar](max) NOT NULL,
    [LikeCount] [int] NOT NULL,
    [DisLikeCount] [int] NOT NULL,
    [DateTimeOfBlogComment] [datetime] NOT NULL,
 CONSTRAINT [PK_BlogComment] PRIMARY KEY CLUSTERED 
(
    [BlogCommentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[BlogComment]  WITH CHECK ADD  CONSTRAINT [FK_BlogComment_BlogId] FOREIGN 
KEY([BlogId])
REFERENCES [dbo].[Blog] ([BlogId])
GO

ALTER TABLE [dbo].[BlogComment] CHECK CONSTRAINT [FK_BlogComment_BlogId]
GO

ALTER TABLE [dbo].[BlogComment]  WITH CHECK ADD  CONSTRAINT [FK_BlogComment_UserId] FOREIGN 
KEY([UserId])
REFERENCES [dbo].[User] ([UserId])
GO

ALTER TABLE [dbo].[BlogComment] CHECK CONSTRAINT [FK_BlogComment_UserId]
GO

----------------------- BlogCommentReply table (child)

CREATE TABLE [dbo].[BlogCommentReply](
    [BlogCommentReplyId] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,
    [BlogCommentId] [int] NOT NULL,
    [BlogId] [int] NOT NULL,
    [BlogCommentReplyContent] [varchar](max) NOT NULL,
    [LikeCount] [int] NOT NULL,
    [DisLikeCount] [int] NOT NULL,
    [DateTimeOfBlogCommentReply] [datetime] NOT NULL,
CONSTRAINT [PK_BlogCommentReply] PRIMARY KEY CLUSTERED 
(
    [BlogCommentReplyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[BlogCommentReply] WITH CHECK ADD CONSTRAINT [FK_BlogCommentReply_BlogCommentId] 
FOREIGN KEY([BlogCommentId])
REFERENCES [dbo].[BlogComment] ([BlogCommentId])
GO

ALTER TABLE [dbo].[BlogCommentReply] CHECK CONSTRAINT [FK_BlogCommentReply_BlogCommentId]
GO

ALTER TABLE [dbo].[BlogCommentReply]  WITH CHECK ADD CONSTRAINT [FK_BlogCommentReply_BlogId] FOREIGN 
KEY([BlogId])
REFERENCES [dbo].[Blog] ([BlogId])
GO

ALTER TABLE [dbo].[BlogCommentReply] CHECK CONSTRAINT [FK_BlogCommentReply_BlogId]
GO

ALTER TABLE [dbo].[BlogCommentReply]  WITH CHECK ADD CONSTRAINT [FK_BlogCommentReply_UserId] FOREIGN 
KEY([UserId])
REFERENCES [dbo].[User] ([UserId])
GO

ALTER TABLE [dbo].[BlogCommentReply] CHECK CONSTRAINT [FK_BlogCommentReply_UserId]
GO

删除存储过程的提取部分 (@a_UserId = 5)：

                    --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++       
                    -- Step 6. Get the number of blog comments replys for the user.
                    -- Note: there may not be any. 
                    --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
                    SELECT @BlogCommentReplysCount = Count(*)
                    FROM dbo.BlogCommentReply
                    WHERE ( UserId = @a_UserId )

                    SELECT @ReturnCode = @@ERROR

                    IF @ReturnCode <> 0
                        BEGIN 
                            ROLLBACK TRANSACTION

                            SELECT @Message =  'Critical Error - procedure DeleteBlog failed during the select of BlogCommentReply. User id: ' + CAST(@a_UserId AS VARCHAR) + '. '
                            RAISERROR (@Message, 16, 1)
                    END

                    SELECT '@BlogCommentReplysCount: ' + CAST(@BlogCommentReplysCount AS VARCHAR) + '. '

                    --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++       
                    -- Step 7. Delete the BlogCommentReply entries for the user.        
                    -- Note: the user may not have made any.                                 
                    --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
                    DELETE dbo.BlogCommentReply
                    WHERE ( UserId = @a_UserId )
         
                    SELECT @ReturnCode = @@ERROR,
                           @RowCount = @@ROWCOUNT

                    IF @ReturnCode <> 0 AND @RowCount > 0
                        BEGIN
                            ROLLBACK TRANSACTION

                            SELECT @Message =  'Critical Error - procedure DeleteBlog failed during the delete of BlogCommentReply. User id: ' + CAST(@a_UserId AS VARCHAR) + '. '
                                RAISERROR (@Message, 16, 1)
                    END
                           
                   SELECT 'Deleted BlogCommentReply'
                   SELECT '@ReturnCode: ' + CAST(@ReturnCode AS VARCHAR) + '. '
                   SELECT '@RowCount: ' + CAST(@RowCount AS VARCHAR) + '. '
                   SELECT '@a_UserId: ' + CAST(@a_UserId AS VARCHAR) + '. '

                   SELECT Count(*) as AFTERDELETE
                   FROM dbo.BlogCommentReply
                   WHERE ( UserId = @a_UserId )

                   --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++        
                   -- Step 8. Get the number of blog comments for this user.
                   -- Note: there may not be any. 
                   --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
                   SELECT @BlogCommentsCount = Count(*)
                   FROM dbo.BlogComment
                   WHERE ( UserId = @a_UserId )

                   SELECT @ReturnCode = @@ERROR

                    IF @ReturnCode <> 0
                        BEGIN 
                            ROLLBACK TRANSACTION

                            SELECT @Message =  'Critical Error - procedure DeleteBlog failed during the select of BlogComment. User id: ' + CAST(@a_UserId AS VARCHAR) + '. '
                            RAISERROR (@Message, 16, 1)
                    END

                   SELECT '@BlogCommentsCount: ' + CAST(@BlogCommentsCount AS VARCHAR) + '. '

                    --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++       
                    -- Step 9. Delete the BlogComment entries for the user. 
                    -- Note: the user may not have made any.                                     
                    --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
                    SELECT Count(*) as BEFORE
                    FROM dbo.BlogComment
                    WHERE ( UserId = @a_UserId )

                    SELECT  'Going to delete BlogComment'

                    DELETE dbo.BlogComment
                    WHERE ( UserId = @a_UserId )
         
                    SELECT @ReturnCode = @@ERROR,
                                    @RowCount = @@ROWCOUNT

                    IF @ReturnCode <> 0 AND @RowCount > 0
                        BEGIN
                            ROLLBACK TRANSACTION

                            SELECT @Message =  'Critical Error - procedure DeleteBlog failed during the delete of BlogComment. User id: ' + CAST(@a_UserId AS VARCHAR) + '. '
                            RAISERROR (@Message, 16, 1)
                    END
                    
                   SELECT  'Deleted BlogComment'

标签： sql

解决方案

我认为最好有完整的存储过程脚本来检查错误，但同时我猜问题出在此处：

DELETE dbo.BlogCommentReply
WHERE ( UserId = @a_UserId )

好的，它正在删除相关用户（即@a_UserId = 5）拥有的所有评论回复。

但看看你的脚本的其余部分：

DELETE dbo.BlogComment
WHERE ( UserId = @a_UserId )

它正在删除所有用户的评论，而他们可能对其他用户有一些其他评论回复并且他们没有被删除。所以我认为问题就在这里。

也许最好将第一个删除部分更改为：

DELETE dbo.BlogCommentReply
WHERE ( UserId = @a_UserId )

DELETE dbo.BlogCommentReply
FROM [dbo].[BlogComment]
WHERE  dbo.BlogCommentReply.BlogCommentId=[dbo].[BlogComment].BlogCommentId
AND [dbo].[BlogComment].UserId=@a_UserId

推荐阅读