Foreign keys and reference integrity in Mysql Development

Referentialintegrity 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 can prevent various errors and provide more accuracy.

Referentialintegrity 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 can prevent various errors and provide more accuracy.

Referentialintegrity 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 open-source RDBMSMySQL tool 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> CREATETABLEspecies (idTINYINTNOTNULLAUTO_INCREMENT, nameVARCHAR (50) NOTNULL, PRIMARYKEY (id) ENGINE = INNODB;

QueryOK, 0 rowsaffected (0.11sec)

Mysql> INSERTINTOspeciesVALUES (1, 'orangutan '), (2, 'elephant'), (3, 'hippotamus'), (4, 'yak ');

QueryOK, 4 rowsaffected (0.06sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql> CREATETABLEzoo (idINT (4) NOTNULL, nameVARCHAR (50) NOTNULL, FK_speciesTINYINT (4) NOTNULL, INDEX (FK_species), FOREIGNKEY (FK_species) REFERENCESspecies (id ), PRIMARYKEY (id) ENGINE = INNODB;

Note: For non-InnoDB tables, the FOREIGNKEY 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 HarryHippopotamus record:

Mysql> INSERTINTOzooVALUES (1, 'Harry ', 5 );

ERROR1216 (23000): Cannotaddorupdateachildrow: aforeignkeyconstraintfails

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> INSERTINTOzooVALUES (1, 'Harry ', 3 );

QueryOK, 1 rowaffected (0.06sec)

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 SHOWCREATETABLE to find the internal tag of InnoDB, as shown in table B:

Table B

+ ------- + --------------------------------------------------- +

| Table | CreateTable |

+ ------- + --------------------------------------------------- +

| Zoo | CREATETABLE 'zoo '(

'Id' int (4) NOTNULLdefault '0 ',

'Name' varchar (50) NOTNULLdefault '',

'Fk _ species 'tinyint (4) NOTNULLdefault '0 ',

KEY 'fk _ species '('fk _ species '),

CONSTRAINT 'zoo _ ibfk_1 'FOREIGNKEY ('fk _ species ')

REFERENCES 'species '('id ')

) ENGINE = innodbdefacharcharset = latin1 |

+ ------- + ---------------------------------------------------- +

Use the ALTERTABLE command with the DROPFOREIGNKEY statement, as shown below:

Mysql> ALTERTABLEzooDROPFOREIGNKEYzoo_ibfk_1;

QueryOK, 1 rowaffected (0.11sec)

Records: 1 Duplicates: 0 Warnings: 0

To add a foreign key to a ready-made table, use the ALTERTABLE Statement of ADDFOREIGNKEY to specify an appropriate field as a foreign key:

Mysql> ALTERTABLEzooADDFOREIGNKEY (FK_species) REFERENCESspecies (id );

QueryOK, 1 rowsaffected (0.11sec)

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.

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