Re: Separate PK in Jxn Tbl?

Neil wrote:
> Whenever I've created junction tables in the past, I always made the PK of
> the junction table the combined pks from the two other tables. Ex: Table1
> PK=A; Table2 PK=B; Junction table consists of two fields, A and B, which
> together comprise the PK for the junction table.
>
> However, I just came across some code in which the person created a junction
> table with a separate PK consisting of an autonumber field, and then the two
> fields.
>
> So I was wondering how others did junction tables -- with a standalone
> autonumber PK, or with a PK consisting of the PKs of the tables being
> joined? And, if a standalone PK, then why?
>
> Thanks!
>
> Neil

Whenever I have multiple key fields, natural or not, I create an AutoNumber PK for pragmatic reasons. The main reason is that it makes it easier to create the joins. The theorists are champions at joining tables and don't have to be concerned with the complexity of the SQL they write. If I convert an Access table over to SQLServer I add even another field as a primary key, usually prefixed with SS (Gasp!).

I keep Jamie's advice in the back of my mind, about how enforcing constraints at the table level is better than enforcing them through code, but where I work, no one is going to access the table data using anything other than Access so I am able to take the high road at my leisure. Maybe my coding practice just needs to catch up with my philosophy. Theoretically, the idea of using natural keys is more intellectually satisfying, but for now the lure of simpler joins is winning out. Lately, I've increased the amount of normalization in one of my databases and the joins got even more complicated, adding about a line or so in the SQL view in Access for every new query using those tables. Queries involving many to many relationships often add additional tables later and highlight the need to keep joins as simple as possible.

James A. Fortune
MPAPoster_at_FortuneJames.com Received on Fri Jan 25 2008 - 00:00:36 CET