Foreign key and referential integrity in the development of MySQL database

mysql| Data | 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 (m) not NULL, PRIMARY KEY (ID)) Engine=inn ODB; Query OK, 0 rows affected (0.11 sec) mysql> INSERT into species VALUES (1, ' Orangutan '), (2, ' Elephant '), (3, ' Hippopota Mus '), (4, ' Yak '); Query OK, 4 rows affected (0.06 sec) records:4 duplicates:0 warnings:0mysql> CREATE TABLE Zoo (ID INT (4) not NULL, NA Me VARCHAR not NULL, Fk_species TINYINT (4) is 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 in the species.idfield, and 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)

Here, MySQL checks the species table to see if the species code exists and allows the record to be saved in the zoo table when it is found.

To remove a foreign key relationship, first use show CREATE table to find the internal label for the InnoDB, as shown in table B:

Table B

+-------+---------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------+
| Zoo | CREATE TABLE ' Zoo ' (
' ID ' int (4) Not NULL default ' 0 ',
' Name ' varchar ' not NULL default ',
' Fk_species ' tinyint (4) Not NULL default ' 0 ',
KEY ' fk_species ' (' fk_species '),

REFERENCES ' species ' (' id ')
) Engine=innodb DEFAULT charset=latin1 |
+-------+----------------------------------------------------+

Then use the ALTER TABLE command with the DROP FOREIGN KEY statement, as follows:

mysql> ALTER TABLE Zoo DROP FOREIGN KEY zoo_ibfk_1;
Query OK, 1 row affected (0.11 sec)
Records:1 duplicates:0 warnings:0

To add a foreign key to a ready-made table, use the ALTER TABLE Statement of add FOREIGN key to specify the appropriate field as a foreign key:

mysql> ALTER TABLE Zoo ADD FOREIGN KEY (fk_species) REFERENCES species (ID);
Query OK, 1 rows affected (0.11 sec)
Records:1 duplicates:0 warnings:0

As explained in the example above, the foreign key plays an important role in the elusive data entry error, thereby creating a more robust and integrated database. On the other hand, it is worth mentioning that performing a foreign key verification is a process of internal data processing, and that specifying complex internal relationships between different tables can result in degraded performance of the database. Therefore, it is important to find a balance between referential integrity and performance considerations, and the use of foreign keys ensures an optimal combination of performance and robustness.

I expect the introduction of the foreign key in this issue to be of benefit to you, and you will feel the benefits of the foreign key in the next MySQL database design. Happy programming!