mysql 外键

最新推荐文章于 2024-05-28 23:35:44 发布

DXT00

最新推荐文章于 2024-05-28 23:35:44 发布

阅读量227

点赞数

分类专栏： MySQL

MySQL 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

转自：https://www.cnblogs.com/programmer-tlh/p/5782451.html

https://www.cnblogs.com/bhlsheji/p/5332910.html

外键定义：

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。
ON DELETE、ON UPDATE表示事件触发限制，可设参数：
RESTRICT（限制外表中的外键改动）
CASCADE（跟随外键改动）
SET NULL（设空值）
SET DEFAULT（设默认值）
NO ACTION（无动作，默认的）

外键约束含义:

在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时，父表的行为取决于：在定义子表的外键时指定的on update/on delete子句。

关键字

含义

CASCADE

删除包含与已删除键值有参照关系的所有记录

SET NULL

修改包含与已删除键值有参照关系的所有记录，使用NULL值替换(只能用于已标记为NOT NULL的字段)

RESTRICT

拒绝删除要求，直到使用删除键值的辅助表被手工删除，并且没有参照时(这是默认设置，也是最安全的设置)

NO ACTION

啥也不做

MySQL中NO ACTION和RESTRICT的功能是相同的！

mysql> create table dage(
    -> id int auto_increment,
    -> name varchar(30),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.15 sec)

mysql> create table xiaodi(
    -> id int auto_increment,
    -> dage_id int,
    -> primary key(id),
    -> foreign key(dage_id) references dage(id)
    -> );

mysql> insert into dage select 1,'dxt00';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from dage;
+----+-------+
| id | name  |
+----+-------+
|  1 | dxt00 |
+----+-------+
1 row in set (0.00 sec)

mysql> insert into xiaodi select 1,1,'maomao';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

delete和update ---RESTRICT

插入2号大哥会出错，因为dage_id=2 并不存在

mysql> insert into xiaodi select 2,2,'maomao';
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`foriegn`.`xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))

不能删除dage ，因为xiaodi的外键关联着dage的id.

delete from dage where id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`foriegn`.`xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))

delete和update级联--cascade

cascade之后，dage删除，xiaodi也会跟着删除

dage update了id,小弟的 foreign key(dage_id)也会跟着update

把xiaodi 的外键设成与dage级联，再试一下：

mysql> alter table xiaodi add foreign key(dage_id) references dage(id) on delete cascade on update cascade;
Query OK, 1 row affected (0.18 sec)
Records: 1  Duplicates: 0  Warnings: 0

这时xiaodi的dage_id有两种foreign key所以要把原来是删掉，即`xiaodi_ibfk_1`

mysql> show create table xiaodi\G;
*************************** 1. row ***************************
       Table: xiaodi
Create Table: CREATE TABLE `xiaodi` (
  `id` int(11) DEFAULT NULL,
  `dage_id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  KEY `dage_id` (`dage_id`),
  CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`),
  CONSTRAINT `xiaodi_ibfk_2` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)


mysql> alter table xiaodi drop foreign key xiaodi_ibfk_1;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0



mysql> show create table xiaodi\G;
*************************** 1. row ***************************
       Table: xiaodi
Create Table: CREATE TABLE `xiaodi` (
  `id` int(11) DEFAULT NULL,
  `dage_id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  KEY `dage_id` (`dage_id`),
  CONSTRAINT `xiaodi_ibfk_2` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

这时候就可以删除id=1的大哥了，由于xiaodi与大哥级联，自然也被删掉了 = =

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from dage where id =1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from dage;
Empty set (0.00 sec)

mysql> select * from xiaodi;
Empty set (0.00 sec)

mysql>

update例子：

mysql> show create table xiaodi\G;
*************************** 1. row ***************************
       Table: xiaodi
Create Table: CREATE TABLE `xiaodi` (
  `id` int(11) DEFAULT NULL,
  `dage_id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  KEY `dage_id` (`dage_id`),
  CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> update dage
    -> set id = 3 where name = 'dun';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dage;
+----+-------+
| id | name  |
+----+-------+
|  1 | dxt00 |
|  3 | dun   |
+----+-------+
2 rows in set (0.00 sec)

mysql> select * from xiaodi;
+------+---------+--------+
| id   | dage_id | name   |
+------+---------+--------+
|    1 |       1 | maomao |
|    2 |       3 | dundun |
+------+---------+--------+
2 rows in set (0.00 sec)

delete和update--set null

dage某一行删除后，xiaodi对应的dage_id变成null

mysql> show create table xiaodi\G;
*************************** 1. row ***************************
       Table: xiaodi
Create Table: CREATE TABLE `xiaodi` (
  `id` int(11) DEFAULT NULL,
  `dage_id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  KEY `dage_id` (`dage_id`),
  CONSTRAINT `xiaodi_ibfk_2` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> alter table xiaodi drop foreign key xiaodi_ibfk_2;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table xiaodi add foreign key (dage_id) references dage(id) on delete set null on update set null;
Query OK, 1 row affected (0.14 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table xiaodi\G;
*************************** 1. row ***************************
       Table: xiaodi
Create Table: CREATE TABLE `xiaodi` (
  `id` int(11) DEFAULT NULL,
  `dage_id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  KEY `dage_id` (`dage_id`),
  CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> delete from dage where id =1;
Query OK, 1 row affected (0.05 sec)

mysql> select * from dage;
Empty set (0.00 sec)

mysql> select * from xiaodi;
+------+---------+--------+
| id   | dage_id | name   |
+------+---------+--------+
|    1 |    NULL | maomao |
+------+---------+--------+
1 row in set (0.00 sec)

可以看到删除dage id =1时，xiaodi的dage_id 变成了NULL

delete和update--no action

mysql> show create table xiaodi\G;
*************************** 1. row ***************************
       Table: xiaodi
Create Table: CREATE TABLE `xiaodi` (
  `id` int(11) DEFAULT NULL,
  `dage_id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  KEY `dage_id` (`dage_id`),
  CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> alter table xiaodi drop foreign key xiaodi_ibfk_1;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table xiaodi add foreign key(dage_id) references dage(id) on delete no action on update no action;
Query OK, 1 row affected (0.10 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table xiaodi\G;
*************************** 1. row ***************************
       Table: xiaodi
Create Table: CREATE TABLE `xiaodi` (
  `id` int(11) DEFAULT NULL,
  `dage_id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  KEY `dage_id` (`dage_id`),
  CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

no action 删除会发生错误！

mysql> select * from dage;
+----+-------+
| id | name  |
+----+-------+
|  1 | dxt00 |
+----+-------+
1 row in set (0.00 sec)

mysql> select * from xiaodi;
+------+---------+--------+
| id   | dage_id | name   |
+------+---------+--------+
|    1 |       1 | maomao |
+------+---------+--------+
1 row in set (0.00 sec)

mysql> delete from dage where id =1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`foriegn`.`xiaodi`, CONSTRAINT `xiaodi_ibfk_2` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))

DXT00

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 外键

转自：https://www.cnblogs.com/programmer-tlh/p/5782451.htmlhttps://www.cnblogs.com/bhlsheji/p/5332910.html外键定义：[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...) REFERENCES tbl_name...
复制链接

扫一扫

专栏目录