Database primary key foreign key design principles

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.

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.

Reprinted: http://www.cnblogs.com/tianyamoon/archive/2008/04/02/1134394.html

Use of Foreign database keys

I think there are two main roles of Foreign keys: one is to allow the database to ensure data integrity and consistency through foreign keys, and the other is to increase the readability of ergraphs. I think the second point is even more important than the first point.

Some people think that the establishment of foreign keys will cause a great deal of trouble to operate the database during development, because the database sometimes fails to pass the detection of Foreign keys, causing developers to delete and insert operations to fail, they think this is very troublesome. In fact, this formal foreign key forces you to ensure data integrity and consistency, which is a good thing.

It should be said that if the system is relatively small, the role of foreign keys may not be very obvious. If your system has hundreds of tables in the background, I cannot imagine the database design without foreign keys, there is a basic data table: item, other tables store the item ID, you need to connect to the table to query the product name, the product table in document 1 contains the item ID field, the item ID field also exists in the item table of document 2. If the foreign key is not pulled, after the item with the item ID 3 is used in document 1 and 2, the product name cannot be found when the document is 1 or 2.

When there are few tables, some people think that data integrity and consistency can be ensured by writing scripts during program implementation, that is, when you delete a product, check whether the product with the product ID 3 is used in document 1 and 2. After you finish the script, the system adds document 3.
He also saves the item ID to find a field. If you do not pull the foreign key, you will still be unable to find the product name, you cannot modify the script to check whether the product is used every time you add a document that uses the item ID field.

The second step is to increase the readability of the erimage. This is also reflected when there are many background database tables. Foreign keys can clearly define the relationship between two tables, for example, if the two tables do not have a foreign key, the relationship is indicated, and the positions of the two tables are far away in the ER diagram, for a person who is not very familiar with this system, it is very troublesome to let him understand the relationship between the two tables. If you pull the foreign key, even if the two tables are far away, the external keys can also be used to identify the relationship between them. the readability of the ERTU is extremely important for a newbie who just came into contact with a large system.

Of course, the number of foreign keys does not have no scale, because the excessive pulling of Foreign keys will make the erdiagram extremely messy (line everywhere), so you should master the appropriate scale, necessary Foreign keys must be pulled out. If you really don't want to confuse the ergraph because of too many foreign keys, you can use a false Delete method to delete the basic data, to avoid data inconsistency without foreign key constraints and check.

Reprinted: http://blog.csdn.net/dowson2002/archive/2007/08/29/1764148.aspx

Uniqueidentifier Data Type

The uniqueidentifier data type can store 16-byte binary values, which act the same as the Globally Unique Identifier (GUID. GUID is the unique binary number: No duplicate GUID value is generated on any two computers in the world. GUID is used to assign a unique identifier to networks of multiple nodes and computers. In SQL, ROWGUIDCOL indicates that the new column is the Globally Unique Identifier column of the row. Only one uniqueidentifier column can be assigned to each table as the ROWGUIDCO column. The ROWGUIDCOL attribute can only be assigned to the uniqueidentifier Column
1. What is uniqueidentifier?
Uniqqueidentifier is a globally unique identifier.

P d [3 ~) F c E0 two UniqueIdentifier: How do I assign values to columns of the Data Type?
1. Use the NewID () function to implement
2. directly convert the constant of the string to the format xxxxxxxx-xxxx-xxxxxxxxxxxx.
Example: 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid UniqueIdentifier data
3. assign a 32-bit sixteen-bit data directly.
Example: 0xffffffff00000000ffffff00000000
3. How is the UniqueIdentifier data actually stored in the database?
The actual data stored in UniqueIdentifier is a binary value of 16 bytes,
UniQueIdentifier can be converted to the actual string type and binary data type.

How does the NewID () function generate a unique UniqueIdentifier value?
The NewID () function is used to generate new UniqueIdentifier data from the unique numbers on their NICs that identify numbers and CPU clocks, this data is the same as the GUID, and each computer can generate a globally unique value.
In this way, a unique identifier is generated between multiple computers and multiple networks.

5 main advantages of using the Uniqueidentifier Data Type
The main advantage of Uniqueidentifier data type is that the global uniqueness of values can be guaranteed when the newid function is used to generate values.
A unique record that identifies a single row is more effective for multiple databases (especially databases with multiple machines and multiple network segments) than IDEntity.
Secondly, when Identity is used, the automatically generated values cannot be modified, while the Uniqueidentifier data type can be modified at any time.

6. disadvantages of using the Uniqueidentifier Data Type
1. The value of the Uniqueidentifier type is unordered.
When relevant data information is normally displayed, the returned information is unordered ITPUB personal space p e % A _ 0 '2i l (G! V t0]
When the data identified by Identity is displayed, it is displayed by default in the order of adding records. In this way, for the information set whose uniqueidentifier is the primary key, you still need a field that identifies the sorting by default.

2. For the Uniqueidentifier field, the actual information of the data is 16 bytes, which is much larger than the Identity, which reduces the efficiency of storage space and query.

7. In the design of the system database, we should consider how to use Uniqueidentifier, Identity, and identifiable record attributes (information with actual meanings) as primary keys.

System design with attribute as the primary key
In the process of system design
A single message contains attributes that can represent uniqueness (generally, no more than three attributes) and such attributes are mandatory fields. Generally, no changes are made during the record life cycle, and more than 50 such systems are used in the table.
It is best to use attribute as the primary key.

For example, the student management information system uses the student's student ID as the primary key.

When Uniqueidentifier is used as the primary key
When database replication is required between multiple databases and between multiple network segments, we need to identify each single record in each unique identifier, you can use the uniqueidentifier column to generate a primary key if no proper attribute is used as the primary key.

Use identity as the primary key
Database Replication is not required. If the system is relatively small (within 50 tables), you can use the identity column to generate a primary key, which is suitable for rapid development.