Purpose of PK's in Junction table?

[bmoses]

What is the purpose of setting FK's (from other tables) as Primary Keys in a
Junction table? What is the purpose of setting multiple PK's in the Junction
table?

Thanks....b

 

[Roger Carlson]

First of all, I don't want to sound pedantic, but there is no such thing as
multiple primary keys. A table can have only one primary key. This key can
be composed of multiple fields in which case it is a Compound Primary Key
(cpk). I realize this is probably what you meant, but in any field, terms
and definitions are important.

OK, the biggest reason to create a compound primary key in a junction table
is that a Primary Key is automatically indexed by Access. This greatly
speeds up the performance of Joins.

Opinions differ, but when a table's only reason for existance is to
implement a Many-to-Many relationship (ie a junction table), I will create a
compound primary key of all of the foreign keys. However, if this table
will have some relationship with some other table (ie, NOT one of the tables
in the M:M), I will create an autonumber primary key to use in relating it
to the other table.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[peregenem]

First of all, I don't want to sound pedantic, but there is no such thing as
multiple primary keys. A table can have only one primary key. This key can
be composed of multiple fields in which case it is a Compound Primary Key

OK, the biggest reason to create a compound primary key in a junction table
is that a Primary Key is automatically indexed by Access.

I don't want to sound pedantic, but do you mean the index created by
Access or do you actually mean Jet? As you know, terms and definitions
are important <g>, as is identifying the correct component. For
example, when I've created FOREIGN KEYs using Access and/or DAO, I've
run into problems where Access/DAO, not Jet, has created indexes
implicitly which caused problems because I was also expecting to
explicitly create my own, leading to duplication etc. This doesn't seem
to happen when using SQL DDL via the OLE DB provider.

I think you meant that *Jet* creates the index associated with the
PRIMARY KEY (the clustered index, discussed elsewhere?) but I'm not
entirely sure. If you do indeed mean Access creates an index, is this a
duplication of the Jet index? Thanks in advance for your clarification.

As an aside, do you ever use Jet's 'fast foreign keys' i.e. using
FOREIGN KEY NO INDEX in the DDL? If would seem to be relevant to this
thread but it's not something I've used in earnest.

 

[peregenem]

Suggested reading:

http://www.intelligententerprise.com/010101/celko.jhtml

 

[peregenem]

the biggest reason to create a compound primary key in a junction table
is that a Primary Key is automatically indexed by Access. This greatly
speeds up the performance of Joins.

The order of the columns in the PRIMARY KEY are relevant and should be
given due consideration because PRIMARY KEY (col1, col2) may build an
inferior index to PRIMARY KEY (col2, col1).