Should database primary keys be integer?

Question

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

I'm interested just in case if I want my primary key to be varchar in the future.

Answer 1

You can use varchar , and make sure each one is unique . This, however, is not ideal (see the article link below for more information).

What you are looking for is called a natural key , but the auto-incremented primary key and the RDBMS being processed are called a surrogate key , which is the preferred way. Therefore you need to have an integer .

More details:

• Surrogate keys or natural keys for the primary key?
• Why do I prefer surrogate keys over natural keys in database design

Answer 2

Why integers make good primary keys

Most often it is easier to use an integer for indexing compared to a string or compound key, since it is well suited for processing results (conceptually or in practice) as an array. Depending on the database implementation, integers can also be faster for access, sorting, or comparison, and an integer type usually offers additional functions, such as auto-increment, that are not available for other data types. How could you make an auto-increment of a composite key, for example?

MySQL has to say about the primary key:

The primary key for the table is the column or set of columns that you use in your most important queries. It has a linked index for fast query performance. Request performance depends on NOT NULL optimization, as it cannot contain NULL values.

SQL allows you to use any non-zero, unique column (or set of columns) 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 expectations

Although this is not a strict requirement, some frameworks optimize for whole primary keys. For example, Ruby on Rails simplifies the use of a primary key with automatic addition by default; you must consciously work against the convention if you want to use something else.

This does not mean that integers should or should not be used as primary keys. It just means that the choice of the primary key depends partly on your underlying database system and the queries you expect from it, and partly on the applications you expect to use to retrieve the data. All of these things should be considered when considering potential keys.

Answer 3

The primary key is unique. It is easy to satisfy this condition with automatic increment. If you make it char, you need to create a way to make it unique when adding data.

Answer 4

No, the primary key does not have to be an integer; it is just very common. As an example, we have a user ID that can have leading zeros and therefore should be stored in the varchar field. This field is used as the primary key in our Employee table.

Answer 5

For a primary key that is an Integer, it is easier to manage and makes its index more efficient. As you know, while keys are automatically indexed, indexes are stored as a binary tree, which is best for integers when moving. There is no restriction that the key be int, you can also declare it varchar.

Answer 6

Basically, a primary key must fulfill only 2 conditions: it must be a non-null column, and it must be unique. Any column of typeof that complies with these 2 conditions can be set as primary keys. If the primary key is several columns, then both columns must not be empty.

Although you can theoretically use other fields as primary keys, integers are easiest to manage and also be the fastest indexes.