Considerations for SQL Server primary key design

When designing a primary key, consider the following:

1. Meaningless: the meaning here is defined from the user's perspective. This meaningless solution also reduces database information redundancy to a certain extent. Some people often refer to the primary key as an internal identifier. One of the reasons is "internal". The so-called internal refers to a table record to some extent and a database to a large extent, if you select meaningful information as the primary key during the design, you will sooner or later need to update this information, then you violate the static state it should have.

2. static: In addition to uniquely identifying a record and foreign key Association, the primary key should not consider other meanings. The ideal state is that it will not change after it is generated, therefore, you should consider not updating the primary key value after it is generated. If an update operation is performed, it indicates at least that this information is of some significance to the user, and you are against the meaningless nature. (Primary keys may need to be processed for data integration and other operations, so as to ensure the integrity of the database-the unique record, not within this scope .)
Non-significance often determines its static nature.

3. brief: the number of fields that contain the primary key must be small, and the storage type of a single field in the primary key must be short. Generally, an integer is used. The former mainly considers the foreign key Association factors; the latter mainly considers performance. The accessibility and retrieval performance of a brief primary key table can be greatly improved.

Let's take a look at the following primary key design scheme (MsSQL) with a defective "Master Production Schedule ):
Copy codeThe Code is as follows:
-- Master table
Create table PP_MPSHeader (
BillNo VARCHAR (20) not null primary key,
PlanDate DATETIME NOT NULL
)
-- Slave table
Create table PP_MPSBody (
BillNo VARCHAR (20) not null,
LineNumber smallint not null,
ProductID int not null,
ProductQty DECIMAL (18, 2) not null,
Primary key (BillNo, LineNumber)
)
-- Set the foreign key
Alter table PP_MPSBody
Add constraint FK_PP_MPSHeader_MPSBody foreign key (BillNo) REFERENCES PP_MPSHeader (BillNo)

This is a typical master-slave table structure. The master table records the master plan for which the ticket number is issued, and the table records the number of products produced in the plan, which are associated with BillNo. When a user receives a master production plan, he may find that he has mistakenly entered the plan number information in BillNo due to carelessness. When he modifies the plan number, the code writer needs to control the change of the ticket number from the table following the ticket number of the main table in the Code. Otherwise, the document cannot be saved under the foreign key constraint. If there is no foreign key constraint, data will lose its integrity.

According to the preceding three notes, the solution is as follows (MsSQL ):
Copy codeThe Code is as follows:
-- Master table
Create table PP_MPSHeader (
BillId int primary key,
BillNo VARCHAR (20) not null,
PlanDate DATETIME NOT NULL
)
-- Slave table
Create table PP_MPSBody (
BillId int primary key,
LineNumber smallint not null,
ProductID int not null,
ProductQty DECIMAL (18, 2) not null,
Primary key (BillId, LineNumber)
)
-- Set the foreign key
Alter table PP_MPSBody
Add constraint FK_PP_MPSHeader_MPSBody foreign key (BillId) REFERENCES PP_MPSHeader (BillId)

Currently, the master and slave tables are associated by BillId. When a production plan is generated, A BillId is generated, which is meaningless to users, the above master-slave Information Coordination issue will not occur in subsequent changes to the document information. At the same time, the amount of information from the table is smaller than the above defect design. Because the length of the original foreign key BillNo has changed from 20 bytes to 4 bytes of the current biloan, which reduces information redundancy.

There are actually many such examples, such:
Some raw material tables use the part number as the primary key, which means that the foreign key information of the part number appears in the purchase, production, sales, and other related tables, when the part number information changes, all the first-off information needs to be changed. If this defect is not fundamentally solved, then you may need to write a process for changing the part map number to deal with these problems in batches. During the process, you may have to consider the order of problems to be handled ......;
In some designs, the ID card number is used as the primary key of the personnel table, but the ID card is changed from 15 to 18, which means that the ID card information of each person in the personnel table needs to be changed, if you are an Application Designer at a social security agency, you need to update millions of records; all the information records that are exported by the person table through the ID card number will be counted in, so you may not need to do other work for the rest of your life.

Therefore, selecting meaningless key values as part of the primary key also avoids such changes in the long term.