Postgresql 可以向判断枚举值的SQL列添加约束吗

由于PostgreSQL不支持判断约束中的直接表引用，因此我结合使用了定制函数(check_user_role)和触发器来确保数据完整性.check_user_role函数判断给定的用户ID是否对应于指定的角色(CAPTAIN或PLAYER).然后，在USER_USER表上创建两个触发器(trigger_check_captain_role和trigger_check_player_role).这些触发器在任何INSERT或UPDATE操作之前调用该函数，以验证user_captain_id始终与CAPTAIN相关联，user_player_id与PLAYER相关联.这种方法将确保连接表准确地反映用户及其各自角色之间的关系，维护数据库中角色关联的完整性和正确性.

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;

然后写一个触发器

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