Re: CHECK Integrity constraint help

"sali" <skhan_at_mdsp.org> wrote in message news:oQG2d.11$Ov3.1350_at_news.abs.net...
| Thanks so much for your help. I'm very new to the world of SQL/Oracle, so
| Im still trying to get a handle on this stuff. Im creating my tables with
| SQL Plus.
| Now I know the right direction I should be looking towards. Triggers and
| Procedures. Thanks so much.
| "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
| news:N7-dneJE4MTsq9bcRVn-gg_at_comcast.com...
| >
| > "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
| >
| >

|
|

actually, i should have asked, what's your deployment environment -- how will the end-users by inserting the records?

keep us posted ... always happy to help someone connected with the police force ;-)

++ mcs Received on Fri Sep 17 2004 - 16:46:26 CDT