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 (
Treasurer). Both those columns are
int and FKs back to
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.