MySQL Forums
Forum List  »  Newbie

Re: access to other columns though other table's foreign key
Posted by: Peter Brawley
Date: June 16, 2022 04:17PM

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.

Options: ReplyQuote


Subject
Written By
Posted
Re: access to other columns though other table's foreign key
June 16, 2022 04:17PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.