In the early design of database table structure, one table often references the field (usually ID) of another table as a foreign key. With the help of MySQL automatic maintenance of foreign keys, it can indeed save a lot of development work, but the actual cost of foreign keys is not low, and many data table design specifications have explicitly forbidden the use of foreign keys. This article describes the drawbacks of foreign keys.
Currently, InnoDB is the only one of MySQL’s built-in storage engines that still supports foreign keys, so there are few storage engines to choose from if you want to use foreign keys.
Foreign keys are not free. In fact, foreign keys often require the server to check another table when changing data. Although InnoDB uses indexes to speed up this operation, it doesn’t take the impact of data checking away. It is even possible to produce a large, non-filtered index. For example, suppose we have a status field in a large table and constrain status to use only valid values, which have only three options. This can cause additional indexes to greatly increase the storage size of the data table, even if the column itself has little storage space. This is especially true if the primary key has a lot of storage space, and this index is usually useless except for foreign key checking.
Of course, foreign keys can also improve performance in some cases. If we need to ensure that the data of two associated tables must be consistent, using MySQL server for detection will be more efficient than the program for detection. Foreign keys are also useful for cascading deletions or updates. However, such operations are line by line and therefore slower than bulk delete or batch operations.
Foreign keys cause queries to depend on other tables, which means InnoDB needs to verify values in parent tables (or related tables). This also locks rows in the parent table to ensure that the row is not deleted until the transaction completes. This can lead to unexpected lock waits, or even deadlocks, which can be difficult to locate.
Since foreign keys have so many flaws, they can be replaced by something else. One way is to use triggers instead of foreign keys. The purpose of a foreign key is to trigger automatic task association during similar cascading updates. Foreign keys that are merely constrained values, such as the status example discussed earlier, can be overridden by a trigger with an explicit list of available values (you can also use ENUM ENUM types).
Another way to do this is to use programs to accomplish foreign key-like constraints. Foreign keys can significantly increase workload, which cannot be accurately measured, but there have been many performance issues due to foreign key constraint checking, and performance can be significantly improved by removing the exclusion key.
Conclusion: From the database design specifications of many Internet enterprises, foreign keys are repeatedly reiterated to be disabled. Not only performance problems, but also the changeable business of the Internet, if the table structure changes, it is likely to cause unexpected problems in the tables associated with foreign keys. Therefore, do not use foreign keys unless you want to verify their functionality.