What Is a Primary Key?

The database primary key refers to a combination of one or more columns whose value can uniquely identify each row in the table, through which the entity integrity of the table can be enforced. The primary key is mainly related to the foreign keys of other tables, as well as the modification and deletion of this record.

Database primary key

Primary key: There are often one or more column combinations in a table, and its value can uniquely identify each row in the table. Such a column or columns is called the primary key of the table, and it can be used to enforce the physical integrity of the table. You can create a primary key by defining a PRIMARY KEY constraint when creating or altering a table. A table can have only one PRIMARY KEY constraint, and the columns in the PRIMARY KEY constraint are not acceptable

Number as the 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 can simplify the project, but it may cause some trouble in use. For example, when "number modification" is to be performed, many other related tables may be involved, like Uncle Li said, "the consequences are very serious"; there is the above mentioned "when business requirements allow duplicate numbers", we will Then the prophet cannot know what the business will be modified into?

Automatic numbering primary key

This method is also used by many friends. It is to create a new ID field and automatically increase it. It is very convenient and meets the principle of the primary key. The advantages are: the database is automatically numbered, it is fast, and it is incrementally increased. The clustered primary key is stored in order. Very beneficial for retrieval; digital, small footprint, easy to sort, and easy to transfer in the program; if you add records through a non-system (such as manual entry, or use other tools to insert new records directly into the table, or the old system Data import), it is very convenient, do not worry about the problem of duplicate primary keys.

Disadvantages: In fact, the disadvantages come from its advantages. Because of the automatic growth, it will appear troublesome when manually inserting the records of the specified ID. Especially when the system is integrated with other systems and data is imported, it is difficult to guarantee the ID of the original system No primary key conflicts (provided that the old system is also numeric); if the primary key of other systems is not numeric, it will be more troublesome, which will lead to the modification of the primary key data type, which will also lead to the modification of other related tables with the same consequences Serious; even if other systems are also digital, in order to distinguish between old and new data when importing, you may want to add an "o" (old) before the old data primary key to indicate that this is old data. Facing a challenge.

Max plus one

Because of the problems with automatic numbering, some friends use their own generation, which is also digital, but just removes the automatic growth. In Insert, read the Max value and add one. This method can avoid the problem of automatic numbering. However, there is also an efficiency problem. If the record is very large, then Max () will also affect the efficiency; more serious is the concurrency problem. If two people read the same Max at the same time, add the ID inserted after one The value will repeat, and this is already a lesson learned.

Homemade plus one

After considering the efficiency of Max plus one, some people use homemade plus one, which is to build a special table, the fields are: table name, current sequence value. In this way, when inserting values into the table, first find the maximum value of the corresponding table from this table and then add one, and then insert. Some people may find that there may be concurrent processing. For this concurrent processing, we can use the lock thread to avoid When generating this value, lock it first, and after getting the value, unlock it out so that no two people will generate it at the same time. This is much faster than Max plus one. But there is also a problem: when integrating with other systems, after leaving the generation method in the system, it is very troublesome to ensure that the maximum value in the self-made table is consistent with the imported value, and the digital type has the "o" mentioned above Problems importing old data. Therefore, you can set the primary key to "character type" in "Homemade Plus One". Character-type homemade plus one is quite recommended. Character-type primary keys can cope with many unexpected situations.

GUID primary key

A better primary key is the GUID. Of course, I recommend the primary key or the character type, but the value is generated by the GUID. The GUID can be generated automatically or programmatically, and the key value cannot be repeated, which can solve system integration problems. When the GUID values of two systems are brought together, there will be no duplication. Even if there are "o" old data, they can be distinguished, and the efficiency is very high. In .NET, you can use System.Guid.NewGuid () to generate directly You can also use NewID () in SQL. The advantage is:

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

Convenient for database migration. IDENTITY columns are not necessarily available in other databases, and Guid columns can be converted to other databases as character columns. At the same time, GUID values generated in the application are stored in the database. It will not bring original data. influences.

It is convenient for database initialization. If the application needs to load some initial data, the IDENTITY column is more troublesome to handle, while the uniqueidentifier column does not need any processing and can be loaded directly by T-SQL.

Facilitate the permanent identification of certain objects or constants, such as the ClassID of a class, the instance ID of an object, contacts, service interfaces, tModel identification definitions in UDDI, etc.

weakness is:

The GUID value is long, it is not easy to remember and input, and the value is random and unordered

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