1. 约束简介
https://blog.csdn.net/w_linux/article/details/79655073
1.1 什么是约束
约束英文：constraint
什么是约束：约束实际上就是表中数据的限制条件，如不能为空，不能重复等
1.2 为什么要约束
为了保证表中的数据完整与有效，防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。
1.3 有哪些约束
约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：
| 约束种类 | 英文描述 | 作用 |
|---|---|---|
| 非空 | not null | 非空约束；指定某列不能为空 |
| 默认值 | default | 默认值约束；指定某列的默认值 |
| 唯一 | unique | 唯一约束；指定某列或某几列组合不能重复 |
| 自增 | auto_increment | 自增；指定某int、非空列自动增加 |
| 主键 | primary key | 主键；指定该列的值可以唯一地表示其所在行的记录 |
| 外键 | foreign key | 外键；指定该行记录从属于主表中的一条记录，主要用于参照完整性(如关联其他表) |
2. 约束详解
2.1 非空约束
用not null 约束的字段不能为null(空)，必须要给定具体的值/数据
1）创建student1表，设置"name"为非空字段
create table student1(id int,name char(12) not null,phone char(11),gender enum('male','female'));
2）查看student1表结构信息
mysql> desc student1;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(12) | NO | | NULL | |
| phone | char(11) | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
+--------+-----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql>
从上述表结构信息可以看出，目前"name"字段的Null属性为NO，即非空
3）插入name为空的数据进行测试
mysql> insert into student1(id,name,phone,gender) values(1,'tom','12323233232','male');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student1(id,name,gender) values(1,'vincent','male');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student1;
+------+---------+-------------+--------+
| id | name | phone | gender |
+------+---------+-------------+--------+
| 1 | tom | 12323233232 | male |
| 1 | vincent | NULL | male |
+------+---------+-------------+--------+
2 rows in set (0.00 sec)
mysql> insert into student1(id,phone,gender) values(2,'12323233232','male');
ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql>
由于只有"name"字段做了非空约束，因此当插入的数据中name字段为空时，就会报错！
报错信息：ERROR 1364 (HY000): Field ‘name’ doesn’t have a default value
由报错信息我们可以看出，由于没有插入name字段，所以报name字段没有默认值；这也就引申出我们下一个约束
2.2 默认约束
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
1）创建student2表，设置"gender"字段的默认值为"male"
create table student2(id int,name char(12) not null,phone char(11),gender enum('male','female') default 'male');
2）查看student2表结构
mysql> desc student2;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(12) | NO | | NULL | |
| phone | char(11) | YES | | NULL | |
| gender | enum('male','female') | YES | | male | |
+--------+-----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql>
从上面表结构可以看出，目前"gender"字段的Default属性为"male"，即默认值为"male"
3）插入gender字段为空的数据进行测试
mysql> insert into student2(id,name,phone) values(1,'jack','13923233232');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student2;
+------+------+-------------+--------+
| id | name | phone | gender |
+------+------+-------------+--------+
| 1 | jack | 13923233232 | male |
+------+------+-------------+--------+
1 row in set (0.00 sec)
mysql>
由上述结果可知，即便gender字段未主动插入数据，但实际插入的数据中，gender字段的值仍然为"male"
2.3 唯一约束
指定某列或某几列组合不能重复
1）创建student3表，设置"id"字段为唯一约束
方法一：
create table student3(id int unique,name char(12) not null,phone char(11),gender enum('male','female') default 'male');
方法二：
create table student3(id int ,name char(12) not null,phone char(11),gender enum('male','female') default 'male',unique(id));
2）查看student3表结构
mysql> desc student3;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | char(12) | NO | | NULL | |
| phone | char(11) | YES | | NULL | |
| gender | enum('male','female') | YES | | male | |
+--------+-----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql>
从上述表结构可以看出，目前"id"字段的"key"属性值为UNI,即唯一
3）插入id字段重复的数据进行测试
mysql> insert into student3(id,name,phone,gender) values(1,'tom','12323233232','male');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student3(id,name,phone,gender) values(1,'jane','13923233232','female');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
mysql>
由于id字段目前是唯一约束，因此当插入的数据与表中已存在的数据重复时就会报错
报错信息：ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘id’
4）扩展：联合唯一
a）设置id和name字段为联合唯一属性
create table student5(id int ,name char(12) not null,phone char(11),gender enum('male','female') default 'male',unique(id,name));
b）查看student5表结构
mysql> desc student5;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | char(12) | NO | | NULL | |
| phone | char(11) | YES | | NULL | |
| gender | enum('male','female') | YES | | male | |
+--------+-----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql>
从上面表结构可以看出：目前id字段的key属性为MUL
c）插入数据测试
mysql> insert into student5(id,name,phone,gender) values(1,'tom','12323233232','male');
Query OK, 1 row affected (0.44 sec)
mysql> insert into student5(id,name,phone,gender) values(1,'jack','13623233232','male');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student5(id,name,phone,gender) values(2,'tom','13723233232','male');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student5(id,name,phone,gender) values(1,'tom','13923233232','male');
ERROR 1062 (23000): Duplicate entry '1-tom' for key 'id'
mysql>
如上结果，只有当id和name字段都相同时才会报错无法插入
2.4 自增约束
约束字段为自动增长，被约束的字段必须同时被key约束
注意：
1.定义自增之前，必须提前保证该字段为唯一约束
2.自增约束默认带非空属性
1）创建student6表，设置"id"字段为自增
create table student6(id int unique auto_increment,name char(12) not null,phone char(11),gender enum('male','female') default 'male');
2）查看student6表结构
mysql> desc student6;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(12) | NO | | NULL | |
| phone | char(11) | YES | | NULL | |
| gender | enum('male','female') | YES | | male | |
+--------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql>
从上面表结构可以看出，
1.目前"id"字段的Extra属性为"auto_increment"，即自增属性
2.目前"id"字段的Null属性为"NO"，即非空属性
3.目前"id"字段的Key属性为"PRI"，即id字段成了主键
也就是说：自增 = unique + not null + 自增效果
3）插入数据测试自增效果
mysql> insert into student6(name,phone,gender) values('tom','13123233232','male');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student6(name,phone,gender) values('jack','13623233232','male');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student6(name,phone,gender) values('jane','13823233232','female');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student6;
+----+------+-------------+--------+
| id | name | phone | gender |
+----+------+-------------+--------+
| 1 | tom | 13123233232 | male |
| 2 | jack | 13623233232 | male |
| 3 | jane | 13823233232 | female |
+----+------+-------------+--------+
3 rows in set (0.00 sec)
mysql>
由于目前id字段为自增约束，因此即便插入的数据不带id字段，id字段也会自动增加1
4）删除历史数据后再插入数据，id是什么情况？
将student6表中id=3的数据删掉，然后重新插入，观察id的变化
mysql> delete from student6 where id=3;
Query OK, 1 row affected (0.00 sec)
mysql> insert into student6(name,phone,gender) values('jane','13823233232','female');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student6;
+----+------+-------------+--------+
| id | name | phone | gender |
+----+------+-------------+--------+
| 1 | tom | 13123233232 | male |
| 2 | jack | 13623233232 | male |
| 4 | jane | 13823233232 | female |
+----+------+-------------+--------+
3 rows in set (0.00 sec)
mysql>
由上述结果可知，即便删除历史数据，重新插入新数据时，id不会重新计数
为什么id不会重新计数呢？
因为在表结构信息中有一个AUTO_INCREMENT字段对自增字段进行计数(即每增加一条数据该值加1)，而这个字段的值不会因为删除数据而发生改变。
所以即便删除数据，自增字段的值还是接着原来的值加1
mysql> show create table student6;
+----------+------------------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------+
| student6 | CREATE TABLE `student6` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(12) NOT NULL,
`phone` char(11) DEFAULT NULL,
`gender` enum('male','female') DEFAULT 'male',
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
2.5 主键约束
1. 主键为了保证表中的每一条数据的该字段都是表格中的唯一值。换言之，它是用来独一无二地确认一个表格中的每一行数据。
2. 主键可以包含一个字段或多个字段。当主键包含多个栏位时，称为组合键 (Composite Key),也可以叫联合主键。
3. 主键可以在建置新表格时设定 (运用 CREATE TABLE 语句)，或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。
4. 主键必须唯一，且非空；第一个非空且唯一的字段==这张表的主键
5. 每张表只能有一个主键；可以是单一字段，也可以是多字段组合。
2.5.1 单字段主键情况
1）创建student7表，设置"id"字段为主键；创建student8表，设置"name"字段为主键；
-- id为主键
create table student7(id int unique auto_increment,name char(12) not null unique,phone char(11) unique,gender enum('male','female') default 'male');
-- name为主键
create table student8(name char(12) not null unique,id int unique auto_increment,phone char(11) unique,gender enum('male','female') default 'male');
2）查看student7/8表结构
-- student7 表结构
mysql> desc student7;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(12) | NO | UNI | NULL | |
| phone | char(11) | YES | UNI | NULL | |
| gender | enum('male','female') | YES | | male | |
+--------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)
mysql>
-- student8表结构
mysql> desc student8;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| name | char(12) | NO | PRI | NULL | |
| id | int(11) | NO | UNI | NULL | auto_increment |
| phone | char(11) | YES | UNI | NULL | |
| gender | enum('male','female') | YES | | male | |
+--------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql>
由上述student7和student8两个表对比可知：
- 主键(PRI) == 非空且唯一；primary key
- 如有多个字段均非空且唯一，则取第一个字段为此表的主键
- 一个表只能有一个主键
3）通过primary key 关键字设置主键
-- 设置id为之间
create table student9(name char(12) not null unique,id int primary key auto_increment,phone char(11) unique,gender enum('male','female') default 'male');
4）查看student9表结构
mysql> desc student9;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| name | char(12) | NO | UNI | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| phone | char(11) | YES | UNI | NULL | |
| gender | enum('male','female') | YES | | male | |
+--------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql>
由上述student9表结构可知：
- 即便id字段非第一个字段，但是仍可以通过
primary key字段设置为主键 - 主键可以设置
auto_increment约束
2.5.2 联合唯一 与 联合主键
注：联合唯一常用，联合主键并不常用
场景描述
如，一张用来描述 服务-ip地址-端口号 关系的表，如下表结构：
| ID | Name | IP | Port |
|---|---|---|---|
| 1 | Mysql | 192.168.1.1 | 3306 |
| 2 | Mysql | 192.168.2.1 | 3306 |
| 3 | Nginx | 192.168.1.1 | 80 |
由上述表，我们无法通过单一字段来确定唯一的一个服务，如mysql，我们必须通过IP+Port的方式才能确定唯一的一个Mysq服务；
此时就需要用到我们的联合唯一
1）联合唯一
a）建表，定义ip与port联合唯一
create table server_info1(id int primary key auto_increment,name char(20),ip char(15) not null,port int(5) not null,unique(ip,port));
b）查看表结构
mysql> desc server_info1;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | YES | | NULL | |
| ip | char(15) | NO | MUL | NULL | |
| port | int(5) | NO | | NULL | |
+-------+----------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> show create table server_info1;
+--------------+--------------------------------------------+
| Table | Create Table |
+--------------+--------------------------------------------+
| server_info1 | CREATE TABLE `server_info1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(20) DEFAULT NULL,
`ip` char(15) NOT NULL,
`port` int(5) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ip` (`ip`,`port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------+--------------------------------------------+
1 row in set (0.00 sec)
mysql>
由上述表结构UNIQUE KEYip(ip,port)可知，server_info1表中，将ip和port两个字段联合成一个名为"ip"的UNIQUE KEY
c）插入数据观察结果
mysql> insert into server_info1(name,ip,port) values('mysql','192.168.1.1',3306);
Query OK, 1 row affected (0.00 sec)
mysql> insert into server_info1(name,ip,port) values('mysql','192.168.1.2',3306);
Query OK, 1 row affected (0.00 sec)
mysql> insert into server_info1(name,ip,port) values('nginx','192.168.1.1',80);
Query OK, 1 row affected (0.00 sec)
mysql> insert into server_info1(name,ip,port) values('xxxxx','192.168.1.1',80);
ERROR 1062 (23000): Duplicate entry '192.168.1.1-80' for key 'ip'
mysql>
由上述插入数据结果可知，只有当插入的数据的ip和port两个字段都相同时才会报错
2）联合主键
a）建表，定义ip与port为联合主键
create table server_info2(id int unique auto_increment,name char(20),ip char(15),port int(5),primary key(ip,port));
b）查看表结构
mysql> desc server_info2;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | UNI | NULL | auto_increment |
| name | char(20) | YES | | NULL | |
| ip | char(15) | NO | PRI | | |
| port | int(5) | NO | PRI | 0 | |
+-------+----------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> show create table server_info2;
+--------------+--------------------------------------------+
| Table | Create Table |
+--------------+--------------------------------------------+
| server_info2 | CREATE TABLE `server_info2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(20) DEFAULT NULL,
`ip` char(15) NOT NULL DEFAULT '',
`port` int(5) NOT NULL DEFAULT '0',
PRIMARY KEY (`ip`,`port`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------+--------------------------------------------+
1 row in set (0.00 sec)
mysql>
将ip+port设为联合主键之后，两个字段会由默认非空的属性
2.6 外键约束
1）场景说明
看如下一个学生信息表：
| id | name | gender | classname |
|---|---|---|---|
| 1 | 李一 | male | 语文 |
| 2 | 王二 | female | 语文 |
| 3 | 张三 | male | 英语 |
| 4 | 李四 | female | 数学 |
| 5 | 刘五 | male | 英语 |
上述表结构会发现，再classname字段，一旦学生较多，就会造成表没必要的空间占用，因为课程是固定的几门，所以，我们可以优化成下面两个表结构
student_info
| id | name | gender | class_id |
|---|---|---|---|
| 1 | 李一 | male | 1 |
| 2 | 王二 | female | 1 |
| 3 | 张三 | male | 3 |
| 4 | 李四 | female | 2 |
| 5 | 刘五 | male | 3 |
class_info
| cid | class_name |
|---|---|
| 1 | 语文 |
| 2 | 数学 |
| 3 | 英语 |
可以通过class_id---cid字段将student_info表的数据和class_info表的数据进行关联，因此：
即便没有外键，也能使两个表关联起来
BUT，两张表中的数据是没有被保护的，比如，直接删除class_info表里的语文课的操作是允许的，但是实际上，student_info中仍然有同学关联了着语文课；另外，student_info表也可以添加class_info表中没有的课程，因此这类操作从逻辑上说是应该被禁止的
所以应用到了外键
外键：关联外表中的一个键
被关联的表：被关联的表必须要先于关联表创建
被关联的字段：被关联的键，必须是一个unique key或者primary key
2）建表student_info和class_info
--class_info
create table class_info(cid int primary key,class_name char(12));
--student_info 定义外键关联
create table student_info(id int unique auto_increment,name char(12),gender enum('male','female') default 'male',class_id int,foreign key(class_id) references class_info(cid));
3）查看表结构
class_info表
mysql> desc class_info;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| cid | int(11) | NO | PRI | NULL | |
| class_name | char(12) | YES | | NULL | |
+------------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> show create table class_info;
+------------+-------------------------------------------+
| Table | Create Table |
+------------+-------------------------------------------+
| class_info | CREATE TABLE `class_info` (
`cid` int(11) NOT NULL,
`class_name` char(12) DEFAULT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+-------------------------------------------+
1 row in set (0.00 sec)
mysql>
student_info表
mysql> desc student_info;
+----------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(12) | YES | | NULL | |
| gender | enum('male','female') | YES | | male | |
| class_id | int(11) | YES | MUL | NULL | |
+----------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> show create table student_info;
+--------------+----------------------------------------------------------------------------+
| Table | Create Table |
+--------------+----------------------------------------------------------------------------+
| student_info | CREATE TABLE `student_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(12) DEFAULT NULL,
`gender` enum('male','female') DEFAULT 'male',
`class_id` int(11) DEFAULT NULL,
UNIQUE KEY `id` (`id`),
KEY `class_id` (`class_id`),
CONSTRAINT `student_info_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class_info` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
可以从student_info的表结构看出，目前定义了一个名为student_info_ibfk_1的外键，外键具体的关联信息是本表的class_id—>class_info表的cid
4）插入数据，做基础数据准备
class_info表插入数据
-- 插入课程信息
insert into class_info(cid,class_name) values(1,'语文');
insert into class_info(cid,class_name) values(2,'数学');
insert into class_info(cid,class_name) values(3,'英语');
insert into class_info(cid,class_name) values(4,'地理');
student_info表插入数据
insert into student_info(name,gender,class_id) values('李一','male',1);
insert into student_info(name,gender,class_id) values('王二','female',1);
insert into student_info(name,gender,class_id) values('张三','male',3);
insert into student_info(name,gender,class_id) values('李四','female',2);
insert into student_info(name,gender,class_id) values('刘五','male',3);
查看已插入的数据
mysql> select * from class_info;
+-----+------------+
| cid | class_name |
+-----+------------+
| 1 | 语文 |
| 2 | 数学 |
| 3 | 英语 |
| 4 | 地理 |
+-----+------------+
3 rows in set (0.00 sec)
mysql> select * from student_info;
+----+--------+--------+----------+
| id | name | gender | class_id |
+----+--------+--------+----------+
| 1 | 李一 | male | 1 |
| 2 | 王二 | female | 1 |
| 3 | 张三 | male | 3 |
| 4 | 李四 | female | 2 |
| 5 | 刘五 | male | 3 |
+----+--------+--------+----------+
5 rows in set (0.00 sec)
mysql>
5）测试外键的约束效果
向student_info表中插入一个学生，其class_id为5(任意不在calss_info表中的cid)
mysql> insert into student_info(name,gender,class_id) values('张六','male',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`csdnblog`.`student_info`, CONSTRAINT `student_info_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class_info` (`cid`))
mysql>
-- 必须要满足class_info表中存在该cid=4代表的课程数据才能插入数据
删除class_info表中的语文课
mysql> delete from class_info where cid=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`csdnblog`.`student_info`, CONSTRAINT `student_info_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class_info` (`cid`))
mysql>
-- 必须要保证student_info表中已无学生关联语文课，才能进行删除
6）外键–级联更新
关键语法：on update cascade
针对外表中已被/未被关联的字段进行修改
mysql> select * from class_info; ;
+-----+------------+
| cid | class_name |
+-----+------------+
| 1 | 语文 |
| 2 | 数学 |
| 3 | 英语 |
| 4 | 地理 |
+-----+------------+
4 rows in set (0.00 sec)
mysql> update class_info set cid=6 where cid=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`csdnblog`.`student_info`, CONSTRAINT `student_info_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class_info` (`cid`))
mysql>
-- 因为cid=1的课程目前有和student_info表中的学生进行关联，所以不能随意更新
mysql> update class_info set cid=6 where cid=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 因为cid=4的课程未和student_info里的学生记录做关联，所以可以随意更改
mysql> select * from class_info;
+-----+------------+
| cid | class_name |
+-----+------------+
| 1 | 语文 |
| 2 | 数学 |
| 3 | 英语 |
| 6 | 地理 |
+-----+------------+
4 rows in set (0.00 sec)
mysql>
由上可以看出，如果若字段已被关联则无法随意进行修改
如果我们确实需要对cid进行修改，该如何是好？下面就了解一下级联更新的定义与效果
a）新建表(定义级联更新)
-- 新建class_info2表
create table class_info2(cid int primary key,class_name char(12));
-- 新建student_info2表(关键字:on update cascade)
create table student_info2(id int unique auto_increment,name char(12),gender enum('male','female') default 'male',class_id int,foreign key(class_id) references class_info2(cid) on update cascade);
b）查看student_info2的表结构
mysql> show create table student_info2;
+---------------+----------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+----------------------------------------------------------------------------------------------------+
| student_info2 | CREATE TABLE `student_info2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(12) DEFAULT NULL,
`gender` enum('male','female') DEFAULT 'male',
`class_id` int(11) DEFAULT NULL,
UNIQUE KEY `id` (`id`),
KEY `class_id` (`class_id`),
CONSTRAINT `student_info2_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class_info2` (`cid`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
关键字：ON UPDATE CASCADE
c）插入数据
mysql> insert into class_info2 select * from class_info;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into student_info2 select * from student_info;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from class_info2;
+-----+------------+
| cid | class_name |
+-----+------------+
| 1 | 语文 |
| 2 | 数学 |
| 3 | 英语 |
| 6 | 地理 |
+-----+------------+
4 rows in set (0.00 sec)
mysql> select * from student_info2;
+----+--------+--------+----------+
| id | name | gender | class_id |
+----+--------+--------+----------+
| 1 | 李一 | male | 1 |
| 2 | 王二 | female | 1 |
| 3 | 张三 | male | 3 |
| 4 | 李四 | female | 2 |
| 5 | 刘五 | male | 3 |
+----+--------+--------+----------+
5 rows in set (0.00 sec)
mysql>
d）针对外表中已被关联的字段进行修改
mysql> update class_info2 set cid=8 where cid=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from class_info2;
+-----+------------+
| cid | class_name |
+-----+------------+
| 2 | 数学 |
| 3 | 英语 |
| 6 | 地理 |
| 8 | 语文 |
+-----+------------+
4 rows in set (0.00 sec)
mysql> select * from student_info2;
+----+--------+--------+----------+
| id | name | gender | class_id |
+----+--------+--------+----------+
| 1 | 李一 | male | 8 |
| 2 | 王二 | female | 8 |
| 3 | 张三 | male | 3 |
| 4 | 李四 | female | 2 |
| 5 | 刘五 | male | 3 |
+----+--------+--------+----------+
5 rows in set (0.00 sec)
mysql>
在主表增加级联更新属性之后，class_info2表中已被关联的语文课的cid仍可以进行修改，且student_info2表中关联了语文课的class_id的值也会对应发生改变
92
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
