112 | Chapter 4: Database
If the table had been namedclients, you would not even need theset_table_namecall.
The relationships and constraints will be inferred from thecustomers table’s constraints.
Composite Keys
Composite keys, primary keys made up of two or more attributes, are best avoided.
Not only are they harder to manage than simple primary keys, they are usually more
fragile. The motivation for using composite keys is usually based in some inherently
unique aspect of the data, which means the composite key will be meaningful (tied
to the data) rather than meaningless (tied to the database only). It is usually much
more resilient to assign a meaningless primary key used only within the database.
That way, data integrity is internal to the database rather than being tied to an exter-
nal system or process.
As an example, consider a database that tracks U.S. members by their driver’s license
numbers. A candidate key would be{Issuing state,License number}. One immediate
advantage of a meaningless key is that integer values are easier to represent than lists; it
is easier to refer to a record as 12345 than as[IL,1234]. This makes foreign keys much
simpler, and it simplifies web services and other protocols used for interoperability.
But the most basic problem is that a primary key is usually treated as a unique, sta-
ble identifier for a record. A composite key may not actually be unique in practice
and may even change. If you were to use the preceding composite key, you should be
prepared to answer questions like:
- What happens when a member moves or has a new license number issued?
- What happens if some inherent characteristic of the key changes? For example,
how would you handle it if license numbers were always 9 digits and changed to
10? This is a problem in general with keying off of meaningful data. - Are you prepared to have every record with a duplicate or missing key rejected?
Or might it be desirable to have the system hold invalid data for a time until it is
corrected?
There are some valid situations for using composite keys, though. A good example is
in multimaster replication. One big problem in asynchronous multimaster replica-
tion is synchronizing primary key sequences. If you insert two records at roughly the
same time to two master servers, there must be some mechanism to ensure that the
two servers issue different values for the primary keys on each record, lest problems
ensue when the records are replicated.
The composite-key solution to the problem of multimaster sequences is to issue each
server an ID and use that as part of the key; then each server can maintain its own
sequence independently of the others. The two records could have primary keys of
{ServerA, 5 }and{ServerB, 5 }and there would be no conflict. Note that this is a legit-
imate use of composite keys, since the keys are meaningless (relative to the data
being stored in attributes).