MySQL foreign keys do not support Where clauses.
MySQL Check Constraint is extremely limited, eg it does not support subqueries or references to foreign key columns..
So you might be best off writing Insert and Update Triggers which examine the relevant employee.role value and issue a Signal error message if it fails to match your rule for it, if the project fk column is `empid` then something like ...
create trigger project_ins before insert on project for each row
begin
if 'pm' <> (select role from employee where id=new.empid) then
signal sqlstate '45000' set message_text='invalid employee role';
end if;
end;
... and likewise for an Update Trigger.
Edited 1 time(s). Last edit at 06/16/2022 07:06PM by Peter Brawley.