MySQL 之约束（主键、唯一、非空、自增、外键）

#MySQL 之约束（主键、唯一、非空、自增、外键）| 来源: 网络整理| 查看: 265

目的：使得数据更准确，更完整。约束的分类： 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:如果在建立外键时，指定了“置空”策略，那么可以做到主表的记录被修改或删除时，从表的对应字段变为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 主表名(主表被参考的列名);

【本文地址】

公司简介

联系我们

MySQL 之 约束 （主键、唯一、非空、自增、外键）

MySQL 之约束（主键、唯一、非空、自增、外键）