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
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 thephp trainingcolumn!
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!