Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jan 2009
    Posts
    10

    Unanswered: Primary Key Best Practice

    I was wondering if anyone would comment on the following:

    Which strategy would you prefer for your primary keys, and why?

    Auto-incrementing int
    1.) Let the db come up with the next number for the table row on insert.
    1,2,3,4,5,6,7....and so on.

    or

    "Enterprise Key"
    1.) Come up with unique prefixes like PROD or ITEM and map that for a table in a lookup table, plus the number of digits it could potentially have.

    PROD = Products.dbo.tProduct could potentially have 00000000

    2.) Put an identity column + insert trigger on every table in the db.

    3.) Have the trigger check the lookup table for the table you are inserting into, get the text prefix, append the number of 00000s followed by the identity next identity from the table.

    Example: PROD000001, PROD000002, PROD000003

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    between those two choices, i like the auto-incrementing number

    that other stuff looks far too complex, to say nothing of fragile
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    10
    I prefer the auto-incrementing number as well. I understand the PK is only unique in that table and not the database or enterprise as a whole. However, I have heard people saying you should use an EID or GUID so that the PK would be unique across the enterprise. Sure, that would be cool, but it isn't natively supported by sql server, and I don't really see any benefits strong enough to justify the continual overheard of setup and maintenance this would add to each table?

    (EID supporter -> http://www.mindfuldata.com/Modeling/Keys.html) specifically chapter 8.

    In your experience have you found a need for something like this?

    Additionally, without the limitation of the question framed between the two choice above, is there a third option you would recommend as best practice?

    Thanks for any help on this.
    Last edited by devman42; 01-10-09 at 11:07.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this should always be the first option considered: a natural key

    do a search for "natural vs surrogate"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2009
    Posts
    10

    Natural vs Surrogate

    That is a good point, I should have been more specific.

    I am a strong believer in unintelligent keys (or surrogate) vs natural or (intelligent keys). A natural key would be something like a last name, vs a surrogate key like a customer id correct? Unless the key is some kind of ISO standard (country code, etc) I will always use a surrogate key.

    So my question should be framed in that context, PK generation strategy for surrogate keys, as you wouldn't need one for a natural key.
    Last edited by devman42; 01-10-09 at 12:06.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so you just answered your own question about your best practice, didn't you

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2009
    Posts
    10

    Surrogate PK Generation

    My question was regarding surrogate PK generation strategy best practices, not when to use a natural vs a surrogate key.

    Some people say let the db auto-increment an int, some say manually implement a GUID or EID strategy.
    I personally haven't found a good reason to make it any more complicated than allowing sql server to natively support this with an auto-incrementing int.

    Just wondering what you found in your experience.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm having a lot of trouble keeping up with this thread if you keep going back and editing your posts after i've replied to them

    good luck with your surrogate keys, i'm sure you'll be fine no matter which way you assign them

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2009
    Posts
    10

    Surrogate

    Ah ok, thank you for your time.

    Just out of curiosity if you don't mind me asking, what strategy do you personally use when making surrogate pks?
    "Premature optimization is the root of all evil."

  10. #10
    Join Date
    Jan 2009
    Posts
    10
    I am hoping others will comment on this as well. There doesn't seem to be a widely accepted standard for this in relational theory?
    "Premature optimization is the root of all evil."

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by devman42
    what strategy do you personally use when making surrogate pks?
    i'll use an auto-incrementing number when (1) no natural key exists, or (2) in some cases, a multi-column compound primary key has foreign keys referencing it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2009
    Posts
    10

    Pk

    I agree completely. Thank you for your response.
    "Premature optimization is the root of all evil."

  13. #13
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by devman42
    I am hoping others will comment on this as well. There doesn't seem to be a widely accepted standard for this in relational theory?
    Standard for what? Whether or not to use integers or alphanumerics in your keys? Why would it make any difference in relational theory? A key is a key is a key.

    I suggest you use whatever makes sense for your business requirements. Without a clear statement of the UoD and how the keys are being used I don't think it's helpful to make sweeping generalisations about one type of identifier being "better" than another.

  14. #14
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    IDENTITY columns in SQL Server have certain disadvantages:

    - They can't be updated
    - They are not necessarily unique (typically a constraint is used to ensure uniqueness but the constraint is optional)
    - The values assigned to inserted rows can't be reliably predicted
    - The sequence of values may have gaps
    - The sequence of values is not necessarily in chronological order of insertion
    - The values of multi-row inserts cannot always be retrieved unless there is another alternate key in the table
    - IDENTITY has several potential problems for replicated and distributed systems

    These are all reasons why in some circumstances you might want to use your own key generator in preference to IDENTITY. Given these complexities and the occasionally absurd syntax associated with IDENTITY, I tend to avoid using IDENTITY where I can.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by dportas
    - They can't be updated
    This is a good thing.
    Quote Originally Posted by dportas
    - They are not necessarily unique
    I'd like to see the syntax for creating a non-unique identity column...
    Quote Originally Posted by dportas
    - The values assigned to inserted rows can't be reliably predicted
    Irrelevant for surrogate keys.
    Quote Originally Posted by dportas
    - The sequence of values may have gaps
    Also irrelevant.
    Quote Originally Posted by dportas
    - The sequence of values is not necessarily in chronological order of insertion
    I'd dearly like to see some code illustrating this as well.
    Quote Originally Posted by dportas
    - The values of multi-row inserts cannot always be retrieved unless there is another alternate key in the table
    True, but your table should have natural keys as well. Contrary to Rudy's opinion, the non-existence of a natural key is not the reason for using a surrogate. Instead, its an indication of a design flaw.
    Quote Originally Posted by dportas
    - IDENTITY has several potential problems for replicated and distributed systems
    True, but so do other surrogate keys, with the exception of GUIDs.

    Using your own key generator is more than a little foolish these days.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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