How to Select Primary Keys

Let’s look at the data we store in the databases. Every record needs a primary key, a unique identifier used to distinguish this field from others. There are multiple ways to define primary keys. Let’s look at them.

Using meaningful data as a primary key

This looks like a great method at first. We use names to identify people in real life. Why wouldn’t we use user names or emails as identifiers in our systems?

This method would work fine until we have two users with the same name or until someone changes their name. The primary key is often used in other tables and external references. It should never change. The only way to guarantee that is to use a value that’s only an identifier.

There is one exception. We might store application parameters in a key-value database. In this case, the record key is a parameter name.

Using incremental IDs as a primary key

One common way to set primary keys is to use the incremental IDs provided by the database. In this case, we define the ID field as an AUTO_INCREMENT and don’t provide a value for this key when creating records. The database assigns incrementing ...