Foreign keys and referential integrity in MySQL database development _ MySQL

Foreign keys in MySQL database development and Mysql foreign keys with reference integrity

Referential integrity is an important concept in database design. In different lists of systems, the integrity of the reference is involved when all references of the database are legal or illegal. When the integrity of the reference exists, any association with the non-existent record becomes invalid, which prevents various errors and provides a more accurate and practical database.

Integrity of reference is usually widely used through the use of foreign keys. For a long time, the popular tool open-source RDBMS MySQL does not support foreign keys because such support will reduce the speed and performance of RDBMS. However, many users are interested in the advantages of the integrity of the reference. recently, different MySQL versions support foreign keys through the new InnoDB list engine. Therefore, it is very easy to maintain the integrity of the reference in the list of databases.

To establish a foreign key relationship between two MySQL tables, the following three conditions must be met:

The two tables must be InnoDB tables.

The fields using the foreign key relationship must be indexed ).

The fields using the foreign key relationship must be of the same data type.
The example is the best way to understand the above points. As shown in Table A, create two tables, one of which lists the animal types and the corresponding code (table name: species), and the other lists the animals in the zoo (table name: zoo ). Now, we want to associate these two tables through species, so we only need to accept and save the zoo table's entry containing valid animals in the species table to the database.

Table

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, 'hippotamus'), (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: For non-InnoDB tables, the foreign key statement is ignored.

There is a foreign key relationship between fieldszoo. species and species. id. Only one corresponding zoo. specie matches the species. idfield value. The input eloquence in the animal table can be accessed. The following output demonstrates the use of invalid species code when you want to input a Harry hippotamus 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 check whether the species code exists. If no species exists, this record is rejected. 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 check whether the species code exists. if the code is found to exist, records can be saved in the zoo table.

To delete a foreign key relationship, use show create table to find the internal tag of InnoDB, as shown in table B:

Table B

+ ------- + --------------------------------------------------- +
| Table | Create Table |
+ ------- + --------------------------------------------------- +
| Zoo | create table 'zoo '(
'Id' int (4) not null default '0 ',
'Name' varchar (50) not null default '',
'Fk _ species 'tinyint (4) not null default '0 ',
KEY 'fk _ species '('fk _ species '),
CONSTRAINT 'Zoo _ ibfk_1 'foreign key ('fk _ species ')
REFERENCES 'species '('id ')
) ENGINE = InnoDB default charset = latin1 |
+ ------- + ---------------------------------------------------- +
Use the alter table command with the drop foreign key statement, as shown in the following code:

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 an 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 preceding example, foreign keys play an important role in identifying data entry errors, so that a more robust and integrated database can be created. On the other hand, it is worth mentioning that executing foreign key verification is a process of internal data processing, and specifying complex internal relationships between different tables can lead to database performance degradation. Therefore, it is important to find a balance between the integrity of the reference and performance considerations. the use of foreign keys ensures the optimal combination between performance and stability.