Results 1 to 4 of 4

Thread: PK vs. Identity

  1. #1
    Join Date
    Mar 2011

    Question PK vs. Identity

    As a software developer, I have a solid understanding of relational databases and normalization and have designed many. But what is the best or most pragmatic answer to the whole debate of 'when to use the real candidate key, when to use an identity PK', and the byproduct concerns around joining on keys that are strings (varchar) as opposed to an identity?

  2. #2
    Join Date
    Sep 2003
    NE Florida w/ view of co-workers
    I found a writeup on this exact question: Choosing a Primary Key: Natural or Surrogate?

    About half way down it sums up the tradeoff
    The advantage of natural keys is that they exist already, you don’t need to introduce a new “unnatural” value to your data schema. However, the disadvantage of natural keys is that because they have business meaning they are effectively coupled to your business: you may need to rework your key when your business requirements change.
    Personally I have been burned more than once by choosing a natural key only to have the business change later on. My rule of thumb is to go with surrogates unless it can be guaranteed that the natural will not change definition.

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    an even more important rule of thumb is: never assign a surrogate key until you have also declared a UNIQUE key elsewhere in the same table | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Agree with that.
    I use surrogate keys on all my tables, for consistency of design and coding. But I also declare a unique natural key.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts