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.