Using compound primary keys

A primary key provides a value that is guaranteed to be unique for every record in a table. This is often a number in a single field. For example, if we sell books, we could give each book a unique ProductID.

It is a good idea to use a simple primary key like this because it’s easy to understand and queries will be fast.

Access’ AutoNumber data type is often a good choice. However, it is possible to use two (or even more) fields for the primary key; this is called a ‘compound’ or ‘joint’ primary key.

If you do this, it is fine for two records to have the same value in one of the fields as long as the other contains a different value. For example, the screenshot of the table’s primary key is made up of ProductID and ProductID2.

We can now have duplicate values ProductID as long as the value in ProductID2 is different (and vice versa). As the screen shows, if we tell Access these two form the primary key, it will ensure we never duplicate the values in both fields between two rows.

So, a good question, and one that PCW reader Tom Boyd asked, is: under what circumstances is it worth accepting the added complexity of a compound primary key?

One answer is that the table above is a good example of bad practice. All I have done is to make the table more complex for no gain.

However, when used correctly, compound primary keys are wonderful. More than that, they are common because we use them to solve a common problem.

Databases are constructed to model the behaviour of the real world. In that world, we find real-world objects that have a many-to-many relationship between them.

Imagine a situation where we have many products for sale and many orders being placed by our customers. Each product can appear on many different orders and each order can have many different products on it.

Our database needs to be able to store this relationship, so we need a many-to-many relationship between the Order table and the Product table. We do this by creating a table that sits between Order and Product ­ I’ve called it OrderDetail.

Following the joins, we can see that Order number 1 was for two different products: three copies of ‘Gordon the Wonder Land Rover’ and two copies of ‘EF goes to France’. Order number 2 was for three products.

Suppose the customer who placed order number 1 rings back and adds four more copies of ‘Gordon the Wonder Land Rover’, we simply amend the Number field from 3 to 7.

We actively don’t want more than one row in OrderDetails that points to the same order and the same product; it would just be confusing. If we declare these two fields to be the primary key of OrderDetail, Access ensures that we can never do this.

There is an important point here. When people (myself included) first use a relational database, they often find that it seems obstructive. No matter what you try to do, the database engine interferes. You can’t change this, you mustn’t do that.

But, if the database is designed properly, it never gets in the way unless you try to do something foolish (such as adding the same product to an order twice).

Unnatural primary keys
Tom also mentioned my brief coverage of natural primary keys, which use a value that already exists in the real world and is guaranteed to be unique, such as the registration number of a car.

Even if you find a natural key, it’s always worth considering whether it is the best solution; just because there is a natural key doesn’t mean it’s definitely the best in every situation.

For instance, where you have a table of employees, each one with a National Insurance number, it’s tempting to use the NI number as the primary key.

However, NI numbers contain a mix of characters and numbers and will make queries slower. This is unlikely to be a problem in a small business, but it could become so with a very large table.

You may also have to cope with rare instances where employees don’t have a number. Store NI numbers by all means, but consider using a more compact numerical value/AutoNumber as the primary key.

Like this story? Spread the news by clicking below:

 del.icio.us     Digg this     reddit!