CODEWITHSUNDEEP
sqlsql-server
Bread
Bread

Reputation: 443

Foreign Key Cascade On Delete

Assume that i have the following tables

User,Article,Comment

A User can Comment on an Article or write an Article.

I wanted the behavior that:

So i thought i should use FOREIGN KEYS and tried to model the above as the following

CREATE TABLE [User](
   UserId int PRIMARY KEY IDENTITY(1,1),
   Username nvarchar(50) NOT NULL
)

CREATE TABLE [Article](
   ArticleId int PRIMARY KEY IDENTITY(1,1),
   UserId int NOT NULL,
   FOREIGN KEY(UserId) references [User](UserId) ON DELETE CASCADE
)

CREATE TABLE [Comment](
   CommentId int PRIMARY KEY IDENTITY(1,1),
   UserId int Not NULL,
   ArticleId int NOT NULL ,
   FOREIGN KEY(UserId) references [User](UserId) ON DELETE CASCADE,
   FOREIGN KEY(ArticleId) references [Article](ArticleId) ON DELETE CASCADE
)

But the problem comes with the Comment causing

Introducing FOREIGN KEY constraint 'FK__Comment__Article__32E0915F' on table 'Comment' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

My question is how would you model this behavior and still use CASCADE ON DELETE AND FOREIGEN KEYS?

Upvotes: 0

Views: 61

Answers (1)

Neil
Neil

Reputation: 36

You should just be able to remove the Cascade Delete on the User Foreign Key on the Comment.

As when the user is deleted, this will cascade delete the Article, which will in turn Cascade Delete the comments:

CREATE TABLE [Comment](
   CommentId int PRIMARY KEY IDENTITY(1,1),
   UserId int Not NULL,
   ArticleId int NOT NULL ,
   FOREIGN KEY(UserId) references [User](UserId),
   FOREIGN KEY(ArticleId) references [Article](ArticleId) ON DELETE CASCADE
)

Upvotes: 2

Related Questions