Foreign key constraints

1. Referential integrity

Referential integrity requires that a relationship does not allow references to nonexistent entities. The way to set referential integrity in MySQL is to use foreign key constraints. A foreign key is a combination of one or more columns used to establish and enforce links between data in two tables, controlling the data that can be stored in a foreign key table. For example, there are two tables, the student table and the score table, which are as follows.

Student Table:

Score:

The stu_NO column in the score table stores student numbers in the student table. When we set this field as a foreign key field in the score table, an error will be reported when inserting a non-existent student score. For example, insert a student score with stu_NO number 2021003, MySQL will report an error. This is because there is no student whose number is 2021003 in the student table. If you want to insert the student whose number is 2021003 into the score table, you must create the student whose number is 2021003 in the student table and then insert the student’s score into the score table.

Foreign key constraints

The previous section mentioned foreign key constraints. What are foreign key constraints? The foreign key constraint is used to hold the data of the primary table (parent table) and secondary table (child table). Only InnoDB storage engine in MySQL supports foreign key constraints. The syntax for foreign key constraints is as follows:

[CONSTRAINT [symbol]] FOREIGRN KEY [index_name] (col_name,...) REFERENCES tbl_name(col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
Copy the code

In syntax, reference_option refers to a foreign key constraint, which includes the following five options:

1. RESTRICT: Declines the deletion or update of the parent table.
2. CASCADE: Automatically deletes or updates matched rows in the child table when deleting or updating from the parent table.
3. SET NULL: Automatically sets the foreign key of the child table to NULL when the parent table is deleted or updated.
4. C. NO ACTION D. NO ACTION
5. SET DEFAULT: uses the DEFAULT constraint

Example: The score table for the student table above can be created like this:

Create a student tablecreate table student(
    stu_no varchar(30) not null primary key,
    name varchar(30),
    phone varchar(30)
) engine=InnoDB # create score table, set stu_NO as foreign keycreate table score(
    score_no int not null auto_increment primary key,
    stu_no varchar(30),
    gross_score int.foreign key(stu_no) references student(stu_no)
) engine=InnoDB
Copy the code