locked
Primary key which includes all columns RRS feed

  • Question

  • Hi, 

    Have a table with 7 columns, the last one a datetime.  In order to add a unique index to this table I would have to include all columns as this is what makes up the uniqueness.  So, is there any point adding a primary key which covers all columns or should I just add an identity column and make that the primary key?

    The datatypes are all mostly integers and a varchar(11). 

    Monday, October 13, 2014 3:02 PM

Answers

  • Add an Auto Incremented Id column
    Monday, October 13, 2014 3:06 PM
  • It is bad idea to create a PK with all the columns, it will affect the performance of the code, increase storage etc..

    instead, add an Identity column and make that as pk

    create unique constraint and add all you 7 columns

    vt 


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, October 13, 2014 3:14 PM
  • Yes , adding an IDENTITY is general and almost in all situation a good choice... What if some of the columns that make uniqueness are VARCHAR(n) or CHAR(n). It could be pretty costly to put all of them as PK..

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, October 13, 2014 3:15 PM
  • If you have related tables that reference this table's primary key, I would opt for the surrogate key plus a unique constraint to ensure uniqueness of the natural key.  Otherwise, the composite PK may be a better choice in order to avoid creating the second index.  The table will likely be queried using the natural key anyway.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, October 13, 2014 3:16 PM
    Answerer

All replies

  • Add an Auto Incremented Id column
    Monday, October 13, 2014 3:06 PM
  • Are these columns are natural keys. Does it requires to identified independently? if so, yes. If not, you can think of identity column.

    --Prashanth

    Monday, October 13, 2014 3:11 PM
  • It is bad idea to create a PK with all the columns, it will affect the performance of the code, increase storage etc..

    instead, add an Identity column and make that as pk

    create unique constraint and add all you 7 columns

    vt 


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, October 13, 2014 3:14 PM
  • Yes , adding an IDENTITY is general and almost in all situation a good choice... What if some of the columns that make uniqueness are VARCHAR(n) or CHAR(n). It could be pretty costly to put all of them as PK..

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, October 13, 2014 3:15 PM
  • If you have related tables that reference this table's primary key, I would opt for the surrogate key plus a unique constraint to ensure uniqueness of the natural key.  Otherwise, the composite PK may be a better choice in order to avoid creating the second index.  The table will likely be queried using the natural key anyway.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, October 13, 2014 3:16 PM
    Answerer
  • Thanks for all the confirmations regarding identity. 

    Monday, October 13, 2014 3:59 PM