0

If a table has two columns that act as a composite candidate key (uniquely identifying each row), is it a good practice to create a surrogate key and use it as the primary key instead?

The reason would be: foreign keys referencing it would be simpler to read (referencing one column instead of two).

Also, if a surrogate key is used, should I create a unique constraint on the two columns previously mentioned?

ehmicky
  • 1,915
  • 4
  • 20
  • 29
  • You mean there's only 1 `John Doe` in the United States? SS should be PK, period. Assuming traditional aspects, people can change names and it's possible two people have the same name--but no two people with share a SS (unless they did their Christmas shopping at Target...) – Brad Christie Jul 10 '14 at 15:39
  • 4
    I strongly disagree that SS should be the primary key. This is sensitive data and should NEVER be stored in plain text in any system. It should always be encrypted. Do yourself and your company a favor, encrypt that data immediately. – Sean Lange Jul 10 '14 at 15:41
  • I should have picked a better example sorry! I used first name + last name, but I meant any pair of attribute that together uniquely identify a person, and are immutable. – ehmicky Jul 10 '14 at 15:43
  • 3
    Couple things you should think about. Social security number is not unique. There are people that share the same social security number. If you don't believe me look it up and you'll find plenty of articles. I had a real world situation involving this scenario. – SQLChao Jul 10 '14 at 15:46
  • 1
    You might care to review [Do I need a primary key if I have an index?](http://stackoverflow.com/questions/12775641/do-i-need-a-primary-key-if-i-have-an-index/12776018#12776018) – Jonathan Leffler Jul 10 '14 at 15:47
  • I rephrased my question, as I understood what I actually wanted to ask thanks to your comments :) – ehmicky Jul 10 '14 at 15:56
  • 2
    A primary key should be unique, NOT NULL and stable. SSN can be wrong or absent , and: **it is not under your control**, its defintion/format could be changed at any time by the controlling agency. Since a PK can serve as a target for an FK from another table, you should not use anything that is not stable as a PK (besides: it would cause horrible cascading). Instead: use a surrogate as PK. – joop Jul 10 '14 at 16:04

1 Answers1

3

You have actually asked several questions:

  1. If there are multiple columns (or sets of columns) that are unique from the logical standpoint, should I make a key on all of them?
  2. When there are multiple keys, how do I decide which to make primary?
  3. If there is already a natural key (or keys), can I replace it (them) with a surrogate key?
  4. If there is already a natural key (or keys), should I add a surrogate key?

And here are the answers:

  1. Yes.
  2. From the logical standpoint, all keys are equivalent. However, there may be physical considerations in favor of making one of them primary over the others.
  3. No.
  4. It depends. More on that here.
Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • Thanks a lot, that answers my question. What I get from this: if I see a natural key, whether simple or complex, I will add a surrogate key (which seems to be a matter of taste). Then I will use it as a primary key and apply a unique constraint of the natural keys. Thanks! – ehmicky Jul 10 '14 at 16:18
  • Yes, that's one way to interpret it. In a more rare, but still realistic scenario, you could have just as easily made a natural primary key and a surrogate alternate key - for example if your wanted to [cluster](http://use-the-index-luke.com/sql/clustering/index-organized-clustered-index) on the natural key and your DBMS doesn't let you cluster on anything else but primary key. – Branko Dimitrijevic Jul 10 '14 at 16:23