Constraint for self-referencing columns based on row value in another column – postgres

I have a table like

user_id  |     team     |   role   | reports_to
___________________________________________
lion       carnivore      manager    
tiger      carnivore      lead       lion
cheetah    carnivore      player     tiger
deer       herbivore      player     tiger

CREATE TABLE team(
    user_id TEXT NOT NULL,
    team_name TEXT NOT NULL,
    role TEXT NOT NULL,
    reports_to TEXT,

    CONSTRAINT teampk PRIMARY KEY (user_id),
    CONSTRAINT public_team_name_team CHECK (team_name = ANY (ARRAY['carnivore'::text, 'herbivore'::text])),
    CONSTRAINT public_team_name_role CHECK (role = ANY (ARRAY['manager'::text, 'lead'::text, 'player'::text])),
    CONSTRAINT teamfk_team FOREIGN KEY (reports_to)
        REFERENCES team(user_id)
);

Can I have another constraint on the FOREIGN KEY to check reports_to to be either a manager or a lead?

Answer

You cannot create a foreign key for this check but you can create a check constraint with a function for this:

create function f_check_reports_to(p_user_id text) returns boolean
language plpgsql
as
$$
declare
v_role text;
begin
 select role into v_role from team where user_id = p_user_id;
 if (v_role = 'manager' or v_role = 'lead' or v_role is null)
 then return true;
 else return false; 
 end if;
end;
$$;
CREATE FUNCTION

alter table team add constraint check_reports_to check(f_check_reports_to(reports_to));
ALTER TABLE

insert into team values('lion', 'carnivore', 'manager', null);
INSERT 0 1
insert into team values('tiger', 'carnivore', 'lead', 'lion');
INSERT 0 1
insert into team values('cheetah', 'carnivore', 'player' ,'tiger');
INSERT 0 1
insert into team values('deer', 'herbivore', 'player', 'tiger');
INSERT 0 1

select * from team;
 user_id | team_name |  role   | reports_to 
---------+-----------+---------+------------
 lion    | carnivore | manager | 
 tiger   | carnivore | lead    | lion
 cheetah | carnivore | player  | tiger
 deer    | herbivore | player  | tiger
(4 rows)

insert into team values('KO','KO','player','cheetah');
ERROR:  new row for relation "team" violates check constraint "check_reports_to"
DETAIL:  Failing row contains (KO, KO, player, cheetah).

update team set reports_to='cheetah' where user_id='tiger';
ERROR:  new row for relation "team" violates check constraint "check_reports_to"
DETAIL:  Failing row contains (tiger, carnivore, lead, cheetah).