Foreign key with many-to-many containment constraint

Question

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)
);

Answer 1

The simplest possible design, a real-world solution could possible involve more complexity.

• a player is part of exactly one team so player.team_id is functionally dependent on player.id, and points to teams.id
• a team has one captain which should be present in the players table
• The captain should be part of the team, which requires an additional constraint teams.{team_id,captain_id} -> players.{team_id,id}

---

CREATE TABLE players
  ( id bigserial primary key
  , team_id INTEGER NOT NULL
  , name text not null
  , UNIQUE (team_id, id) -- we need this because the FK requires it
);

CREATE TABLE teams
  ( id bigserial primary key
  , captain_id bigint not null references players(id)
  , name text not null
);


ALTER TABLE players
        ADD FOREIGN KEY (team_id )
        REFERENCES teams(id)
        ;

        -- captain must be part of the team
ALTER TABLE teams
        ADD FOREIGN KEY (id, captain_id )
        REFERENCES players( team_id, id)
        ;

---

UPDATE it appears a player can take part of more than one team, so a N:M junction-table will be needed:

CREATE TABLE players
  ( player_id bigserial PRIMARY KEY
  , name text not null
);

CREATE TABLE teams
  ( team_id bigserial PRIMARY KEY
  , captain_id bigint
  , name text not null
);

CREATE TABLE players_teams
        (player_id INTEGER NOT NULL REFERENCES players(player_id)
        , team_id INTEGER NOT NULL REFERENCES teams(team_id)
        , PRIMARY KEY (player_id,team_id)
        );

ALTER TABLE teams
        ADD FOREIGN KEY (team_id,captain_id)
        REFERENCES players_teams(team_id,player_id)
        ;

Answer 2

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.)