When designing a primary key, you often need to consider the following points:
1. Meaningless: The meaningless here is defined from the user's point of view. This nonsense also reduces the information redundancy of the database to some extent. It is often called the primary key as an internal identity, why this is called, one of the reasons is "internal", the so-called internal to a certain extent, refers to the table records, from a large range is the database, if you choose the design of the user's meaningful information as a primary key, Sooner or later you will be confronted with the user's need to update this piece of information, then you are violating the static it should be.
2. Static: The primary key in addition to uniquely identify a record and foreign key of the association, should no longer consider other significance, the most ideal state is no longer changes after the production, so after the primary key value should be considered not to update the operation. If the update is done then at least it means that the piece of information is meaningful to the user, then you violate the meaningless. (Consolidation of data may require a primary key to be processed, in order to ensure the integrity of the database-the only record that is not considered within this scope.) ）
The sense of being meaningless can often determine its static nature.
3. Brevity: Contains a small number of primary key composition fields, as well as the primary key in a single field storage type short, general use of plastic, for the former mainly consider the factors of foreign Key association, for the latter is mainly considered performance. The brevity of the primary key is helpful to the convenience of the table's Association and the performance of the retrieval.
Look at the following flawed master production schedule primary key design (MsSQL):
Copy Code code as follows:
--Main Table
CREATE TABLE Pp_mpsheader (
Billno VARCHAR not NULL PRIMARY KEY,
Plandate DATETIME not NULL
)
--From the table
CREATE TABLE Pp_mpsbody (
Billno VARCHAR is not NULL,
LineNumber SMALLINT not NULL,
ProductID INT not NULL,
Productqty DECIMAL (18,2) not NULL,
PRIMARY KEY (Billno,linenumber)
)
--Set 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 plan for which number to release is recorded in the primary table, which is recorded from the table as to how many products are produced by this plan, and are associated through Billno. After the user has issued a master production plan, it is likely to find that due to carelessness in the wrong Billno plan number information, then in his amendment number, code writers need to control the code from the table to follow the main table of the number of a number to change, otherwise the document will be foreign KEY constraints cannot be saved, if there is no constraint , the data will lose its integrity.
If you follow the above 3 points of attention, the solution is as follows (MsSQL):
Copy Code code as follows:
--Main Table
CREATE TABLE Pp_mpsheader (
billID INT PRIMARY KEY,
Billno VARCHAR is not NULL,
Plandate DATETIME not NULL
)
--From the 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 FOREIGN key
ALTER TABLE Pp_mpsbody
ADD CONSTRAINT fk_pp_mpsheader_mpsbody FOREIGN KEY (billid) REFERENCES Pp_mpsheader (billID)
Now, the master-slave table is connected through billID, when producing a production plan, generate a billid, for the user is meaningless, in the subsequent changes in the document information will not appear above the master-slave information coordination problem. At the same time the information from the table is less than the defect design above. Because the length of the original foreign key Billno from 20 bytes to the current BillId4 byte, the redundancy of the information is reduced.
There are many examples, such as:
Some design the raw material table, use part drawing as the primary key, which means that the purchase, production, sales and so on related tables will appear in the part of the information of the parts and components, when the parts of the information changes, all the first-pass information needs to follow the change, if this flaw is not fundamentally resolved, Then you may need to write a part number change processing process, to deal with these problems in batches, in the process of processing you may have to consider the order of processing ... ；
Some design, the ID card number is used as the primary key for the people table, but the ID card later changed from 15 to 18, which means that everyone's ID card information needs to be changed in the person's table, if you are a design person for the application in a Social security agency, you need to update the millions record , all the information records that are linked by the person's list through the ID card will be counted in billions, so you may not need to do other work for the rest of your life.
So choosing meaningless key values as part of a primary key is also a long-term way to avoid similar changes.
