sql - 跨多个表的参照完整性

Question

确保输入表的任何数据与对应关系中的数据相匹配的最简单方法是什么？在我的例子中，我有一个体育俱乐部。玩家可以在没有团队的情况下进行运动。我想强制要求玩家在加入团队之前参加这项运动。

这是我的表格:

create table IF NOT EXISTS sports (
    sport_id SERIAL,
    sport_name VARCHAR(50),
    PRIMARY KEY(sport_id)
    );

create table IF NOT EXISTS players (
    player_id SERIAL,
    player_name VARCHAR(50),
    player_phone VARCHAR(20),
    PRIMARY KEY(player_id)
    );

create table IF NOT EXISTS players_sports (
    player_id INT REFERENCES players(player_id),
    sport_id int REFERENCES sports(sport_id),
    PRIMARY KEY (player_id, sport_id)
    );

create table IF NOT EXISTS teams (
    team_name VARCHAR(50),
    sport_id INT,
    captain_id INT,
    FOREIGN KEY (sport_id, captain_id) 
        REFERENCES players_sports(sport_id, player_id),
    PRIMARY KEY (team_name, sport_id)
    );

create table IF NOT EXISTS has_players (
    team_name VARCHAR(50),
    sport_id int,
    player_id INT REFERENCES players(player_id),
    FOREIGN KEY (team_name, sport_id) 
        REFERENCES teams(team_name, sport_id),
    PRIMARY KEY(team_name, sport_id, player_id)
    );

CREATE TYPE DAY AS ENUM (
  'Monday',
  'Tuesday',
  'Wednesday',
  'Thursday',
  'Friday',
  'Saturday',
  'Sunday'
);

create table IF NOT EXISTS time_slot (
    training_id SERIAL,
    training_day DAY,
    training_time TIME,
    team_name VARCHAR(50),
    sport_id int,
    FOREIGN KEY (team_name, sport_id) 
        REFERENCES teams (team_name, sport_id),
    PRIMARY KEY (training_id)
    );

Answer 1

I want to enforce that the player plays the sport BEFORE joining a team.

再添加一个FK constraint像这样:

CREATE TABLE IF NOT EXISTS has_players (
  team_name varchar(50)
, sport_id  int
, player_id int
, PRIMARY KEY(team_name, sport_id, player_id)
, FOREIGN KEY (team_name, sport_id)
    REFERENCES teams(team_name, sport_id)
<b>, FOREIGN KEY (player_id, sport_id)
    REFERENCES players_sports(player_id, sport_id)</b>
);

您可以在多个 FK 约束中包含相同的列(如果这是阻止您这样做的原因)。