locked
Can we use identity column as primary key RRS feed

  • Question

  • Hi,

    I have designed tables with having identity columns as primary key.

    Please let me know whether this is correct or not?

    With Regards,
    Anand
    Thursday, August 13, 2009 5:41 PM

Answers

  • Anand,
         This works ideally.  This assures that the primary key is unique and overcomes many of the problems that can occur by setting the primary key to an internalally derived value.  Consider an employee table that uses the employees internally created id, such as MAR10, or the tenth employee hired in the marketing department.  After time goes by it is decided to change the internal employee identifier, which is also used in the table as the primary key.  This would require updating not just the primary key, but also going through and assuring that all foreign keys are also updated.

         The use of the IDENTITY seed is perfect to assure that there is consistent referential integrity.

    Hope this helps
    David Dye
    • Proposed as answer by sqldbg Friday, August 14, 2009 1:29 AM
    • Marked as answer by Xiao-Min Tan – MSFT Friday, August 21, 2009 2:13 AM
    Thursday, August 13, 2009 5:52 PM

All replies

  • Anand,
         This works ideally.  This assures that the primary key is unique and overcomes many of the problems that can occur by setting the primary key to an internalally derived value.  Consider an employee table that uses the employees internally created id, such as MAR10, or the tenth employee hired in the marketing department.  After time goes by it is decided to change the internal employee identifier, which is also used in the table as the primary key.  This would require updating not just the primary key, but also going through and assuring that all foreign keys are also updated.

         The use of the IDENTITY seed is perfect to assure that there is consistent referential integrity.

    Hope this helps
    David Dye
    • Proposed as answer by sqldbg Friday, August 14, 2009 1:29 AM
    • Marked as answer by Xiao-Min Tan – MSFT Friday, August 21, 2009 2:13 AM
    Thursday, August 13, 2009 5:52 PM
  • I agree with David. I would say that IDENTITY columns are ideal for setting as PRIMARY keys.

    Beyond Relational
    Friday, August 14, 2009 9:04 AM