What is the use of mysql foreign keys?

mysql foreign key is mainly used to establish the relationship between the master table and the slave table, establish a connection for the data of the two tables, and constrain the consistency and integrity of the data in the two tables; its role is to keep the data consistent The main purpose is to control the data stored in foreign key tables. A table can have one or more foreign keys, and the foreign key can be null. If it is not null, the value of each foreign key must be equal to a certain value of the primary key in the main table.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

MySQL foreign key constraint (FOREIGN KEY) is a special field of the table, often used together with primary key constraints. For two tables with an associated relationship, the table where the primary key in the associated field is located is the primary table (parent table), and the table where the foreign key is located is the secondary table (child table).

The role of MySQL 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, foreign keys can only reference the values ​​of columns in the table!

Foreign keys are used to establish the association between the master table and the slave table, establish a connection for the data in the two tables, and constrain the consistency and integrity of the data in the two tables. For example, a fruit stall only has four kinds of fruits: apples, peaches, plums, and watermelons. Then, when you come to the fruit stall to buy fruits, you can only choose apples, peaches, plums, and watermelons. Other fruits are not available for purchase.

When a record is deleted from the main table, the corresponding record from the table must also be changed accordingly. A table can have one or more foreign keys, and the foreign key can be null. If it is not null, the value of each foreign key must be equal to a certain value of the primary key in the main table.

When defining foreign keys, you need to comply with the following rules:

• The main table must already exist in the database, or be the table currently being created. If it is the latter case, the master table and the slave table are the same table. Such a table is called a self-referential table, and this structure is called self-referential integrity.

• A primary key must be defined for the main table.

• The primary key cannot contain null values, but null values ​​are allowed in foreign keys. That is, as long as every non-null value of the foreign key appears in the specified primary key, the contents of the foreign key are correct.

• Specify the column name or a combination of column names after the table name of the main table. This column or combination of columns must be the primary key or candidate key of the primary table.

• The number of columns in the foreign key must be the same as the number of columns in the primary key of the main table.

• The data type of the column in the foreign key must be the same as the data type of the corresponding column in the primary key of the main table.

Add foreign key constraints when modifying the table

The prerequisite for adding foreign key constraints is: from the foreign key in the table The data in the column must be consistent with the data in the primary key column in the main table or there is no data.

The syntax format for adding foreign key constraints when modifying the data table is as follows:

ALTER TABLE <数据表名> ADD CONSTRAINT <外键名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

Example: Modify the data table tb_emp2, set the field deptId as a foreign key, and match the data Association with the primary key id of table tb_dept1

ALTER TABLE tb_emp2 ADD CONSTRAINT fk_tb_dept1 FOREIGN KEY(deptId)REFERENCES tb_dept1(id);

Delete foreign key constraint

When a table does not When a foreign key constraint is required, it needs to be removed from the table. Once the foreign key is deleted, the association between the master table and the slave table will be released.

The syntax format for deleting foreign key constraints is as follows:

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

Example: Delete the foreign key constraint fk_tb_dept1 in the data table tb_emp2

ALTER TABLE tb_emp2 DROP FOREIGN KEY fk_tb_dept1;

[Related recommendations: mysql video tutorial]

The above is the detailed content of What is the use of mysql foreign keys?. For more information, please follow other related articles on the PHP Chinese website!