Detailed explanation of MySQL foreign key constraints

1. Create the power table (parent table) country
create table country (
id int not null,
name varchar(30),
primary key(id)
);

2. Insert records insert into country values(1,'Western Europe');
insert into country values(2,'Maya');
insert into country values(3,'Sicily');

3. Create a arms table (sub-table) and establish constraints create table solider (
id int not null,
name varchar(30),
country_id int,
primary key(id),
foreign key(country_id) references country(id) on delete cascade on update cascade,
);

4. Reference integrity test insert into solider values(1,'Western European trainee infantry',1);
#insert successfully insert into solider values(2,'Maya short spearman',2);
#insert successfully insert into solider values(3,'Sicily Norman Knights',3)
#insert successfullyinsert into solider values(4,'French Swordsman',4);
#Insert failed because there is no faction with id 4 in the country table. 5. Constraint method test insert into solider values(4, 'Maya Tiger Warriors', 2);
#Successfully inserted delete from country where id=2;
#This will cause the records with id 2 and 4 in the solider table to be deleted at the same time, because this faction no longer exists in the parent table, so the corresponding arms will naturally disappear update country set id=8 where id=1;
#As a result, all records in the solider table with country_id 1 will also be modified to 8

1. Create a soldier table (sub-table) and establish a constraint relationship drop table if exists solider;
create table solider(
id int not null,
name varchar(30),
country_id int,
primary key(id),
foreign key(country_id) references country(id) on delete set null on update set null,
);

2. Reference integrity test insert into solider values(1,'Western European trainee infantry',1);
#insert successfully insert into solider values(2,'Maya short spearman',2);
#insert successfully insert into solider values(3,'Sicily Norman Knights',3)
#insert successfullyinsert into solider values(4,'French Swordsman',4);
#Insert failed because there is no faction with id 4 in the country table. 3. Constraint method test insert into solider values ​​(4, 'Sicilian Archer', 3);
#Successfully inserted delete from country where id=3;
#This will cause the records with id 3 and 4 in the solider table to be set to NULL
update country set id=8 where id=1;
# Causes all records in the solider table where country_id is 1 to be set to NULL

1. Create a soldier table (sub-table) and establish a constraint relationship drop table if exists solider;
create table solider(
id int not null,
name varchar(30),
country_id int,
primary key(id),
foreign key(country_id) references country(id) on delete RESTRICT on update RESTRICT,
);

2. Reference integrity test insert into solider values(1,'Western European trainee infantry',1);
#insert successfully insert into solider values(2,'Maya short spearman',2);
#insert successfully insert into solider values(3,'Sicily Norman Knights',3)
#insert successfullyinsert into solider values(4,'French Swordsman',4);
#Insert failed because there is no faction with id 4 in the country table 3. Constraint method test insert into solider values(4, 'Western European Knights', 1);
#Successfully inserted delete from country where id=1;
#An error occurred. There are related records in the child table, so the corresponding records in the parent table cannot be deleted. That is, the arms table also has arms belonging to Western Europe, so the Western European forces in the parent table cannot be deleted alone. update country set id=8 where id=1;
#Error, there are related records in the child table, so the parent table cannot be modified