Re:database constraints

In article 9ry_at_iceonline.com, kenl_at_icebox.iceonline.com (Ken Levine) writes:
>>We have an application that needs more than a foreign key ,
>>the ability to restrict codes to those marked current in
>>another table.
>>
>>This requires a constraint along the lines of
>>CONSTRAINT check_current
>> CHECK dept_code EXISTS IN ( select d.dept_code
>> from division d
>> where retired_ind is null )
>>
>>So the table being defined has a constraint that column
>>dept_code must be a current division code (not retired)
>>
>>Can someone advise when integrity constraints will be extended
>>in this direction ?
>>
>>Stephen Gerard
>>
 

>Triggers can do this right now and it doesn't take much more work. Just
>define a before insert or update of dept_code on row trigger that
>does this validation. As long as the trigger is enabled on the table the
>condition must hold.
>
>Ken Levine
>Vancouver, BC

The major difference is a trigger will not validate the *existing* data the way a constraint does. This can be good or bad depending on the situation, but it shouldn't be ignored (IMHO) ;-}

Happy computing!

Sincerely,

Martin Farber
Independent Oracle Consultant

                        |  NYNEX Science & Technology
                        |  500 Westchester Ave, Rm 1D10
                        |  White Plains, NY  10604
                        |     -----------------
                        |  email: farber_at_nynexst.com
                        |  Voice: 914/644-2656
                        |    FAX: 914/644-2216