How to use MySQL foreign key

Methods of using MySQL external keys: 1. The two tables must be InnoDB table types; 2. The domain used in the foreign key relationship must be an index type Index; 3. The domain used in the foreign key relationship must be of the same data type resemblance.

【Related learning recommendation: mysql tutorial(video)】

Methods of using MySQL external keys:

1. Only InnoDB tables can use foreign keys. Mysql defaults to MyISAM, which does not support foreign key constraints

2. The benefits of foreign keys: it can make two tables associated, ensure data consistency and realize some cascading operations.

3. The role of foreign keys:

To maintain data consistency and integrity, the main purpose is to control the data stored in foreign key tables. To associate two tables, the foreign key can only refer to the values ​​of the columns in the foreign table.

4. The prerequisite for establishing a foreign key:

The two tables must be of the InnoDB table type.

The field used in the foreign key relationship must be an index type (Index).

The field used in the foreign key relationship must be similar to the data type.

5. Steps of creation

Specify the primary key keyword: foreign key (column name).

Reference foreign key keywords: references (foreign key column name).

6. Event trigger limit: on delete and on update, the parameter cascade can be set (following the foreign key change).

restrict (restrict foreign key changes in the table), set

Null (set empty value), set Default (set default value).

[Default] no action

7. Example

OutTable table primary key id type int

Create a table with foreign keys:

The code is as follows:

create table temp(
 id int,
 name
 char(20),
 foreign key(id) references outTable(id) on delete cascade on update
 cascade);

Description: set the id column as a foreign key and refer to the id column of the outer table outTable as the value of the foreign key delete the corresponding column in this table and filter out when the value of the foreign key changes the corresponding column in this table The column value changes.

The code is as follows:

create table temp( id int, name char(20),
 foreign key(id) references outTable(id) on delete cascade on update
 cascade);

For more programming learning, please pay attention to the PHP training column!

The above is the detailed content of how to use MySQL external keys. For more information, please pay attention to other related articles on 1024programmer.com!