Foreign key and referential integrity in the development of MySQL database

Referential integrity (referential integrity) is an important concept in database design. In a different list of systems, referential integrity is involved when all references to the database are legitimate or not legitimate. When referential integrity exists, any association with nonexistent records becomes invalid, thereby preventing a user from having a variety of errors, thus providing a more accurate and useful database.

Referential integrity is often widely used through the use of foreign keys (foreign key). For a long time, popular tools open source RDBMS MySQL does not support foreign keys, because this support will reduce the speed and performance of the RDBMS. However, because many users are interested in the benefits of referential integrity, recent versions of MySQL support foreign keys through the new InnoDB list engine. As a result, it is easy to keep referential integrity in a list of database components.

In order to establish a foreign key relationship between two MySQL tables, the following three scenarios must be met:

Two tables must be InnoDB table types.

Fields that use foreign key relationships must be indexed (index).

Fields that use foreign key relationships must be similar to data types.

Examples are the best way to understand the above points. As shown in table A, create two tables, one of which lists the animal species and the corresponding code (table name: species), and the other table lists the animals in the Zoo (table name: Zoo). Now, we want to associate these two tables through species, so we just need to accept and save the entry of the zoo table that contains the legitimate animals in the species table into the database.

Table A

mysql> CREATE TABLE species　(id TINYINT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,　PRIMARY KEY(id)) ENGINE=INNODB;
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO species VALUES (1, 'orangutan'), (2, 'elephant'), (3, 'hippopotamus'), (4, 'yak');
Query OK, 4 rows affected (0.06 sec) Records: 4 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE zoo (id INT(4) NOT NULL,
name VARCHAR(50) NOT NULL,
FK_species TINYINT(4) NOT NULL,
INDEX (FK_species),
FOREIGN KEY (FK_species) REFERENCES species (id),　PRIMARY KEY(id)) ENGINE=INNODB;Note: The FOREIGN KEY statement will be ignored for InnoDB tables.

There is now a foreign key relationship between Fieldszoo.species and Species.id. Only the corresponding Zoo.specie matches a value of Species.idfield, the entry in the animal table can be accessed. The following output demonstrates the use of an illegal species code when you want to enter a Harry Hippopotamus record:

mysql> INSERT INTO zoo VALUES (1, 'Harry', 5);
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails

Here, MySQL checks the species table to see if the species code exists and rejects the record if it finds it does not exist. When you enter the correct code, you can compare it with the above.

mysql> INSERT INTO zoo VALUES (1, 'Harry', 3);
Query OK, 1 row affected (0.06 sec)