Thinking of database primary key design

The necessity of a primary key:

Some friends may not advocate that database tables must be primary keys, but in my thinking, feel that each table should have a primary key, whether it is Tanki or double primary key, the existence of the primary key represents the integrity of the table structure, the table records must have a unique field, the primary key is used for other tables of foreign Key Association, the record of modification and deletion , these operations can become cumbersome when we do not have a primary key.

The meaningless nature of a primary key:

I emphasize that the primary key should not have practical meaning, this may not be very agreeable to some friends, such as order form, there will be "Order Number" field, and this field in the actual business is itself should be unique, with a unique identity record of the function, but I do not recommend the order Number field as the primary key, Because of the fields that have a real meaning, the possibility of a "meaningful change", such as the order number at the beginning of the time we all smooth, and then the customer said "orders can be set aside, and rebuild the order, and order number to keep the original order number consistent", so that the original primary key is in jeopardy. Therefore, the actual field that has uniqueness also represents the primary key that can be used. Therefore, I recommend that you set up a new field dedicated to the primary key, this key itself is not reflected in the business logic, does not have practical significance. And this key in a certain program to increase the complexity, so depending on the size of the actual system, for small projects, after the expansion will not be very large, also check to allow the actual unique field as the primary key.

Selection of primary keys

We are now thinking about what should be used as a table of the primary key more reasonable, affirmed that the primary key design does not have a verdict, each person has the method, even if the same, in different projects, will also adopt different primary key design principles.

First: Number as primary key

This method uses the "number" of the unique field in the actual business as the primary key design, this is recommended in small projects because it makes the project simpler, but it can be a bit of a hassle to use, such as "numbering changes", which may involve many other related tables, as Uncle Lee is said. The consequences are serious "and, as mentioned above," Business requirements allow serial numbers to repeat, "we are so prophetic that we don't know what the business is going to change.

Number two: Automatic numbering of primary keys

This method is also used by many friends, is to create a new ID field, automatic growth, very convenient also satisfies the principle of the primary key, the advantage is: the database automatic numbering, fast, and is incremental growth, clustered primary key in order to store, for the search is very advantageous; digital, occupy space small, easy to sort, It is also convenient to pass through the program, if it is convenient to add records (such as manual entry, or to insert new records directly in the table, or to import the old system data) through a system that does not have to worry about primary key duplication.

Disadvantage: In fact, the disadvantage is also from its advantages, because of automatic growth, it is troublesome to insert the record of the specified ID manually, especially when the system is integrated with other systems, it is difficult to ensure that the ID of the original system does not conflict with the primary key (if the old system is also digital); If the other system primary key is not a number, then the problem is even greater, can result in modification of the primary key data type, which can also cause other related tables to be modified, and the consequences are also serious; even if other systems are digital, in order to differentiate between old and new data, you may want to unify an "o" before the old data primary key. To show that this is the old data, then the automatic growth of the digital model faces a challenge.

Third: Max plus one

Because of the problems with automatic numbering, so some friends use their own generation, the same number, but only to remove the automatic growth, in the INSERT, read Max value after adding one, this method can avoid the problem of automatic numbering, but there is an efficiency problem, if the record is very large, then Max ( can also affect efficiency; more serious is concurrency, and if you have two people reading the same max, then inserting the ID value will repeat, which is already a lesson.

Four: Homemade plus a

Considering the efficiency of Max plus one, someone uses homemade plus one, which is to build a special table, field: Table name, current sequence value. So when you insert a value into a table, you first find the maximum value of the table from this table, and then add one, to insert, one may find that there may also be concurrent processing, this concurrent processing, we can use the lock thread to avoid, in the generation of this value, first lock, after the value, and then unlock out, So there won't be two people generating it at the same time. It's going to be a lot faster than Max plus one. However, there is also a problem: when integrated with other systems, out of the system's generation method, it is troublesome to ensure that the maximum in the homemade table is consistent with the import, and the number of the above mentioned "O" the old data import problem. Therefore, the primary key can be set to the character type in the "Homemade plus one". Character type of self-made plus one I am quite recommended that the character of the primary key can handle a lot of our unexpected situation.

Five: GUID primary key

At present a good primary key is to use the GUID, of course I recommend the primary key or character type, but the value is generated by the GUID, the GUID can be generated automatically, can also be generated, and the key value can not be repeated, to solve system integration problems, the GUID values of several systems to lead together, will not happen again, Even if there are "O" old data can be differentiated, and efficient, in. NET can be directly used System.Guid.NewGuid () for generation, in SQL can also use NewID () generation.

The advantages are:

Compared to the IDENTITY column, the uniqueidentifier column can know the newly added row IDs in advance through the NewID () function, providing a great convenience for subsequent processing of the application.

Facilitate database porting, other databases do not necessarily have IDENTITY columns, and GUID columns can be converted to other databases as character columns, while the GUID values generated in the application are stored in the database, which does not affect the original data.

Easy to initialize the database, if the application to load some initial data, the IDENTITY column processing is more cumbersome, and the uniqueidentifier column without any processing, directly with T-SQL load.

It facilitates the permanent identification of certain objects or constants, such as the ClassID of classes, the identity of instances of objects, the contacts in UDDI, service interfaces, tmodel identity definitions, and so on.

Disadvantages are:

The GUID value is longer, is not easy to remember and input, and this value is random, no order.

The value of the GUID is 16 bytes and is relatively large compared to other integers such as 4 bytes. This means that if you use the uniqueidentifier key in your database, it can have two negative effects: increased storage space, slower indexing time.

I am not recommended GUID is the best, in fact, in different situations, we can use the above a certain way, thinking about some advantages and disadvantages, but also convenient for everyone in the design reference. These are just a little thought of me, and may be my knowledge limitations, there will be some misunderstanding in the inside, I hope you have any ideas to welcome the discussion.