none
Is the PrimaryKey as Int necessary? RRS feed

  • Question

  • I have a table called Country. Should I use a primary key CountryId which is an Integer field or the CountryCode which is a NCHAR(3) as primary? Does EF require the tables to have a primary key which is Integer?

    I have heard advises from DBAs asking any table to have a primary key which is any integer which will help the backup/restore process faster.

    What I'm trying to do in my example is I dont want two fields CountryId, CountryCode in the table like below:

    Table: Country

    • CountryId (Is it necessary?)
    • CountryCode
    • Name
    Monday, July 9, 2012 1:57 AM

Answers

  • Hi Salecrusade;

    If you will be doing CRUD operations on the database each table that will participate in the CRUD operations will need to have a primary key. The primary key need not be an integer but be unique.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, July 9, 2012 2:46 AM
  • On 7/8/2012 9:57 PM, salescrusade wrote:
    > I have a table called Country. Should I use a primary key *CountryId*
    > which is an Integer field or the *CountryCode* which is a NCHAR(3) as
    > primary? Does EF require the tables to have a primary key which is Integer?
    >
    > I have heard advises from DBAs asking any table to have a primary key
    > which is any integer which will help the backup/restore process faster.
     
    I never heard of that one, but would make your program access the data
    in the table since it would be indexed.
    >
    > What I'm trying to do in my example is I dont want two fields CountryId,
    > CountryCode in the table like below:
    >
    > Table: *Country*
    >
    >   * CountryId (Is it necessary?)
     
    Necessary an int value based on identity-auto-increment primary-key
    value assigned to the record.
     
    >   * CountryCode
     
    An assigned Country-Code code of the country the code represents.
     >   * Name
    >
     
     
    Monday, July 9, 2012 2:56 AM
  • Hi,

    I don't see any relation between the nature of a primary key and the time needed to backup/restore a db. It might be an allusion to integer indexes possibly quicker than string indexes at least if the string is long enough but unless you have really long keys, it's more likely a micro optimization (I even saw once pretending he preferred to store dates as explicit numeric values for this reason !).

    The main reason I see myself is rather that a primary key is supposed to never change. So IMO the real benefit could be that if you choose to change the country codes you are using (AFAIK even if using ISO codes they provides several standards and if user invented codes they might want to change sometimes these codes), the CountryId key will be just left unchanged and you'll have nothing special to do. Else you would have to change those codes accross all the db.

    What if you ask some clarification to your DBA ?


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Monday, July 9, 2012 8:50 AM

All replies

  • Hi Salecrusade;

    If you will be doing CRUD operations on the database each table that will participate in the CRUD operations will need to have a primary key. The primary key need not be an integer but be unique.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, July 9, 2012 2:46 AM
  • Basically, primary key is about uniqueness and identity for a record and also for relationships. And there is no resctriction on which datatype to be used for PrimaryKey in EF or anywhere else.

    The performance related matter you need to learn more on Indexing. http://msdn.microsoft.com/en-us/library/ms188783.aspx

    Hope this helps!

    Monday, July 9, 2012 2:48 AM
  • On 7/8/2012 9:57 PM, salescrusade wrote:
    > I have a table called Country. Should I use a primary key *CountryId*
    > which is an Integer field or the *CountryCode* which is a NCHAR(3) as
    > primary? Does EF require the tables to have a primary key which is Integer?
    >
    > I have heard advises from DBAs asking any table to have a primary key
    > which is any integer which will help the backup/restore process faster.
     
    I never heard of that one, but would make your program access the data
    in the table since it would be indexed.
    >
    > What I'm trying to do in my example is I dont want two fields CountryId,
    > CountryCode in the table like below:
    >
    > Table: *Country*
    >
    >   * CountryId (Is it necessary?)
     
    Necessary an int value based on identity-auto-increment primary-key
    value assigned to the record.
     
    >   * CountryCode
     
    An assigned Country-Code code of the country the code represents.
     >   * Name
    >
     
     
    Monday, July 9, 2012 2:56 AM
  • Hi,

    I don't see any relation between the nature of a primary key and the time needed to backup/restore a db. It might be an allusion to integer indexes possibly quicker than string indexes at least if the string is long enough but unless you have really long keys, it's more likely a micro optimization (I even saw once pretending he preferred to store dates as explicit numeric values for this reason !).

    The main reason I see myself is rather that a primary key is supposed to never change. So IMO the real benefit could be that if you choose to change the country codes you are using (AFAIK even if using ISO codes they provides several standards and if user invented codes they might want to change sometimes these codes), the CountryId key will be just left unchanged and you'll have nothing special to do. Else you would have to change those codes accross all the db.

    What if you ask some clarification to your DBA ?


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Monday, July 9, 2012 8:50 AM
  • Surely this table will be used for CRUD operations to enter a new Country. As Patrice said, if I allow the user to change the CountryCode once it's entered, then all other data once depended on that code would be wrong in a report. So, I'm not going to allow the user to change the CountryCode once it has been added and it has been used by the system.

    Monday, July 9, 2012 11:54 AM
  • On 7/9/2012 7:54 AM, salescrusade wrote:
    > Surely this table will be used for CRUD operations to enter a new
    > Country. As Patrice said, if I allow the user to change the
    > *CountryCode* once it's entered, then all other data once depended on
    > that code would be wrong in a report. So, I'm not going to allow the
    > user to change the *CountryCode* once it has been added and it has been
    > used by the system.
    >
     
    What does this have to do with a primary key question?
     
    Monday, July 9, 2012 5:58 PM
  • The question was about Primary Key as Int not just the primary key. Since EF does not require it, I have the freedom to use CountryId or CountryCode in which I choose CountryCode as the primary key. Hope that clarity.
    Monday, July 9, 2012 7:48 PM
  • On 7/9/2012 3:48 PM, salescrusade wrote:
    > The question was about Primary Key as Int not just the primary key.
    > Since *EF* does not require it, I have the freedom to use *CountryId* or
    > *CountryCode* in which I choose *CountryCode* as the primary key. Hope
    > that clarity.
     
    I would assume that CountryCode is the primary key based on a primary
    key index.
     
    It doesn't matter who is the primary key as long as the table has one,
    and indexing a table based on a primary-key or other indexes makes
    accessing the data faster.
     
    Monday, July 9, 2012 9:25 PM