Search code examples
sqlperformanceindexing

SQL primary key: integer vs varchar

The team I'm working with decided to create a table with a varchar primary key. This table is referenced by another table on this primary key.

I've the habit to create an integer primary key, following what I learnt at university. I've read that there is a performance boost using integer primary key.

The matter is that I don't know any other reason for creating an integer primary key. Do you have any tips?

Solution

  • The primary key is supposed to represent the identity for the row and should not change over time.

    I assume that the varchar is some sort of natural key - such as the name of the entity, an email address, or a serial number. If you use a natural key then it can sometimes happen that the key needs to change because for example:

    • The data was incorrectly entered and needs to be fixed.
    • The user changes their name or email address.
    • The management suddenly decide that all customer reference numbers must be changed to another format for reasons that seem completely illogical to you, but they insist on making the change even after you explain the problems it will cause you.
    • Maybe even a country or state decides to change the spelling of its name - very unlikely, but not impossible.

    By using a surrogate key you avoid problems caused by having to change primary keys.