MySQL foreign key and reference integrity: Part 1

From: http://sofee.cn/blog/2006/08/07/26/
1. What is referential integrity?
-------
Integrity of reference (integrity constraints) is an important concept in database design. When a table in the database is associated with one or more tables, the integrity of reference is involved. For example:
Document category table-categories
Category_id name
1. SQL Server
2 Oracle
3 PostgreSQL
5 SQLite
Article table-articles
Article_id category_id title
1 1 aa
2 2 bb
3 4 cc
The preceding two tables use category_id. The categories table has four records and the articles table has three records.
However, for some reason, we may have deleted the records whose category_id is 4 in the categories table, but the articles table still has a record whose category_id is 4. Obviously, this record with category_id = 4 should not exist in the articles table, which can easily cause data disorder.
On the contrary, the Foreign Key relationship (Foreign Key relationships) discusses the relationship between the parent table (categories) and the sub-table (articles) by introducing the Foreign Key (Foreign Key) this concept ensures Referential integrity, which makes the database quite simple. For example, to delete the category_id = 4 record in the categories table and delete all records in the category_id = 4 in the articles table, if the foreign key is not introduced, we must execute two SQL statements; if there is a foreign key, you can easily use an SQL statement to meet the requirements.
2. Conditions for using foreign keys
------
MySQL only introduces foreign keys after v3.23.34, so do not think about the previous versions :). In addition, you must meet the following conditions: