Re: help: Constaints Based on Data of Other Tables
Date: Wed, 2 Nov 1994 14:41:01 GMT
Message-ID: <CynA4E.MIH_at_lanier.com>
In article <38rqas$e11_at_martha.utk.edu>,
David Zlotchenko <zlotchen_at_solar.rtd.utk.edu> wrote:
>Hi,
>Do anybody know if I can use actual data of existing table for using
>in constraint on a column in another table?
>
>I have a table JOBS:
>
>JCODE CHAR(2) NOT NULL
>MIN_PAY NUMBER NOT NULL
>MAX_PAY NUMBER NOT NULL
>Unique(JCODE)
>
>And I want to create a table ASSIGNMENT:
>
>EID NUMBER NOT NULL
>JCODE REFERENCES JOBS(JCODE)
>SALARY ------!!------ - is is my question below
>
>I am trying define column SALARY so that it could contain only values
>in the range set in the tabke JOBS for same JCODE and this is what I write:
>
>SALARY NUMBER CHECK (
> SALARY >= (select MIN_PAY from JOBS
> where JOBS.JCODE = JCODE)
>AND SALARY <= (select MAX_PAY from JOBS
> where JOBS.JCODE = JCODE)
>)
>
>Oracle complains on JOBS.JCODE: only simple column names allowed here
>
>Is there another solution involving constraints?
>
This is a very common problem in application design. I think the preferred solution for this in Oracle is to use a stored procedure to do all updates to the SALARY field. You can include logic in the procedure to check the value against the other table before updating. If this is not feasible in your application, you are not alone.
This is an example of a referential integrity check. Many database systems support simple RI checks (foreign keys, for example). The general form is
<column_value> <operator> <values from database> For foreign keys, this becomes
<column_value> in (set of key values from parent table) Your example is
<column_value> in_range <numeric_range from another table> I don't know of a commercial database system that offers much in the way of complex RI checks. Maybe someone else does ???
Donald E. Vick (dvick_at_lanier.com, dvick_at_crl.com) Voice: (404) 493-2194 Fax: (404) 493-2399 Received on Wed Nov 02 1994 - 15:41:01 CET