I can't seem to create a seemingly simple database relationship in SQL Server Management Studio with foreign keys or with triggers

Question

So, I am trying to simplify the problem as much as possible. I'm trying to create 2 tables.

A Users table that only contains the primary key column, User_ID.

And a Friends table with 3 columns, Friendship_ID, User_ID, and Friend_ID.

Friendship_ID is the primary key of the table. I want User_ID and Friend_ID to be foreign keys referencing the User_ID in the Users table.

This way, the User_ID and Friend_ID must exist in the Users table to be created in the Friends table and that Friendship_ID will be deleted anytime that either of the 2 in the relationship are deleted from the Users table.

Thus, I would like for the delete action to cascade through those 2 columns. But when I try to specify my second foreign key in a cascade mode, I get the following error.

Unable to create relationship 'FK_Friends_Users1'.
Introducing FOREIGN KEY constraint 'FK_Friends_Users1' on table 'Friends' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Here is the database diagram for the layout that I would like, where both of those foreign keys cascade their delete.

Database Diagram

I understand how multiple cascade paths could occur from that layout, so the solution that I had read was to use triggers, so I made sure that I removed all cascading from my foreign keys and instead I chose to use a trigger to accomplish the deleting that I needed. Here is the code for my trigger

CREATE TRIGGER DeletePrimaryUsers
ON dbo.Users
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM Friends WHERE User_ID IN (SELECT User_ID FROM deleted)
    DELETE FROM Friends WHERE Friend_ID IN (SELECT User_ID FROM deleted)
    DELETE FROM Users WHERE User_ID IN (SELECT User_ID FROM deleted)
END
GO

This did not work either. It has no problem when I delete a row from the Friends tables, but when I attempt to delete a row from my Users table, sometimes I get an error. Here's an example

Users:

User_ID
-------
501
502
503
504
505

Friends:

Friendship_ID  User_ID  Friend_ID
----------------------------------
5              503      504
6              503      501
8              503      505

When I attempt a delete of row 1 in the Users table, User_ID number 501, I get the following error.

No rows were deleted

A problem occurred attempting to delete row 1.
Error Source:.NET SqlClient Data Provider
Error Message: The DELETE statement conflicted with the REFERENCE constraint "FK_Friends_Users1". The conflict occurred in database "FriendTest", table "dbo.Friends", column 'Friend_ID'.

Correct the errors and attempt to delete the row again or press ESC to cancel the change(s).

FK_Friends_Users1 is the foreign key for the Friend_ID column. That foreign key, like the one for my User_ID column, is set to No Action on UPDATE and DELETE for my trigger.

Any help would be greatly appreciated.

Edit:

Foreign Key Creation script.

USE FriendTest
ALTER TABLE dbo.Friends WITH CHECK
   ADD CONSTRAINT FK_Friends_Users FOREIGN KEY (User_ID)
   REFERENCES dbo.Users (User_ID)

USE FriendTest
ALTER TABLE dbo.Friends WITH CHECK
   ADD CONSTRAINT FK_Friends_Users1 FOREIGN KEY (Friend_ID)
   REFERENCES dbo.Users (User_ID)

Answer 1

The trigger should be updated as follows:

CREATE TRIGGER DeletePrimaryUsers
ON dbo.Users
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE FROM Friends WHERE User_ID IN (SELECT User_ID FROM deleted) 
         OR Friend_ID IN (SELECT User_ID FROM deleted);
    DELETE FROM Users WHERE User_ID IN (SELECT User_ID FROM deleted)
END
GO`

Answer 2

Perhaps an easier way to do it would be to replace the first two lines with this:

DELETE FROM Friends 
WHERE friend_id IN (SELECT user_id FROM deleted) 
OR user_id IN (SELECT userid FROM deleted);