Considerations on keys outside of database design and database design

Considerations on keys outside of database design and database design

There is no uniform standard for the use of foreign keys in the industry. The focus of the debate is data consistency and performance.

Supports the use of foreign keysIt is emphasized that data consistency cannot be guaranteed if no foreign key is used, and performance consumption can be ignored.

Foreign key usedData Consistency can be ensured through a program. The performance is very high and data maintenance is very troublesome. If it is a large system, the relationship between the entire foreign key is like a large network. Furthermore, it is difficult for developers to make good use of foreign keys.

In fact, I support both of them. The current situation is that I have never used foreign keys. What will happen if the foreign key is not used?

1. Data Consistency cannot be guaranteed. The most common case is that, for example, A indicates A base table, which is referenced by many modules. Table B is A business table, and table A deletes data, the information of Table B associated with table A is not deleted, resulting in A large number of external connections during SQL writing.

2. No index is created from the table. If a foreign key exists in the table, this is a tragedy. For more information, see what I wrote earlier.Performance problems caused by no index on Foreign keys

3. It is very troublesome to use a foreign key to modify data in the background. Of course, this is not a problem of the foreign key, but a problem of the system itself.

If you read any book that involves database design, it will tell you that you must use the foreign key. However, there is always a gap between the ideal and the implementation. How to Choose? My suggestion:

1. If you have a high requirement on data consistency, it is related to human life and money. You must add Foreign keys, such as financial systems. On the contrary, you can sacrifice it for convenience.

2. If no foreign key is added, the basic table (that is, the table to be referenced) needs to be logically deleted (add a deleted identifier), which can avoid a large number of external connections.

3. No matter whether or not a foreign key is added, the index is required.

Foreign key Association for Database Design

Data consistency and integrity are maintained to control data stored in the foreign key table. Associate two tables. The foreign key can only reference the values of columns in the External table!
If you don't have one, you won't find any inconsistency in your data.

What is the role of foreign keys in databases? Why do I see that many mysql databases do not use foreign keys?

For example, if you have two tables: Table A and table B put the primary key of Table A into Table B and it is called the foreign key of Table B. This function is generally used for multi-table join queries, in general, the two tables are connected. I have never used mysql, but the truth is the same. The primary key and foreign key that need to be linked do not need to be naturally unavailable,