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