MySQL foreign Key Usage Details, MySQL Key Usage Details

MySQL foreign Key Usage Details, MySQL Key Usage Details

Recently, I started to create a laboratory management system. Because I have stored several tables, I want to maintain the association between tables. I studied the Foreign keys of MySQL.

(1) only InnoDB tables can use foreign keys. mysql defaults to MyISAM. This type does not support foreign key constraints.

(2) Advantages of Foreign keys: two tables can be associated to ensure data consistency and perform cascade operations;

(3) functions of Foreign keys:

Data consistency and integrity are maintained to control data stored in the foreign key table. Associate two tables. The foreign key can only reference the values of columns in the External table!

(4) prerequisites for creating a foreign key:

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 similar to the data type.

(5) Creation steps

Specify the primary key Keyword: foreign key (column name)

Reference foreign key Keyword: references <foreign key table name> (foreign key column name)

(6) event trigger restrictions: on delete and on update. You can set the parameters cascade (with foreign key changes), restrict (with foreign key changes in the external table), and set Null (with Null values ), set Default (set Default value), [Default] no action

(7) Example

OutTable table primary key id type int

Create a table with foreign keys:

Copy codeThe Code is as follows:
Create table temp (
Id int,
Name char (20 ),
Foreign key (id) references outTable (id) on delete cascade on update cascade );

Note: set the id column as the foreign key reference External table id column as the foreign key value Delete this table corresponding to the column sieve when the foreign key value changes the corresponding column value in this table.

Copy codeThe Code is as follows:
Create table temp (id int, name char (20), foreign key (id) references outTable (id) on delete cascade on update cascade );

Disadvantages: optimization like Query cache and index cache does not work for InnoDB tables during MySQL optimization, also, synchronous replication in the overall database architecture does not take effect for InnoDB tables. For example, for product tables similar to the core tables in the database, try not to use foreign keys, if the product library must be synchronized during synchronization, the foreign key cannot be used, and the optimization does not work for it, so it is not worth the candle. The foreign key is designed to ensure data integrity, please use a program to achieve this purpose rather than a foreign key. Remember!

The above is all the content of this article. I hope you will like it.

Please take a moment to share your article with your friends or leave a comment. Thank you for your support!