Sql-server – Two FOREIGN KEY constraints with SET NULL targeting the same PRIMARY table

database-designforeign keysql serversql server 2014sql-server-2017

I feel like I'm missing something obvious here…

I have a situation with a primary table (let's call it People) and a secondary table (let's call it Groups) with two different roles that people might fill (Secretary & Treasurer). Both those columns are int and FKs back to People.ID. 

CREATE TABLE People(
ID int NOT NULL,
FullName varchar(50) NOT NULL,
CONSTRAINT [PK_People] PRIMARY KEY CLUSTERED (ID ASC))

CREATE TABLE Groups(
ID int NOT NULL,
Treasurer int NULL,
Secretary int NULL,
CONSTRAINT PK_Groups PRIMARY KEY CLUSTERED (ID ASC))

So far so good. But the roles are optional (nullable) so I want to be able to delete a Person and have the FK values in Groups set to NULL.

ALTER TABLE dbo.Groups ADD CONSTRAINT FK_Groups_Treasurer FOREIGN KEY (Treasurer) REFERENCES dbo.People (ID) ON DELETE SET NULL

ALTER TABLE dbo.Groups ADD CONSTRAINT FK_Groups_Secretary FOREIGN KEY (Secretary) REFERENCES dbo.People (ID) ON DELETE SET NULL

Now we have a problem:

Introducing FOREIGN KEY constraint 'FK_Groups_Secretary' on table 'Groups' may cause cycles or multiple cascade paths.

What is the problem here, and why does SQL Server care? What is the possible cascade conflict? If I delete a Person, set NULL the FK values in any related tables. This would be no problem against two different tables, each with a FK. But having two FK values in the same table seems to be prohibited.

Am I missing something? Is this somehow bad design? What's the correct way to deal with this situation? It seems like a common use-case.

Best Answer

It would seem you have cascading deletes on and you'll end up deleting someone who could theoretically be referenced in another group as a treasurer or secretary.

The group table is violating the 1st normal form. This is akin to having address1, address2 in a table.

You need a third table GroupPersonRole that holds groupid, the person id and the roleid.

Define your person in one table. Define your group in another table. And define your role in another table. Then in a fourth, say GroupPersonRole, you can combine them and draw your FKs as needed.

What if someone wants to add more group roles? You don't want to be modifying schema every time someone gets a new idea.

Related Question