删除具有参照完整性的表 - 获取 REFERENCE 约束

大佬教程收集整理的这篇文章主要介绍了删除具有参照完整性的表 - 获取 REFERENCE 约束，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

如何解决删除具有参照完整性的表 - 获取 REFERENCE 约束？

我想删除一个有博客评论和博客评论回复的用户（父表）

我将它编码为首先删除 BlogCommentReply（BlogComment 的子项），然后是 BlogComment（BlogCommentReply 的父项），然后是用户（两者的父项）。

我收到错误：

deletE 语句与 REFERENCE 约束“FK_BlogCommentReply_UserID”冲突。冲突发生在数据库“DBGbngDev”、表“dbo.blogCommentReply”、“UserID”列中。

我在 BlogCommentReply 和 BlogComment 表上有 FK 键。

1.) 我是否正确创建了表结构？ 2.) 我需要级联 - 为什么？ 3.) 删除码顺序是否正确？ _ 我相信在
之前不能删除父表 子项首先被删除。对吗？

表创建：

create table [dbo].[User]
(
   [UserID] [int] IDENTITY(1,1) NOT NulL,-- PK
   other columns....      
   CONSTraiNT [PK_User] PRIMary KEY CLUSTERED
   (
     [UserID] 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

create table [dbo].[BlogComment]
(
  [BlogCommentID] [int] IDENTITY(1,-- PK      
  [UserID] [int] NOT NulL,-- FK
  other columns....   
  CONSTraiNT [PK_BlogComment] PRIMary KEY CLUSTERED
  (
    [BlogCommentID] ASC
  ) WITH (PAD_INDEX = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMary]
         ) ON [PRIMary]
  GO

  alter table [dbo].[BlogComment] WITH checK ADD CONSTraiNT [FK_BlogComment_UserID] FOREIGN 
  KEY([UserID]) REFERENCES [dbo].[User] ([UserID])
  GO

create table [dbo].[BlogCommentReply]
(
  [BlogCommentReplyID] [int] IDENTITY(1,-- FK
  [BlogCommentID] [int] NOT NulL,-- FK    
  other columns.... 
  CONSTraiNT [PK_BlogCommentReply] PRIMary KEY CLUSTERED
  (
     [BlogCommentReplyID] ASC
  ) WITH (PAD_INDEX = OFF,ALLOW_PAGE_LOCKS = ON) ON [PRIMary]
         ) ON [PRIMary]
  GO

  alter table [dbo].[BlogCommentReply] WITH checK ADD CONSTraiNT [FK_BlogCommentReply_UserID] FOREIGN 
  KEY([UserID]) REFERENCES [dbo].[User] ([UserID])
  GO

 alter table [dbo].[BlogCommentReply] WITH checK ADD CONSTraiNT [FK_BlogCommentReply_BlogCommentID] 
 FOREIGN KEY([BlogCommentID]) REFERENCES [dbo].[BlogComment] ([BlogCommentID])
 GO

执行删除操作的存储过程（简化讨论）：

deletE dbo.blogCommentReply
FROM dbo.blogComment a
WHERE ( BlogCommentReply.blogCommentID = a.blogCommentID AND BlogCommentReply.UserID = @a_UserID )

deletE dbo.blogComment
WHERE UserID = @a_UserID

deletE dbo.[User]
WHERE ( UserID = @a_UserID )

解决方法

-- Remove replies added by the deleted user
deletE
  FROM  dbo.blogCommentReply
  WHERE (UserId = @a_UserId)

-- Remove blog comment replies added by other users creaTing replies to a comment made by the deleted user
deletE
  FROM  dbo.blogCommentReply
  WHERE (BlogCommentId IN (
                          SELECT  BlogCommentId
                            FROM  BlogComment
                            WHERE (UserId = @a_UserId)
                          ))

大佬总结

以上是大佬教程为你收集整理的删除具有参照完整性的表 - 获取 REFERENCE 约束全部内容，希望文章能够帮你解决删除具有参照完整性的表 - 获取 REFERENCE 约束所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。