Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TYPE user_role AS enum ('CAPTAIN', 'PLAYER');

CREATE TABLE users (
  id   int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, role user_role NOT NULL
, CONSTRAINT users_id_role_uniq UNIQUE (id, role)  -- required for FKs
);

CREATE TABLE user_user (
  captain_id   int
, player_id    int
, captain_role user_role NOT NULL GENERATED ALWAYS AS ('CAPTAIN') STORED
, player_role  user_role NOT NULL GENERATED ALWAYS AS ('PLAYER') STORED
, CONSTRAINT pk_user_user PRIMARY KEY (captain_id, player_id)
, CONSTRAINT fk_captain FOREIGN KEY (captain_id, captain_role) REFERENCES users(id, role)
, CONSTRAINT fk_player  FOREIGN KEY (player_id, player_role)   REFERENCES users(id, role)
);

CREATE TYPE

CREATE TABLE

CREATE TABLE

INSERT INTO users (role) VALUES ('CAPTAIN'), ('PLAYER')
RETURNING *;

id
role

1
CAPTAIN

2
PLAYER

INSERT 0 2

-- works
INSERT INTO user_user (captain_id, player_id) VALUES (1, 2)
RETURNING *;

captain_id
player_id
captain_role
player_role

1
2
CAPTAIN
PLAYER

INSERT 0 1

-- fails
INSERT INTO user_user (captain_id, player_id) VALUES (2, 2);

ERROR:  insert or update on table "user_user" violates foreign key constraint "fk_captain"
DETAIL:  Key (captain_id, captain_role)=(2, CAPTAIN) is not present in table "users".

-- fails
INSERT INTO user_user (captain_id, player_id) VALUES (1, 1);

ERROR:  insert or update on table "user_user" violates foreign key constraint "fk_player"
DETAIL:  Key (player_id, player_role)=(1, PLAYER) is not present in table "users".

-- fails
INSERT INTO user_user (captain_id, player_id) VALUES (2, 1);

ERROR:  insert or update on table "user_user" violates foreign key constraint "fk_captain"
DETAIL:  Key (captain_id, captain_role)=(2, CAPTAIN) is not present in table "users".