Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Auto-generated primary key versus user defined primary key

    i am designing a database using sql server 2008 r2. In around 90% of the tables i have added an identifier column that will contain an auto-generated number that can uniquely indentify the table columns.
    But in some table i will have a unique value that the users can enter which can also uniquely indentify the table columns , for example i have a table named Document_Status which have Description column such as (new, draft, published, canceled,etc); now in the Document_status table no two columns can have the same Description values.
    so should I in this case set the Description column as the primary key and there will be no need to set an auto-generated ID field in the Document_status table.
    BR

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oracle_user_bi View Post
    so should I in this case set the Description column as the primary key and there will be no need to set an auto-generated ID field in the Document_status table.
    that's what i would do

    you're gonna hear the opposing opinion in a second

    then you'll have to make the decision yourself

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

  3. #3
    Join Date
    Feb 2012
    Posts
    76
    See this:
    Primary Key Characteristics

    It may help if you show your tables. What do you mean with "no two columns can have the same Description values" - perhaps it should be "no two rows"? But if that's the case, it sounds unlikely - can there only be one new document, one in draft, one cancelled, one published, etc., for all time?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by r937 View Post
    that's what i would do

    you're gonna hear the opposing opinion in a second

    then you'll have to make the decision yourself

    That's my cue.

    There are lots of reasons for adding a surrogate key. Narrower and faster indexes, consistency across the database, etc.

    I put two keys on all my tables: A surrogate key (int, bigint, or unqiueidentifier) and a natural key (enforced as unique).

    This allows me to write scripts that will work across all of my tables.
    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
  •