none
Auto-generated primary key versus user defined primary key

    Question

  • i am designing a database using sql server 2008 r2. In around 90% of the tables i have added an identifier column (i set the Identity specification to Yes) that will contain an auto-generated number that can uniquely indentify the table rows.

    But in some tables i have a unique value that the users can enter which can also uniquely indentify the table rows without that need to have an auto-generated ID column. for example i have a table named Document_Status which have a Description column that include values such as (new, draft, published, canceled,etc); now in the Document_status table no two rows  can have the same Description value.

    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.? OR it is always better to have an auto-generated column as the primary key?

    BR

    Wednesday, March 28, 2012 2:55 PM

Answers

  • I mean to say that in case of deletion of records only. I gave you example in there. You may not see delete record numbers at some time if you/someone from your group/admin may raise a question for those deleted or missing records. You should have tracking history/information on such deleted/missing records.

    Another situation would be, out of records inserted 1, 2,3 ,4, 5 as on 1/1/2012

    1. user1 has deleted record 3 and 4

    2. user2 requests to insert records again with id 3 and 4 later say 1/10/2012.

    How would you differentiate records inserted on 1/1/2012 and 1/10/2012

    If you do not have such situation to occur in your application/business then there would not be any hurdle to use auto increment values.


    Thanks,
    Suresh
    Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, March 28, 2012 3:33 PM

All replies

  • Hi,

    It usualy depends as per business need, here are few points that can be considered,

    1) Do you have certain format for the key(id) value, example: EMP001, EMP002 or HR001, ADMIN001 where HR & ADMIN define departments. Then you have to have your own sequence/id generation logic and have to validate for duplicates before inserts.

    2) If records are not deleted at all from the table then you can use auto generation key. Otherwise, it may be difficult to manage the gaps between records.

    Example: Table has records 1, 2, 3, 4, 5 and later some time record 2 and 3 were deleted from the table. Now you will have only 1,4 and 5. you cannot really know why the gap between records. You must have these tracking info.

    Hope this helps.


    Thanks,
    Suresh
    Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Manish_BI Wednesday, March 28, 2012 3:11 PM
    Wednesday, March 28, 2012 3:10 PM
  • Otherwise, it may be difficult to manage the gaps between records.
    but what will be the problem if i have gaps between the ID values - this should be normal - ? let say that the last ID generated is 100 then this should not mean that i should already have 99 records....
    Wednesday, March 28, 2012 3:20 PM
  • I mean to say that in case of deletion of records only. I gave you example in there. You may not see delete record numbers at some time if you/someone from your group/admin may raise a question for those deleted or missing records. You should have tracking history/information on such deleted/missing records.

    Another situation would be, out of records inserted 1, 2,3 ,4, 5 as on 1/1/2012

    1. user1 has deleted record 3 and 4

    2. user2 requests to insert records again with id 3 and 4 later say 1/10/2012.

    How would you differentiate records inserted on 1/1/2012 and 1/10/2012

    If you do not have such situation to occur in your application/business then there would not be any hurdle to use auto increment values.


    Thanks,
    Suresh
    Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, March 28, 2012 3:33 PM
  • A PRIMARY KEY is a datum or data that uniquely identifies the record. Ideally, the "natural" key should be used, that is, data that is inherently unique. If that cannot be found, the uniqueness is defined by the existence of the record itself, in which case a generated Id would have to be used.

    With that said, the question of whether this or that COLUMN should be the PK is: does this COLUMN inherently define the record as being unique?



    Wednesday, March 28, 2012 4:49 PM
    Answerer