A few days ago Pablo Iglesias tweeted a Stack Exchange question about the immutability of primary keys. It sparked a pretty interesting debate on Twitter that went beyond the immutability question. This is an attempt to gather and explain some of the concepts that came up during that debate.

Mutable or immutable primary keys

For anyone a bit lost with the concept, it’s basically about deciding whether an entity’s primary key should be allowed to change once it’s set. For example: if I sign up for an online service and they use my email as the PK, and the service lets me change my email account, the PK is mutable. This means that everywhere the service persists my email as a PK, it’ll have to update the data to reflect the change. Whether or not we use foreign keys, this is a source of pain, especially if we use different data persistence mechanisms. If on top of that our software has features like auditing changes or any kind of integration with external systems such as CRM tools and the like, having mutable PKs can become impossible.

There seemed to be general consensus that PKs should be immutable, so I won’t go on about this point.

Surrogate or natural keys

When we use a natural key, the value of the key is related to the data it identifies. For example, if we’re identifying books, the ISBN could be a PK candidate. The chassis number for a car, the serial number of a computer, the MAC address of a network card, an invoice number, etc. In short, we use a piece of business data to identify our entities when we persist them.

If, on the other hand, we choose to use a surrogate key, what we’re doing is generating a unique identifier that has nothing to do with the data it identifies, but the infrastructure guarantees the value is unique.

Although I always add a big “it depends” and I’m not a big fan of absolute statements, the truth is I’ve been using surrogate keys for years because experience has shown me that using business data is usually a bad idea. The reason is very simple: the business changes.

There are thousands of examples. To pick one, Sergio León mentioned book ISBNs. When developing software to manage bookstores we might think the ISBN would do the job, but very old books don’t have an ISBN. So if the bookseller suddenly decided to start selling antique collector’s books, we’d have a big problem. These kinds of situations happen very often, and always when development is already well advanced, which is when they hurt the most.

There are many examples like the ISBN one, and we have to take the cost of change into account. Someone could argue that thinking about the future is a mistake and that using a surrogate key is a premature decision based on anticipating a possible business change that we don’t know will ever happen. In those circumstances it’s usually a good exercise to think about what the cost of the change would be in the future versus the cost of using a surrogate key now. If we think about it in economic terms, using a natural key could mean signing a very expensive mortgage.

Also, as Pedro J. Molina pointed out, “If you go with natural keys you easily end up with composite keys. The 4-field ones are no fun anymore”. And on top of that, it ends up being a real pain in the neck when the time comes to “plug in” to other things. It’s typical for the service that lets you attach comments, likes, etc. to ask you for a unique identifier for your entity, and we could go crazy looking for examples that make us wish we’d always worked with a surrogate key.

Generating surrogate keys

Come on, let’s assume that by now we’re convinced we’re going to identify our entities with immutable surrogate keys, at least some of the time. How do we generate them? This point also sparked debate, and this time there was nothing close to unanimity.

To generate a surrogate key we can let the database generate it for us automatically when we insert the record, or we can provide the key to the database ourselves from the code.

The classic option is to let the database generate the identifier for you and that’s it. We can use an identity, a sequence or whatever the database we’re using offers, and we don’t make life very complicated. But there are scenarios where we might want to use other kinds of mechanisms.

Mechanisms for generating surrogate keys

Although we could get very creative when it comes to generating our keys, there are a few predominant mechanisms. I’m not going to take sides on any of them because I think using them depends a lot on the business requirements and the application’s infrastructure.

Guids

Generating Guids is easy, it can be done from anywhere and the chances of collision are absolutely negligible. In principle it’s an interesting mechanism since it lets us keep control from the business side and not depend on the database to generate them. Also, among its virtues, by using a Guid we’re using an identifier that’s unique not only within the table but across all tables and even across several databases. This makes “merge” scenarios, where we want to combine data coming from several sources, much easier.

Of course, it also has its downsides, among them that a Guid takes up much more space than a numeric key, and it also has a negative impact on the performance of clustered indexes.

There’s a lot of literature on this topic on the internet, including some attempts to analyze the difference from a performance point of view. We also have to keep in mind that the database implementation we use makes a huge difference. For example, using MySQL is not the same as using SQL Server.

Auto-incrementing numbers

This mechanism is one of the most common and I don’t think it needs much explanation. The SQL:2003 standard defines the IDENTITY and SEQUENCE types, but we depend on the database we’re using. For example, MySQL has AUTO_INCREMENT and PostgreSQL also has SERIAL (I think it’s “syntactic sugar” for defining a SEQUENCE).

HiLo

Broadly speaking, with HiLo the application reserves in advance in the database a range of identifiers that it will use when it needs to insert new records into the database. When the application has used all the identifiers, it goes to the database and reserves more. NHibernate uses this technique and it can also be used in Entity Framework. Among its advantages, it lets us use numeric identifiers (for those who demand performance), and among its disadvantages, it creates “gaps” between the generated identifiers. If an application reserves identifiers 1 to 10 and then only uses 5, identifiers 6 to 10 will go unused.

Depending on a third party

This consists of delegating our key generation strategy to a third party. From our application, when we want to identify an entity, we call this external service and it gives us an identity that we’ll then persist.

When we’re doing HiLo, we’re partly using this mechanism. Even though it’s our database that generates the identifiers, it’s really a third party (the class that manages the algorithm) that hands them to us.

An example (odd but real) is using it when persistence happens on mechanisms that don’t support auto-numbering, delegating the generation of the auto-number to a third party and then persisting the value. Another use case would be if we need to make sure our identifiers are unique across several systems and we also want to apply special rules to generate them.

To be realistic, it’s a mechanism that adds complexity and isn’t needed most of the time, but it’s still worth mentioning.

Which one do I use?

Going by the replies to the conversation that started the debate, a large majority would answer “auto-incrementing numeric sequences and I keep it simple”. They’re not wrong, but sometimes complicating things is the least complicated option. Complicated, huh? 😉

There are many reasons that could lead us to want to complicate our lives, and I’m not trying to make a detailed study, but I at least want to give some common examples.

CQS

If we want to use CQS (Command-Query Separation), we start from the principle that our commands don’t return anything. If we have a command that inserts an entity, we should know the id of the entity we’re inserting before calling the command itself, since the command isn’t going to return anything.

We might think it’s no big deal for an insert command to return a value and that’s that. I’m not going to get into judging this, but as soon as we want our commands’ execution model to be asynchronous (through a queue or similar), we’re back to the original problem.

Offline mode

If our application has a business requirement to support working offline, going to the database server to get an entity’s id isn’t an option.

Imagine an application like Evernote. When we create a new note and we don’t have a connection, what identifier will that note have then? If we generate that identifier from the code (for example, with a GUID) we can sync easily once we have a connection.

Although in the CQS case HiLo could be a perfectly valid option, here it would be the least recommended one, since we’d need to connect to the database server if we created so many entities that we ran out of our “quota” of identifiers.

If we don’t use SQL

This case is ignored by many developers whose professional context is limited to SQL databases for persisting information. When we step out of the SQL world, lots of persistence mechanisms ask us to provide our identifiers ourselves from the code. As an example that’s “primitive” but useful, if we persist to disk we’ll have to provide our entity’s identifier ourselves (the file name).

Conclusions

We have plenty of mechanisms to identify our entities. We should know them well and use them wisely according to our business requirements, but never complicate our lives unnecessarily. And very importantly, performance should only be an argument when we have non-functional requirements that talk about performance. Talking about performance when you don’t have performance-related requirements is usually a source of premature optimizations.

And with this I think I’ll wrap up the “summary” of the things discussed in the Twitter thread and some of the ones that crossed my mind. I’m sure I’ve left out plenty of observations and nuances; I’ll be happy if it helps someone understand that there’s life beyond mutable natural identifiers 😀