Delve into the primary key issues of SQL Server tables

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, and the corresponding solutions are 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 the SQL 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:

The following are the referenced contents:

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)--Defining the primary key
)

However, there is a more embarrassing fact that the self-add column in SQL 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:

The following are the referenced contents:

Create TABLE T_pk_demo
(
u_id BIGINT not NULL,--ID of a unique identity record
Col_other VARchar not NULL,--other columns
CONSTRAINT Pk_t_pk_demo PRIMARY key Nonclustered (u_id)--Define primary key
)

Referring to the capabilities of the Oracle sequence, we need to create a new table in the SQL Server database to manage the sequence values:

The following are the referenced contents:

Create TABLE T_db_seq
(
Seq_namevarchar not NULL,--sequence name
Seq_owner VARchar not NULL DEFAULT ' DBO ',
--Sequence owner (SYSTEM_USER)
Seq_current BIGINT not NULL default 0,--sequence current value
Seq_min BIGINT not NULL default 0,--sequence minimum
Seq_max BIGINT not NULL default 0,--sequence minimum
Seq_max BIGINT not NULL default 0,--sequence maximum
Seq_stepint not NULL DEFAULT 1,--sequence growth step
If_cycleint not NULL DEFAULT 0,--whether the loop (0, not loop; 1, loop)
CONSTRAINT t_db_seq PRIMARY KEY CLUSTERED
(Seq_name,seq_owner)--primary key
)

The application creates a sequence name for the table that needs to be created, and is reflected in the table "T_db_seq" as a row in the database.

First, you need to create a sequence for the table that needs to be set up. Method: F_create_seq (sequence name). The function passes in the name of the sequence and inserts a row in the table "T_db_seq". The owner of the sequence, using the system variable SYSTEM_USER.

Second, get the next value. Method: F_get_next_seq_val (sequence name). The function obtains the next value of the sequence based on the sequence name, based on the current value and the growth step. At the same time, the function ensures concurrent consistency when acquiring the same sequence at the same time.

Third, returns the return value to the application use.

In addition, to ensure the integrity of the application, you may also need to provide some methods of overloading the method, while providing some other methods:

Get the current value of the sequence: F_get_seq_cur_val (sequence name)

Set sequence value: F_set_seq_val (sequence name)

Delete sequence: f_del_seq (sequence name)

To determine whether a sequence exists: f_seq_exists (sequence name)

In the master-slave table design, the child table also uses the Sequence field as the unique primary key, associating the sequence field of the parent table as a foreign key:

The following are the referenced contents:

Create TABLE T_pk_demo_c
(
u_id BIGINT not NULL,--ID of a unique identity record
Col_other VARchar not NULL,--other columns
p_id INT not NULL,--parent table ID
CONSTRAINT Pk_t_pk_demo_c PRIMARY KEY
Nonclustered (u_id)--Defines the primary key
CONSTRAINT fk_t_pk_demo_c FOREIGN KEY (p_id)
REFERENCES T_pk_demo (u_id) on delete CASCADE,
)

Problems in using sequences and solutions

Because the system uses an additional field as the primary key, no PRIMARY KEY constraint is established for the business logic. For example, in the Enterprise User Information table, it is required that the user login name must be unique in the enterprise. In general, when creating a table, the login masterpiece is the primary key, which creates another primary key uniqueness constraint naturally at the database layer. Instead of using the logon masterpiece as the primary key, there is no such constraint. Solution:

One is to solve in the database layer. You can create a unique (unique) constraint or unique index for the table. As：

Alter TABLE T_pk_demo ADD CONSTRAINT c_t_pk_demo unique nonclustered (col_other)-Unique constraint

Create unique index Ix_t_pk_demo on T_pk_demo (col_other) – Unique index

The other is resolved on the application side. That is, in the application to determine whether the column has duplicate values, and then based on the results of the decision to ensure uniqueness.

We note that in the previous example, the primary key was indexed by nonclustered (not clustered). As for how to design an index, this is not the focus of this article, here is a general principle of whether or not to build an index:

As a primary key column for a non-business field, is a column that does not have duplicate values and does not update operations at all. Also, in a SQL Server database, a clustered index can have only one table. Therefore, the clustering index is very important and needs to be left to more important fields to use. Therefore, the index is created here in a nonclustered way, in contrast to the table above and the importance of the clustered index.

Specific application

This type of primary key design has many benefits, as already explained in the previous article. Now let's use a specific application to illustrate how this primary key is used.

The current application system has basically adopted B/s mode, although the network speed has been greatly improved, but because of the number of users in the Web application, and basically all the operations are concentrated in the Web application server, so in the web design to consider the performance optimization, To reduce network traffic and pressure on the server. One of the most common applications is the way in which the list is presented. Generally, in the case of small amount of data, generally do not pay attention to this problem, usually take the data completely out, and then on the Web server paging. However, when the volume of data is large, this approach can lead to slow or even unavailable. Therefore, the general use of stored procedures, in the database side paging