Re: Many to many

Hi Arne,

Arne Jonsson wrote:

> On 5 Dec 2000 04:10:34 GMT, hidders_at_REMOVE.THIS.win.tue.nl (Jan
> Hidders) wrote:
>
> >Arne Jonsson wrote:
> >> For about two years I´ve been living happily in the belief that if you
> >> have a many to many-relation you split it in a third table between the
> >> other two, where you make the primary keys of these two tables to a
> >> composite primary key in the third. Now, taking a course in
> >> SQL-server, I am taught that the two primary keys can be used as two
> >> foreign keys in the new table, or as one primary and one foreign key.
> >> This have made me confused. Are all the ways right?
> >
> >No. You cannot implement a many-to-many relationship with a primary key
> >and a foreign key. Suppose you have tables A and B with the primary
> >keys #a and #b. If you add #b as a foreign key to A then there will
> >always be at most one tuple in B associated with every tuple in B. So
> >that is not a many-to-many relationship.
> >
> >--
> > Jan Hidders
>
> Does it make any sense to keep the two keys as foreign keys in the
> third table, not making them to composite primary keys?
>
> Arne

In the third table you should have a composite primary key, but every component of this composite key is a foreign key too.

Raimundo Lozano Received on Tue Dec 05 2000 - 10:22:44 CET