约束强制配对的数据库设计

小编典典

约束强制配对的数据库设计

sql

如何最好地设计一个数据库，其中我有一个球员表（具有主键 player_id ），我希望将其配对成两 人一组
，以便数据库可以强制执行以下约束：每个团队正好由两个球员组成，每个球员都在 最多一 队？

我可以想到两个解决方案，但是我都不满意。

一种可能是拥有两列 player1_id* 和 player2_id ，它们是指向播放器表中的 player_id 列的唯一
外键。需要进行额外的检查，以使没有一个玩家同时是一个团队的玩家1和第二个团队的玩家2。 *

我想到的另一种可能性是将玩家表和团队表与团队成员关系表连接，该成员表具有 唯一的 外键到玩家表中的 player_id
列，而第二个外键指向团队的主键桌子。在这里，必须添加一个检查，即每个团队都只有两个成员。

有没有更好的设计可以简化约束的检查？

如果重要的话：我正在使用的数据库是PostgreSQL
8.4，并且我希望它强大的规则系统尽可能地触发。

编辑：基于AlexKuznetsov的答案的解决方案

它对我来说还不是很完美，但是我比以前更喜欢它。我修改了Alex的解决方案，因为我不想从球员到团队拥有外键，因为在申请阶段，球员可以报名。

create table TeamMemberships(
  player_id int not null unique references Players(player_id),
  team_id int not null references Teams(team_id),
  NumberInTeam int not null check(NumberInTeam in (0,1)),
  OtherNumberInTeam int not null, -- check(OtherNumberInTeam in (0,1)) is implied
  check(NumberInTeam + OtherNumberInTeam = 1)
  foreign key (team_id, OtherNumberInTeam) references TeamMemberships(team_id, NumberInTeam),
  primary key (team_id, NumberInTeam)
);

此定义可确保团队成员身份成对出现（并成对插入）。现在，玩家最多只能加入一个团队，而团队中最多只能有0位或2位玩家。为了确保每个团队都有成员，我可以在团队表中添加一个指向其两个成员资格中任何一个的外键。但是像Erwin一样，我也不喜欢延迟约束检查。有什么想法可以对此进行改进吗？还是有一种完全不同的，更好的方法？

PS：该方法也适用于n> 2的玩家。只需用值（即约束）NumberInTeam + 1 mod
n将NextNumberInTeam替换为OtherNumberInTeam。

阅读 166

2021-04-15

共1个答案

小编典典

我不知道这是否可以在Postgress上使用，但这是一个SQL Server解决方案：

CREATE TABLE dbo.Teams(TeamID INT NOT NULL PRIMARY KEY);
GO
CREATE TABLE dbo.Players(PlayerID INT NOT NULL PRIMARY KEY,
  TeamID INT NOT NULL FOREIGN KEY REFERENCES dbo.Teams(TeamID),
  NumberInTeam INT NOT NULL CHECK(NumberInTeam IN (1,2)),
  TeamMateID INT NOT NULL,
  TeamMatesNumberInTeam INT NOT NULL,
-- if NumberInTeam=1 then TeamMatesNumberInTeam must be 2
-- and vise versa
  CHECK(NumberInTeam+TeamMatesNumberInTeam = 3), 
  UNIQUE(TeamID, NumberInTeam),
  UNIQUE(PlayerID, TeamID, NumberInTeam),
  FOREIGN KEY(TeamMateID, TeamID, TeamMatesNumberInTeam)
    REFERENCES dbo.Players(PlayerID, TeamID, NumberInTeam)
);

INSERT INTO dbo.Teams(TeamID) SELECT 1 UNION ALL SELECT 2;
GO

-您只能成对插入玩家

INSERT INTO dbo.Players(PlayerID, TeamID, NumberInTeam, TeamMateID, TeamMatesNumberInTeam)
SELECT 1,1,1,2,2 UNION ALL
SELECT 2,1,2,1,1;

您可以尝试插入一个球员，或者从一个团队中删除一个球员，或者每个团队中插入两个以上的球员-由于一组完整的约束，所有这些都将失败。

注意：SQL Server的做法是显式命名所有约束。我没有命名我的约束，以防万一与Postgres不兼容。

2021-04-15