sql - Oracle - How to enforce rules on relationships depending on attributes records (simple example) -

in school system have 2 tables, 1 called staff holds records of member of staff school, teachers, admin, cooks , cleaners etc. have second table called course foreign key relating staff state course leader, want allow teachers course leader, i.e. cook can't be, not sure how restrict on database level.

note : asked more complicated wrong question here - oracle unique constraint - trigger check value of property in new relation

in scenario revise data model. take generic table staff , add under tables each staff_type: caterers, teachers, admin, etc. simple matter enforce foreign key between courses , teachers without need triggers.

this standard solution kind of problem. further investigate requirements you'll find there similar foreign key issues cooks , janitors. find there attributes teachers have administrators lack. that's why separate tables each type useful. @ same time have things in common. that's why need table staff super type.

---

of course, in proposing answer echoing @jeffreykemp's suggestion in previous question. well, @robvanwijk has borrowed mine, why not? :)