mysql基础(35)_外键约束

最新推荐文章于 2022-09-16 09:49:05 发布

风之舞_yjf

最新推荐文章于 2022-09-16 09:49:05 发布

阅读量391

点赞数

分类专栏： MySQL 文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/no15870678621/article/details/116211802

版权

MySQL 专栏收录该内容

55 篇文章 3 订阅

订阅专栏

外键约束

外键约束：通过建立外键关系之后，对主表和从表都会有一定的数据约束效率。

约束的基本概念：
1、当一个外键产生时：外键所在的表(从表)会受制于主表数据的存在从而导致数据不能进行某些不符合规范的操作(不能插入主表不存在的数据)
2、如果一张表被其他表外键引入，那么该表的数据操作就不能那么随意，必须保证从表数据的有效性(不能随便删除一个被从表引入的记录)

-- 上节内容已经知道了：外键列只能插入参照列(主表)存在的值，在上节内容基础上设置了字符集然后清空内容：
mysql> select * from foreign_key;
Empty set (0.00 sec)

mysql> insert into foreign_key values(null,'张三','1');
Query OK, 1 row affected (0.04 sec)

mysql> insert into foreign_key values(null,'李四','2');
Query OK, 1 row affected (0.13 sec)

mysql> select * from foreign_key;
+----+------+----------+
| id | name | class_id |
+----+------+----------+
|  1 | 张三 |        1 |
|  2 | 李四 |        2 |
+----+------+----------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | 小明 |
|  2 | 小可 |
|  3 | 小艾 |
|  4 | 小新 |
+----+------+
4 rows in set (0.00 sec)

-- 主表id=1字段信息被foreign_key表外键引入，因此不能随意删除，所以提示错误！
mysql> delete from student where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`foreign_key`, CONSTRAINT `foreign_key_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `student` (`id`))

-- 经过试验，没引入的id=4可以删掉的
mysql> delete from student where id=4;
Query OK, 1 row affected (1.77 sec)

mysql> insert into student values(4,'小新');
Query OK, 1 row affected (0.12 sec)

外键约束的概念

可以在创建外键的时候，对外键约束进行选择性的操作。
基本语法：add foreign key(外键字段) references 主表(主键) on 约束模式;

约束模式有三种：
1、district：严格模式，默认的，不允许操作
2、cascade：级联模式，一起操作，主表变化，从表数据跟着变化
3、set null：置空模式，主表变化(删除)，从表对应记录设置为空，前提是从表中对应的外键字段允许为空

外键约束主要约束的对象是主表操作：从表就是不能插入主表不存在的数据
通常在进行约束使用的时候，需要指定操作：update和delete
常用的约束模式：on update cascade on delete set null;更新级联，删除置空(主表数据更新，那么从表数据也会更新，主表删除数据时，从表显示置空，即null)

-- 外键约束
mysql> alter table foreign_key add foreign key(class_id) references student(id)
    -> on update cascade
    -> on delete set null;
Query OK, 2 rows affected (2.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

-- 这里更新失败为什么呢？下面查看结构知道了有两个外键名
mysql> update student id=2 where name='小明';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=2 where name='小明'' at line 1

mysql> show create table foreign_key;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| foreign_key | CREATE TABLE `foreign_key` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `class_id` (`class_id`),
  CONSTRAINT `foreign_key_ibfk_2` FOREIGN KEY (`class_id`) REFERENCES `student` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `foreign_key_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 删除一个默认有严格约束的外键名
mysql> alter table foreign_key drop foreign key foreign_key_ibfk_1;
Query OK, 2 rows affected (1.96 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table foreign_key;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                      |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| foreign_key | CREATE TABLE `foreign_key` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `class_id` (`class_id`),
  CONSTRAINT `foreign_key_ibfk_2` FOREIGN KEY (`class_id`) REFERENCES `student` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 现在可以看到主表student更新id为6时，从表foreign_key自动更新为6(更新级联)
mysql> update student set id=6 where id=2;
Query OK, 1 row affected (0.12 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from foreign_key;
+----+------+----------+
| id | name | class_id |
+----+------+----------+
|  1 | 张三 |        1 |
|  2 | 李四 |        6 |
+----+------+----------+
2 rows in set (0.00 sec)

-- 主表student删除id=1的信息时，从表foreign_key自动置空(删除置空)
mysql> delete from student where id=1;
Query OK, 1 row affected (1.84 sec)

mysql> select * from foreign_key;
+----+------+----------+
| id | name | class_id |
+----+------+----------+
|  1 | 张三 |     NULL |
|  2 | 李四 |        6 |
+----+------+----------+
2 rows in set (0.00 sec)