Usage of composite primary keys

Question

Lets say that I have a table where I want to record usernames. At minimum the table should have two columns : UserID and Name. Typically I see people put a primary key on UserID turn on auto increment and call it day.

However that seems wrong to me, shouldn't the key be a composite key between both columns? As we don't want the username to ever be repeated. We want to also keep UserID so that we don't break existing references to a particular user if their name changes.

What I'm getting at is it seems like many people default to an ID column with a PK on it for every table they make without making sure that key enforces uniqueness on other columns as well.

With that being said, am I correct in stating that a table should always have an ID column for referencing, but it is equally important to use composite primary keys?

Answer 1

No, you don't want it as a composite key; that means that the combination is the primary key, so you could then have different names on the same ID, and different ID's on the same name.

I.E.:

UserID    Name
-----------------
1         BobaFett
2         JarJar
1         JarJar
2         BobaFett

In the absence of any other constraints, making the primary key a composite of (UserID, Name) would make the previous data perfectly legal.

Your UserID column is a surrogate key; I'll leave the discussion about the wisdom of using surrogate keys vs. natural keys (your Name column is a natural candidate key) for another time, but if you want to keep your design the way it is now, then you should leave UserID as the primary key but also add a unique constraint on Name. This will prevent the same name from being attached to multiple records.

Answer 2

You should make both UserID a key and Name a key, which is not the same thing as making a compound key (a compound key means one key, not two).

You are right that some people will naively or carelessly just assign only a surrogate key to tables, without necessarily thinking about what natural keys are needed. At least that is the impression you could get from many people who advocate surrogates as somehow being a "better" alternative to natural keys. Obviously they are nothing of the kind. Surrogates are no alternative at all because they achieve a different thing altogether.

The important thing is that a table should have as many keys as are needed for data integrity to ensure that the table accurately represents the business rules it is suppposed to represent. Don't ever be mislead into thinking that one key is always all you need.

Answer 3

If you want Name to be unique, make a separate constraint for that. It's a different issue. Have your key be a combination of the two has no advantages, only disadvantages. It would mean that both the UserID and Name could be repeated. Is this what you want?

The primary key is not for uniqueness only. It also can be great for index performance. And often you think your natural key is going to be unique, but that changes later. A natural key can be a good move though. Another defensible option is to use GUIDs. for your primary key.

There is a lot of good information out about using autonumber numeric or natural keys, and the advantages of multi-column keys. Many (including me) believe that autonumbers are usually best, although there are good opinions the other way. It certainly is the case that you should understand the factors, and will eventually use different strategies in different situations.