Primary Key and foreign key of the 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.

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.

Foreign keys are integrity constraints at the database level. They are the database implementation method of "Reference integrity" as described in the basic database theory.

Foreign key attributes can certainly be removed. If you don't want to use these constraints, it will certainly not affect programming, however, the "reference integrity" check is not performed for the input data.

For example, there are two tables

A (A, B): A is the primary key, and B is the foreign key (from B. B)

B (B, c, d): B is the primary key

If I remove the foreign key attribute of field B, it will not affect programming.

As shown above, B in a is either empty or a value in B's B. When a foreign key exists, the database automatically checks whether B of A exists in B.

1. External construction expresses the integrity of reference: this is inherent in the data and has nothing to do with the program. Therefore, it should be handed over to the DBMS.

2. External building is simple and intuitive, and can be directly reflected in the data model. It has great benefits in terms of design, maintenance, and other backtracing, especially when analyzing the benefits of existing databases, it was obvious that I recently analyzed an existing enterprise database. The reference integrity constraints in the database are foreign key descriptions, and some are implemented using triggers, obviously. Of course, there may be but not all in the document, but the foreign key is very obvious and intuitive.

3. Since we can do this with triggers or programs (referring to integrity constraints), DBMS has provided a means. Why should we do it ourselves? What we do should be said that no RDBMS is doing well. In fact, the early RDBMS did not have any foreign keys, and now it is available. I think it makes sense for the database vendor to add this function. From this perspective, foreign keys are more convenient.

4. For convenience, according to my project, the programmer does reflect the trouble of inputting data during debugging: if the data violates the integrity of the reference, that is to say, the integrity of the reference itself does not conflict with the reputation business. At this time, it should not be implemented by triggering the Futures program; otherwise, it indicates that the data is wrong and should not be written into the database! In addition, this should also be a part of the test system: Blocking illegal data. In fact, the foreground program should handle such submission failures. Data is from an enterprise rather than a program. The stored program should be separated from the data whenever possible, and vice versa.

Finally, the key creation principles are as follows:

1. Create a foreign key for the associated field.

2. All keys must be unique.

3. Avoid using compound keys.

4. Foreign keys are always associated with unique key fields.