How to implement MySQL foreign key cascade

Introduction

MySQL foreign keys play a constraint role and ensure data integrity at the database level.
For example, using the CASCADE (cascade concatenation) type of foreign keys, when a child table (such as user_info) is associated with a parent table (such as user), when the parent table is updated or deleted, the child table will update or delete records. This process is a database level completed.
There are many database designs in early enterprise systems. Although it helps programmers save delete and update operations, it actually increases hidden rules, increases software complexity, and weakens performance.

So in application design, we should try our best to ensure data integrity at the application layer (such as using transaction processing mechanism), rather than at the database level.

The following is an introduction to MySQL’s foreign keys.

The only storage engine that supports foreign keys in MySQL is InnoDB. When creating a foreign key, it is required that the parent table must have a corresponding index. Sub tableThe corresponding index will also be automatically created when creating foreign key.

When creating an index, you can specify the corresponding operations on the child table when deleting or updating the parent table, including

• RESTRICT (restrict restriction)

• NO ACTION

• SET NULL

• CASCADE (Concatenation)

RESTRICT is the same as NO ACTION, which means that when the child table has related records, the parent table cannot be updated;
CASCADE means parent tableWhen updates or deletes , updates or deletes the corresponding records of the child table ;
SET NULL means that when the parent table updates or deletes, the corresponding fields of the child table is SET NULL.

Example

Because only the InnoDB engine allows the use of foreign keys, our data table must use the InnoDB engine.

Create database:

Create database test;

1. First create two tables stu,sc

create table stu(
sid int UNSIGNED primary key auto_increment,
name varchar(20) not null)
TYPE=InnoDB charset=utf8;

create table sc(
scid int UNSIGNED primary key auto_increment,
sid int UNSIGNED not null,
score varchar(20) default '0',
index (sid),   --外键必须加索引
FOREIGN KEY (sid) REFERENCES stu(sid) ON DELETE CASCADE ON UPDATE CASCADE)
TYPE=InnoDB charset=utf8;

–Note: Foreign keys must be indexed;

FOREIGN key(sid) sets the foreign key, and sets sid as the foreign key

REFERENCES stu(sid) reference function. Reference the sid in the stu table

ON DELETE CASCADE cascade delete
ON UPDATE CASCADE cascade update

2. Insert data into two tables

insert into stu (name) value ('zxf');
insert into stu (name) value ('ls');
insert into stu (name) value ('zs');
insert into stu (name) value ('ww');

insert into sc(sid,score) values ('1','98');
insert into sc(sid,score) values ('1','98');
insert into sc(sid,score) values ('2','34');
insert into sc(sid,score) values ('2','98');
insert into sc(sid,score) values ('2','98');
insert into sc(sid,score) values ('3','56');
insert into sc(sid,score) values ('4','78');
insert into sc(sid,score) values ('4','98');

Note: When inserting data into the sc table, if the inserted sid is 22, the insertion will fail, violating the foreign key constraint, because the foreign key sid
comes from the primary key of id in the stu table. That is, the id in stu does not have data equal to 22.

Cascade deletion: Delete the student with id 2 in the stu table, and the student’s grades in the sc table will also be cascade deleted

delete from stu where sid = '2';

Cascade update: The student with id 3 in the stu table is changed to id 6, and the corresponding id of the student in the sc table will also be updated in cascade

update stu set sid=6 where sid='3';

Note

When deleting a table, you must first delete the foreign key table (sc), and then delete the primary key table (stu)

The above picture violates the foreign key constraint and cannot be Delete

#The picture above shows normal deletion. Delete the sc table first, and then delete the stu table!

The above is the detailed content of How to implement MySQL foreign key cascade. For more information, please follow other related articles on the PHP Chinese website!