Re: 1:1 relationships

Jan Hidders wrote:
> João Martiniano wrote:
> >
> > I recently tried to implement a 1:1 relationship in Microsoft
> > Access... but I couldn't. I always thought that 1:1 relationships
> > looked like this:
> >
> > Persons
> > --------
> > Person ID (primary key)
> > Age
> > Car ID (foreign key)
> >
> >
> > Cars
> > -----
> > Car ID (primary key)
> > Brand
> > Model
> > Person ID (foreign key)
> >
> > My question is: is this the correct way of implementing a 1:1 relationship
> > (in Ms Access or any other RDMS) ?
>
> Correct is a relative notion but usually redundancy is a bad idea when
> you are data modelling. And that is what you have here because you are
> modeling the same relationship twice; once with a foreign key from
> Persons to Cars and once with a foreign key from Cars to Persons. And
> that is also the reason that Access doesn't understand that this is a
> 1:1 relationship; it thinks that these are simply two independent 1:M
> relationships. So, you should drop one of the foreign keys. Let us say
> that you keep the foreign key Car ID in Persons.
>
> The problem is then that Access doesn't know that the remaining foreign
> key reprents not a 1:M but a 1:1 relationship. As far as I know Access
> (but I haven't used it much lately) recognizes 1:1 relationships only
> if the foreign key point to a primary key. It would be nice if you
> could specify that Car ID in Persons is a (candidate) key. But I doubt
> if that is possible.

Oops. I just remembered. Defining a unique index on Car ID in Persons should do the trick. And Access will then recognize that it is a 1:1 relationship.

-- 
  Jan Hidders