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

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,

imagine my person table as:

 create table person
 (persid varchar2(10)

           CONSTRAINT person_persid_pk PRIMARY KEY,
           pername  varchar2(10));

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 (!!!)
in fact let's imagine i can put a constraint on the person table that says that a corresponding entry has to exist in the car table, well then if I had to put that entry into the car table first I would have had a value in the car table which is not already existing in the person table (but this is against the definition of foreign key which has to be either null or have ALREADY a corresponding entry in the parent table).

I still do not get it....
maybe databases are not for me :-)

Jan Hidders <hidders_at_REMOVE.THIS.win.tue.nl> wrote in message news:9ount0$6dj$1_at_news.tue.nl...
> Andrea Fare' wrote:
> > I am a beginner in database design and I am experiencing some problems
> > trying to translate with accuracy an ERD into
> > code.
> >
> > Let's say I have two entities CAR and PERSON and the following
> > relationships exist between them:
> >
> > each CAR MUST be property of one and only one PERSON.
> > each PERSON MAY own one or more CARs.
> >
> > Now as soon as I start creating the tables it seems logical to me to put
a
> > foreign key
> > in the CAR table that references the primary key in the PERSON table.
> > Because each CAR MUST be property of one and only one PERSON it seems
also
> > logical that the foreign key in the CAR table will not be allowed to
have
> > NULL values,but how do I enforce the condition that each PERSON MAY HAVE
a
> > car, or in other words what do I have to put in my tables to make a
> > distinction between this situation and a situation in which each PERSON
MUST
> > have a CAR?( this last sentence could be translated in to a condition
that
> > says that each entry in PERSON TABLE must be referenced at least one
time
> > in the foreign key field of the CAR table).
>
> In theory you might add the following foreign key constraint:
>
> PERSON( [Person_id] ) --> CAR( [Owner] )
>
> In practice this may be a problem because some DBMSs only allow foreign
> key constraints that end in primary keys.
>
> --
> Jan Hidders
Received on Fri Sep 28 2001 - 06:05:31 CEST