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

From: Jan Hidders <hidders_at_REMOVE.THIS.win.tue.nl>
Date: 27 Sep 2001 08:27:12 GMT
Message-ID: <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 Thu Sep 27 2001 - 10:27:12 CEST

Original text of this message