Re: CHECK Integrity constraint help

"sali" <skhan_at_mdsp.org> wrote in message news:TqG2d.9$Ov3.1217_at_news.abs.net...
| 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.
|
|

no can do -- CHECK constraints can't reference other rows, which is what you need to do to find out how many classes the teacher has

this will likely need to be done with a procedure or with triggers (but limitations on triggers make this a bit tricky)

what's your development environment? if you can encapsulate your SQL in a stored procedure then you can perform the check in the procedure before or after the DML on your schedule table and at the end of the transaction

++ mcs Received on Fri Sep 17 2004 - 14:09:18 CDT