"Key" in the relational database"

 

The primary key and foreign key are the adhesives that organize multiple tables into a valid relational database. The primary key and foreign key design have a decisive impact on the performance and availability of the physical database.

The database mode must be converted from the logical design to the physical design. The structure of the primary key and the foreign key is the crux of this design process. Once the database is used in the production environment, it is difficult to modify these keys. Therefore, it is necessary and worthwhile to design the primary keys and Foreign keys during the development stage.

First, let's talk about the primary key.

Relational databases depend on primary keys. It is the cornerstone of the physical database mode. The primary key has only two purposes on the physical layer:
1. uniquely identifies a row.

2. As an object that can be effectively referenced by a foreign key.

Based on the above two purposes, the following provides some principles that I follow when designing the primary keys of the physical layer:

1. Primary keys should be meaningless to users. If you see data in a connection table that represents many-to-many relationships and complain about its usefulness, it proves that its primary key is well designed.

2. Primary keys should be single columns to improve the efficiency of connection and filtering operations.

Note: people who use composite keys usually have two reasons for self-release, which are both incorrect. The first is that the primary key should have practical significance. However, making the primary key meaningful only makes it easy to artificially damage the database. The second is that two external keys can be used as the primary key in the connection table describing multiple-to-multiple relationships. I also oppose this approach because: composite primary keys often lead to bad Foreign keys. That is, when the connected table becomes another master table of the slave table, it becomes part of the primary key of the table according to the second method above. However, this table may become another master table of another slave table, and its primary key may be a part of another primary key of the slave table. If this is passed on, the closer it is to the back of the slave table, the primary key will contain more columns.

3. Never update the primary key. In fact, because the primary key only identifies a row and has no other purposes, there is no reason to update it. If the primary key needs to be updated, it indicates that the primary key should be meaningless to the user.

Note: This principle is not applicable to data that often needs to be sorted during data conversion or multi-database merger.

4. The primary key should not contain dynamically changed data, such as the timestamp, Creation Time column, and modification time column.

5. The primary key should be automatically generated by a computer. If a person intervene in the creation of a primary key, it will have a meaning other than a unique row. Once this boundary is crossed, the motive for modifying the primary key may be generated, this system is used to link record rows and manage record rows. It will fall into the hands of people who do not know the database design.

Uniqueidentifier

Guid ).

Note

UniqueidentifierYou can initialize a column or local variable of the Data Type into a value in two ways:

• Use newid Function.

• Convert a String constant to the following format (XXXXXXXX-XXXX-xxxxxxxxxxxx, where each X is a hexadecimal number in the range of 0-9 or a-f ). For example, 6f9619ff-8b86-d011-b42d-00c04fc964ff is validUniqueidentifierValue.

The comparison operator can beUniqueidentifierValue. However, the arrangement is not achieved by comparing the bit modes of two values. AllowUniqueidentifierOnly the operations performed by the value are comparison (=, <>,<, >,<=, >=) and check null (is null and is not null ). Other arithmetic operators are not allowed. All column constraints and attributes (except identity) can be usedUniqueidentifierData type.