2

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:

enter image description here

  • 2
    Please add the error you get when you try to define the foreign key constraints with the cascade option. – ypercubeᵀᴹ Jan 14 '15 at 8:54
2

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];
  • You haven't created a Comment -> User foreign key. – ypercubeᵀᴹ Jan 14 '15 at 9:52
  • @ypercube, Yes you are right edited my answer. Thanks – vijayp Jan 14 '15 at 10:41
  • okey thank you :-D. I solve my problem :D – lecuong92 Jan 15 '15 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.