Thinking about the design of database primary key (turn)

In our database design, can not escape the database table is the primary key, there may be many friends did not think deeply, the design of the primary key to the entire database design has a great impact, so we have to pay attention to.

The primary key is necessary:

Some friends may not advocate the database table must have the primary key, but in my thinking, I think 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, table records must have a unique field, the primary key is used for other tables of foreign Key association, this record modification and deletion , these operations can become cumbersome when we don't have a primary key.

The meaningless nature of the primary key:

I emphasize that the primary key should not have the actual meaning, this may be for some friends to disagree, such as the order form bar, there will be "Order Number" field, and this field in the business is itself should be unique, has a unique identity record function, but I do not recommend the order Number field as the primary key, Because of the actual field, there is the possibility of a "change of meaning", such as the order number at the beginning of our all smooth, and then the customer said "order can be voided, and re-generate orders, and order number to maintain the original order number consistent", so the original primary key is at risk. Therefore, the actual field that is unique also represents the primary key that can be used. Therefore, I recommend that you set a new field specifically for the primary key, which itself is not reflected in the business logic and has no practical significance. And this primary key in a certain program to increase the complexity, so depending on the actual size of the system, for small projects, the future expansion will not be very large, but also to allow the actual unique field as the primary key.

Selection of primary keys

We are now thinking about what should be used to make the table of the primary key is reasonable, stating that the design of the primary key is not a conclusion, each person has a method, even if the same, in different projects, will also adopt different primary key design principles.

First: Number as primary key

This approach is based on the "number" of the unique field in the actual business as the primary key design, which is recommended in small projects, because it can make the project more simplistic, but it may cause some trouble in use, such as "number modification", may involve a lot of related other tables, as Uncle Lee said " The consequences are serious "; there is the above mentioned" business requirements allow numbers to repeat ", we again so prophets, can not know what the business will be modified?

Second: auto-numbering primary key

This method is also a lot of friends in use, is to create a new ID field, automatic growth, very convenient also satisfies the principle of the primary key, the advantage is: Database automatic numbering, fast, and is incremental growth, clustered primary key in order to store, for the search is very advantageous; digital type, occupy small space, easy to sort, It is convenient to pass in the program, if the record is added by non-system (such as manual input, or the new record is inserted directly in the table by other tools, or the old system data is imported), it is very convenient, do not have to worry about the primary key repetition problem.

Disadvantage: In fact, the disadvantage is from its advantages, that is, because of automatic growth, in the manual to insert the record of the specified ID will be troublesome, especially when the system integration with other systems, the need for data import, it is difficult to ensure that the original system ID does not occur primary key conflict (if the old system is also digital type); If the other system primary key is not the number type then the trouble is bigger, will cause the change primary key data type, this also causes the other related table's modification, the consequence is also very serious; even if other systems are also digital, when importing, in order to distinguish between old and new data, you may want to add an "O" To show that this is old data, the auto-growing digital model faces a challenge.

Third: Max plus one

Because of the problem of automatic numbering, so some friends use their own generation, is also a digital type, just to remove the automatic growth, the use of the INSERT, read the 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 ( It can also affect efficiency, and more serious is the concurrency problem, and if two people read the same max, the ID value inserted after the addition is duplicated, which is already a lesson.

IV: Homemade plus One

Considering the efficiency of Max plus one, someone uses homemade plus one, that is, to build a special table, fields: Table name, current sequence value. In this way, when inserting values into a table, we first find the maximum value of the corresponding table from this table and add one, insert, someone may find that there may also be concurrency processing, this concurrent processing, we can use the lock thread way to avoid, when generating this value, first lock, take the value, then unlock out, This will not create two people at the same time. That's a lot faster than Max plus one. But there is also a problem: when integration with other systems, out of the system of the generation method, it is troublesome to ensure that the maximum value in the self-made table and after the import consistency, and the number of the above mentioned "O" Old data import problem. Therefore, the primary key can be set to the character type in the "Homemade plus one". The character of the homemade plus I am quite recommended, should be the character of the primary key can cope with a lot of our unexpected situation.

V: GUID primary key

At present a better primary key is the GUID, of course, I am the recommended primary key or character type, but the value is generated by the GUID, the GUID can be generated automatically, can also be generated by the program, and the key value can not be duplicated, to solve the system integration problems, several system GUID values are not duplicated, Even if there are "O" old data can be distinguished, and very efficient, in. NET can be directly used in System.Guid.NewGuid () generation, in SQL can also use NewID () generation.

The advantages are:

Compared to the IDENTITY column, the uniqueidentifier column can know the new row ID in advance with the NewID () function, which provides great convenience for subsequent processing of the application.

Easy to migrate databases, other databases do not necessarily have an IDENTITY column, and GUID columns can be converted to other databases as character columns, while storing the GUID values generated in the application into the database, it 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 loaded with T-SQL.

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

The disadvantages are:

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

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

I do not recommend the GUID the best, in fact, in different situations, we can use one of the above way, think of some advantages and disadvantages, but also convenient for everyone in the design of reference. These are just a little bit of my thinking, and maybe I have limited knowledge, there will be some misunderstanding in the inside, I hope you have any ideas to welcome the discussion.

Thinking about the design of database primary key (turn)