MySQL之约束
目录
约束（CONSTRAINT）
什么是约束？
是一种限制，对某一个东西的限制。例如宪法规定了你违反的事情你是不能做的。这就是一种约束
数据库的约束，是对数据的安全性，完整性的保证
mysql中的约束有哪些？
1. unique key(普通约束)
唯一性约束，表示这个不能出现重复的值，
# 完整的建表语句 create table table_name(字段名 字段类型[长度] [约束]) charset utf8; # 创建表 mysql> create table student( -> naem char(20) not null, -> gender enum("g","b") default "b", -> id int unique) charset utf8; Query OK, 0 rows affected (0.23 sec) # 查看表结构 mysql> desc student; +--------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------+------+-----+---------+-------+ | naem | char(20) | NO | | NULL | | | gender | enum('g','b') | YES | | b | | | id | int(11) | YES | UNI | NULL | | +--------+---------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> insert into student values("潘立府",null,null); Query OK, 1 row affected (0.07 sec) mysql> select * from student; +-----------+--------+------+ | naem | gender | id | +-----------+--------+------+ | 潘立府 | NULL | NULL | +-----------+--------+------+ 1 row in set (0.00 sec) # 对name字段插入null时，提示报错 mysql> insert into student values(null,null,null); ERROR 1048 (23000): Column 'name' cannot be null # 对name字段给定值后，发现能够插入数据 mysql> insert into student values("张三",null,null); Query OK, 1 row affected (0.06 sec) # 查看数据 mysql> select * from student; +--------+--------+------+ | name | gender | id | +--------+--------+------+ | 张三 | NULL | NULL | +--------+--------+------+ 1 row in set (0.00 sec) # 发现问题：id字段为唯一性约束，不能出现重复值，但是重复插入null值，唯一约束没有生效 mysql> insert into student values("李四",null,null); Query OK, 1 row affected (0.07 sec) mysql> select * from student; +--------+--------+------+ | name | gender | id | +--------+--------+------+ | 张三 | NULL | NULL | | 李四 | NULL | NULL | +--------+--------+------+ 2 rows in set (0.00 sec) # 此时想把id字段的属性改为：唯一性约束 + not null ，但是发现报错，原因：表中已经存在null值。 mysql> alter table student modify id int unique not null; ERROR 1062 (23000): Duplicate entry '0' for key 'id' # 于是删除数据，再修改id的属性 mysql> delete from student; Query OK, 2 rows affected (0.29 sec) mysql> alter table student modify id int unique not null; Query OK, 0 rows affected, 1 warning (0.56 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> desc student; +--------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------+------+-----+---------+-------+ | name | char(20) | NO | | NULL | | | gender | enum('g','b') | YES | | b | | | id | int(11) | NO | PRI | NULL | | +--------+---------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) # 此时发现，插入数据时，id不能为null了。 mysql> insert into student values("李四",null,null); ERROR 1048 (23000): Column 'id' cannot be null结论：字段只设置unique唯一性约束，null值依然能够插入，原因：mysql不能对null进行比较
2. not null
非空约束，表示这个字段的值不能为空
例如：账户名、密码等
3. default
默认值，用于给某一个字段设置默认值
4. primary key 主键约束
主键约束，从约束角度来看，主键等同于非空 + 唯一
主键与普通约束的区别：# 创建主键字段：id mysql> create table persong( -> id char(19) primary key, -> name char(20)); Query OK, 0 rows affected (0.46 sec) # OK mysql> insert into persong values("1","rose"); Query OK, 1 row affected (0.29 sec) # 主键冲突 mysql> insert into persong values("1","rose"); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> desc persong; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | char(19) | NO | PRI | NULL | | | name | char(20) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
主键的特点：
主键是一种索引，索引的作用是加速查询效率。因此主键约束能加速我们查询的效率。
在执行sql语句的前面加上：explain 可查看sql语句的执行计划
主键对于innodb引擎来说是必须要的，没有不行。即使我们在创建innodb引擎的表，没有创建主键，系统也会自动创建一个隐藏的主键。
一个表中只能存在一个主键
auto_increment（自增长）
mysql> create table teacher (id int primary key auto_increment,name char(20)); Query OK, 0 rows affected (0.47 sec) # 第一种插入方法 mysql> insert into teacher values(null,"jack"); Query OK, 1 row affected (0.28 sec) mysql> insert into teacher values(null,"rose"); Query OK, 1 row affected (0.29 sec) mysql> insert into teacher values(null,"panlifu"); Query OK, 1 row affected (0.29 sec) mysql> insert into teacher values(null,"lt"); Query OK, 1 row affected (0.06 sec) mysql> select * from teacher; +----+---------+ | id | name | +----+---------+ | 1 | jack | | 2 | rose | | 3 | panlifu | | 4 | lt | +----+---------+ 4 rows in set (0.00 sec) # 第二种方式 插入数据 mysql> insert into teacher(name) values("xs"); Query OK, 1 row affected (0.29 sec) mysql> select * from teacher; +----+---------+ | id | name | +----+---------+ | 1 | jack | | 2 | rose | | 3 | panlifu | | 4 | lt | | 5 | xs | +----+---------+ 5 rows in set (0.00 sec) # 当auto_increment 约束 primary key时，即使我们插入数据指定为自增列为空，系统也会帮助我们自动增长。
5. foreign key 外键约束
一. 数据出现了大量的重复
二. 数据结构环混乱（耦合度高）
三. 当后期修改数据时，由于有大量的重复数据，必须每个都修改
如何创建外键索引：
第一阶段
应该先确定主键所在表的数据结构。例如：部门表（主表）
create table dept( id int primary key auto_increment, name char(20), job char(50), manager char(30) ) charset utf8;
- 在确认外键所在表的数据结构。例如：员工表（从表、子表）
create table teacher( id int primary key auto_increment, name char(20), gender char(21), dept_id int ) charset utf8;这里存在缺点：部门表和员工表之间没有建立联系，目前部门表修改数据，员工表无感知
第二阶段
- 对员工表进行约束（跟部门表建立联系）
create table teacher( id int primary key auto_increment, name char(20), gender char(21), dept_id int, foreign key(dept_id) references dept(id) ) charset utf8; # dept_id 表示员工表的外键字段 # dept 表示要关联的哪个表,这里指部门表 # references 引用的意思 # dept(id) 表示要关联dept表中的id字段这样从表就跟主表建立了外键联系。
第三阶段：建立级联操作
因为外键存在第4点和第5点的原因，因此才有了级联的操作，来简化对数据库的管理
另外级联是建立在子表中的。
# 主库 create table dept( id int primary key auto_increment, name char(20), job char(50), manager char(30) ) charset utf8; # 子表 create table teacher( id int primary key auto_increment, name char(20), gender char(21), dept_id int, foreign key(dept_id) references dept(id) on update cascade on delete cascade ) charset utf8; # one delete cascade 应对 特征中的第4点 # on update cascade 应对 特征中的第5点总结特征：
外键字段的数据类型必须跟关联表的字段类型 相近或者相等。例如主键的数据类型为int，外键的数据类型可以为logint等其他的整型数据。但是不能为字符串，date等数据
在从表中插入数据时，如果主表不存在对应的数据，那么会导致插入失败。
create table dept( id int primary key auto_increment, name char(20), job char(50), manager char(30) ) charset utf8; create table teacher( id int primary key auto_increment, name char(20), gender char(21), dept_id int, foreign key(dept_id) references dept(id) ) charset utf8; mysql> insert into teacher values(null,"bgon","m",1); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`plf`.`teacher`, CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))
- 从表更新外键时，必须保证外键的值在主表中是存在的
create table dept( id int primary key auto_increment, name char(20), job char(50), manager char(30) ) charset utf8; create table teacher( id int primary key auto_increment, name char(20), gender char(21), dept_id int, foreign key(dept_id) references dept(id) ) charset utf8; mysql> select *from dept; Empty set (0.00 sec) mysql> select * from teacher; Empty set (0.00 sec) mysql> insert into dept values(null,"教学部","教学","bgon"); Query OK, 1 row affected (0.29 sec) mysql> insert into dept values(null,"销售部","销售","plf"); Query OK, 1 row affected (0.07 sec) mysql> select *from dept; +----+-----------+--------+---------+ | id | name | job | manager | +----+-----------+--------+---------+ | 1 | 教学部 | 教学 | bgon | | 2 | 销售部 | 销售 | plf | +----+-----------+--------+---------+ 2 rows in set (0.00 sec) mysql> insert into teacher values(null,'张三',"男",1); Query OK, 1 row affected (0.30 sec) mysql> select *from teacher; +----+--------+--------+---------+ | id | name | gender | dept_id | +----+--------+--------+---------+ | 1 | 张三 | 男 | 1 | +----+--------+--------+---------+ 1 row in set (0.00 sec) # 当将dept_id更新为3时，报错。原因：主表中并没有编号为3的数据 mysql> update teacher set dept_id = 3 where id =1; ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`teacher`, CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)) mysql>
- 删除主表记录前，要保证从表中没有外键关联被删除的id（可以级联）
mysql> select *from dept; +----+-----------+--------+---------+ | id | name | job | manager | +----+-----------+--------+---------+ | 1 | 教学部 | 教学 | bgon | | 2 | 销售部 | 销售 | plf | +----+-----------+--------+---------+ 2 rows in set (0.00 sec) mysql> select *from teacher; +----+--------+--------+---------+ | id | name | gender | dept_id | +----+--------+--------+---------+ | 1 | 张三 | 男 | 1 | +----+--------+--------+---------+ 1 row in set (0.00 sec) mysql> delete from dept where id = 1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`teacher`, CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))
- 更新主表记录的主键时，要保证从表中没有外键关联被删除的id（可以级联）
mysql> select *from dept; +----+-----------+--------+---------+ | id | name | job | manager | +----+-----------+--------+---------+ | 1 | 教学部 | 教学 | bgon | | 2 | 销售部 | 销售 | plf | +----+-----------+--------+---------+ 2 rows in set (0.00 sec) mysql> select *from teacher; +----+--------+--------+---------+ | id | name | gender | dept_id | +----+--------+--------+---------+ | 1 | 张三 | 男 | 1 | +----+--------+--------+---------+ 1 row in set (0.00 sec) # 当更新主表编号为1的部门时，因为编号1部门下有一个人，所以不能更新 mysql> update dept set id = 3 where id=1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`teacher`, CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)) mysql>
- 必须先创建主表，子表才能用外键
- 删除表的操作，先删除从表，再删除主表
mysql> select *from dept; +----+-----------+--------+---------+ | id | name | job | manager | +----+-----------+--------+---------+ | 1 | 教学部 | 教学 | bgon | | 2 | 销售部 | 销售 | plf | +----+-----------+--------+---------+ 2 rows in set (0.00 sec) mysql> select *from teacher; +----+--------+--------+---------+ | id | name | gender | dept_id | +----+--------+--------+---------+ | 1 | 张三 | 男 | 1 | +----+--------+--------+---------+ 1 row in set (0.00 sec) # 不能删除 mysql> drop table dept; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
级联操作
产生的原因：
当我们需要删除部门（主表）信息时，必须先删除从表中关联的数据，很麻烦。
级联操作指的就是，当你操作主表时，自动操作从表
两种级联的定义方式
一. 在表定义阶段进行定义：
-
级联的删除
create table student_b( class_id int, name char(10), age int, foreign key(class_id) references class_b(id) on delete cascade ) charset utf8; -
级联的更新
create table student_b( class_id int, name char(10), age int, foreign key(class_id) references class_b(id) on update cascade ) charset utf8; -
级联的删除和更新
create table teacher( id int primary key auto_increment, name char(20), gender char(21), dept_id int, foreign key(dept_id) references dept(id) on update cascade on delete cascade ) charset utf8;
二. 表存在的情况下，先将之前的外键删除掉，然后通过alter增加外键级联：
# 删除外键
mysql> alter table teacher drop foreign key 外键的ID;
# 增加外键并增加级联删除和更新
mysql> alter table teacher add constraint dept_id foreign key(dept_id) references dept(id) on delete cascade on update cascade;
