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));

唯一键约束

（一）概述

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;

索引：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 ;

自增约束

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,'赵六');

外键约束（了解）

外键约束不是必须的，而且现在很多大的公司，数据量比较大时，不建议在数据库层面设计外键，

因为他觉得这样效率低，把这个数据的约束挪到代码层面去判断。

（一）概述

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:如果在建立外键时，指定了“置空”策略，那么可以做到主表的记录被修改或删除时，从表的对应字段变为NULLforeign 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可以取名didjobid 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 主表名(主表被参考的列名);