Can you add a constraint to an SQL column that checks the value of an enum?

Question

I have a users table in my Postgres database with a role column that uses an enum. The user can either be a CAPTAIN or PLAYER.

DROP TYPE IF EXISTS USER_ROLE CASCADE;
CREATE TYPE USER_ROLE AS ENUM ('CAPTAIN', 'PLAYER');

CREATE TABLE IF NOT EXISTS "user" {
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  role USER_ROLE NOT NULL
);

I need to create a junction table, connecting many CAPTAIN users with many PLAYER users.

I can create a table like this:

CREATE TABLE IF NOT EXISTS user_user (
  user_captain_id INT,
  user_player_id INT,
  CONSTRAINT pk_user_user PRIMARY KEY (user_captain_id, user_player_id),
  CONSTRAINT fk_user_captain FOREIGN KEY (user_captain_id) REFERENCES "user"(id),
  CONSTRAINT fk_user_player FOREIGN KEY (user_player_id) REFERENCES "user"(id),
);

Is there any way to add a constraint so that user_captain_id has to be a user who has the CAPTAIN role and user_player_id has to be a user who has the PLAYER role?

Answer 1

Since PostgreSQL doesn't support direct table references in check constraints, I used a combination of a custom function (check_user_role) and triggers to ensure data integrity. The check_user_role function checks if a given user ID corresponds to the specified role (CAPTAIN or PLAYER). Then, two triggers (trigger_check_captain_role and trigger_check_player_role) are created on the user_user table. These triggers call the function before any insert or update operation to validate that user_captain_id is always associated with a CAPTAIN and user_player_id with a PLAYER. This approach will make sure that the junction table accurately reflects the relationships between users and their respective roles, maintaining the integrity and correctness of the role associations in your database.

CREATE OR REPLACE FUNCTION check_user_role(user_id INT, role USER_ROLE)
RETURNS BOOLEAN AS $$
DECLARE
  user_role USER_ROLE;
BEGIN
  SELECT role INTO user_role FROM "user" WHERE id = user_id;
  RETURN user_role = role;
END;
$$ LANGUAGE plpgsql;

And then write a Trigger

CREATE OR REPLACE FUNCTION trigger_check_captain_role()
RETURNS TRIGGER AS $$
BEGIN
  IF NOT check_user_role(NEW.user_captain_id, 'CAPTAIN') THEN
    RAISE EXCEPTION 'user_captain_id must have the CAPTAIN role';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_user_user_before_insert_or_update_captain
BEFORE INSERT OR UPDATE ON user_user
FOR EACH ROW EXECUTE FUNCTION trigger_check_captain_role();

CREATE OR REPLACE FUNCTION trigger_check_player_role()
RETURNS TRIGGER AS $$
BEGIN
  IF NOT check_user_role(NEW.user_player_id, 'PLAYER') THEN
    RAISE EXCEPTION 'user_player_id must have the PLAYER role';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_user_user_before_insert_or_update_player
BEFORE INSERT OR UPDATE ON user_user
FOR EACH ROW EXECUTE FUNCTION trigger_check_player_role();

Answer 2

Here is a bullet-proof implementation with multicolumn FK constraints:

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

fiddle

Downsides:

Redundant UNIQUE constraint users_id_role_uniq in table users.

Redundant columns captain_role and player_role in table user_user. Since those hold constant values, I made both out to be generated columns. So you don't have to worry about them, and you also cannot corrupt them. (Sadly, FK constraints do not allow constants instead of actual columns.)
About generated columns:

• Computed / calculated / virtual / derived / generated columns in PostgreSQL

Related:

• How to enforce by constraint that a referenced row also has a given type
• Enforcing constraints "two tables away"

Aside: I wouldn't create an enum for allowed roles, and just use a 1-byte "char" field with a CHECK constraint. Simpler, smaller, faster, and easier to adapt later on. See:

fiddle