Re: Separate PK in Jxn Tbl?

From: Sylvain Lafontaine <"Sylvain>
Date: Tue, 29 Jan 2008 15:14:32 -0500
Message-ID: <ur$UROrYIHA.4028_at_TK2MSFTNGP06.phx.gbl>

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

Shouldn't a database be designed right from the beginning?

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

"James A. Fortune" <> wrote in message 
> 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
>>>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 >
Received on Tue Jan 29 2008 - 21:14:32 CET

Original text of this message