Re: Check constraints Foreign Key

David Zlotchenko (zlotchen_at_solar.rtd.utk.edu) wrote:
: 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.

As I understand it, a FOREIGN KEY constraint means that a row may be added *only if* the column with the FK constraint *already exists* as a primary key in the foreign table. (In your example: "Only add an assignment to ASSIGNMENT for a job iff the job exists in the JOB table.")

It doesn't have anything to do with 1-to-1 relationships, I don't think. (Correct me, folks.) That has more to do with UNIQUE constraints.

: 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) );
 
: ORACLE does not let to use other table's columns in check statment.
: Why this could not work?

I checked the manual. The "Oracle 7 SQL Language Reference Manual", p. 4-133 says under "CHECK Constraints": "The condition of a CHECK constraint can refer to any column in the table, but it cannot refer to columns of other tables."

En paz,
Steve

--
Stephen W. Thompson, U. of PA, Data Administration, 215-898-1236
thompson_at_pobox.upenn.edu -OR- st_at_eniac.seas.upenn.edu
URL: http://www.seas.upenn.edu/~st/st.html