1

My question is more or less the opposite of this one: Why would one ever want to bother finding a natural primary key in a relation when using a sequence as a surrogate seems so much easier.

BradC mentioned in his answer to a related question that the criteria for choosing a primary key are uniqueness, irreductibility, simplicity, stability and familiarity. It looks to me like using a sequence sacrifices the last criterion in order to provide an optimal solution for the first four.

If I hold those criteria to be correct, I can reformulate my question as: In which circumstances would one ever consider it advantageous to complicate one's life by looking for a unique, irreductible, simple and stable key that is also familiar?

Community
  • 1
  • 1
scozy
  • 2,511
  • 17
  • 34
  • 1
    I'm pretty sure "familiar" is in order to do the exact opposite of what you are saying, i.e. to *not complicate one's life* – bengoesboom Oct 02 '13 at 16:25
  • The question appears to be founded on a very basic misconception that tables will only need one key and that any key will do. Wrong and Wrong! More sensible would be to ask "Given the fundamental importance of natural keys in database design, in what circumstances would one consider it advantageous to complicate one's life by adding a surrogate key?" – nvogel Oct 02 '13 at 20:50
  • 1
    Have a look at this [link](http://stackoverflow.com/tags/surrogate-key/info) for some more musings on the topic. – Branko Dimitrijevic Oct 03 '13 at 18:11

4 Answers4

5

To get a meaningful value from a lookup table without doing unnecessary joins.

Example case: garments references a lookup table of colors, which has an auto-increment primary key. Getting the name of the color requires a join:

SELECT c.color
FROM garments g
JOIN colors c USING (color_id);

Simpler example: the colors.color itself is the primary key of that table, and therefore it's the foreign key column in any table that references it. 

SELECT g.color
FROM garments g
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • That's an interesting example. Such a situation has never happened in practice for me, but I don't specialise in databases. Does it happen often in your professional experience? – scozy Oct 02 '13 at 17:24
  • Using lookup tables? Yes, all the time. Using natural keys in lookup tables? Yes, all the time. – Bill Karwin Oct 02 '13 at 17:25
4

The answer is data integrity. Instances of entities in the business domain outside the database are by definition identifiable things. If you fail to give them external, real world identifiers in the database then that database stands little chance of modelling reality correctly.

A natural key[1] is what ensures facts in the database are identifiable with actual things in the reality you are trying to model. They are the means which users rely on when they act on and update the data in the database. The constraints that enforce those keys are an implementation of business rules. If your database is to model the business domain accurately then natural keys are not just desirable but essential. If you doubt that then you haven't done enough business analysis. Just ask your customers how they think their business would operate if they were left looking at screens full of duplicate data!

[1] I recommend calling them business keys or domain keys rather than natural keys. Those are far more appropriate and less overloaded terms even though they mean exactly the same thing.

nvogel
  • 24,981
  • 1
  • 44
  • 82
2

You generally need to identify what the unique key on the data is anyway, as you still need to be able to ensure that the data is not duplicated.

The strength of the synthetic key is that it allows the values of the unique natural key to be modifiable in future, with child records not needing to be updated.

So you're not really skipping the "identify the key" part of the design by using a synthetic primary key, you're just insulating yourself from the possibility of the values changing.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • That sounds like a good point. So in a perfect world, we'd have both a primary key and the possibility of applying a `unique` constraint of a _set_ of attributes? – scozy Oct 02 '13 at 17:31
  • @scozy In the case of keys from domains not under your jurisdiction (such as SSNs) it is even hard (and thus _silly_) to enforce a UNIQUE constraint on them. Logically they are unique, and in a perfect world they _should be_ unique. But in real life they are not, because the data-sources are not perfect. This problem gets worse when multiple (timestamped) versions of the almost-candidate keys could co-exist. (for more information: google for "data vault" ) – wildplasser Oct 02 '13 at 18:44
  • If you do opt for the surrogate key, you will still have to set a UNIQUE key for the inherent data constraint or you will have junk in your table. So why use a surrogate key in the first place? If you really need to open up the possibility to change the data, you can add a auto-increment key afterwards any time. – DanMan Oct 02 '13 at 19:51
  • @DanMan adding that key can mean changing the definition of multiple child tables and the application, so I'd always err towards creating a synthetic key from the start. It's future-proofing. – David Aldridge Oct 02 '13 at 21:50
0

Below are the benefits of using a natural primary key:

  • In case you need to have a unique constraint on any column then making it primary key will fulfill the need for that,if you aren't suppose to receive any null value into that.So, anyways it's saving your cost of 1 extra key.

  • In some RDBMS, the key you are declaring as primary key is automatically creating a btree index on that column and if you make a natural primary key based on your access pattern then it is like Icing on the cake because now you are making two shots with one stone. Saving cost of an extra index and making your queries faster by having that meaningful primary key in where clause.

  • Last but not least ,you will be able to save space of one extra column/key/index.

desertnaut
  • 57,590
  • 26
  • 140
  • 166
Channa
  • 742
  • 17
  • 28