Multiple Primary Keys?

[XelaIrodavlas]

Hi all,

Wasn't sure if this question should come under the Tables section so sorry if i'm in the wrong place.

I'm just trying to figure out why Access allows me to apply multiple Primary Keys to a table, surely every table should have just one?

The situation comes about in many to many joins, If I have a table linking Events to Attendees the join table will look something like:

EventID (PrimaryKey)
PersonID (PrimaryKey)
OtherStuff

I was once told that both the EventID and PersonID should both be the primary keys, but now I am not so sure... Surely it would be better practice to add another field (AutoNumber) and use this as the single primary key, that way it will be easier to link into any other table if required later.

ID (Primary Key)
EventID
PersonID
OtherStuff

Does it actually make a difference?

Any thoughts appreciated,

Thanks all,

 

[jdraw]

There is only 1 Primary Key for any table. When you see multiple fields making up a primary key, that key is known as a composite or compound primary key. The rationale for composite primary keys is usually the handling of Many to Many relationships. In the M:M situation, there are 2 common ways to set up primary keys.
1) Use the Primary Keys from each of the tables in the M:M relationship to build a compound Primary Key for the junction table.
2) Use a meaningless surrogate (autonumber) as the Primary key, then make a unique composite index of the Primary keys from each of the tables in the M:M relationship.
Add any other fields to that unique composite index necessary to avoid duplicates.

Option 1 is often used, but it can get difficult/complex if there are other M:M relationships involving the junction table created.
Option 2 reduces some of the potential complexity, but does require the unique composite index to prevent duplication.

Hope this is helpful. You can google the terms for more info and examples.

 

[XelaIrodavlas]

Hi JDraw,

So it's basically down to personal preference. I just wondered if there were any performance/practical reasons why one method should be used over the other.

thanks for the reply,

 

[jdraw]

See these links for general discussion(various DBMS):
http://stackoverflow.com/questions/159087/composite-primary-keys-versus-unique-object-id-field
https://engineering.eventbrite.com/optimizing-a-table-with-composite-primary-keys/

 

[gemma-the-husky]

Hi all,

Wasn't sure if this question should come under the Tables section so sorry if i'm in the wrong place.

I'm just trying to figure out why Access allows me to apply multiple Primary Keys to a table, surely every table should have just one?

The situation comes about in many to many joins, If I have a table linking Events to Attendees the join table will look something like:

EventID (PrimaryKey)
PersonID (PrimaryKey)
OtherStuff

I was once told that both the EventID and PersonID should both be the primary keys, but now I am not so sure... Surely it would be better practice to add another field (AutoNumber) and use this as the single primary key, that way it will be easier to link into any other table if required later.

ID (Primary Key)
EventID
PersonID
OtherStuff

Does it actually make a difference?

Any thoughts appreciated,

Thanks all,

EventID (PrimaryKey)
PersonID (PrimaryKey)
OtherStuff


this is not 2 distinct primary keys. (or even 2 distinct unique indexes). If it was, you would not be able to have the same eventid (or personid) in the table more than once.

you have a primary key which (correctly) comprises 2 segments. (therefore confirming that a person can be entered in a given event once and once only)

You could instead do the same thing by setting eventid+personid as a unique index, and adding a surrogate autonumber key. It's a matter of taste really. Indeed, It may be worth having both eventid+personid AND personid+eventid as indexes. It depends how the table gets referenced in other queries.

(Keys/indexes are much the same really)

If you need to use this table in a lot of queries, you may find it useful to have the autonumber key available, for use as the foreign key in related tables, as this will simplify your query management down the line.

Personally, I tend to add the autonumber key.

 

[The_Doc_Man]

I'll second the suggestion of using an autonumber key even though a natural compound key might exist, because if you are using a very large table, the autonumber key is likely to be shorter when used as a foreign key in other tables. Short = more records fit in the Access working buffer = faster lookup (admittedly by fractions of a millisecond) and smaller child tables. If the child tables are large enough, that second factor can become relatively significant.

 

[ButtonMoon]

I'll second the suggestion of using an autonumber key even though a natural compound key might exist, because if you are using a very large table, the autonumber key is likely to be shorter when used as a foreign key in other tables. Short = more records fit in the Access working buffer = faster lookup (admittedly by fractions of a millisecond) and smaller child tables. If the child tables are large enough, that second factor can become relatively significant.

However, if you replace every composite foreign key in a database with a synthetic single attribute then you will be forced to do a join every time you need to retrieve the meaningful key values that really matter. If you use composite keys you may not need to do a join at all or you will need fewer joins. The more synthetic keys (i.e. "surrogate" keys) you have, the more joins you have to do. This can prove a very costly overhead in a complex application. So the performance advantages are not all on the side of using a surrogate key. You need to decide whether the potential benefits outweigh the costs in your particular case.

 

[The_Doc_Man]

Button, I regret being dense here, but...

If I have a compound PK on a table and I have another table with a compound FK, am I not still doing JOINs to use the tables in combination? And in that case, if I have two LONG values (or worse, two STRING values) as the compound FK, I have to do the comparison operation (to match up the keys) on 64 bits or longer, vs. only doing a single 32-bit comparison on the surrogate keys.

The question about the size of the records IS significant and your point is correct in that I have added 32 bits to the record by adding a surrogate key. My issue is mostly about performance, though. The trick is that when Access joins tables across their indexes, the index is a separate "table" that contains, in essence, an index value and a pointer to the record bearing that value - and that's all. The PK isn't a part of the surrogate key's index. (Nor is the FK part of the surrogate key's index.) Therefore, the joins can be fairly efficient.

 

[ButtonMoon]

So let's take the example of a very simple query on a table with three attributes where (b,c) just happens to be a composite foreign key:

SELECT a,b,c FROM tbl1;

If you insist on referencing a surrogate instead then to get the same result I have to do a join:

SELECT tbl1.a, tbl2.b, tbl2.c
FROM tbl1, tbl2
WHERE tbl1.id = tbl2.id;

I never wanted the "id" column in the query of course, but I'm still forced to do the join just in order to get the b and c values that I do need.

Worse still, some people (I'm not assuming that includes you BTW) are particularly rabid about eliminating composite natural keys and will insist on decomposing them even further to make three tables:

SELECT tbl1.a, tbl2.b, tbl3.c
FROM tbl1, tbl2, tbl3
WHERE tbl1.id_a = tbl2.id_a AND tbl1.id_b = tbl3.id_b;

This kind of thing quickly multiplies the complexity of a database. The more your tables fill up with meaningless surrogate keys the more work you have to do to make sense of the data. The cost is human effort and complexity of code as well as potential processor cycles.

Clearly the cost or benefit depends on the intended usage and that's why each case needs to be evaluated on its merits. The lazy approach of "surrogates everywhere" has more in common with religious dogma than practicality (again, I'm not assuming you take a dogmatic point of view, but sadly some people seem to).

 

[The_Doc_Man]

In fact I agree with you that blindly moving towards OR away from surrogate keys is probably not a good idea. My point was that when you have a slow network, the JOIN is faster on surrogate keys than it would be on natural compound keys. And perhaps you should know that I learned this the hard way by having a dog-slow network for the shared back-end file. The idea of joining across surrogate keys was one of the tricks that actually DID improve overall performance, because once the participating records are identified in the JOIN part of the query, we were able to get onto the process of updating records. Now, if you look at this as a JOIN + UPDATE as two phases of the overall query, all I'm saying is that over a slow network, the surrogate JOIN is faster than the compound JOIN, but then the UPDATE portion is about even.

If you are more fortunate than I was, you didn't have a dog-slow network and therefore didn't have to worry about relative speed of surrogate vs. compound joins.

 

[ButtonMoon]

Doing joins over the network sucks I agree. But in the 21st Century there's no very good reason to use a file-based database on a network anyway. And even less reason to let legacy tech get in the way of sensible database design.

 

[The_Doc_Man]

Button, I was doing this for a U.S. Navy facility. Whatever made you think that the words "sensible" and "database design" belonged in the same sentence?