Sinto que estou perdendo algo óbvio aqui...
Eu tenho uma situação com uma tabela primária (vamos chamá-la de People
) e uma tabela secundária (vamos chamá-la de Groups
) com dois papéis diferentes que as pessoas podem preencher ( Secretary
& Treasurer
). Ambas as colunas são int
e FKs de volta para 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))
Até agora tudo bem. Mas as funções são opcionais (anuláveis), então eu quero poder excluir uma Pessoa e ter os valores FK em Grupos definidos como 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
Agora nós temos um problema:
A introdução da restrição FOREIGN KEY 'FK_Groups_Secretary' na tabela 'Groups' pode causar ciclos ou vários caminhos em cascata.
Qual é o problema aqui e por que o SQL Server se importa? Qual é o possível conflito em cascata? Se eu excluir um Person
, defina NULL os valores FK em qualquer tabela relacionada. Isso não seria problema contra duas tabelas diferentes, cada uma com um FK. Mas ter dois valores FK na mesma tabela parece ser proibido.
Estou esquecendo de algo? Isso é de alguma forma um design ruim? Qual é a maneira correta de lidar com essa situação? Parece um caso de uso comum.
Parece que você tem exclusões em cascata e acabará excluindo alguém que teoricamente poderia ser referenciado em outro grupo como tesoureiro ou secretário.
A tabela de grupo está violando a 1ª forma normal. Isso é semelhante a ter address1, address2 em uma tabela.
Você precisa de uma terceira tabela GroupPersonRole que contenha groupid, o ID da pessoa e o roleid.
Defina sua pessoa em uma tabela. Defina seu grupo em outra tabela. E defina sua função em outra tabela. Então, em um quarto, digamos GroupPersonRole, você pode combiná-los e desenhar seus FKs conforme necessário.
E se alguém quiser adicionar mais funções de grupo? Você não quer modificar o esquema toda vez que alguém tiver uma nova ideia.