引用组合主键的一部分 [英] referencing part of the composite primary key

问题描述

我在数据库表上设置引用时遇到问题。
我具有以下结构：

I have problems with setting the reference on database table. I have following structure:

CREATE TABLE club(
    id INTEGER NOT NULL,
    name_short VARCHAR(30),
    name_full VARCHAR(70) NOT NULL
);
CREATE UNIQUE INDEX club_uix ON club(id);
ALTER TABLE club ADD CONSTRAINT club_pk PRIMARY KEY (id);

CREATE TABLE team(
    id INTEGER NOT NULL,
    club_id INTEGER NOT NULL,
    team_name VARCHAR(30)
);
CREATE UNIQUE INDEX team_uix ON team(id, club_id);
ALTER TABLE team ADD CONSTRAINT team_pk PRIMARY KEY (id, club_id);
ALTER TABLE team ADD FOREIGN KEY (club_id) REFERENCES club(id);

CREATE TABLE person(
    id INTEGER NOT NULL,
    first_name VARCHAR(20),
    last_name VARCHAR(20) NOT NULL
);
CREATE UNIQUE INDEX person_uix ON person(id);
ALTER TABLE person ADD PRIMARY KEY (id);

CREATE TABLE contract(
    person_id INTEGER NOT NULL,
    club_id INTEGER NOT NULL,
    wage INTEGER
);
CREATE UNIQUE INDEX contract_uix on contract(person_id);
ALTER TABLE contract ADD CONSTRAINT contract_pk PRIMARY KEY (person_id);
ALTER TABLE contract ADD FOREIGN KEY (club_id) REFERENCES club(id);
ALTER TABLE contract ADD FOREIGN KEY (person_id) REFERENCES person(id);

CREATE TABLE player(
    person_id INTEGER NOT NULL,
    team_id INTEGER,
    height SMALLINT,
    weight SMALLINT

);
CREATE UNIQUE INDEX player_uix on player(person_id);
ALTER TABLE player ADD CONSTRAINT player_pk PRIMARY KEY (person_id);
ALTER TABLE player ADD FOREIGN KEY (person_id) REFERENCES person(id);
-- ALTER TABLE player ADD FOREIGN KEY (team_id) REFERENCES team(id); --this is not working

它给了我这个错误：

Error code -5529, SQL state 42529: a UNIQUE constraint does not exist on referenced columns: TEAM in statement [ALTER TABLE player ADD FOREIGN KEY (team_id) REFERENCES team(id)]

如您所见，团队表具有复合主键（club_id + id），人通过合同提及俱乐部。
Person具有球员和其他职员类型的一些共同属性。

As you can see, team table has composite primary key (club_id + id), the person references club through contract. Person has some common attributes for player and other staff types.

一个俱乐部可以拥有多支球队。用人单位必须与俱乐部订立合同。可以将球员（人的规格）（如果愿意的话）指定给俱乐部的一支球队。

One club can have multiple teams. Employed person has to have a contract with a club. Player (is the specification of person) - if emplyed - can be assigned to one of the club's teams.

有没有更好的方法来设计我的结构？我曾考虑过从团队的主键中排除club_id，但我想知道这是否是唯一的方法。谢谢。

Is there better way to design my structure? I thought about excluding the club_id from team's primary key, but I would like to know if this is the only way. Thanks.

更新1

我希望将ID作为团队使用仅在俱乐部内进行标识，因此多个团队可以具有相同的ID，只要它们属于不同的俱乐部即可。

I would like to have the id as team identification only within the club, so multiple teams can have equal id as long as they belong to different clubs. Is it possible?

UPDATE 2
根据philip的建议更新了命名约定

UPDATE 2 updated the naming convention as adviced by philip

一些业务规则可以更好地理解结构：

Some business rules to better understand the structure:

• 一个俱乐部可以拥有1..n支球队（主队，预备队小队，青年小队
  或A队，B队...只有一支球队可以参加比赛，而不是俱乐部）


• 一个团队只能属于一个俱乐部


• 球员是人的类型（其他类型的（人员）是球探，教练等，因此他们不需要属于特定的团队，只要属于俱乐部，就可以）


• 人员可以与1个俱乐部签订0..1合同（这意味着他已被雇用或失业）


• 玩家（如果受雇）属于俱乐部的一个团队

• One club can have 1..n teams (Main squad, Reserve squad, Youth squad or Team A, Team B... only team can play match, not club)
• One team belongs to one club only
• A player is type of person (other types (staff) are scouts, coaches etc so they do not need to belong to specific team, just to the club, if employed)
• Person can have 0..1 contract with 1 club (that means he is employed or unemployed)
• Player (if employed) belongs to one team of the club

现在考虑一下-将team_id从玩家转移到合同可以解决我的问题，并且可以满足以下条件：玩家（如果受雇）属于一个团队俱乐部，但对于其他类型的员工而言则是多余的。您的想法是什么？

Now thinking about it - moving team_id from player to contract would solve my problem, and it could hold the condition "Player (if employed) belongs to one team of the club", but it would be redundant for other staff types. What do you think?

推荐答案

当一个表中的一个子行必须是另一个（引用的）表中的一个子行时，即包含依赖项（IND）。例如，玩家team_id引用团队ID（而不是团队中的键）。当存在IND 和时，引用的子行是一个键，即外键（FK）。例如，玩家person_id引用了个人ID（个人密钥）。在SQL中，FOREIGN KEY声明表示存在IND，并且引用的列是唯一的。 （即通过PRIMARY KEY或UNIQUE声明）。 （因此，它实际上意味着外来超键。）在SQL中，理想情况下，我们会通过CHECK约束声明一个IND（如果还没有FK）。 （但是DBMS不太支持CHECK）。即检查玩家team_id是否在ID中预测的球队中。但是您尝试声明一个SQL FK。失败的原因是，如错误消息所示，团队ID并非唯一。

When a subrow in one table has to be a subrow in another (referenced) table, that is an inclusion dependency (IND). Eg player team_id referencing team id (not a key in team). When there is an IND and the referenced subrow is a key, that is a foreign key (FK). Eg player person_id referencing person id (a key in person). In SQL a FOREIGN KEY declaration says that there is an IND and the referenced columns are unique. (Ie declared by either PRIMARY KEY or UNIQUE). (So it actually means "foreign superkey".) In SQL we would ideally declare an IND (when there isn't also a FK) by a CHECK constraint. (But DBMSes do not support CHECK well). Ie check for player that team_id is in team projected on id. But you tried to declare an SQL FK. It fails because, as the error message says, team id is not unique.

（请注意，区别在团队及其团队ID之间进行了评论。由于团队ID不能识别团队，因此您只能谈论已识别的团队OOP不仅必须区分世界上各支球队之间的 和世界上各支球队的ID（某种字符串），而且还必须区分 team_id指针/编程语言的参考值。评论 id是一个坏名字，因为仅团队ID不能识别

(Notice here the distinction commented between teams and their team ids. Because a team id doesn't identify a team, you should only speak of a team identified by a team id and club. OOP must distinguish not only between teams in the world and team ids (some kind of string) in the world but also "team_id" pointer/reference values of a programming language. It was commented that id" is a bad name because a team id alone doesn't identify a team. We could just use the term used in the world.)

但是您实际上想要的东西要比玩家表team_id IND和player_id FK都强。

But you actually want something stronger than both your player table team_id IND and player_id FK. Ie check for player that team_id is paired in club with a club_id that is paired in contract with a person_id that is person_id.

如果您的设计涉及花名册（club_id，team_id，player_id）并且在播放器中没有team_id并且也可能没有合同，那么实际上不需要这种复杂的约束。

Such complicated constraints are actually unnecessary if your design involved, say, roster(club_id,team_id,player_id) and didn't have team_id in player and probably didn't have contract either.

否，我们无法从人员通过合同引用俱乐部的模式中看到。 （并且该短语是表达您的意思的一种糟糕方法。）我们无法从表和键中看出这一点。 每个表都包含满足某些语句的行，这些语句由其列名参数化；您必须提供以下声明。例如，玩家：[person_id]人参加由[team_id]&一些俱乐部。您还必须提供 all 业务规则，以限制可能出现的情况。例如：一个人最多只能签约一个俱乐部。一个团队最多只能属于一个俱乐部。没有声明的用户（我们也不能）无法使用数据库，而没有声明和业务规则的用户（我们也就无法）确定约束。把它们写出来。

No, we cannot "see" from a schema that "person references club through contract". (And that phrase is a poor way of expressing what you mean.) We cannot tell that from the tables and keys. Every table holds rows that satisfy some statement parameterized by its column names; you must give these statements. Eg for player: person [person_id] plays on a team identified by [team_id] & some club. You must also give all business rules that restrict what situations can arise. Eg: A person can be contracted to at most one club. A team belongs to at most one club. A user (nor we) cannot use the database without the statements and you (nor we) cannot determine the constraints without the statements and business rules. Write them out.

这篇关于引用组合主键的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！