Re: Check constraints Foreign Key

In article <3bm00p$dkg_at_martha.utk.edu>, zlotchen_at_solar.rtd.utk.edu (David Zlotchenko) writes:
> Could anyone explain me how exactly the "FOREIGH KEY" constraint serves?
>
> I thought that if I have a local PRIMARY KEY field(s) tied with the
> PRIMARY KEY field of a master table by FOREIGH KEY constraint, I am
> getting one-to-one relationship.
>
> Assuming that, I've tryed use to values from the master table for
> CHECK contstraints in other fields of the dependent table.
>
> I wrote this:
>
> create table JOBS (
> JCODE char(4) not null PRIMARY KEY ,
> MIN_PAY number(7) not null,
> MAX_PAY number(7) not null
> );
> create table ASSIGNMENT (
> EID char(5) not null,
> JCODE REFERENCES jobs(jcode),
> SALARY number(7)
> check( salary >= jobs(MIN_PAY) and salary <= jobs(MAX_PAY))
> FOREIGN KEY EID,JCODE EMPLOYEE(EID), JOBS(JCODE),
> PRIMARE KEY (EID,JCODE)
> );
>
> I expect jobs(MAX_PAY) and jobs(MIN_PAY) be definite single values
> because of the PRIMARY KEY/FOREIGN KEY constraints.
>
> ORACLE does not let to use other table's columns in check statment.
> Why this could not work?
> --

It won't work 'cause CHECK doesn't work that way. You can use a trigger to do this.

create table jobs

( jcode		varchar2 (4) constraint jobs_pk primary key,
  min_pay	number(7),
  max_pay	number(7)

create table assignments

( eid		varchar2(5),
  jcode		varchar2(4) constraint assignment_fk_01 references jobs,
  salary	number(7)

create or replace trigger assignment_trigger before insert or update on assignment
for each row
declare
tmp_min number;
tmp_max number;
begin
select min_pay, max_pay into tmp_min, tmp_max

        from jobs where jcode = :new.jcode;
if :new.salary < tmp_min or :new.salary > tmp_max then

   raise_application_error (-20100,'Salary is not with valid range'); end if;
end;
.
/

Then suppose you did this:

SQL> insert into jobs values ('job1',1000,10000);

1 row created.

SQL> insert into assignment values ('bob','job1',5000);

1 row created.

SQL> insert into assignment values ('bob','job1',500); insert into assignment values ('bob','job1',500)

            *
ERROR at line 1:

ORA-20100: Salary is not with valid range
ORA-06512: at line 8
ORA-04088: error during execution of trigger 'OPS$JL34778.ASSIGNMENT_TRIGGER'

Hope this helps.

-- 
Bob Swisshelm                | swisshelm_at_Lilly.com     | 317 276 5472
Eli Lilly and Company        | Lilly Corporate Center  | Indianapolis, IN 46285