Mysql cascade operations (instances) and mysql-level operation instances
MySQL only supports the InnoDB Storage engine for Foreign keys. When creating foreign keys, the parent table must have corresponding indexes, when a sub-Table creates a foreign key, the corresponding index is automatically created. When creating an index, you can specify operations for the child table When deleting or updating the parent table, including RESTRICT, NOACTION, set null, and CASCADE. The RESTRICT and no action are the same, which means that the parent table cannot be updated when the sub-table has associated records. CASCADE indicates that when the parent table is updated or deleted, update or delete the corresponding records of the sub-table; set null indicates that when the parent table is updated or deleted, the corresponding fields of the sub-table are set null. The following is a news table. The structure of the News Database is as follows:
Create database yynews;
Use yynews;
# News category table
Create table categories (
CatId int AUTO_INCREMENT primary key,
CatName varchar (40) not null unique
) Charset utf8;
# News table:
Create table news (
NewsId int AUTO_INCREMENT primary key,
Title varchar (100) not null unique,
Content text not null,
CreateTime timestamp not null,
CatId int
) Charset utf8;
# Add a foreign key reference
Alter table news add constraint foreign key (catid) references categories (catid );
# Comment table:
Create table comments (
CommId int AUTO_INCREMENT primary key,
Content text not null,
CreateTime timestamp not null,
NewsId int not null,
UserIP char (15) not null
) Charset utf8;
# Add a foreign key reference
Alter table comments add constraint foreign key (newsid) references news (newsid );
# Insert Test Data
Insert into categories (catname) values ("Entertainment News ");
Insert into categories (catname) values ("International News ");
Insert into news (title, content, createTime, catId) values ('test1', 'test1', now (), 1 );
Insert into news (title, content, createTime, catId) values ('test2', 'test2', now (), 2 );
Insert into news (title, content, createTime, catId) values ('test3', 'test3', now (), 1 );
Insert into comments (content, createTime, newsId, userIP) values ('you', now (), 1, '2017. 0.0.1 ');
Insert into comments (content, createTime, newsId, userIP) values ('you', now (), 2, '2017. 0.0.1 ');
Insert into comments (content, createTime, newsId, userIP) values ('you', now (), 3, '2017. 0.0.1 ');
Insert into comments (content, createTime, newsId, userIP) values ('you', now (), 1, '2017. 0.0.1 ');
As follows:
Mysql> select * from categories;
+ ------- + -------------- +
| CatId | catName |
+ ------- + -------------- +
| 2 | international news |
| 1 | entertainment news |
+ ------- + -------------- +
2 rows in set (0.00 sec)
Mysql> select * from news;
+ -------- + ------- + --------- + ------------------- + ------- +
| NewsId | title | content | createTime | catId |
+ -------- + ------- + --------- + ------------------- + ------- +
| 1 | test1 | test1 | 15:22:53 | 1 |
| 2 | test2 | test2 | 15:22:53 | 2 |
| 3 | test3 | test3 | 15:22:53 | 1 |
+ -------- + ------- + --------- + ------------------- + ------- +
3 rows in set (0.00 sec)
Mysql> select * from comments;
+ -------- + --------- + --------------------- + -------- + ----------- +
| CommId | content | createTime | newsId | userIP |
+ -------- + --------- + --------------------- + -------- + ----------- +
| 1 | you | 15:22:53 | 1 | 127.0.0.1 |
| 2 | you | 15:22:53 | 2 | 127.0.0.1 |
| 3 | you | 15:22:53 | 3 | 127.0.0.1 |
| 4 | you | 15:22:54 | 1 | 127.0.0.1 |
+ -------- + --------- + --------------------- + -------- + ----------- +
4 rows in set (0.00 sec)
If no cascade operation is added, an error is returned when you delete the associated data.
Mysql> delete from categories where catid = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ('yynews '.
'Comments', CONSTRAINT 'comments _ ibfk_1 'foreign key ('newsid') REFERENCES 'News' ('newsid '))
A Database Error tells you that a foreign key has blocked your operation. Therefore, you can add cascade operations. You can also specify cascade operations when creating a database.
As follows:
# Cascade operations
Alter table news add constraint foreign key (catid) references categories (catid) on delete cascade
On update cascade;
Alter table comments add constraint foreign key (newsid) references news (newsid) on delete cascade
On update cascade;
# The above two statements add cascade operations between the table and the table when adding a foreign key, that is, when the data table is deleted or updated
Associated tables are also updated or deleted.
For example:
Mysql> delete from categories where catid = 1;
Query OK, 1 row affected (0.03 sec)
We deleted the data whose category is catid 1, that is, entertainment news. The data double in news about entertainment news will be deleted, while the news will be deleted, comments in the news will also be deleted.
As follows:
Mysql> select * from news;
+ -------- + ------- + --------- + ------------------- + ------- +
| NewsId | title | content | createTime | catId |
+ -------- + ------- + --------- + ------------------- + ------- +
| 2 | test2 | test2 | 15:17:03 | 2 |
+ -------- + ------- + --------- + ------------------- + ------- +
1 row in set (0.00 sec)
Mysql> select * from comments;
+ -------- + --------- + --------------------- + -------- + ----------- +
| CommId | content | createTime | newsId | userIP |
+ -------- + --------- + --------------------- + -------- + ----------- +
| 2 | you | 15:17:03 | 2 | 127.0.0.1 |
+ -------- + --------- + --------------------- + -------- + ----------- +
1 row in set (0.00 sec)
