Primary Key design principles

Http://www.chenjiliang.com/Article/View.aspx? ArticleID = 6803

Maybe everyone has designed databases and tables and defined primary keys. Today I want to explain how to correctly design a primary key. In some previous documents, the primary key design principle is not mentioned. I have summarized the following:

1. Do you want to use guid as the primary key?

Using guid as the primary key has its advantages and disadvantages. the advantage is that GUID is unique and can generate globally unique values under any circumstances. this is the biggest advantage of guid and facilitates data import. For example, if you want to import data from another system, you don't have to worry about it. During the import, primary key conflicts will occur. the GUID value is too complex. it is hard to remember, because sometimes we will use the record method for record judgment. and the data is too long, which affects the database efficiency. the generation of guids is not in a certain order. For a database physically ordered by the primary key, if a record is inserted at the front of the record, it may cause the number of data records to be removed after the nth power. this results in data insertion efficiency. therefore, use guid with caution.

2. Do you want to use auto-incrementing mode?

The primary key mentioned earlier requires uniqueness, so we use auto increment. this method is very undesirable. it may be to facilitate the insertion of records without having to manually create primary key values. I thought this would be convenient, but not actually. the trouble is far better than the so-called "convenience ". first, it is inconvenient to import data. Data is often imported from another system. The auto-incrementing primary key does not allow the IDs in the original table to be imported. this will cause the loss of the primary key. second: For tables with primary foreign keys such as orders, if the "primary table" primary key of the Order is automatically generated. when saving an order, you need to store transactions in the same table and table. At this time, you must first generate an order and then retrieve the primary key automatically generated by this order, save the details as a foreign key of the detail table. this process will become complex and unfeasible. how to handle the transaction. after an order is inserted to the primary table, if an error occurs while saving the details, the records of the primary table must be deleted. annoying. after successful insertion, the maximum value is also taken out. this is a serious waste. if there are too many records, the speed will be affected and there will be parallel inserts. the obtained records may be incorrect. therefore, do not use auto-incrementing mode for the above serious problems.

3. Whether int type is used as the primary key

In the past, we used the int type. All primary keys are numbers. in fact, we also understand. it is not just a number, but a number. for example, phone number. therefore, the advantage of using the int type for primary keys is that the speed is fast, and the insertion and query operations may be faster than other methods. however, my quick results may not be much obvious. For example, in varchar (15), data sorted by the physical primary key is automatically sorted by the primary key. therefore, even the data in bytes type is inserted to the corresponding physical location during insertion, that is, the insertion may affect the speed. however, in future queries, the speed impact will not be too obvious. what I want to say is that the int type is not used as the primary key. It does not mean that data is not stored in it. I suggest you store numbers in the primary key. Such sorting is faster than sorting with mixed letters. The reason for adopting the character type is to prepare for future data import. One day, when importing data from other tables, you can add a specific letter to the primary key of the imported data to avoid conflicts with the original primary key. for example, add an "N" letter before the primary key of the imported data. this eliminates the need to worry about whether the primary key of the imported data table is numeric or numeric.

4. whether to use numbers to define the primary key

This is a common issue. the primary key design principle is that the primary key should not have any practical significance. this article is actually very important. Some people think that the number itself is unique and can be used as the primary key, but it may cause troubles in the future. because fields with actual meanings still have the possibility of being modified. the biggest taboo for a primary key is modifying the primary key, which may lead to very serious unmeasurable consequences. for example, the student ID is never modified, but it may still exist.

Another type is unique on the surface, but should actually allow repetition. for example, the order number should be unique. yes. however, in this case, an original order is required to be voided for some reason. the order status is marked as "cancel ". then, you can enter orders of the same number again. therefore. in this case, although there is only one valid order number, duplicate numbers are allowed in the database. therefore, we recommend that you create a primary key with no significance for the table, such as ID.

So, to sum up, I will adopt the primary key type in the design. auto increment is not used. When a record is added, the system generates a primary key value. generally, it is stored in full numbers. As for the rules for generating primary key values, rules can be defined as needed. if there are no special requirements, you can define a field to store a value to keep it unique. automatically add one at the time of production. and save it back. this is faster than finding the maximum value from a table.