Re: Separate PK in Jxn Tbl?

From: James A. Fortune <>
Date: Tue, 29 Jan 2008 02:59:40 -0500
Message-ID: <eFts00kYIHA.4880_at_TK2MSFTNGP03.phx.gbl>

Neil wrote:

> "James A. Fortune" <> wrote in message 
> news:uLMF40tXIHA.1208_at_TK2MSFTNGP03.phx.gbl...

>>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?
>>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
> I tend to do the same as you: I tend to put autonumber primary key fields in 
> place where a multi-field PK will do, for the same reasons: i like to be 
> able to refer to a single key. However, when it comes to junction tables, it 
> just seems pointless. Referring to two fields instead of one isn't that big 
> of a deal; and a table made up of two foreign keys is very clean -- adding 
> an additional key on top of that seems useless and added baggage.
> Larry posted that there are times when an autonumber PK is needed, such as 
> when using the junction talbe in a combo box, and I would agree with him 
> there. So in a few cases it might serve a purpose (even though it's not 
> absolutely necessary there either). But for the most part, it seems 
> unnecessary.
> Neil 

I concede the point that for the two keys of the junction table, using an autonumber primary key is overkill except for special situations.

James A. Fortune

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==---- The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Farms - Total Privacy via Encryption =---- Received on Tue Jan 29 2008 - 08:59:40 CET

Original text of this message