Deleting tables with referential integrity - get REFERENCE constraint

Question

I want to delete a User (parent table) which has blog comment(s) and blog comment reply(s)

I have it coded to 1st delete the BlogCommentReply (child to the BlogComment), then the BlogComment (parent to BlogCommentReply), then the user (parent to both).

I get the error:

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

I have FK keys on the BlogCommentReply and BlogComment tables.

1.) Did I create the table structure correctly? 2.) Do I need cascades - why? 3.) Is the delete code order correct? _ I am of the belief that the parent table cannot be deleted until the
children are deleted first. Is that correct?

Table creates:

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,1) NOT NULL,      -- PK      
  [UserId] [int] NOT NULL,                           -- FK
  other columns....   
  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]
  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,1) NOT NULL, -- PK      
  [UserId] [int] NOT NULL,                           -- FK
  [BlogCommentId] [int] NOT NULL,                    -- FK    
  other columns.... 
  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]
  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

Stored procedure (simplified for discussion) that does the deletes:

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 )

Martin · Accepted Answer · 2021-04-21T15:52:20.450

It appears that your FK's are doing their jobs and preventing you from creating orphaned rows when deleting data from BlogCommentReply.

Your first DELETE from BlogCommentReply can be rewritten as two statements to make work correctly:

-- 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)
                          ))

In the first statement you are simply trying to delete rows from BlogCommentReply that use a FK to the User table.

In the second statement you delete any reply to a blog comment that will be deleted.

In response to your specific questions:

1. Did I create the table structure correctly?

It looks fine although there are several ways to achieve the same.

2. Do I need cascades - why?

No. Cascade deletes reek of poor design and lazy development. Try to avoid them (note this is an opinion not a statement of fact).

There are some interesting points in this question.

3. Is the delete code order correct?

Yes, delete from the tables in order of key precidence.

4. I am of the belief that the parent table cannot be deleted until the children are deleted first. Is that correct?

Yes indeed, without cascaded deletes that is correct and, in your case, the error thrown shows that the referential integrity of the database is sound.

Thanks..any insight into this one? https://stackoverflow.com/questions/67118480/how-to-create-a-list-within-a-list-in-sql-temp-table — user3020047, Apr 22 '21 at 16:12

Deleting tables with referential integrity - get REFERENCE constraint

1 Answers1