How to use MySQL external keys

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

【Related learning recommendations: mysql tutorial(Video)】

How to use MySQL foreign keys:

1. Only InnoDB type tables can use foreign keys. MySQL defaults to MyISAM. This type does not support foreign key constraints

2. Advantages of foreign keys: It can associate two tables, ensure data consistency and implement some cascading operations.

3. The role of foreign keys:

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

4. Prerequisites for establishing foreign keys:

The two tables must be of InnoDB table type.

Fields using foreign key relationships must be indexed (Index).

The fields using foreign key relationships 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 column name).

6. Event triggering restrictions: on delete and on update, parameter cascade can be set (following foreign key changes).

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

Null (set null 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. When the value of the foreign key is deleted, the corresponding column in this table is deleted. When the value of the foreign key is changed, the corresponding column in this table is changed. 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);

If you want to learn more about programming, 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 follow other related articles on the PHP Chinese website!