MySql基础之约束

MySql基础之约束

1、约束概述

什么是约束

• 可以在创建表时规定约束（通过 CREATE TABLE 语句）
• 或者在表创建之后通过 ALTER TABLE 语句规定约束

约束的分类

• 根据约束数据列的限制，约束可分为：   1）单列约束：每个约束只约束一列   2）多列约束：每个约束可约束多列数据
• 根据约束的作用范围，约束可分为：   1）列级约束：只能作用在一个列上，跟在列的定义后面   2）表级约束：可以作用在多个列上，不与列一起，而是单独定义

• 根据约束起的作用，约束可分为：   1）OT ULL 非空约束，规定某个字段不能为空   2）UIQUE 唯一约束，规定某个字段在整个表中是唯一的   1）PRIMARY KEY 主键(非空且唯一)约束   2）FOREIG KEY 外键约束   1）CHECK 检查约束   2）DEFAULT 默认值约束

注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果

• 查看某个表已有的约束

代码语言：javascript代码运行次数：0运行复制

#information_schema数据库名（系统库） 
#table_ctraints表名称（专门存储各个表的约束） 
SELECT * FROM information__ctraints 
WHERE table_name = '表名称';

2、非空约束

作用及特点

• 限定某个字段/某列的值不允许为空
• 关键字：not null
• 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空
• 一个表可以有很多列都分别限定了非空

添加非空约束

• 建表时

代码语言：javascript代码运行次数：0运行复制

CREATE TABLE emp( 
id IT(10) OT ULL, 
AME VARCHAR(20) OT ULL, 
 CHAR(1) [null]
);

• 建表后

代码语言：javascript代码运行次数：0运行复制

ALTER TABLE emp 
MODIFY  CHAR(1) OT ULL;

删除非空约束

代码语言：javascript代码运行次数：0运行复制

#去掉not null，相当于修改某个非注解字段，该字段允许为空 
alter table 表名称 
modify 字段名 数据类型 [ULL];

或

alter table 表名称 
modify 字段名 数据类型;

举例：

代码语言：javascript代码运行次数：0运行复制

ALTER TABLE emp 
MODIFY  CHAR(1) ULL;

、唯一性约束

作用及特点

• 用来限制某个字段/某列的值不能重复。
• 关键字：UIQUE
• 同一个表可以有多个唯一约束。
• 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
• 唯一性约束允许多列值为空。
• 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
• MySQL会给唯一约束的列上默认创建一个唯一索引。

添加唯一约束

• 建表时

代码语言：javascript代码运行次数：0运行复制

create table 表名称( 
字段名 数据类型, 字段名 数据类型 unique, 
字段名 数据类型 unique [key], 
字段名 数据类型 
);

或

create table 表名称( 
字段名 数据类型, 
字段名 数据类型, 
字段名 数据类型, 
[ctraint 约束名] unique [key](字段名) 
);

举例：

代码语言：javascript代码运行次数：0运行复制

-- 使用列级约束语法 
create table student( 
sid int, 
sname varchar(20), 
tel char(11) unique, 
cardid char(18) unique key 
);

代码语言：javascript代码运行次数：0运行复制

-- 使用表级约束语法 
CREATE TABLE USER( 
id IT OT ULL, 
AME VARCHAR(25), 
PASSWORD VARCHAR(16),
COSTRAIT uk_user_id UIQUE(id) 
);

• 建表后

代码语言：javascript代码运行次数：0运行复制

#字段列表中如果是一个字段，表示该列的值唯一。
#如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的

#方式1： 
alter table 表名称 
add unique [key](字段列表);

#方式2： 
alter table 表名称 
modify 字段名 字段类型 unique [key];

举例：

代码语言：javascript代码运行次数：0运行复制

ALTER TABLE USER 
ADD UIQUE(AME);

代码语言：javascript代码运行次数：0运行复制

ALTER TABLE USER 
ADD COSTRAIT uk_name
UIQUE(AME);

代码语言：javascript代码运行次数：0运行复制

ALTER TABLE USER 
MODIFY AME VARCHAR(20) UIQUE;

复合唯一约束

• 字段列表中写的是多个字段名，多个字段名用逗号分隔
• 表示那么是复合唯一，即多个字段的组合是唯一的
• 不设置约束名则为第一个字段名

代码语言：javascript代码运行次数：0运行复制

create table 表名称( 
字段名 数据类型, 
字段名 数据类型, 
字段名 数据类型, 
[ctraint 约束名] unique key(字段列表)
);

举例：

代码语言：javascript代码运行次数：0运行复制

#选课表 
create table student_course( 
id int, 
sid int, 
cid int, 
score int, 
ctraint uk_sid_cid unique key(sid,cid) #学号和课程id的复合唯一 
);

删除唯一约束

• 添加唯一性约束的列上也会自动创建唯一索引。
• 删除唯一约束只能通过删除唯一索引的方式删除。
• 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
• 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；
• 如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。

举例：

代码语言：javascript代码运行次数：0运行复制

ALTER TABLE USER 
DROP IDEX uk_name;

• 可以通过 show index from 表名称; 查看表的索引

4、主键约束

作用及特点

• 用来唯一标识表中的一行记录。
• 关键字：primary key
• 主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。
• 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
• MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
• 当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。

注意： Oracle主键约束名可以修改

添加主键约束

• 建表时

代码语言：javascript代码运行次数：0运行复制

create table 表名称( 
字段名 数据类型 primary key, #列级模式 
字段名 数据类型, 
字段名 数据类型 
);

create table 表名称( 
字段名 数据类型, 
字段名 数据类型, 
字段名 数据类型, 
[ctraint 约束名] primary key(字段名) #表级模式 
);

举例：

代码语言：javascript代码运行次数：0运行复制

# 列级约束
CREATE TABLE emp4( 
id IT PRIMARY KEY , 
AME VARCHAR(20) 
);

# 表级约束
CREATE TABLE emp5( 
id IT OT ULL , 
AME VARCHAR(20), 
pwd VARCHAR(15), 
COSTRAIT pk_emp5_id PRIMARY KEY(id) 
);

• 建表后

代码语言：javascript代码运行次数：0运行复制

#字段列表可以是一个字段，也可以是多个字段，
#如果是多 个字段的话，是复合主键
ALTER TABLE 表名称 
ADD PRIMARY KEY(字段列表); 

举例：

代码语言：javascript代码运行次数：0运行复制

ALTER TABLE emp5 
ADD PRIMARY KEY(AME,pwd);

删除主键约束

代码语言：javascript代码运行次数：0运行复制

alter table 表名称 drop primary key;

说明：删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在。

5、自增列

作用及特点

• 某个字段的值自增
• 关键字：auto_increment
• 一个表最多只能有一个自增长列
• 当需要产生唯一标识符或顺序值时，可设置自增长
• 自增长列约束的列必须是键列（主键列，唯一键列）
• 自增约束的列的数据类型必须是整数类型
• 如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接 赋值为具体值。

指定自增约束

• 建表时

代码语言：javascript代码运行次数：0运行复制

create table 表名称( 
字段名 数据类型 primary key auto_increment, 
字段名 数据类型, 
字段名 数据类型, 
字段名 数据类型
);

create table 表名称( 
字段名 数据类型, 
字段名 数据类型 unique key auto_increment, 
字段名 数据类型,
primary key(字段名) 
);

• 建表后

代码语言：javascript代码运行次数：0运行复制

alter table 表名称 
modify 字段名 数据类型 auto_increment;

举例：

代码语言：javascript代码运行次数：0运行复制

create table employee( 
eid int primary key , 
ename varchar(20) 
);

alter table employee modify eid int auto_increment;

通过desc employee查看

删除自增约束

代码语言：javascript代码运行次数：0运行复制

#去掉auto_increment相当于删除
alter table 表名称 
modify 字段名 数据类型; 

举例：

代码语言：javascript代码运行次数：0运行复制

alter table employee 
modify eid int;

MySQL 8.0新特性—自增变量的持久化

• mysql5.7自增变量在内存中，重启计数器会被初始化。（删除的不做累计计算了）
• mysql8.0将自增主键的计数器持久化到 重做日志 中。每次计数器发生改变，都会将其写入重做日志 中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。

6、外键约束

作用及特点

• 关键字：FOREIG KEY
• 从表的外键列，必须引用/参考主表的主键或唯一约束的列
• 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。
• 删除外键约束后，必须 手动 删除对应的索引

使用外键约束

• 创建

代码语言：javascript代码运行次数：0运行复制

create table dept( #主表 
did int primary key, #部门编号 
dname varchar(50) #部门名称 
);

create table emp(#从表 
eid int primary key, #员工编号 
ename varchar(5), #员工姓名 
deptid int, #员工所在的部门
ctraint fk_dept_did foreign key (deptid) references dept(did) 
#在从表中指定外键约束 
#emp表的deptid和和dept表的did的数据类型一致，
#意义都是表示部门的编号 
);

• 修改

代码语言：javascript代码运行次数：0运行复制

ALTER TABLE emp1
ADD [COSTRAIT fk_emp_dept_id] FOREIG KEY(dept_id) REFERECES dept(dept_id);

• 删除

代码语言：javascript代码运行次数：0运行复制

#删外键
alter table emp 
drop foreign key fk_dept_did;

#查索引
SHOW IDEX FROM emp;
#删索引
alter table emp 
drop index fk_dept_did;

约束等级

• cascade方式 ：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
• set null方式 ：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null o
• action方式（默认方式） ：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
• restrict方式 ：同no action， 都是立即检查外键约束

举例：

代码语言：javascript代码运行次数：0运行复制

create table dept( 
did int primary key, #部门编号 
dname varchar(50) #部门名称 
);

create table emp( 
eid int primary key, #员工编号 
ename varchar(5), #员工姓名 
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) 
#把修改操作设置为级联修改等级，把删除操作设置为set null等级
on update cascade on delete set null  
);

• 修改部门表did，则员工表对应的did响应改变
• 删除部门表did，则员工表对应did变为null

7、CHECK 约束

• 检查某个字段的值是否符号xx要求，一般指的是值的范围
• 关键字：check
• MySQL5.7 可以使用check约束，但不起作用，MySQL 8.0支持

举例：

代码语言：javascript代码运行次数：0运行复制

create table employee( 
eid int primary key, 
ename varchar(5), 
gender char check ('男' or '女') 
);

CREATE TABLE temp( 
id IT AUTO_ICREMET, 
AME VARCHAR(20), 
age IT CHECK(age > 20), 
PRIMARY KEY(id) 
);

8、DEFAULT约束

给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

举例：

代码语言：javascript代码运行次数：0运行复制

create table employee( 
eid int primary key, 
ename varchar(20) not null, 
gender char(1), 
#给tel字段增加默认是空字符串 
tel char(11) not null default '' 
);

#给gender字段增加默认值约束为男
alter table employee 
modify gender char(1) default '男'; 

本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。 原始发表：2025-01-20，如有侵权请联系 cloudcommunity@tencent 删除前往查看索引mysqltable基础数据类型