Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: optionality and cardinality moving from from ERD to TABLES.

Re: optionality and cardinality moving from from ERD to TABLES.

From: Jan Hidders <hidders_at_REMOVE.THIS.win.tue.nl>
Date: 28 Sep 2001 12:09:52 GMT
Message-ID: <9p1pag$s3$1@news.tue.nl>


Andrea Fare' wrote:
> Thanks JAN and HEINZ (hope this is your name and not your lastname;-) for
> your answers .
> Since it is impossible to create a REFERENCE to a non unique field in Oracle
> , it seems impossible to me to implement your solution,

Then formulate it as a general constraint with CHECK where you can define any constraint that you can formulate with SQL. It does mean that you have to formulate the foreign key constraint in SQL, which may not be easy if you are new at SQL. It's also not very efficient.  

> imagine my person table as:
>
> create table person
> (persid varchar2(10)
> CONSTRAINT person_persid_pk PRIMARY KEY,
> pername varchar2(10));
>
>
> what kind of constraint can I put on the table to enforce that each entry
> has at least one corresponding entry in the following table (the car each
> owner must have?)
>
>
> create table car
> (carid varchar2(10)
> constraint car_carid_pk primary key,
> model varchar2(10),
> owner varchar2(10) references person(persid));
>
>
> it seems impossible to me to solve this problem at the table level because
> it generates the paradox of beeing able to enter values in both tables at
> exaclty the same time (!!!)

The magic phrase is "deferred constraint checking". See

  http://www-db.stanford.edu/~ullman/fcdb/oracle/or-faq.html

under "How do I specify deferred constraint checking?".

-- 
  Jan Hidders
Received on Fri Sep 28 2001 - 07:09:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US