Setting the primary key in a database table

There are 3 ways to set the primary key:

One is to set the self-growth primary key, the second is to use the business primary key, and the third is to generate a unique sequence (using UUID/GUID);
How to choose the primary key setting method:

1. Purpose of PRIMARY key use:

A. To ensure that data is found only;

B. Improve storage efficiency and indexing efficiency.

2. Use the scene with the self-increment primary key:

A: The table cannot find 3 business primary keys that can be represented by a field within
B: Not a large number of insert operations are concentrated
C: Do not use self-increment ID as foreign key (that is, id change does not affect system logic and function)
In addition, the Law of self-increment primary key is obvious, in order not to let others easily collect data, at the same time meet the above conditions, the preference is to choose self-increment primary key;

3. Use the business primary key to do the ID:

A. If you design a table on a non-repeating data column, and often in this column to retrieve data, or the column also associated with other table foreign keys, such as school number, membership card number, ID number these, can be used as a primary key, the primary key in the default setting is the clustered index, so the retrieval number, card number these efficiency will be higher

4. Use the Generate unique sequence
A. If the use of self-increasing requirements is not met, it is recommended that the word Fu be preferred. Similar to the Web site database, you can use the UUID, GUID, such as the rules of irregular characters key. Because the character Fu query speed is not slower than the self-increment primary key.

B. Consider the drawbacks of self-increasing primary keys. Many table IDs will be referenced by other tables as foreign keys, and the disadvantage of the self-increment primary key is that after deleting with delete, it will not be added from the new, but then the ID increment before deletion, in the multi-database migration, the self-increment will be very cumbersome, the need for additional joint primary key to ensure the uniqueness of the data.

Setting the primary key in a database table