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

In a school system I have 2 tables, one called Staff which holds records of all member of staff for a school, teachers, admin, cooks and cleaners etc. Then I have a second table called Course with a foreign key relating to Staff to state who is the Course leader, now I only want to allow teachers to be the Course Leader, i.e. a cook can't be, but am not sure how to 开发者_如何学运维restrict this on the database level.

Note : I asked a more complicated wrong question here - Oracle Unique Constraint - Trigger to check value of property in new relation

---

You could check this restriction within an after insert or update triger on curses tabelle.

CREATE or replace TRIGGER check_leader
AFTER INSERT OR UPDATE ON  Course
FOR EACH ROW
declare
  v_type varchar2(30);
BEGIN
  select type into v_type from stuff where :NEW.leader_id = stuff.stuff_id;
  if v_type != 'teacher' then 
   RAISE_APPLICATION_ERROR(-20000, 'course leader must be teacher');
  end if;
end;
/

But you need another trigger on the staff table. In the case of a change of stuff type (from teacher to cleaner ) It must be checked for the entries in curses table.

CREATE or replace TRIGGER check_courses
AFTER UPDATE ON  STUFF
FOR EACH ROW
declare
  v_num number;
BEGIN
  if :OLD.type = 'teacher' and :NEW.type != 'teacher' then
     select count(*) into v_num from curses where courses.leader_id = :NEW.stuff_id;
     if v_num > 0 then 
       RAISE_APPLICATION_ERROR(-20000, 'there are courses assigned ');
      end if;
  end if;
end;
/

---

In this scenario I would revise the data model. I would take the generic table staff and add under it tables for each STAFF_TYPE: CATERERS, TEACHERS, ADMIN, etc. Then it is a simple matter to enforce a foreign key between COURSES and TEACHERS without the need for triggers.

This is a standard solution to this kind of problem. As you further investigate the requirements you'll find there will be similar foreign key issues with cooks and janitors. It is also likely that you will find there are attributes which teachers have that administrators lack. That's why separate tables for each type are useful. At the same time they all have things in common. That's why you need a table for the STAFF super type.

---

Of course, in proposing this answer I am echoing @JeffreyKemp's suggestion in your previous question. Well, as @RobVanWijk has borrowed mine, why not? :)

---

You could add another table named COURSE_LEADERS to track everyone who is currently able to lead a course, then have a relation from COURSE to COURSE_LEADERS, as well as a relation from COURSE_LEADER to STAFF. This way, a cook can only lead a course if they are in COURSE_LEADER, but if it's a course aboue cooking, maybe that's OK...? ;)

This method of course, means that there must be some way to add/remove staff members from the course_leader. If you want it to be automatic, you could have a trigger that inserts a record to COURSE_LEADER when a new STAFF is added if the staff is a teacher - assuming that it's possible to determine if a staff is a teacher when their record is inserted.

---

You can do it like APC said in the other thread.

Here is an example:

SQL> create table staff
  2  ( id         number(10)   not null primary key
  3  , name       varchar2(10) not null
  4  , staff_type varchar2(10) not null check (staff_type in ('TEACHER','COOK','ADMIN','CLEANER'))
  5  , constraint staff_uk unique (id,staff_type)
  6  )
  7  /

Table created.

SQL> insert into staff values (1, 'ALAN', 'COOK')
  2  /

1 row created.

SQL> insert into staff values (2, 'BOB', 'TEACHER')
  2  /

1 row created.

SQL> create table course
  2  ( id         number(10)   not null primary key
  3  , name       varchar2(30) not null
  4  , staff_id   number(10)   not null
  5  , staff_type varchar2(10) default 'TEACHER' not null
  6  , constraint course_staff_fk
  7      foreign key (staff_id,staff_type)
  8      references staff (id,staff_type)
  9  )
 10  /

Table created.

SQL> insert into course (id,name,staff_id) values (1, 'Mathematics', 1)
  2  /
insert into course (id,name,staff_id) values (1, 'Mathematics', 1)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCHEMA.COURSE_STAFF_FK) violated - parent key not found


SQL> insert into course (id,name,staff_id) values (2, 'Physics', 2)
  2  /

1 row created.

Regards,
Rob.

---

I don't see the point of doing this at database level, or, at least, complicate your life with triggers and so on.

Just create those objects:

• a table named STAFF, that has a "type" field (type can be NULL or "COURSE LEADER")
• a table named COURSE, that has a foreign key to STAFF
• a view named COURSE_LEADERS, that is select * from STAFF where type="COURSE LEADER"

Then, use only COURSE and COURSE_LEADERS objects in your application when dealing with courses and course leaders. As simple as that.