CODEWITHSUNDEEP
sql-servert-sqlcascading-deletes
Artyomska
Artyomska

Reputation: 1335

ON CASCADE DELETE on a many to many relationship between the same table

In my SQL Database I have created the following tables for a M-to-M relationship

User
ID username userpass

User_Relationship
ID user1ID, user2ID

So user1ID is a foreign key to the User ID, and the same goes for the user2ID. What I am trying to do is that when I delete one row from the user with the ID lets say 1, I also want to delete the rows from User_Relationship where user1ID or user2ID is 1, but only that, not any other rows. I already tried to set ON CASCADE DELETE to both of the foreign keys from the User_Relationship table, but I receive the following error:

Introducing FOREIGN KEY constraint on table 'User_Relationship' may cause
cycles or multiple cascade paths

So how could I delete one value from the User table and to delete every row in User_Relationship where either user1ID or user2ID has the foreign key set as the ID of the User? I am using SQL Management Studio. Thank you.

Upvotes: 1

Views: 2646

Answers (2)

mikolaj semeniuk
mikolaj semeniuk

Reputation: 2458

According to these articles:

You should use trigger and remove on delete cascadeto achieve desired result.

Example solution to your problem could look like below:

CREATE TABLE User ( -- consider changing name of this table to `Users` instead of `User`
    ID INT PRIMARY KEY IDENTITY(1,1), -- probably `Id` would be better name instead of `ID` because `ID` looks like SQL reserved keyword
    username VARCHAR(MAX), 
    userpass VARCHAR(MAX) -- please consider hash passwords before saving to database
)

CREATE TABLE User_Relationship ( -- here `User_Relationships` instead of `User_Relationship` as well
    user1ID INT NOT NULL,
    user2ID INT NOT NULL,
    CONSTRAINT ID PRIMARY KEY (user1ID, user2ID), -- here `Id` instead of `ID` as well
    CONSTRAINT fk_user1ID FOREIGN KEY (user1ID) REFERENCES User (ID),
    CONSTRAINT fk_user2ID FOREIGN KEY (user2ID) REFERENCES User (ID)
)

CREATE TRIGGER Delete_User_Relationships
   ON User
   INSTEAD OF DELETE
AS 
BEGIN
 SET NOCOUNT ON;
 DELETE FROM User_Relationship WHERE (user1ID IN (SELECT ID FROM DELETED)) OR (user2ID IN (SELECT ID FROM DELETED))
 DELETE FROM User WHERE ID IN (SELECT ID FROM DELETED)
END

Upvotes: 0

Tomas Hesse
Tomas Hesse

Reputation: 395

I got this to work: tables: A=> PK a_id B=> PK b_id A_B =>

CREATE TABLE [dbo].[A_B](
    [a_id] [int] NOT NULL,
    [b_id] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_A_B] PRIMARY KEY CLUSTERED 
(
    [a_id] ASC,
    [b_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[A_B]  WITH CHECK ADD  CONSTRAINT [FK_a_b_b] FOREIGN KEY([A_Id])
REFERENCES [dbo].[A] ([a_id]) on delete cascade
GO

ALTER TABLE [dbo].[A_B] CHECK CONSTRAINT [FK_a_b_b]
GO

ALTER TABLE [dbo].[A_B]  WITH CHECK ADD  CONSTRAINT [FK_a_b_a] FOREIGN KEY([B_Id])
REFERENCES [dbo].[B] ([b_Id]) on delete cascade
GO

ALTER TABLE [dbo].[A_B] CHECK CONSTRAINT [FK_a_b_a] GO

Upvotes: 1

Related Questions