How to Design the primary key of a time table on SQL Server

The logical design of databases is a wide range of issues. This article focuses on the primary key design issues and corresponding solutions when designing tables on ms SQL Server during application development.

Primary Key design status quo and problems

The primary key design of database tables is generally based on business needs and business logic to form a primary key.

For example, to record the sales situation during sales, two tables are generally required. One is the brief description of the sales order, such as the sales order number and total amount, the other table records the quantity and amount of each item. For the first table (master table), we usually use the document as the primary key; for the detail table (slave table) of product sales ), we need to add the document number of the main table to the item list to associate it to form a master-slave relationship. At the same time, this document number is encoded with the commodity to form the joint primary key of the detail table. This is only the general situation. We will extend this question a little: If we have details, each of our products may be sold at different prices. Some are sold at discounted prices, and some are sold at normal prices. To record these situations, we need the third table. In this case, the primary key of the third table requires the document number of the first table and the commodity number of the second table together with the information needed to form the joint primary key. or in other cases, in the first primary table, the Union primary key is formed. You also need to add multiple fields in the primary table to join them to form your primary key.

Redundant data storage: as the master-slave relationship expands, data that needs to be repeatedly stored in the database will become larger and larger. Or when the primary table itself is the Union primary key, all fields must be re-stored from the table.

SQL complexity increases: when multiple fields join the primary key, we need to associate multiple fields in the master table with multiple fields in the sub-table to obtain all detailed records meeting certain conditions.

Increasing Program Complexity: multiple parameters may need to be passed.

Lower efficiency: the database system needs to determine more conditions, and the length of SQL statements increases. Meanwhile, the Union primary key automatically generates the Union index.

Difficulties in WEB paging: Due to the union of primary keys (for most sub-tables), it is difficult to process self-association when paging is performed on WEB pages.

Solution

From the above, we have seen that the existing structure has many drawbacks, mainly because of the complexity of the program, reduced efficiency, and unfavorable paging.

To solve the preceding problem, this article proposes that when there is a master-slave relationship between the database tables in the background of the application system, a non-business field is added to the database table as the primary key, and this field is Numeric; this design should also be considered when the table needs to be queried by page in the application. Generally, we can add a field unrelated to the business logic to almost any table as the primary key field of the table.

Because this field is used as the primary key of the table, the first condition is that it must be unique in the table. In addition, you can create an auto-increment column for the SQL Server database based on its own characteristics:

Create TABLE T_PK_DEMO ( U_ID bigint not null identity (1, 1 ), -Unique ID of the record COL_OTHER VARchar (20) not null, -Other Columns CONSTRAINT PK_T_PK_DEMO PRIMARY KEY NONCLUSTERED (U_ID)-defined as the primary key )

However, there is an embarrassing fact in the auto-increment column in SQL Server, that is, once this field is defined and used, the user cannot directly intervene in the value of this field, which is completely controlled by the database system itself:

Full database system control, user cannot modify Value

When publishing and subscribing to databases, using auto-incrementing columns is troublesome.

It is troublesome to use auto-increment columns to restore some data.

The value of this column must be obtained after data is inserted.

In view of this, we recommend that you do not use the auto-incrementing column method to define it. Instead, you can refer to the sequence in the Oracle database system to implement the sequence function similar to the Oracle database system in the SQL Server system. This is described in the following section. We only need to modify the table definition as follows:

Create TABLE T_PK_DEMO ( U_ID bigint not null,-Unique ID of the record COL_OTHER VARchar (20) not null,-other columns CONSTRAINT PK_T_PK_DEMO primary key nonclustered (U_ID)-defined as a PRIMARY KEY )

With reference to the Oracle sequence function, we need to create a new table in the SQL Server database to manage the sequence values:

Create TABLE T_DB_SEQ ( SEQ_NAMEVARchar (50) not null,-sequence name SEQ_OWNER VARchar (50) not null default 'dbo ', -Sequence owner (SYSTEM_USER) SEQ_CURRENT bigint not null default 0,-current Sequence Value SEQ_MIN bigint not null default 0,-minimum value of the sequence SEQ_MAX bigint not null default 0,-minimum value of the sequence SEQ_MAX bigint not null default 0,-maximum Sequence Value SEQ_STEPINT not null default 1,-sequence growth step IF_CYCLEINT not null default 0,-whether to cycle (0, NOT loop; 1, loop) CONSTRAINT T_DB_SEQ PRIMARY KEY CLUSTERED (SEQ_NAME, SEQ_OWNER)-primary key )

The application system creates a sequence name for the table that needs to create an auto-incrementing column, which is reflected as a row in the database in the table "T_DB_SEQ.

First, you need to create a sequence for the table that requires sequence creation. Method: F_create_SEQ (sequence name ). This function is used to input the sequence name and insert a row in the table "T_DB_SEQ. The sequence owner. The system variable SYSTEM_USER is used.

Second, obtain the next value. Use F_GET_NEXT_SEQ_VAL (sequence name ). This function obtains the next value of the sequence based on the sequence name, and obtains the value based on the current value and the Growth step. At the same time, this function ensures concurrency consistency when obtaining the same sequence at the same time.

Third, return the return value to the application for use.

In addition, to ensure application integrity, some methods may need to be overloaded, and some other methods are also provided:

Obtain the current Sequence Value: F_GET_SEQ_CUR_VAL (sequence name)

Set the Sequence Value: F_SET_SEQ_VAL (sequence name)

Delete sequence: F_DEL_SEQ (sequence name)

Determine whether a sequence exists: F_SEQ_exists (sequence name)

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

Create TABLE T_PK_DEMO_C ( U_ID bigint not null,-Unique ID of the record COL_OTHER VARchar (20) not null,-other columns P_ID int not null,-parent table ID CONSTRAINT PK_T_PK_DEMO_C PRIMARY KEY NONCLUSTERED (U_ID)-defined as the primary key CONSTRAINT FK_T_PK_DEMO_C foreign key (P_ID) REFERENCES T_PK_DEMO (U_ID) ON delete CASCADE, )

Sequence Problems and Solutions

Because the system adds an additional field as the primary key, no primary key constraint is set for the business logic. For example, in the enterprise user information table, the enterprise user login name must be unique. Generally, when creating a table, the login name is used as the primary key. At this time, the database layer naturally creates another primary key uniqueness constraint. However, if the logon name is not used as the primary key, this constraint does not exist. Solution:

First, solve the problem at the database layer. You can create a UNIQUE (UNIQUE) constraint or UNIQUE index for the table. For example:

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

Second, solve the problem on the application side. That is to say, in the application, determine whether the column has repeated values, and then guarantee uniqueness based on the judgment results.

In the previous example, the primary key adopts the NONCLUSTERED (non-clustered) index method. How to design indexes is not the focus of this Article. Here we only provide a general principle of clustering or non-clustering when creating indexes:

As a primary key column of a non-business field, it is a column with no duplicate values and is basically not updated. In addition, in the SQL Server database, a clustered index can have only one table. Therefore, clustered indexes are very important and need to be reserved for more important fields. Therefore, non-clustered indexes are used to create indexes based on the importance of clustered indexes in the preceding table.

Specific Application

Using this primary key design method has many advantages, which have been described previously. Now we will explain how to use this primary key with a specific application.

The current application system basically adopts the B/S mode, although the network speed has been greatly improved, however, because there are a large number of WEB applications, and basically all the operations are concentrated on the WEB application server, performance optimization must be taken into account in WEB design, to reduce network traffic and load on servers. One of the most common applications is the paging mode when the list mode is displayed. Generally, when the data volume is small, you do not need to pay attention to this issue. Generally, you can retrieve the data completely and then perform paging on the WEB server. However, when the data volume is large, this method will lead to a reduction in speed, or even unavailability at all. Therefore, stored procedures are generally used for paging at the database end.