MySQL supports foreign key storage engine only InnoDB, when creating foreign keys, requires the parent table must have a corresponding index, the child table when creating foreign keys will also automatically create the corresponding index. When you create an index, you can specify the corresponding actions on the child table when the parent table is deleted, updated, including restrict, noaction, SET null, and Cascade. where restrict and no action are the same, the parent table cannot be updated if the child table is associated with records, Cascade means that the parent table updates or deletes the child table corresponding records when updating or deleting, and SET NULL indicates that the parent table is updated or deleted. The corresponding field of the child table is set NULL. The following is a news table explaining the structure of the news database as follows:
Create Database yynews;
Use Yynews;
#新闻类别表
CREATE TABLE Categories (
catId int Auto_increment primary KEY,
CatName varchar (+) NOT null unique
) CharSet UTF8;
#新闻表:
CREATE TABLE News (
newsId int Auto_increment primary KEY,
Title varchar (+) is not null unique,
Content text NOT NULL,
Createtime timestamp NOT NULL,
catId int
) CharSet UTF8;
#添加外键的引用
ALTER TABLE NEWS ADD constraint foreign key (CATID) references categories (CATID);
#评论表:
CREATE TABLE Comments (
Commid int Auto_increment primary KEY,
Content text NOT NULL,
Createtime timestamp NOT NULL,
NewsId int NOT NULL,
Userip char (NOT NULL)
) CharSet UTF8;
#添加外键的引用
ALTER TABLE comments Add constraint foreign key (NEWSID) references News (NEWSID);
#插入测试数据
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, ' 127.0.0.1 ');
Insert into Comments (Content,createtime,newsid,userip) VALUES ("You", Now (), 2, ' 127.0.0.1 ');
Insert into Comments (Content,createtime,newsid,userip) VALUES ("You", Now (), 3, ' 127.0.0.1 ');
Insert into Comments (Content,createtime,newsid,userip) VALUES ("You", Now (), 1, ' 127.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 | 2015-05-19 15:22:53 | 1 |
| 2 | Test2 | Test2 | 2015-05-19 15:22:53 | 2 |
| 3 | Test3 | Test3 | 2015-05-19 15:22:53 | 1 |
+--------+-------+---------+---------------------+-------+
3 Rows in Set (0.00 sec)
Mysql> SELECT * from comments;
+--------+---------+---------------------+--------+-----------+
| Commid | Content | Createtime | NewsId | Userip |
+--------+---------+---------------------+--------+-----------+
| 1 | You | 2015-05-19 15:22:53 | 1 | 127.0.0.1 |
| 2 | You | 2015-05-19 15:22:53 | 2 | 127.0.0.1 |
| 3 | You | 2015-05-19 15:22:53 | 3 | 127.0.0.1 |
| 4 | You | 2015-05-19 15:22:54 | 1 | 127.0.0.1 |
+--------+---------+---------------------+--------+-----------+
4 rows in Set (0.00 sec)
When no cascade operation has been added, deleting the associated data will cause an error.
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 '))
Database error tells you that a foreign key has blocked your operation. So we can add cascading operations. You can also specify cascading operations when you create the database again
As follows:
#级联操作
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;
#上面这句的这两个语句就是在添加外键的时候为该表和表之间添加级联操作, that is, when the data table is deleted or updated, the
Related tables are also updated or deleted at the same time.
For example:
Mysql> Delete from categories where catid=1;
Query OK, 1 row affected (0.03 sec)
We removed the category CATID to 1 data, namely: entertainment news, then the news in the entertainment press data double will be deleted, the news is deleted, the news comments will be deleted at the same time.
As shown below:
Mysql> SELECT * from news;
+--------+-------+---------+---------------------+-------+
| NewsId | Title | Content | Createtime | CatId |
+--------+-------+---------+---------------------+-------+
| 2 | Test2 | Test2 | 2015-05-19 15:17:03 | 2 |
+--------+-------+---------+---------------------+-------+
1 row in Set (0.00 sec)
Mysql> SELECT * from comments;
+--------+---------+---------------------+--------+-----------+
| Commid | Content | Createtime | NewsId | Userip |
+--------+---------+---------------------+--------+-----------+
| 2 | You | 2015-05-19 15:17:03 | 2 | 127.0.0.1 |
+--------+---------+---------------------+--------+-----------+
1 row in Set (0.00 sec)
MySQL cascade operation (instance)
