1

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.

1

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.

  • No cascading deletes (i.e. one delete causes a second delete), and yes, I want to remove the person from either role across Groups when the person is deleted. Technically, I would think this would work. But structurally, yeah, you're 100% right. Role mapping is what I usually do but I was trying to be cheap here. (This is not my real code, obviously.) I have a bunch of places that reference identical "people" tables and I wanted to combine those into one table without a many-to-many mapping since there is zero overlap between roles. – Neil Laslett Oct 3 at 16:54
  • Normalizing has an upside and a downside. Violating 1NF also has an upside and a downside. Your task is to figure out whether, in this case, it's better to follow the normalization rule or to deviate from it. – Walter Mitty Oct 4 at 9:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.