Why do I read so many negative opinions on using composite keys?

Question

I was working on an Access database which loved auto-numbered identifiers. Every table used them except one, which used a key made up of the first name, last name and birthdate of a person. Anyways, people started running into a lot of problems with duplicates, as tables representing relationships could hold the same relationship twice or more. I decided to get around this by implementing composite keys for the relationship tables and I haven't had a problem with duplicates since.

So I was wondering what's the deal with the bad rep of composite keys in the Access world? I guess it's slightly more difficult to write a query, but at least you don't have to put in place tons of checks every time data is entered or even edited in the front end. Are they incredibly super inefficient or something?

Answer 1

A composite key works fine for a single table, but when you start to create relations between tables it can get a bit much.

Consider two tables Person and Event, and a many-to-many relations between them called Appointment.

If you have a composite key in the Person table made up of the first name, last name and birth date, and a compossite key in the Event table made up of place and name, you will get five fields in the Appointment table to identify the relation.

A condition to bind the relation will be quite long:

select Person,*, Event.*
from Person, Event, Appointment
where
  Person.FirstName = Appointment.PersonFirstName and
  Person.LastName = Appointment.PersonLastName and
  Person.BirthDate = Appointment.PersonBirthDate and
  Event.Place = Appointment.EventPlace and
  Event.Name = Appointment.EventName`.

If you on the other hand have auto-numbered keys for the Person and Event tables, you only need two fields in the Appointment table to identify the relation, and the condition is a lot smaller:

select Person,*, Event.*
from Person, Event, Appointment
where
  Person.Id = Appointment.PersonId and Event.Id = Appointment.EventId

Answer 2

If you only use pure self-written SQL to access your data, they are OK.

However, some ORMs, adapters etc. require having a single PK field to identify a record.

Also note that a composite primary key is almost invariably a natural key (there is hardly a point in creating a surrogate composite key, you can as well use a single-field one).

The most common usage of a composite primary key is a many-to-many link table.

When using the natural keys, you should ensure they are inherently unique and immutable, that is an entity is always identified by the same value of the key, once been reflected by the model, and only one entity can be identified by any value.

This it not so in your case.

First, a person can change their name and even the birthdate

Second, I can easily imagine two John Smiths born at the same day.

The former means that if a person changes their name, you will have to update it in each and every table that refers to persons; the latter means that the second John Smith will not be able to make it into your database.

For the case like yours, I would really consider adding a surrogate identifier to your model.