MySQL 之 约束 （主键、唯一、非空、自增、外键）
目的：使得数据更准确，更完整。
约束的分类：
1、键约束
（1）主键约束
（2）唯一键约束
（3）外键约束
2、非空约束
3、默认值约束
4、自增约束
主键约束
（一）概述
1、关键字 ：primary key
2、特点：增加主键约束的列（字段）的值必须是非空 + 唯一的，一个表只有一个主键约束
3、作用：保证表中不会出现两条无法区分的记录
4、要求：每一张表都必须有主键约束
5、分类
单列主键约束
复合主键约束
（二）使用主键约束
1、创建主键约束
（1）在建表时指定主键约束
create table 【数据库名.】表名称(
字段1 数据类型 primary key,
字段2 数据类型,
....
);
create table 【数据库名.】表名称(
字段1 数据类型,
字段2 数据类型,
....,
primary key(字段1)
);
例如：
create table dept(
id int primary key,
name varchar(20),
description varchar(100)
);
或
create table dept(
id int ,
name varchar(20),
description varchar(100),
primary key(id)
);
mysql> desc dept;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| description | varchar(100) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
insert into dept values(1,'财务部','发钱的');
insert into dept values(1,'财务部','发钱的');
mysql> insert into dept values(1,'财务部','发钱的');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
（2）建表后指定主键约束
create table dept(
id int,
name varchar(20),
description varchar(100)
);
修改表结构：
alter table dept add primary key(id);
2、删除主键约束
修改表结构
alter table 表名称 drop primary key;
例如：
alter table dept primary key;
3、复合主键
（1）在建表时指定主键约束
create table 【数据库.】表名称(
字段1 数据类型,
字段2 数据类型,
字段3 数据类型,
...,
primary key(字段列表)
);
说明：复合主键不能在列后面加，需要单独指定
（2）建表后指定主键约束
alter table 【数据库.】表名称 add primary key(字段列表);
例如：
create table stu(
sid int primary key, #学号
sname varchar(20) #姓名
);
create table course(
cid int primary key, #课程编号
cname varchar(20) #课程名称
);
create table score(
sid int, #学号
cid int, #课程编号
score int #对应的成绩
);
insert into stu values(1,'张三'),(2,'李四');
insert into course values(1001,'java'),(1002,'mysql');
insert into score values(1,1001,89),(1,1002,90),(2,1001,56),(2,1002,69);
mysql> select * from stu;
+-----+-------+
| sid | sname |
+-----+-------+
| 1 | 张三 |
| 2 | 李四 |
+-----+-------+
2 rows in set (0.00 sec)
mysql> select * from course;
+------+-------+
| cid | cname |
+------+-------+
| 1001 | java |
| 1002 | mysql |
+------+-------+
2 rows in set (0.00 sec)
mysql> select * from score;
+------+------+-------+
| sid | cid | score |
+------+------+-------+
| 1 | 1001 | 89 |
| 1 | 1002 | 90 |
| 2 | 1001 | 56 |
| 2 | 1002 | 69 |
+------+------+-------+
4 rows in set (0.00 sec)
alter table score add primary key(sid,cid);
create table score(
sid int, #学号
cid int, #课程编号
score int, #对应的成绩
primary key(sid,cid)
);
变通成如下这种：
create table score(
id int, #没有业务意义，只是唯一标记一行
sid int, #学号
cid int, #课程编号
score int, #对应的成绩
primary key(id)
);
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139
唯一键约束
（一）概述
1、关键字：unique key
2、特点：指定了唯一键的列的值必须唯一，不能重复
3、作用：给主键以外的列，限定唯一性
4、唯一键分类
单列的唯一
复合唯一
唯一键和主键的区别：
（1）主键不能为空，唯一键可以为空
（2）主键约束，一个表只能有一个，而唯一键可以有很多个
（二）使用唯一键
1、如何创建/指定唯一键
（1）在建表时
create table 【数据库名.】表名称(
字段1 数据类型 primary key,
字段2 数据类型 【unique key】,
字段3 数据类型 【unique key】,
...
);
或
create table 【数据库名.】表名称(
字段1 数据类型 primary key,
字段2 数据类型 ,
字段3 数据类型 ,
...,
unique key(字段2), #分别唯一
unique key(字段3)
);
create table 【数据库名.】表名称(
字段1 数据类型 primary key,
字段2 数据类型 ,
字段3 数据类型 ,
...,
unique key(字段列表) #复合唯一
);
create table emp(
eid int primary key, #员工编号
ename varchar(20), #姓名
cardid varchar(18) unique key, #身份证号
tel varchar(11) unique key
);
insert into emp values(1,'张三','123456789123456789','12345678912');
insert into emp values(2,'李四','123456789123456788','12345678912');
mysql> insert into emp values(2,'李四','123456789123456788','12345678912');
ERROR 1062 (23000): Duplicate entry '12345678912' for key 'tel'
（2）在建表后
修改表结构：
alter table 【数据库名.】表名称 add unique key(字段名);
alter table 【数据库名.】表名称 add unique key(字段列表); #复合唯一
2、删除唯一键
修改表结构：
alter table 【数据库名.】表名称 drop index 索引名;
如果不知道索引名，可以通过如下的语句查询：
show index from 表名称;
alter table emp drop unique key; #错误的
alter table emp drop unique key(cardid); #错误的
alter table emp drop index cardid;
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354
索引：index
作用：为了提高查询效率，而设置索引
我们的键约束（主键、唯一键、外键），都会自动创建索引。
因为既然你建立键约束，那么该列的值一定很关键，那么在实际中肯定经常用他们的值来查询。
因此，为了提高查询效率，会自动在这些列上增加索引。
非空和默认值约束
1、如何指定非空约束
（1）建表时
create table emp(
eid int primary key, #员工编号
ename varchar(20) not null, #姓名
cardid varchar(18) unique key not null , #身份证号
tel varchar(11) unique key not null,
gender char not null default '男'
);
insert into emp values(1,'张三','111','10086','女');
insert into emp values(2,'李四','111','女'); #错误的，原因是值的数量和列的数量不匹配
insert into emp(eid,ename,cardid,gender) values(2,'李四','111','女'); #错误的 因为tel设置非空，但是又没指定默认值
insert into emp(eid,ename,cardid,tel) values(2,'李四','222','10010');
insert into emp values(3,'王五','3333','10011',default);
（2）建表后
create table 【数据库名.】表名称(
字段1 数据类型 primary key,
字段2 数据类型 【unique key】【not null】【default 默认值】,
字段2 数据类型 【unique key】【not null】【default 默认值】,
...
);
create table emp(
eid int primary key, #员工编号
ename varchar(20) not null, #姓名
cardid varchar(18) unique key , #身份证号
tel varchar(11) unique key ,
gender char
);
alter table emp modify cardid varchar(18) unique key not null;
alter table emp modify tel varchar(11) not null;
alter table emp modify gender char not null default '男';
2、如何去掉非空和默认值约束
alter table emp modify gender char ;
1234567891011121314151617181920212223242526272829303132333435363738394041
自增约束
1、关键字：auto_increment
2、特点：
（1）一个表只能有一个自增列
（2）自增列必须是整型的
（3）自增列必须是键列，例如：主键，唯一键
3、如何指定自增
create table emp(
eid int primary key auto_increment,
ename varchar(20) not null
);
insert into emp values(2,'张三');
insert into emp(ename)values('李四');
insert into emp values(0,'王五');
insert into emp values(null,'赵六');
12345678910111213
外键约束（了解）
外键约束不是必须的，而且现在很多大的公司，数据量比较大时，不建议在数据库层面设计外键，
因为他觉得这样效率低，把这个数据的约束挪到代码层面去判断。
（一）概述
1、关键字：foreign key
2、特点：
（1）约束的是两张表的关系
需要两张表，或者一张表虚拟成两张表
（2）两张表分为主表（父表）和从表（子表）
外键的建立/指定是在从表（子表）上建立。
（3）被参考的表称为主表，主表的被参考列必须是主键或唯一键
（4）一个表可以有多个外键
（二）如何指定外键
1、在建表时指定外键
要求：
（1）建表的顺序
先建主表，再建从表
从表的语法：
create table 【数据库名.】表名称(
字段1 数据类型 primary key,
字段2 数据类型 【unique key】【not null】【default 默认值】,
字段3 数据类型 【unique key】【not null】【default 默认值】,
...,
foreign key(从表的外键列) references 主表名(主表被参考的列名)
);
（2）删表的顺序
先删从表，再删除主表
（3）添加/修改从表数据
添加/修改从表记录时，引用主表的列的值必须是存在的。
例如：添加/修改员工表时，员工所在部门的值必须引用部门表的部门编号，保证该部门编号是存在的。
（4）删除/修改主表记录
A：默认情况下，如果主表的被参考列的值被引用，那么就不能轻易的被删除和修改。
例如：2号部门被员工引用了，那么这个2号部门就不能被删除，并且2这个编号值不能被修改。
foreign key(从表的外键列) references 主表名(主表被参考的列名) 【on update restrict/no action】 【on delete restrict/no action】
B：如果在建立外键时，指定了“级联”策略，那么可以做到级联修改和删除
foreign key(从表的外键列) references 主表名(主表被参考的列名) 【on update cascade】 【on delete cascade】
C:如果在建立外键时，指定了“置空”策略，那么可以做到主表的记录被修改或删除时，从表的对应字段变为NULL
foreign key(从表的外键列) references 主表名(主表被参考的列名) 【on update set null】 【on delete set null】
#部门表：主表
create table dept(
did int primary key, #部门编号
dname varchar(20) not null unique key, #部门名称
description varchar(100) #部门简介
);
insert into dept values(1,'财务部','发钱的'),(2,'后勤部','发礼物的');
#职位表：主表
create table job(
jid int primary key, #职位编号
title varchar(20) not null , #职位名称
description varchar(100) #职位简介
);
insert into job values(1,'会计','算钱的'),(2,'助理','修电脑的');
#员工表：从表
create table emp(
eid int primary key, #员工编号
ename varchar(20) not null, #员工姓名
deptid int, #所在的部门编号 deptid可以取名did
jobid int, #职位编号
foreign key(deptid) references dept(did),
foreign key(jobid) references job(jid) on update set null on delete set null
);
insert into emp values(1,'张三',1,1),(2,'李四',1,1),(3,'王五',2,2),(4,'赵六',2,2);
2、建表后指定外键
alter table 从表名称 add foreign key(从表的字段) references 主表名(主表被参考的列名);
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354