Re: CHECK Integrity constraint help

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 18 Sep 2004 10:24:39 GMT
Message-ID: <slrncko36v.3k0.rene.nyffenegger@zhnt60m34.netarchitects.com>

In article <TqG2d.9$Ov3.1217_at_news.abs.net>, sali wrote:
> I have this table called Faculty_Schedule, which contains a faculty member's
> schedule for a particular semester. In the creation of the table, I need to
> add a check constraint where the teacher can not teach more than 4 classes
> per semester. Here is what i have so far.
>
> CREATE TABLE faculty_schedule(
> fac_schedule_id NUMBER(2),
> faculty_semster VARCHAR(6),
> faculty_id NUMBER(5),
> course_id NUMBER(5),
> CONSTRAINT fac_sch_id_pk PRIMARY KEY (fac_schedule_id),
> CONSTRAINT course_id_fk FOREIGN KEY (course_id) REFERENCES courses
> (course_id),
> CONSTRAINT faculty_id_fk FOREIGN KEY (faculty_id) REFERENCES faculty
> (faculty_id));
>
> How would I include this CHECK constraint in here. This is the first time
> im writing something that has this type of constraint so I am a little
> confused about the way it should be done.. Any help would be appreciated.
> Thanks in advance.

Something like the following two triggers could lead you into the right direction.

drop table faculty_schedule;

CREATE TABLE faculty_schedule(
  fac_schedule_id NUMBER(2) primary key,

• faculty_semster VARCHAR(6), faculty_id NUMBER(5), course_id NUMBER(5) );

create trigger max_4_courses_ins
  before insert on faculty_schedule
  for each row
declare
  cnt number;
begin

  select count(*) into cnt
    from faculty_schedule
   where faculty_id = :new.faculty_id;

  if cnt > 2 then

     raise_application_error(-20001, 'too many courses');   end if;
end max_4_courses_ins;
/

create trigger max_4_courses_upd
  after update on faculty_schedule
declare
  cnt number;
begin

  select max(c) into cnt from
  ( select count(*) c
    from faculty_schedule
    group by faculty_id);

  if cnt > 3 then

     raise_application_error(-20001, 'too many courses');   end if;
end max_4_courses_ins;
/

insert into faculty_schedule values ( 1, 1, 1);
insert into faculty_schedule values ( 2, 1, 2);
insert into faculty_schedule values ( 3, 1, 3);

insert into faculty_schedule values ( 4, 2, 2);

insert into faculty_schedule values ( 5, 2, 3); insert into faculty_schedule values ( 6, 2, 4);

insert into faculty_schedule values ( 7, 3, 4);
insert into faculty_schedule values ( 8, 3, 5);
insert into faculty_schedule values ( 9, 3, 1);

insert into faculty_schedule values (10, 1, 9);

update faculty_schedule set faculty_id = 1

       where fac_schedule_id = 6;

However, if you do something like
  insert into faculty_schedule select * from faculty_schedule; you'll get an
ORA-04091: table RENE.FACULTY_SCHEDULE is mutating, trigger/function may not see it

hth,

Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/

Received on Sat Sep 18 2004 - 05:24:39 CDT