What are the pros and cons for choosing a character varying data type for primary key in SQL?

sql foreign-keys primary-key postgresql-9.1 sqldatatypes
artaxerxe picture artaxerxe · Mar 18, 2013 · Viewed 24.8k times · Source

In the databases course that I did during my education (approx. 4 years ago), I thought that it is recommended avoiding the use of character strings as primary key's data type.

Can someone tell me what are the pros and cons for choosing a character varying data type for primary key in SQL and how much the above premise is true?

N.B.: (I'm using PostgreSQL database). I'm also dealing with a situation when you need to reference such a table from another, thus putting foreign key on character varying data type. Please take in account that also.

Answer

DF_ picture DF_ · Mar 18, 2013

The advantages you have for choosing a character datatype as a primary key field is that you may choose what data it can show. As an example, you could have the email address as the key field for a users table. The eliminates the need for an additional column. Another advantage is if you have a common data table that holds indexes of multiple other tables (think a NOTES table with an external reference to FINANCE, CONTACT, and ADMIN tables), you can easily know what table this came from (e.g. your FINANCE table has an index of F00001, CONTACT table has an index of C00001, etc). I'm afraid the disadvantages are going to be greater larger in this reply as I'm against such an approach.

The disadvantages are as follows:

  1. The serial datatype exists for exactly this reason in PostgreSQL
  2. Numeric indexes will be entered in order and minimal reindexing will need to be done (i.e. if you have a table with keys Apple, Carrot and want to insert Banana, the table will have to move around the indexes so that Banana is inserted in the middle. You will rarely insert data in the middle of an index if the index is numeric).
  3. Numeric indexes unlinked from data are not going to change.
  4. Numeric indexes are shorter and their length can be fixed (4 bytes vs whatever you pick as your varchar length).

In your case you can still put a foreign key on a numeric index, so I'm not sure why you would want to force it to be a varchar type. Searching and filtering on a numeric field is theoretically faster than a text field as the server will be forced to convert the data first. Generally speaking, you would have a numeric primary key that is non-clustered, and then create a clustered key on your data column that you are going to filter a lot.

Those are general standards when writing SQL, but when it comes to benchmarking, you will only find that varchar columns are a little slower on joining and filtering than integer columns. As long as your primary keys are not changing EVER then you're alright.

Related questions

Foreign key referring to primary keys across multiple tables?

I have to two tables namely employees_ce and employees_sn under the database employees. They both have their respective unique primary key columns. I have another table called deductions, whose foreign key column I want to reference to primary …

Read More
How to add composite primary key to table

create table d(id numeric(1), code varchar(2)) After I create the above table how can I add a composite primary key on both fields and also a foreign key?

Read More
Can a foreign key refer to a primary key in the same table?

I just think that the answer is false because the foreign key doesn't have uniqueness property. But some people said that it can be in case of self joining the table. I am new to SQL. If its true please …

Read More