Re: Separate PK in Jxn Tbl?

"Tony Toews [MVP]" <ttoews_at_telusplanet.net> wrote in message news:ii6dp3tc3l2u8m4nv6c8ipg8ejgjggu5oe_at_4ax.com...
> "Neil" <nospam_at_nospam.net> wrote:
>
>>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?
>
> I always use an autonumber PK and a uniqui index set on the two FK fields.
>
> Why? No particular good reason. One of my database rules is that all
> tables have
> an autonumber primary key. It's also slightly easier to delete the
> record in code.
>

Only an idiot would have a rule for no particularly good reason. Only an imbecile would follow such a rule. A strong argument can be made for using autonumber primary keys--especially if the target DBMS doesn't support FOR EACH ROW triggers--but to just blythely add them for no particularly good reason is a recipe for disaster. A clear understanding of how and when they can be used and why is critical or you run the risk of a corrupt database.

> Now if I was to have a child table from the junction table then I would
> absolutely
> use a autonumber primary key for ease of use when designing queries, forms
> and
> reports.
>
> The theorists will argue. I don't care.
>
> Tony
> --
> Tony Toews, Microsoft Access MVP
> Please respond only in the newsgroups so that others can
> read the entire thread of messages.
> Microsoft Access Links, Hints, Tips & Accounting Systems at
> http://www.granite.ab.ca/accsmstr.htm
> Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Received on Thu Jan 24 2008 - 01:33:20 CET