Composite Primary Key

[Thomas A. Anderson]

I am a bit confused in creating a composite primary key. I have three table
with two of the tables containing primary keys. I have two of the tables
(each with a primary key) having one to many relations with the table
containing no primary key. Do I just create two primary keys on the table
that does not contain any primary key for this to become a composite primary
key?

Thank you in advance!

Still a newbie,
-Thomas

[Allen Browne]

Okay, you have 2 table with primary keys, and a junction table that has
foreign keys to the other 2 tables. You could select the 2 fields together
in Table Design view, and click the Key icon on the toolbar to make a
2-field primary key (i.e. the primary key consists of the combination of the
2 fields... there are not 2 primary keys.)

That would be a good approach if the junction table should not contain
duplicates. For example, if the 2 tables are Client and Newsletter, it would
be a good idea not to have the same person on the newsletter group twice.

It would not be a good idea where duplicates are desired. For example, if
the 2 tables are Member and Position, the person might be chairman in 2002
and again in 2005, so you could validly have 2 entries where the member
holds the same position, so using the 2-field primary key would not be
suitable. Instead, you could add an AutoNumber to the 3rd field and use that
as primary key.

If the 3rd table is also likely to be involved in relations to other tables,
you might elect to give it an AutoNumber rather than handle multi-field
joins to other tables, which potentially could themselves have further joins
to other tables and so on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Thomas A. Anderson" <NO************@hotmail.com> wrote in message
news:di**********@gnus01.u.washington.edu...

I am a bit confused in creating a composite primary key. I have three
table with two of the tables containing primary keys. I have two of the
tables (each with a primary key) having one to many relations with the
table containing no primary key. Do I just create two primary keys on the
table that does not contain any primary key for this to become a composite
primary key?

Thank you in advance!

Still a newbie,
-Thomas

[(PeteCresswell)]

Per Thomas A. Anderson:

I am a bit confused in creating a composite primary key. I have three table
with two of the tables containing primary keys. I have two of the tables
(each with a primary key) having one to many relations with the table
containing no primary key. Do I just create two primary keys on the table
that does not contain any primary key for this to become a composite primary
key?

Being easily confused myself, I never, ever use either composite primary keys or
any "real" field for a primary key.

Instead, I slavishly assign an AutoNumber PK (or, if there are strategic
problems with AutoNumber for a given situation, a Long field that I populate
uniquely).

It's been 12 years and I've never had a problem - and it seems to me like it's a
*lot* simpler, especially when I've been away from an app for a year or so and
have to understand it quickly again.
--
PeteCresswell

[polite person]

On Wed, 12 Oct 2005 07:31:28 -0700, "(PeteCresswell)" <[e-mail]> wrote:

Per Thomas A. Anderson:

I am a bit confused in creating a composite primary key. I have three table
with two of the tables containing primary keys. I have two of the tables
(each with a primary key) having one to many relations with the table
containing no primary key. Do I just create two primary keys on the table
that does not contain any primary key for this to become a composite primary
key?

Being easily confused myself, I never, ever use either composite primary keys or
any "real" field for a primary key.

Instead, I slavishly assign an AutoNumber PK (or, if there are strategic
problems with AutoNumber for a given situation, a Long field that I populate
uniquely).

It's been 12 years and I've never had a problem - and it seems to me like it's a
*lot* simpler, especially when I've been away from an app for a year or so and
have to understand it quickly again.

But surely you must sometimes use a composite unique index, or how do you
control unwanted duplicates?

[teddysnips]

polite person wrote:

On Wed, 12 Oct 2005 07:31:28 -0700, "(PeteCresswell)" <[e-mail]> wrote:

Per Thomas A. Anderson:

I am a bit confused in creating a composite primary key. I have three table
with two of the tables containing primary keys. I have two of the tables
(each with a primary key) having one to many relations with the table
containing no primary key. Do I just create two primary keys on the table
that does not contain any primary key for this to become a composite primary
key?

Being easily confused myself, I never, ever use either composite primary keys or
any "real" field for a primary key.

Instead, I slavishly assign an AutoNumber PK (or, if there are strategic
problems with AutoNumber for a given situation, a Long field that I populate
uniquely).

It's been 12 years and I've never had a problem - and it seems to me like it's a
*lot* simpler, especially when I've been away from an app for a year or so and
have to understand it quickly again.

But surely you must sometimes use a composite unique index, or how do you
control unwanted duplicates?

We're discussing something like this at the moment on another project,
where every Employee has a PIN (Personal Identification Number). PINs
are, by their nature, unique, and therefore a good candidate for
Primary Key. But what happens if the company decide in the future to
re-use PINs from retired or ex-employees?

In answer to your question, there is no reason why you cannot have a
composite unique index in addition to your PK. In fact, if there are
business rules like this, I prefer to enforce them at database level
(though currently I do much more work on SQL Server which has some
useful tools lacking in Access such as AFTER and INSTEAD OF Triggers)

Edward

[polite person]

On 12 Oct 2005 06:59:15 -0700, te********@hotmail.com wrote:

polite person wrote:

But surely you must sometimes use a composite unique index, or how do you
control unwanted duplicates?

In answer to your question, there is no reason why you cannot have a
composite unique index in addition to your PK. In fact, if there are
business rules like this, I prefer to enforce them at database level
(though currently I do much more work on SQL Server which has some
useful tools lacking in Access such as AFTER and INSTEAD OF Triggers)

Sorry, the point of my comment was that surely Pete must already be using composite unique indexes
therefore I was unable to see why he found it too difficult to ever use a composite primary key.

The question of whether or when to use articifical versus natural keys is a different one,
discussed here before. In the present dogmatic climate of the group discussions I don't
think reviving it would be useful.

[lylefair]

(PeteCresswell) wrote:

I slavishly assign an AutoNumber PK

The availability of a unique integer that identifies each record in the
database, to the database, and that is available as an identifier to
anything that connects to the database enhances the reliability and
validity of the database.

Sometimes, I think, beginners try to make this PrimaryKey identify Joe,
Mary, or Tuck Number 2005-4. PrimaryKeys have nothing to do with
identifying Joe, Mary, or Truck 2005-04. They are the first designated
unique indexes of tables; as such they order the table in certain
situations (such as JET compact), determine the physical order of the
table in certain circumstances (clustered indexes), and the outcome of
the output of tables in many circumstances, and identify records,
nothing else but records.

I think your slavish assignment is one of the simplest and best things
one can do to create a sound database.

In my databases these are many of these AutoNumber PKs which I never
use explicitly. Does this slow my dbs? Not that I have noticed. Can
Access always find my records? Yes.

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:43***********************@per-qv1-newsreader-01.iinet.net.au:

Okay, you have 2 table with primary keys, and a junction table
that has foreign keys to the other 2 tables. You could select the
2 fields together in Table Design view, and click the Key icon on
the toolbar to make a 2-field primary key (i.e. the primary key
consists of the combination of the 2 fields... there are not 2
primary keys.)

That would be a good approach if the junction table should not
contain duplicates. For example, if the 2 tables are Client and
Newsletter, it would be a good idea not to have the same person on
the newsletter group twice.

It would not be a good idea where duplicates are desired. For
example, if the 2 tables are Member and Position, the person might
be chairman in 2002 and again in 2005, so you could validly have 2
entries where the member holds the same position, so using the
2-field primary key would not be suitable. Instead, you could add
an AutoNumber to the 3rd field and use that as primary key.

Er, why add an AutoNumber for that? Wouldn't you instead (assuming
you're going with natural keys) add the year to the composite key?
You'd need that as a unique index even if you added the AutoNumber.
I'd only add an AutoNumber to a junction table if the PK of the
junction table is a foreign key in a child table (which can
certainly happen, of course).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"(PeteCresswell)" <[e-mail]> wrote in
news:ua********************************@4ax.com:

Per Thomas A. Anderson:

I am a bit confused in creating a composite primary key. I have
three table with two of the tables containing primary keys. I
have two of the tables (each with a primary key) having one to
many relations with the table containing no primary key. Do I
just create two primary keys on the table that does not contain
any primary key for this to become a composite primary key?

Being easily confused myself, I never, ever use either composite
primary keys or any "real" field for a primary key.

Instead, I slavishly assign an AutoNumber PK (or, if there are
strategic problems with AutoNumber for a given situation, a Long
field that I populate uniquely).

It's been 12 years and I've never had a problem - and it seems to
me like it's a *lot* simpler, especially when I've been away from
an app for a year or so and have to understand it quickly again.

But surely you create unique indexes on those natural key
candidates? If not, how do you prevent the addition of duplicates?

Junction tables are the only place where I'll tend to use composite
keys (though they are hardly ever "natural keys," since the joins
are almost always on Autonumber fields), except where there is some
natural unique index that needs to be and *can* be enforced.

You may not use those as *primary keys* and use surrogate keys
instead (i.e., Autonumber), but you still need the unique index, or
your schema is really incomplete.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[jimfortune]

David W. Fenton wrote:

"(PeteCresswell)" <[e-mail]> wrote in
news:ua********************************@4ax.com:

Per Thomas A. Anderson:

I am a bit confused in creating a composite primary key. I have
three table with two of the tables containing primary keys. I
have two of the tables (each with a primary key) having one to
many relations with the table containing no primary key. Do I
just create two primary keys on the table that does not contain
any primary key for this to become a composite primary key?

Being easily confused myself, I never, ever use either composite
primary keys or any "real" field for a primary key.

Instead, I slavishly assign an AutoNumber PK (or, if there are
strategic problems with AutoNumber for a given situation, a Long
field that I populate uniquely).

It's been 12 years and I've never had a problem - and it seems to
me like it's a *lot* simpler, especially when I've been away from
an app for a year or so and have to understand it quickly again.

But surely you create unique indexes on those natural key
candidates? If not, how do you prevent the addition of duplicates?

Junction tables are the only place where I'll tend to use composite
keys (though they are hardly ever "natural keys," since the joins
are almost always on Autonumber fields), except where there is some
natural unique index that needs to be and *can* be enforced.

You may not use those as *primary keys* and use surrogate keys
instead (i.e., Autonumber), but you still need the unique index, or
your schema is really incomplete.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

I suppose it never hurts to have the composite key around to help
ensure uniqueness. Any extra checks and balances that Access can
provide are good. I never want to trap an error saying I have a key
violation so I end up with the same amount of coding either way. The
composite key is just a final safety check. Even better would be not
to present choices on the form that could cause a duplicate in a
junction table. If anyone is allowed to edit the junction table
directly the composite key would be more important. I just don't think
there's a tempest in this teapot.

James A. Fortune

[(PeteCresswell)]

Per polite person:

But surely you must sometimes use a composite unique index, or how do you
control unwanted duplicates?

Frequently. I usually name such a key something like "UniquenessEnforcer".
But, even though it's redundant, I still assign the PrimaryKey role to a dumb,
blind, random number. i.e. you'll never see a composite join in any of my DB
diagrams.
--
PeteCresswell

[(PeteCresswell)]

Per te********@hotmail.com:

We're discussing something like this at the moment on another project,
where every Employee has a PIN (Personal Identification Number). PINs
are, by their nature, unique, and therefore a good candidate for
Primary Key. But what happens if the company decide in the future to
re-use PINs from retired or ex-employees?

Some people think the same thing about social security numbers.

I was one of them, but soon learned better.

Right now, I wouldn't even *think* about using a real, live value as a PK ....
Ok, maybe two-digit state abbreviations in tlkpState.... but for the other
99.9% of the cases to me it just isn't worth wasting the time thinking about
whether a given real-world value that is touted as unique really is so; when it
comes to DB design, I don't waste the time worrying about it.

If something like SSN seems unique and the turns out tb not... there's still
some work to be done, but a lot less work than if it were functioning as a PK.
--
PeteCresswell

[(PeteCresswell)]

Per polite person:

Sorry, the point of my comment was that surely Pete must already be using composite unique indexes
therefore I was unable to see why he found it too difficult to ever use a composite primary key.

The question of whether or when to use articifical versus natural keys is a different one,

To me, at least, compound keys add an additional layer of understanding needed
to grasp a DB's design; so I avoid them based on that issue alone.

My experience in earlier days of computing has been that some of the smartest
people wrote some of the hardest-to-maintain systems. The reason I saw was
that their minds were so quick, so big, and so facile that things that were
convoluted and hard to understand for most people were dirt simple/patently
obvious to them.

Being on the other end of the scale from those guys, I probably err on the side
of simplicity. Working with JET helps - looking at an Oracle-based project I
was on the fringes of a couple years ago, it seemed like that approach wouldn't
have washed.
--
PeteCresswell

[dreadnought8]

What are your opinions on using the (unnatural) PKs as foreign keys in
other tables?
For example, with an Order Header of
tblOrder(OrderPK,OrderNumber,CustomerNumber,OrderD ate,etc)
(OrderNumber being the identifier quoted to the outside world)
would you recommend the OrderDetail be:
(a)
tblOrderDetail(OrderDetailPK,OrderNumber,ItemId,Qt yOrdered,Price,etc)
or
(b) tblOrderDetail(OrderDetailPK, OrderPK,ItemId,QtyOrdered,Price,etc)?
I currently prefer (a) and enforce RI on the join between
tblOrder.OrderNumber and tblOrderDetail.OrderNumber.
With (b) you don't get to see the outside world OrderNumber on
tblOrderDetail which makes me a bit nervous (you have to join to get at
it).
But I must admit I don't feel this is a well informed choice ...
Terry Bell

[lylefair]

b) always;
You probably should access data from tblOrderDetail with a query or
view that joins tblOrder and tblOrderDetail and exposes the fields of
both tables, including OrderNumber. If you are using a Form-SubForm
then OrderNumber will show in the main object and can be coded to show
in the subobject too if that's wanted.

[Allen Browne]

David, you understand that this was just an example which would probably get
more complex in practice, e.g. where the person holds a position for a date
range which may not be the full year.

You could come up with better examples, I'm sure.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@216.196. 97.142...

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:43***********************@per-qv1-newsreader-01.iinet.net.au:

Okay, you have 2 table with primary keys, and a junction table
that has foreign keys to the other 2 tables. You could select the
2 fields together in Table Design view, and click the Key icon on
the toolbar to make a 2-field primary key (i.e. the primary key
consists of the combination of the 2 fields... there are not 2
primary keys.)

That would be a good approach if the junction table should not
contain duplicates. For example, if the 2 tables are Client and
Newsletter, it would be a good idea not to have the same person on
the newsletter group twice.

It would not be a good idea where duplicates are desired. For
example, if the 2 tables are Member and Position, the person might
be chairman in 2002 and again in 2005, so you could validly have 2
entries where the member holds the same position, so using the
2-field primary key would not be suitable. Instead, you could add
an AutoNumber to the 3rd field and use that as primary key.

Er, why add an AutoNumber for that? Wouldn't you instead (assuming
you're going with natural keys) add the year to the composite key?
You'd need that as a unique index even if you added the AutoNumber.
I'd only add an AutoNumber to a junction table if the PK of the
junction table is a foreign key in a child table (which can
certainly happen, of course).

[Thomas A. Anderson]

Thank you so much everyone in expanding my knowledge! From the replies on
this topic, composite Primary Key seems to be more complicated than I
perceived the topic to be. Can anyone provide me with book reference
(titles), web links, or other type of documentation that I may continue to
read up on this topic? I googled the topic and could not really find
anything that was relevant to the topic, or did not explain the topic as
well as the posts in this forum. I am currently taking beginning DB with MS
Access at a local college and would like to become more familiar with this
topic before moving on to the next topic, Queries.

Again, thank you for the insight on this topic!

-Thomas
"Thomas A. Anderson" <NO************@hotmail.com> wrote in message
news:di**********@gnus01.u.washington.edu...

I am a bit confused in creating a composite primary key. I have three
table with two of the tables containing primary keys. I have two of the
tables (each with a primary key) having one to many relations with the
table containing no primary key. Do I just create two primary keys on the
table that does not contain any primary key for this to become a composite
primary key?

Thank you in advance!

Still a newbie,
-Thomas

[polite person]

On Wed, 12 Oct 2005 22:57:15 -0700, "Thomas A. Anderson" <NO************@hotmail.com> wrote:

Thank you so much everyone in expanding my knowledge! From the replies on
this topic, composite Primary Key seems to be more complicated than I
perceived the topic to be. Can anyone provide me with book reference
(titles), web links, or other type of documentation that I may continue to
read up on this topic? I googled the topic and could not really find
anything that was relevant to the topic, or did not explain the topic as
well as the posts in this forum. I am currently taking beginning DB with MS
Access at a local college and would like to become more familiar with this
topic before moving on to the next topic, Queries.

Again, thank you for the insight on this topic!

-Thomas

Hi
Here is a web reference which covers this topic briefly, it is really about the pros and cons of
natural versus artificial keys but this is often the same thing as no one (?) uses composite
artifial keys.
http://www.bcarter.com/intsurr1.htm
It dates from 1997 and is fairly generic.

This is a topic where most people fairly soon take a view (and then stick to it fairly dogmatically)
the commonest of which is "always use a single artificial key".

Note that as an artifical or surrogate key is not part of the data (one of its advantages) it is
important that two records are not identical in their other fields.

Though in principle a table doesn't need to have a primary key (eg linking tables for many-to-many
joins) , in many situations this will make the data engine prevent you from editing the table. This
is an example of where, when you add an artifical key, you should add a composite index on the other
fields or use soime other method to ensure that records are not duplicated.

HTH

[FreeviewClipper]

On Fri, 14 Oct 2005 22:37:24 GMT, polite person <si*****@ease.com> wrote:

Here is a web reference which covers this topic briefly, it is really about the pros and cons of
natural versus artificial keys but this is often the same thing as no one (?) uses composite
artifial keys.

Not sure what I was thnking of when I wrote the last bit, ignore it. Personally I always use
artifical keys but only sometimes add an extra key to replace a composite (artificial) key on a join
table.