Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Must database primary keys be integers?

I always see MySQL database primary keys as integers. Is that because primary keys must be integers, or because of ease of use when setting auto_increment on the column?

I am wondering just in case I want my primary key to be a varchar in the future.

like image 749
GusDeCooL Avatar asked Dec 27 '12 18:12

GusDeCooL


People also ask

Does primary key need to be integer?

The PRIMARY KEY is optional for ordinary tables but is required for WITHOUT ROWID tables. If a table has a single column primary key and the declared type of that column is "INTEGER" and the table is not a WITHOUT ROWID table, then the column is known as an INTEGER PRIMARY KEY.

Can a primary key be a string?

1 Answer. Yes, from a performance standpoint (i.e. inserting or querying or updating) using Strings for primary keys are slower than integers. But if it makes sense to use string for the primary key then you should probably use it.

What data type should primary keys be?

Integer (number) data types are the best choice for primary key, followed by fixed-length character data types. SQL Server processes number data type values faster than character data type values because it converts characters to ASCII equivalent values before processing, which is an extra step.

Does a primary key have to be an unsigned integer?

It doesn't matter if they are signed or unsigned. Obviously you will prefer to deal with positive numbers rather than negative and positive numbers. If you need more rows then you should change it to BIGINT .


2 Answers

You can use varchar as well as long as you make sure that each one is unique. This however isn't ideal (see article link below for more info).

What you are looking for is called natural key but a primary key with auto-increment and handled by the RDBMS is called surrogate key which is preferred way. Therefore you need to have it to be integer.

Learn more:

  • Surrogate Keys vs Natural Keys for Primary Key?
  • Why I prefer surrogate keys instead of natural keys in database design
like image 158
Sarfraz Avatar answered Sep 29 '22 19:09

Sarfraz


Why Integers Make Good Primary Keys

It's often easier to use an integer for indexing, in comparison to a string or composite key, because it lends itself well to treating results (conceptually or in practice) as an array. Depending on the database implementation, integers may also be faster to access, sort, or compare, and the integer type usually offers additional features like auto-incrementing that aren't available for other data types. How would you go about auto-incrementing a composite key, for example?

MySQL has this to say about the primary key:

The primary key for a table represents the column or set of columns that you use in your most vital queries. It has an associated index, for fast query performance. Query performance benefits from the NOT NULL optimization, because it cannot include any NULL values.

SQL allows any non-null, unique column (or set of columns) to be used as a primary key. However, if you don't care about auto-incrementing, you can usually make your primary key any index that is UNIQUE and NOT NULL.

Consider Your Application's Expectations

While not a hard requirement, some frameworks optimize for integer primary keys. For example, Ruby on Rails facilitates the use of an auto-incrementing primary key by default; you have to deliberately work against the convention if you want to use something different.

That doesn't mean one should or shouldn't use integers as primary keys. It just means the choice of primary key is driven in part by your underlying database system and the queries you expect to run against it, and in part by the applications you expect to use to retrieve the data. All of those things should be taken into consideration when considering candidate keys.

like image 29
Todd A. Jacobs Avatar answered Sep 29 '22 17:09

Todd A. Jacobs