Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users (
  id   int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, role "char" NOT NULL
, CONSTRAINT users_role_valid CHECK (role IN ('c', 'p'))  
, CONSTRAINT users_id_role_uniq UNIQUE (id, role)  -- required for FKs
);

CREATE TABLE user_user (
  captain_id   int
, player_id    int
, captain_role "char" NOT NULL GENERATED ALWAYS AS ('c') STORED
, player_role  "char" NOT NULL GENERATED ALWAYS AS ('p') 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 TABLE



CREATE TABLE


INSERT INTO users (role) VALUES ('c'), ('p')
RETURNING *;


id
role


1
c

2
p



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
c
p



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, c) 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, p) 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, c) is not present in table "users".