The use of foreign keys and foreign key constraint strategies in MySQL

1. The concept of foreign key constraints

Foreign key constraint (FOREIGN KEY, abbreviated as FK is a concept of database design, it ensures that the relationship between two tables maintains data consistency and integrity.

Foreign key means that a field in the table depends on the value of a field in another table, and the dependent field must have a primary key constraint or a unique constraint. The dependent data table is called the main table (parent table ), the table on which foreign key constraints are set is called a child table or slave table.

For example: There are now two tables, the student table and the class table. The value of the class number field in the student table depends on the class number field in the class table.

Main table (parent table): class table-class number-primary key
From table (subtable): student table-class number-foreign key

2. Problems that may occur if foreign key constraints are not used

First create these two tables, the student table and the class table, then do not add the foreign key constraint of the class number, and insert some initialization data.

(1) Creating tables and initializing data

--Create class table
create table t_class(
cno int(4) primary key auto_increment, -- class number auto-increment primary key
cname varchar(12) not null -- the class name is not null
);

-- View class schedule
select * from t_class;

-- Insert class information
insert into t_class values (null,'java01 class') ,(null,'python01 class'),(null,'big data 01 class');


--Create a student table (without foreign key constraints)
create table t_student(
sno int(4) primary key auto_increment, -- student number is the primary key auto-increment
sname varchar(5) not null , -- the name is a non-null constraint
age int(3) check (age >= 18 and age <= 55), -- age is between 18-55 check constraints
sex char(1) default 'male' check (sex = 'male' || sex = 'female'), -- the gender defaults to male, and can only be male or female
cno int(4)
);

-- View student table
select * from t_student;

--Insert student information, numbering starts from 1001
insert into t_student values (1001,'Zhang San',21,'Male',1);
insert into t_student values (null,'李思',21,'Male',1);
insert into t_student values (null,'王五',21,'male',2);
insert into t_student values (null,'Zhao Liu',21,'Male',3);
insert into t_student values (null,'Cui Qi',21,'Male',4); # Inserting a non-existent class number is still successful
delete from t_student where cno = 4; -- delete wrong data

In the absence of foreign key constraints, the class number in the student table (slave table) can be inserted arbitrarily, causing data inconsistency.

(2) Update and delete table data

1. Update the class number of java01 class to 9. The data in the student table is not updated and the data is inconsistent again.

--Update class table data
-- Change the number of java01 class to No. 9
update t_class set cno = 9 where cname = 'java01 class' and cno = 1;
select * from t_class; -- successfully modified in the class table
select * from t_student; -- The class number of students in class java01 in the student table has not been changed to 9

2. Delete the class information of class java01. The student information of class java01 in the student table still remains unchanged, and the data is inconsistent.

--Delete table data
-- Delete the class information of java01 class
delete from t_class where cno = 9;
select * from t_class;
select * from t_student;

(3) Summary

Without the use of foreign key constraints, additions, deletions, and modifications will affect data inconsistency and integrity.

3. Use foreign key constraints and foreign key strategies

(1) Creating tables and initializing data

--Create class table
create table t_class(
cno int(4) primary key auto_increment,
cname varchar(12) not null
);

-- View class schedule
select * from t_class;

-- Insert class information
insert into t_class values (null,'java01 class') ,(null,'python01 class'),(null,'big data 01 class');

--Create the student table. Foreign key constraints only have table-level constraints.
create table t_student(
sno int(4) primary key auto_increment, -- student number is the primary key auto-increment
sname varchar(5) not null , -- the name is a non-null constraint
age int(3) check (age >= 18 and age <= 55), -- age is between 18-55 check constraints
sex char(1) default 'male' check (sex = 'male' || sex = 'female'), -- the gender defaults to male, and can only be male or female
cno int(4),
  constraint fk_stu_classcno foreign key (cno) references t_class (cno) -- Add foreign key constraints
);

-- View student table
select * from t_student;

-- Insert student information 1. (In the absence of foreign key constraints, we can actually insert any class number)
insert into t_student values (1001,'Zhang San',21,'Male',1); -- The first id inserted here will affect the following ids, starting from 1001
insert into t_student values (null,'李思',21,'Male',1);
insert into t_student values (null,'王五',21,'male',2);
insert into t_student values (null,'Zhao Liu',21,'Male',3);
-- > 1452 - Cannot add or update a child row: a foreign key constraint fails (`mytestdb`.`t_student`, CONSTRAINT `fk_stu_classcno` FOREIGN KEY (`cno`) REFERENCES `t_class`
insert into t_student values (null,'Cui Qi',21,'Male',4); # Insert a non-existent class number and report an error

When inserting a non-existent class number here, save it directly because the foreign key constraints help us make restrictions.

1452 – Cannot add or update a child row: a foreign key constraint fails (mytestdb.t_student, CONSTRAINT fk_stu_classcno FOREIGN KEY (cno ) REFERENCES t_class

(2) Update and delete data

When updating and deleting data, involving foreign key strategies, we first try to change and delete some data.

1. Update the class number of java01 class to 9

update t_class set cno = 9 where cno = 1;

Error: update t_class set cno = 9 where cno = 1
1451 – Cannot delete or update a parent row: a foreign key constraint fails (mytestdb.t_student, CONSTRAINT fk_stu_classcno FOREIGN KEY (cno ) REFERENCES t_class (cno))
Time: 0.002s

Because foreign key constraints have been added, the two tables have already established a relationship. In order to maintain data consistency, when the class number in the class table is changed, the number in the original student table also needs to be changed. This is not executed by default. Yes, some conditions need to be added.

2. Delete class information of java01 class

delete from t_class where cno = 1;

Error: delete from t_class where cno = 1
1451 – Cannot delete or update a parent row: a foreign key constraint fails (mytestdb.t_student, CONSTRAINT fk_stu_classcno FOREIGN KEY (cno ) REFERENCES t_class (cno))
Time: 0.008s
The same reason as the error reported during update is also due to foreign keys.

(3) Foreign key strategy

Tips: The cascade operation and the set null operation can be added when adding foreign key constraints when creating the table. I am here to facilitate direct modification of the foreign key constraint strategy. Flexibly combine set null and cascade according to the business

1. no action does not allow the operation – the default foreign key strategy (modify the data in the slave table to Null, and then modify the data in the master table) This method is silly, it is hard-written SQL strong>

-- Modify the class number of java01 class to 9
-- 1. First modify the data of class java01 in the student table to null
update t_student set cno = null where cno = 1;
-- 2. Update data in the class table
update t_class set cno = 9 where cno = 1;
-- 3. Update data in the student table
update t_student set cno = 9 where sno in (1001,1002);

2. Cascade cascading operation affects the foreign key information of the slave table when operating the master table (first delete the previous foreign key constraints and then re-add the foreign key constraints).

-- 1. Delete the original foreign key constraints
alter table t_student drop foreign key fk_stu_classcno;
-- 2. Add a new foreign key constraint strategy --- Perform cascading operations when updating and deleting
alter table t_student add constraint fk_stu_classcno foreign key (cno) references t_class (cno) on update cascade on delete cascade;
-- 3. Update data and change the class number of java01 class to 19
update t_class set cno = 19 where cno = 9;
-- 4. Query verification
select * from t_class;
select * from t_student;

3. set null operation affects the foreign key information of the slave table when operating the master table. The corresponding value of the slave table is the null value (first delete the previous foreign key constraint and then re-add the new foreign key constraint ).

-- 1. Delete the original foreign key constraints
alter table t_student drop foreign key fk_stu_classcno;
-- 2. Add a new foreign key constraint strategy --- Set the foreign key values from the table to null when updating and deleting.
alter table t_student add constraint fk_stu_classcno foreign key (cno) references t_class (cno) on update set null on delete set null;
-- 3. Update data and change the class number of java01 class to 29
update t_class set cno = 29 where cno = 19;
-- 4. Manually update student data from the table
update t_student set cno = 29 where sno in (1001,1002);
-- 4. Query verification
select * from t_class;
select * from t_student;

The following is an example of adding a foreign key constraint strategy when creating a table:

--Create class table
create table t_class(
cno int(4) primary key auto_increment,
cname varchar(12) not null
);

-- Insert class information
insert into t_class values (null,'java01 class') ,(null,'python01 class'),(null,'big data 01 class');

-- View class schedule
select * from t_class;

--Create student table. Foreign key constraints only have table-level constraints.
create table t_student(
sno int(4) primary key auto_increment, -- student number is the primary key auto-increment
sname varchar(5) not null , -- the name is a non-null constraint
age int(3) check (age >= 18 and age <= 55), -- age is between 18-55 check constraints
sex char(1) default 'male' check (sex = 'male' || sex = 'female'), -- the gender defaults to male, and can only be male or female
cno int(4),
-- When the data in the master table is updated, the slave table data is cascade updated. When the master table data is deleted, the slave table data is set to Null.
  constraint fk_stu_classcno foreign key (cno) references t_class (cno) on update cascade on delete set null
);

-- Insert student information 1. (In the absence of foreign key constraints, we can actually insert any class number)
insert into t_student values (1001,'Zhang San',21,'Male',1); -- The first id inserted here will affect the following ids, starting from 1001
insert into t_student values (null,'李思',21,'Male',1);
insert into t_student values (null,'王五',21,'male',2);
insert into t_student values (null,'Zhao Liu',21,'Male',3);

-- View student table
select * from t_student;

--Update the class number of java01 class to 9
update t_class set cno = 9 where cno = 1;
select * from t_class;
select * from t_student;

-- Delete big data class 01
delete from t_class where cno = 3;
select * from t_class;
select * from t_student;

4. Summary

When using foreign key constraints, you need to pay attention to the following points:

• Foreign key constraints only have table-level constraints, not column-level constraints.
• Foreign key constraints affect table performance because the database must perform additional checks for each write operation.
• If you try to insert a row that does not meet the foreign key constraints, the database will throw an error.
• Customize different foreign key strategies according to different business needs (cascade || set null) “constraint fk_stu_classcno foreign key (cno) references t_class (cno) on update cascade on delete set null”