Re: Many to many

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

I generally recommend that you specify both as foreign keys separately. That way, you can index both columns (non-unique) for better search/join purposes with other tables on either key. You would, however, specify a primary key on the aggregate of the two keys (fk1,fk2). This has the same effect of an aggregate primary key, but still allows use of the keys separately as needed.

-Bill Boyle Received on Tue Dec 05 2000 - 16:57:26 CET