Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 5 down vote accepted

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.

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.

Other relevant answers: In Database Schema Confusing

share|improve this answer
    
This is what I was thinking - the argument I've been given against bringing things back to the original FK is that in some cases the PK (and thus the FK) was a composite key, and was getting pushback telling me that a composite FK wouldn't work ... I've since researched and it can work, but it's more a pain in the arse to work with. To me an FK that points to the natural key somehow seems, well, more natural. The breaking change is not insurmountable, but it's more a case of whether I run with it or not. It's entirely possible that it's an "it depends" and there is no "right" way per se. –  Chris J Apr 2 '13 at 14:44
    
Natural vs Surrogate is a highly religious war with both sides having pros and cons both in usability and performance. The fact that it is still raging on years after SQL has been well established goes to show that a working solution can use either. :) –  JamesRyan Apr 2 '13 at 15:08
1  
@JamesRyan: It's not a religious issue for me. 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. –  Mike Sherrill 'Cat Recall' Apr 2 '13 at 17:00
    
While I don't entirely agree about the natural keys, I agree totally about your criticisms of ORMS! –  HLGEM Apr 2 '13 at 17:54
add comment

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.

share|improve this answer
1  
"…natural keys are often subject to change…" - then they are not very good keys! If an attribute is changing often then don't use it as a key (for various definitions of "often", of course). Fabian Pascal argued that there are four criteria for choosing a key: familiarity, irreducibility, stability and simplicity. Sometimes you trade off these for the simplicity of a surrogate key. As HLGEM put it, "So there is not a one-size fits all answer." –  Greenstone Walker Apr 2 '13 at 23:13
1  
@GreenstoneWalker, I would agree that you shouldn't coose it as a key then, but often you don't have a key that fits all four criteria and you have to go with what is unique. And when uniqueness is a copmposite key, then the problem can be even greater in terms of performance when you must have the joins. –  HLGEM Apr 3 '13 at 13:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.