Problems in the design of the primary key of SQL Server table

The logical design of database is a very broad problem. In this paper, the main key design of the table is discussed in the design of Ms SQL Server in the development application, and the corresponding solution is given.

The status and problems of primary key design

As for the primary key design of database tables, it is generally based on business logic to form the primary key according to the business requirements.

For example, when sales to record sales, generally need two tables, one is the summary description of the sales list, records such as sales number, the total amount of a class of cases, another table records the quantity and amount of each commodity. For the first table (the primary table), we usually take the document number as the primary key; for the list of merchandise sales (from the table), we need to put the main table's document number into the BOM of the commodity, so that it can be correlated to form a master-slave relationship. The document number, together with the code of the commodity, forms the joint primary key of the schedule. This is just the general situation, we slightly extend this question: if in the details, each of our products may be sold in different prices. Some are sold at discounted prices, and some are sold at normal prices. To record these cases, we need a third table. The primary key of this third table requires the document number of the first table and the commodity number of the second table together with the information that it needs to form a joint primary key, or otherwise, in the first primary table itself, the joint primary key is formed in a joint way, You also need to combine multiple fields from the table to form your primary key together.

Data redundancy storage: With the extension of this master-slave relationship, data that needs to be stored repeatedly in the database will become larger and bigger. Or when the primary table itself is a federated primary key, you must store all the fields from the table again.

Increased SQL complexity: When there are joint primary keys for multiple fields, we need to associate multiple fields of the primary table with multiple fields of the child table to get all the detail records that meet certain criteria.

Increased program complexity: Multiple parameters may need to be passed.

Reduced efficiency: The database system needs to judge more conditions, and theSQL statement length increases. Also, federated primary keys automatically generate federated indexes

Web Paging Difficulty: because it is a federated primary key (for most child tables), it is difficult to process on a Web page when it is being paginated.

Solution

From the above, we have seen a considerable number of shortcomings in the existing structure, mainly resulting in the complexity of the program, inefficient and not conducive to paging.

In order to solve the above problems, this paper proposes that when there is a master-slave relationship between tables in the application system, the database table adds a non-business field as the primary key, the field is numeric, or it should be considered when the table needs to be paged in the application. Generally, we can also add a field that is not a business logic independent of any table as the primary key field for that table.

Because the field is to be the primary key for a table, the first condition is to ensure uniqueness in the table. At the same time, combining the characteristics of the SQL Server database itself, you can create a self-adding column for it:

Create TABLE T_pk_demo ( u_id BIGINT not NULL IDENTITY (1,1), – ID that uniquely identifies the record Col_other VARchar is not NULL, – Other Columns CONSTRAINT Pk_t_pk_demo PRIMARY KEY nonclustered (u_id) – Defines the primary key )

However, there is a more embarrassing fact that the self-add column inSQL Server has the problem that once the field is defined and used, the user cannot directly intervene in the value of the field, which is entirely controlled by the database system itself:

Full database system control, user cannot modify value

When you publish and subscribe to a database, it can be tricky to use the self-added column

Using self-adding columns can be tricky when recovering some data

The value of the column must be inserted before the data can be retrieved

For this reason, it is recommended that you do not define it as a custom, but instead refer to the sequence in the Oracle database system to implement a sequence of similar Oracle database systems in SQL server systems. This is specifically described in the following section. All we need to do is modify the table definition in terms of the normal field definition to:

Create TABLE T_pk_demo ( u_id BIGINT not NULL – ID that uniquely identifies the record Col_other VARchar not NULL, – other columns CONSTRAINT Pk_t_pk_demo PRIMARY key Nonclustered (u_id) – Define primary key )