Primary keys and foreign keys are the glue that organizes multiple tables into an efficient relational database. The design of primary keys and foreign keys has a decisive impact on the performance and availability of the physical database.
The database schema must be converted from a theoretical logical design to an actual physical design. The structure of primary keys and foreign keys is the crux of this design process. Once the designed database is used in a production environment, it will be difficult to modify these keys, so it is very necessary and worthwhile to design the primary keys and foreign keys during the development stage.
Primary key:
Relational databases rely on primary keys - the cornerstone of the database's physical schema. Primary keys have only two purposes at the physical level:
1. Uniquely identify a row.
2. As an object that can be effectively referenced by foreign keys.
Based on the above two uses, here are some principles I follow when designing primary keys at the physical level:
1. The primary key should be meaningless to the user. If a user sees data in a join table that represents a many-to-many relationship and complains that it is of little use, that proves that its primary key is well designed.
2. The primary key should be a single column to improve the efficiency of join and filter operations.
Note: People who use composite keys usually excuse themselves for two reasons, both of which are wrong. One is that the primary key should have actual meaning. However, making the primary key meaningful only provides convenience for artificially destroying the database. The second is that you can use this method to use two foreign keys as primary keys in a join table that describes a many-to-many relationship. I am also opposed to this approach because: composite primary keys often lead to bad foreign keys, that is, when joining tables Become the master table of another slave table, and become part of the primary key of this table according to the second method above. However, this table may become the master table of other slave tables, and its primary key may become the master table of other slave tables. As part of the primary key, if it is passed on in this way, the later the slave table is, the more columns its primary key will contain.
3. Never update primary keys. In fact, since the primary key has no purpose other than to uniquely identify a row, there is no reason to update it. If the primary key needs to be updated, the principle that the primary key should be meaningless to the user is violated.
Note: This principle does not apply to data that often needs to be organized during data conversion or multiple database mergers.
4. The primary key should not contain dynamically changing data, such as timestamp, creation time column, modification time column, etc.
5. The primary key should be automatically generated by the computer. If a human intervenes in the creation of a primary key, it will have meaning other than uniquely identifying a row. Once this boundary is crossed, there may be an incentive to modify the primary key, so that the key means used by this system to link and manage rows of records will fall into the hands of people who do not understand database design.
A foreign key is an integrity constraint at the database level, which is the database implementation method of "referential integrity" mentioned in the basic database theory book.
Of course, the foreign key attribute can be removed. If you no longer want to use this constraint, it will certainly not have any impact on programming, but when entering data, the "referential integrity" check will not be performed on the entered data.
For example, there are two tables
A(a,b): a is the primary key, b is the foreign key (from Bb)
B(b,c,d): b is the primary key
If I remove the foreign key attribute of field b, it will have no impact on programming.
As above, b in A is either empty or a value that exists in b in B. When there is a foreign key, the database will automatically check for you whether b in A exists in b in B.
1. External expression expresses referential integrity: this is inherent to the data and has nothing to do with the program. Therefore, it should be left to the DBMS.
2. Using external databases is simple and intuitive, and can be directly reflected in the data model. It has great benefits in terms of design, maintenance, etc., especially when analyzing existing databases. The benefits are very obvious - I analyzed it not long ago I found an existing enterprise database. Some of the referential integrity constraints in it are described by foreign keys, and some are implemented using triggers. It feels very obvious. Of course, it may be in the document, but it may not be complete, but foreign keys are very obvious and intuitive.
3. Since we can use triggers or programs to complete this work (referring to referential integrity constraints), DBMS has provided the means, why should we do it ourselves? And it should be said that what we do is not as good as RDBMS. In fact, early RDBMS did not have foreign keys, but now they all have them. I think it makes sense for database vendors to add this feature. From this perspective, foreign keys are more convenient.
4. Regarding convenience, based on the projects I led, programmers did report that it was mainly troublesome to enter data during debugging: if the data can violate referential integrity, then referential integrity itself does not conflict with the reputation business. At this time, the trigger futures program should not be used; otherwise, it means that the data is wrong and should not be entered into the database at all! Moreover, this should also be a part of the test system: blocking illegal data. In fact, the front-end program should handle this submission failure. Data belongs to the enterprise, not the program. Stored programs should be separated from the data as much as possible, and vice versa.
Finally, let me talk about several principles for building keys:
1. Create foreign keys for related fields.
2. All keys must be unique.
3. Avoid using compound keys.
4. Foreign keys are always associated with unique key fields.
This article comes from the CSDN blog. Please indicate the source when reprinting: http://blog.csdn.net/c04s31602/archive/2009/12/30/5107568.aspx