Give Up on Natural Primary Keys

There is plenty of debate over how to design your database. At SQLServerCentral we have a Stairway Series as well as a few articles that cover design topics. I think it’s important for anyone that builds tables to spend some time learning what others have done and understand the pros and cons of making different choices. It does often become hard to change designs once they are in use, so trying to choose a good entity design early is important.

One of the things I think is important in modeling your particular entity is including a primary key (PK). In my DevOps talk I stress this, as I’d rather most attendees come away thinking a PK is important as their first takeaway from the session. There are exceptions, but they are rare, and I would prefer that most tables just have some PK included from the beginning.

A PK ought to be stable as well, and there are plenty of written words about how to pick the PK for your particular problem domain. Often I have received the advice that natural keys are preferred over surrogate keys, and it is worth the effort to try and identify a suitable column (or set of columns) that will guarantee uniqueness. I think that’s good advice, and it’s also advice I tend to ignore.

There’s an interesting article about keys and the GDPR. The first part is a rather basic description of what PKs are, but the second part talks about keys and some of the rights that data subjects have under the GDPR. I think these are worth considering, especially as it’s likely similar legislation will make its way into other jurisdictions, as already seen in California. The short part of the argument is that the right to be forgotten or to have your data deleted is incompatible with the use of natural keys.

It’s an argument, though I’m not completely sure if I think it would be solid. There are valid reasons to keep some information about a user, and I suspect keeping a list of emails to delete from a database restore as a separate list would be a valid use. Even if the user asked that their information was removed. It would be, but there would also be a need to ensure that the correct data was removed,  hence a list of emails.

The bigger problem for me is that if I needed to redact or alter this key data, which I would likely do in order to keep some integrity in my database, I’d need to alter this data in lots of tables. That makes for a much more complex set of scripts, including ensuring that I am correctly building a map of the new values I would use for a key. It’s much easier to have a surrogate key that doesn’t change and just redact the other information.

I’m sure there are arguments both ways, but as we move towards the era of not only seeing data as valuable, but also as an asset we can’t completely control, I think surrogate keys make more sense now than ever. Let me know if you agree.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.2MB) podcast or subscribe to the feed at iTunes and Libsyn.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged . Bookmark the permalink.

3 Responses to Give Up on Natural Primary Keys

  1. Peter Kral says:

    Ami Levin has a persuasive presentation called “Where Are My (Primary) Keys?” that examines the pros and cons of natural keys. It left me considering natural keys as PK’s for the first time. This was before the days of GDPR, and though his arguments are strictly from a data design perspective, I wonder how he would factor in that real-world constraint now.


  2. way0utwest says:

    I wonder as well. I do think natural keys make sense at times, but more and more I find that businesses don’t really have as many rules as they think and the ability to clearly define what is unique in a field or two is very hard. Business is too messy. Add in to that the fact that businesses change their models quickly, and we don’t get the chance to deeply model entities. I’ll have to try and get to watch Ami’s talk.


  3. Pingback: Natural Keys? – Curated SQL

Comments are closed.