Re: database constraints

sjg_at_qdot.qld.gov.au writes in article <2tlddi$db2_at_camelot.qdot.qld.gov.au>:
>
> 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 ?
>
You can already do this with a database trigger:

create or replace trigger dept_code
before insert into table for every row
declare dept_code_var number;
begin
  select d.dept_code into dept_code_var from division d

      where d.dept_code = :new.dept_code
        and retired_ind is null;
  exception
      raise_application_error (-20000,'Wrong dept_code choosen');

(This "piece of code" is from memory, and I am not a very experienced programmer, so you have to look for the actual syntax :-))

Willy Klotz

>

Willys Mail     FidoNet   2:2474/117  2:2474/118     Mail Only System
                CIS: 100020,3517       USR Courier HST dual standard
                willyk_at_kbigate.stgt.sub.org
                ->   No Request from 06.00 to 08.00 <-
======================================================================