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 主表名(主表被参考的列名);
如果觉得《MySQL 之 约束 (主键 唯一 非空 自增 外键)》对你有帮助,请点赞、收藏,并留下你的观点哦!