具有多对多约束约束的外键 [英] Foreign key with many-to-many containment constraint
问题描述
快速问题，我在输入搜索字词时遇到问题：
假设我在玩家和团队之间有多对多关系：
CREATE TABLE players（
id bigserial primary key，
name text not null
）;
CREATE TABLE teams（
id bigserial primary key，
name text not null，
team_captain_id bigint not null references players（id）
）;
CREATE TABLE team_players（
id bigserial primary key，
player_id bigint not null，
team_id bigint not null
）;
ALTER TABLE team_players ADD CONSTRAINT uq_team_players UNIQUE（player_id，team_id）;
现在，每个团队都需要有一个团队队长，他也是一个球员。但是我想强制团队队长也是那个团队的成员（或者，在语义上等同于团队队长在连接表中不是多余的）。
有一个标准的方法来模拟这个？我可以想出几种方法来实际完成这项工作，但我想知道是否有一个标准的，优雅的做法。
谢谢！
编辑：虽然船长有必要的领域是很好的，但我也满足以下条件：如果球队至少有一名成员，为它定义。
编辑2：好的，这里是一个澄清的尝试。 Pardon不必要的id列。
CREATE TABLE玩家（
id bigserial主键，
名称text not null
）;
CREATE TABLE teams（
id bigserial primary key，
name text not null
）;
CREATE TABLE领导（
id bigserial主键，
team_id bigint not null引用团队（id），
captain_id bigint not null引用玩家b
$ b - 创建一个键
UNIQUE（team_id，captain_id），
- 每个团队只有一个领导者
UNIQUE $ b）;
CREATE TABLE team_players（
id bigserial主键，
team_id bigint not null，
captain_id bigint not null，
player_id bigint not null，
- 每个玩家一个条目
UNIQUE（team_id，captain_id，player_id），
- 对领导的有效引用
FOREIGN KEY ，captain_id）引用领导（team_id，captain_id），
- 不是队长
CHECK（player_id<&captain_id）
）;
您需要了解数据库设计。
查找fill-in-the-（named-）空白语句描述您的应用程序。每个语句都获得一个表。
// [player_id]是玩家
玩家）
// [team_id]是一个团队
team（team_id）
// player [player_id] plays for team [team_id]
team_players （team_id，player_id）
原来你不需要player_team_id。 team_players（player_id，team_id）对是1：1，因此您可以改用它们。
每个team_players player_id是一个玩家player_id（因为每个团队玩家都是一个播放器）。我们说通过FOREIGN KEY delaration（和DBMS强制执行）：
FOREIGN KEY（team_id）REFERENCES team（team_id）
FOREIGN KEY（player_id）REFERENCES player（player_id）
team_id）是唯一的。但更多的是真的。没有包含的子行是唯一的。这对数据库设计很重要。
唯一的子行是超级键。包含没有较小唯一子行的唯一子行是键。使用KEY。键列的任何超集都是唯一的。但是SQL要求明确声明FOREIGN KEY的目标。使用UNIQUE。传统上在SQL中，您选择一个键作为PRIMARY KEY。这对某些SQL功能很重要。 （技术上，在SQL KEY意味着UNIQUE和PRIMARY KEY意味着UNIQUE NON NULL。Ie SQL不强制no-smaller-contained-unique-subrow。）
KEY（team_id，player_id）
（如果您在team_players中也有team_player_id它也将是一个钥匙，通常是PK。）
有些玩家是船长。是1：1。因此，team_id和player_id都是唯一的。
// [player_id] captains [team_id]
team_captains（team_id，player_id ）
FOREIGN KEY（team_id）REFERENCES team（team_id）
FOREIGN KEY（player_id）REFERENCES player（player_id）
KEY（team_id）
KEY（player_id）
队长对必须显示为队友对。
FOREIGN KEY（team_id，player_id）REFERENCES team_players（team_id，player_id）
你对冗余队长的想法令人钦佩。确实，有一种感觉，在数据库记录中，一个人是一个团队的队长，而他们在一个给定的团队是多余的。
- 而不是有team_players（team_id，player_id）
- team_players team_players FK现在到这里
// player [player_id]是团队中的非队长[team_id ]
team_non_captains（team_id，player_id）
FOREIGN KEY（team_id）REFERENCES team（team_id）
FOREIGN KEY（player_id）REFERENCES player（player_id）
KEY（team_id，player_id）$但是，每次你想要一个球队的球员，你必须说：
- 现在team_player =
// player [player_id]是队伍中的非队长[team_id]
/ / OR player [player_id]是team的队长[teamm_id]
select * from team_non_captains UNION select * from team_captains
事实证明，每个队长拥有一个冗余行可能更糟糕，因为每个涉及整个队列的查询都有一个冗余联合运算（和一个子表达式的冗余人工解析）球队。
（在初始设计中避免使用null，它们使表的含义和查询的含义复杂化，尤其是查询的含义，因为SQL不会评估涉及nulls的表达式一种方式意味着任何特别是在查询中的表的含义，更不用说不知道或不适用。一个使用它们作为工程权衡，你必须学会判断。）
Quick question that I'm having trouble putting into search terms:
Suppose I have a many-to-many relationship between players and teams:
CREATE TABLE players (
id bigserial primary key,
name text not null
);
CREATE TABLE teams (
id bigserial primary key,
name text not null,
team_captain_id bigint not null references players(id)
);
CREATE TABLE team_players (
id bigserial primary key,
player_id bigint not null,
team_id bigint not null
);
ALTER TABLE team_players ADD CONSTRAINT uq_team_players UNIQUE (player_id,team_id);
Now, each team is required to have a team captain, who is also a player. But I want to enforce that the team captain is also a member of that team (or, semantically equivalent, that the team captain is not redundantly in the join table)
Is there a standard way to model this? I can think of several ways that would actually get the job done, but I'm wondering if there's a standard, elegant way of doing it.
Thanks!
EDIT: Although it would be nice to have the captain a required field, I would also be content with the following condition: If the team has at least 1 member, then a captain is defined for it.
EDIT 2: OK, here's an attempt for clarification. Pardon the unnecessary "id" columns.
CREATE TABLE players (
id bigserial primary key,
name text not null
);
CREATE TABLE teams (
id bigserial primary key,
name text not null
);
CREATE TABLE leaderships (
id bigserial primary key,
team_id bigint not null references teams(id),
captain_id bigint not null references players(id),
-- Make a key.
UNIQUE (team_id,captain_id),
-- Only one leadership per team.
UNIQUE (team_id)
);
CREATE TABLE team_players (
id bigserial primary key,
team_id bigint not null,
captain_id bigint not null,
player_id bigint not null,
-- One entry per player.
UNIQUE (team_id,captain_id,player_id),
-- Valid reference to a leadership.
FOREIGN KEY (team_id,captain_id) references leaderships(team_id,captain_id),
-- Not the captain.
CHECK (player_id <> captain_id)
);
解决方案 You need to learn about database design.
Find fill-in-the-(named-)blank statements that describe your application. Each statement gets a table. A table holds the rows that make a true statement.
// [player_id] is a player
player(player_id)
// [team_id] is a team
team(team_id)
// player [player_id] plays for team [team_id]
team_players(team_id,player_id)
Turns out you don't need a player_team_id. The team_players (player_id,team_id) pairs are 1:1 with them so you can use those instead. On the other hand team-player contracts are 1:1 with them so they might have a role.
Every team_players player_id is a player player_id (since every team player is a player). We say that via a FOREIGN KEY delaration (and the DBMS enforces it):
FOREIGN KEY (team_id) REFERENCES team (team_id)
FOREIGN KEY (player_id) REFERENCES player (player_id)
It's true that team_players (player_id,team_id) is unique. But more than that is true. No contained subrow is unique. This matters to database design.
A unique subrow is a "superkey". A unique subrow containing no smaller unique subrow is a "key". Use KEY for that. Any superset of key columns is unique. But SQL requires that the target of a FOREIGN KEY be explictly declared so. Use UNIQUE for that. Traditionally in SQL you pick one key as PRIMARY KEY. This matters to some SQL functionality. (Technically, in SQL KEY means UNIQUE and PRIMARY KEY means UNIQUE NON NULL. Ie SQL does not enforce no-smaller-contained-unique-subrow.)
KEY (team_id,player_id)
(If you also had a team_player_id in team_players it too would be a KEY, typically the PK.)
Some players are captains. It's 1:1. So both team_id and player_id are unique.
// [player_id] captains [team_id]
team_captains(team_id,player_id)
FOREIGN KEY (team_id) REFERENCES team (team_id)
FOREIGN KEY (player_id) REFERENCES player (player_id)
KEY (team_id)
KEY (player_id)
A team-captain pair must appear as a team-player pair.
FOREIGN KEY (team_id,player_id) REFERENCES team_players (team_id,player_id)
Your thoughts on redundancy re captains is admirable. It is true that there is a sense in which it is redundant to have the database record that a person is a team's captain and that they are on a given team.
-- instead of having team_players(team_id,player_id)
-- team_players team_players FK now to here
// player [player_id] is a non-captain on team [team_id]
team_non_captains(team_id,player_id)
FOREIGN KEY (team_id) REFERENCES team (team_id)
FOREIGN KEY (player_id) REFERENCES player (player_id)
KEY (team_id,player_id)
However, every time you wanted the players on a team you'd have to say:
-- now team_player =
// player [player_id] is a non-captain on team [team_id]
// OR player [player_id] is captain of team [teamm_id]
select * from team_non_captains UNION select * from team_captains
It turns out it is probably worse to have one "redundant" row per captain than it is to have one "redundant" union operation (and "redundant" human parsing of a sub-expression) per query involving a whole team. Just make the most straightforward statements.
(Avoid nulls in an initial design. They complicate table meanings and query meanings. Especially query meanings because SQL does not evaluate expressions involving nulls in a way that means means anything in particular in terms of the meanings of tables in a query, let alone "not known" or "not applicable". One uses them as an engineering tradeoff which you must learn to judge.)
这篇关于具有多对多约束约束的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
