SQL Server 2008: Can't create two foreign keys for for the same parent table

Question

I have two tables in SQL Server, one named Users and the other named Connections and connections is simply a connection between user and another user so my tables are like:

CREATE TABLE Users(
   UserUID int IDENTITY NOT NULL,
   UserUN NVARCHAR(100) NOT NULL,
   UserPassword NVARCHAR(100) NOT NULL,

   PRIMARY KEY(UserUID)
)

CREATE TABLE Connections(
   _CID int IDENTITY NOT NULL,
   _UID1 int not null,
   _UID2 int not null,
   _ConDate datetime null DEFAULT GETDATE(),

   PRIMARY KEY(_CID),
   FOREIGN KEY(_UID1) REFERENCES Users(UserUID) ON DELETE CASCADE,
   FOREIGN KEY(_UID2) REFERENCES Users(UserUID) ON DELETE CASCADE
)

But every time I run the query it throws error:

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

I tried searching in google and i found an answer that says I should do a trigger, but I don't know what is that. I hope I can find some answers and thanks a lot.

Answer 1

The problem with the table structure you're trying to define is:

Suppose you have a record in Users with ID 1, and a record in Connections with _UID1 = 1, and _UID2 = 1 too. Logically, in this case, that makes little sense, but the database does not know what Users means, nor what Connections means.

Now, you delete that user. Because of the ON DELETE CASCADE for _UID1, that Connections record should be deleted too. Because of the ON DELETE CASCADE for _UID2, that Connections record should be deleted again. This is a technical limitation in SQL Server, it just cannot handle multiple operations on the same row, and does not know how it should make sure the row is only deleted once in that case.

To prevent this problem, the ON DELETE CASCADE is simply not allowed in such a case where multiple updates of the same row could result.

I tried searching in google and i found an answer that says I should do a trigger, but I don't know what is that.

A trigger is a custom action, that can contain arbitrary SQL commands, that would automatically run after (or even "when") you make any modifications in a table. I could explain in more detail, but unlike what you found, I don't think you should do that.

Instead, if you want to delete user 1, I would recommend writing it as two separate DELETE statements:

DELETE Connections WHERE _UID1 = 1 OR _UID2 = 1;
DELETE Users WHERE UserUID = 1;