Foreign keys - link using surrogate or natural key?

Question

Is there a best practice for whether a foreign key between tables should link to a natural key or a surrogate key? The only discussion I've really found (unless my google-fu is lacking) is Jack Douglas' answer in this question, and his reasoning seems sound to me. I'm aware of the discussion beyond that that rules change, but this would be something that would need to be considered in any situation.

The main reason for asking is that I have a legacy application that makes uses of FKs with natural keys, but there is a strong push from devlopers to move to an OR/M (NHibernate in our case), and a fork has already produced some breaking changes, so I'm looking to either push them back on track using the natural key, or move the legacy app to use surrogate keys for the FK. My gut says to restore the original FK, but I'm honestly not sure if this is really the right path to follow.

The majority of our tables already have both a surrogate and natural key already defined (though unique constraint and PK) so having to add extra columns is a non-issue for us in this insance. We're using SQL Server 2008, but I'd hope this is generic enough for any DB.

Answer 1

Neither SQL nor the relational model are disturbed by foreign keys that reference a natural key. In fact, referencing natural keys often dramatically improves performance. You'd be surprised how often the information you need is completely contained in a natural key; referencing that key trades a join for a wider table (and consequently reduces the number of rows you can store in one page).

By definition, the information you need is always completely contained in the natural key of every "lookup" table. (The term lookup table is informal. In the relational model, all tables are just tables. A table of US postal codes might have rows that look like this: {AK, Alaska}, {AL, Alabama}, {AZ, Arizona}, etc. Most people would call that a lookup table.)

On big systems, it's not unusual to find tables that have more than one candidate key. It's also not unusual for tables that serve one part of the enterprise to reference one candidate key, and tables that serve another part of the enterprise to reference a different candidate key. This is one of the strengths of the relational model, and it's a part of the relational model that SQL supports pretty well.

You'll run into two problems when you reference natural keys in tables that also have a surrogate key.

First, you'll surprise people. Although I usually lobby strongly for the Principle of Least Surprise, this is one situation where I don't mind surprising people. When the problem is that developers are surprised by the logical use of foreign keys, the solution is education, not redesign.

Second, ORMs aren't generally designed around the relational model, and they sometimes embody assumptions that don't reflect best practice. (In fact, they often seem to be designed without ever having input from a database professional.) Requiring an ID number in every table is one of those assumptions. Another one is assuming that the ORM application "owns" the database. (So it's free to create, drop, and rename tables and columns.)

I have worked on a database system that served data to hundreds of application programs written in at least two dozen languages over a period of 30 years. That database belongs to the enterprise, not to an ORM.

A fork that introduces breaking changes should be a show-stopper.

I measured performance with both natural keys and surrogate keys at a company I used to work at. There's a tipping point at which surrogate keys begin to outperform natural keys. (Assuming no additional effort to keep natural key performance high, like partitioning, partial indexes, function-based indexes, extra tablespaces, using solid-state disks, etc.) By my estimates for that company, they'll reach that tipping point in about 2045. In the meantime, they get better performance with natural keys.

Other relevant answers: In Database Schema Confusing

Answer 2

The main reason I support surrogate keys is that natural keys are often subject to change and that means all related tables must be updated which can put quite a load on the server.

Further in the 30 years I have been using a variety of databases on many topics, the true natural key is often fairly rare. Things are supposedly unique (SSN) are not, things that are unique at a particular time can become non-unique later and some things like emails addresses and phone numbers may be unique, but they can be re-used for different people at a later date. Of course some things simply don't have a good unique identifier like names of people and corporations.

As to avoiding joins by using a natural key. Yes that can speed up the select statements that don't need the joins, but it will cause the places where you still need the joins to be slower as int joins are generally faster. It will also probably slow down inserts and deletes and will cause performance problems on updates when the key changes. Complex queries (which are slower anyway) will be even slower. So simple queries are faster but reporting and complex queries and many actions against the database can be slower. It is a balancing act, that may tip one way or the other depending on how your database is queried.

So there is not a one-size fits all answer. It depends on your database and how it will be queried and what type of information is stored in it. You may need to do some testing to find out what works best in your own environment.

Answer 3

Older question but still very relevant to data architecture. The debate to go surrogate or go natural is ongoing and of course it depends. Very good points made for both sides here and other questions similar. The natural composite key benefits answer is well articulated and relevant.

What of the (common) requirement to create parent/child lookup tables and data in dev environment i.e. code / codeCategory, data meant to follow the schema through deployments to other environments? Surrogate keys would require table specific remapping in the data (if using environment generated key values) while natural keyed data just gets copied as-is with referential integrity untouched. Additionally, any cross database referencing to a multi environment sdlc and the argument for surrogate keys adds considerable complexity for deployments. Developers don’t know or use surrogate keys in code so the resiliency to change is not always a given benefit to integer primary keys. We’re talking lookup tables here. Used by developers to constrain data input. Developers use joins with the natural alternate key in the where clause to join the tables. Select b.1 from a join b on a.id = b.id where a.name = “im_a_natural_key”

If the data in the table is environment owned, not application owned then surrogate keys make sense for protocol in the enterprise. but, if the data must move without fault across environments or be referenced by other databases then natural keys pose simplicity for automated deployment pipelines that should elicit consideration even against urban legend architecture protocol e.g. “every table should have an integer surrogate key”. For the purpose as detailed here the surrogate key becomes a question of how and why to mitigate the complexity just to uphold an accepted but not revisited protocol left behind by the original whoever.

Answer 4

If you don't know the answer, go with the Surrogate. Here's why - if assumptions are made about business rules, and those assumptions are false or the rules change, your data is garbage. Here is an example:

Person, Role, PersonRole

current business rule states that a Person has one Role. You make a table that links Person and Role where PersonRole (PersonName, PersonBirthDate, PersonMotherMaidenName, ..., RoleCode)

Now you are a true purist when it comes to Natural Keys! But seriously, what if the org decides that a person can now assume multiple roles? What are the downstream effects of supporting the change in business needs?