Summary of MySQL foreign key constraint knowledge points

This article brings you relevant knowledge about mysql, which mainly organizes the related issues of foreign key constraints. Foreign key constraints (Foreign Key) are between two data tables in the database. A connection established by a certain column. This connection is usually caused by fields with exactly the same meaning in actual scenarios. Let’s take a look at them together. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

1. The role of MySQL foreign key constraints

Foreign key constraints ( Foreign Key) is a relationship established by a column between two data tables in the database. This connection is usually caused by fields with exactly the same meaning in actual scenarios. Through the introduction of foreign key constraints, MySQL can make the data integrity in the data table stronger and more consistent with the display situation. Below, I give an example to illustrate the role of MySQL foreign key constraints.
If we build a database for the university student performance management system, there are two tables. One table is the student table, which stores the student's student number, name, gender, department and other information, and the other table is the grade table. Information such as student ID number, course number, test scores, etc. are stored. In this way, a foreign key constraint will be established between the two tables through the student number. It is natural for us to think that the student ID number in the transcript table depends on the existence of the student ID number in the student table. If a student graduates, or drops out, and is deleted from the student table, then his related grades are not necessary in the transcript table. exists. Before creating a foreign key relationship, these two tables exist completely independently. We can forcibly insert a related score of a non-existent student into the score table, or we can forcibly delete a student in the student table, regardless of the score information. Whether it exists in the score sheet. However, after establishing a foreign key relationship, the MySQL database will constrain the above two behaviors. Every time data is inserted or deleted, the data integrity will be checked, so that our operations must conform to the actual situation.

2. Creation of foreign key constraints

(1) Conditions for creating foreign key constraints

To create a foreign key in MySQL database, the following four things need to be met conditions, otherwise it will be rejected by the MySQL database:
1. The table and columns used to create the foreign key exist
2. The columns that make up the foreign key exist in the index
3. The engine of the data table must be specified as InnoDB
4. The data types of foreign key fields and related fields must be consistent

(2) Create foreign key constraints when creating a data table

Create when creating a data table For foreign key constraints, you only need to use the foreign key keyword to specify the foreign key fields of this table after the create statement of the data table, use the reference keyword to specify the related fields of the related table, and clearly constrain the behavior.
An example of a SQL statement to create a foreign key constraint is as follows:

create table student (id int(8),name varchar(20),department varchar(20) ,index (id))ENGINE=InnoDB;
create table grade (Sid int(8),Cid int(10),score int,index(Sid),foreign key (Sid) references student(id) on  delete  restrict on update cascade)ENGINE=InnoDB;

In the above SQL statement, on delete restrict indicates that the foreign key will restrict the deletion operation when deleting, while on update cascade refers to the name The update operation will be synchronized when updating.

(3) Add foreign key constraints after creating the data table

Similarly, MySQL also supports adding foreign key constraints after creating the data table. In the above example, we first delete the grade table, and then create the grade table. We do not create foreign keys now. Try adding foreign keys after creating the grade table. The relevant SQL commands are as follows:

drop table grade;
create table grade(Sid int(8),Cid int(10),score int);
alter table grade add index(Sid);
alter table grade add foreign key (Sid) references student(id) on delete restrict on update cascade;

The execution results are as follows:

3. Demonstration of foreign key constraint function

Next, let’s test the function of foreign key constraint. First, try to insert a non-existing key into the grade table. The student's grades were found to be rejected:

Afterwards, I tried to delete the students whose grades existed in the student table and found to be rejected:

Immediately afterwards , we tested the MySQL foreign key constraint cascade update function and found that if the data in the student table is changed, the grade table will also change, as shown below:

Recommended learning: mysql video tutorial

The above is the detailed content of Summary of MySQL foreign key constraint knowledge points. For more information, please follow other related articles on the PHP Chinese website!