I can't create relationship in my database sql server

Question

I have designed a database for my website. It has 3 tables: user, topic and comment.

A user has many topics (or articles)
A topic (or article) has many comments
A user has many comments

I created the relationships for them after I set the insert and and update specification as cascade.

I want that

• if I delete a user then the topics of this user and the user's comments will be deleted

• If I delete a topic then all comments of this topic will be delete by cascade

My diagram:

Answer 1

The issue is with creating two CONSTRAINT in Comment table with ON DELETE CASCADE one for UserId and other for TopicId, which can be fixed by INSTEAD OF DELETE Trigger. Just create all the tables and add all foreign key constraint as shown.

You can add your columns as indicated with other columns and Id column can be changed from bigInt to Int.

CREATE TABLE [dbo].[User]
(
UserId BIGINT NOT NULL,
UserName VARCHAR(10),
--Other columns
CONSTRAINT PkUserId PRIMARY KEY CLUSTERED (UserId ASC) 
);
GO
CREATE TABLE [dbo].[Topic]
(
TopicId BIGINT NOT NULL,
UserId BIGINT NOT NULL,
TopicData VARCHAR(20)
--Other columns
CONSTRAINT PkpostId PRIMARY KEY CLUSTERED(TopicId ASC)
);
ALTER TABLE [dbo].[Topic] WITH CHECK 
ADD CONSTRAINT [FkTopicUserId] FOREIGN KEY(UserId)
REFERENCES [dbo].[User]([UserId]);
GO
CREATE TABLE [dbo].[Comment]
(
CommentId BIGINT NOT NULL,
TopicId BIGINT NOT NULL,
UserId BIGINT NOT NULL,
CommentData VARCHAR(20),
--Other columns
CONSTRAINT PkCommentId PRIMARY KEY CLUSTERED(CommentId ASC)
);

ALTER TABLE [dbo].[Comment] WITH CHECK 
ADD CONSTRAINT [FkCommentTopic] FOREIGN KEY(TopicId)
REFERENCES [dbo].[Topic](TopicId);
GO
ALTER TABLE [dbo].[Comment] WITH CHECK 
ADD CONSTRAINT [FkCommentUser] FOREIGN KEY(UserId)
REFERENCES [dbo].[User](UserId);

Now for Multiple delete cascade you can use INSTEAD OF DELETE Triggers as

CREATE TRIGGER [DELETE_User]
   ON [dbo].[User]
   INSTEAD OF DELETE
AS 
BEGIN
 SET NOCOUNT ON;
 DELETE FROM [dbo].[Topic] WHERE UserId IN (SELECT UserId FROM DELETED)
 DELETE FROM  [dbo].[Comment] WHERE UserId IN (SELECT UserId FROM DELETED)
 DELETE FROM dbo.[User] WHERE UserId IN (SELECT UserId FROM DELETED)
END
GO
CREATE TRIGGER [DELETE_Topic]
   ON [dbo].[Topic]
   INSTEAD OF DELETE
AS 
BEGIN
 SET NOCOUNT ON;
 DELETE FROM [dbo].[Topic] WHERE UserId IN (SELECT UserId FROM DELETED)
 DELETE FROM  [dbo].[Comment] WHERE UserId IN (SELECT UserId FROM DELETED)
END
GO

Let's check with some data on these the above design.

INSERT INTO [dbo].[User]
VALUES (1,'user1'),(2,'user2');
GO
INSERT INTO [dbo].[Topic]
VALUES (1,1,'POST1'), (2,1,'POST2'),(3,2,'POST3');
GO
INSERT INTO [dbo].[Comment]
VALUES  (1,1,'COmment1'), (2,1,'Comment2'),
(3,1,'Comment3'), (4,2,'Comment4'), (5,3,'Comment4');

Now check with cascade delete.

SELECT UserId,UserName FROM [dbo].[User];
SELECT TopicId, UserId FROM [dbo].[Topic];
SELECT CommentId,TopicId FROM [dbo].[Comment];
GO
DELETE FROM [dbo].[Topic] WHERE TopicId =1;
GO
SELECT UserId,UserName FROM [dbo].[User];
SELECT TopicId, UserId FROM [dbo].[Topic];
SELECT CommentId,TopicId FROM [dbo].[Comment];
GO
DELETE FROM [dbo].[User] WHERE UserId = 2;
GO
SELECT UserId,UserName FROM [dbo].[User];
SELECT TopicId, UserId FROM [dbo].[Topic];
SELECT CommentId,TopicId FROM [dbo].[Comment];