MySql基础之约束

Java微观世界

发布于 2025-01-21 08:14:26

5780

文章被收录于专栏：springbootspringboot

1、约束概述

什么是约束

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

约束的分类

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

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

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

查看某个表已有的约束

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

2、非空约束

作用及特点

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

添加非空约束

建表时

CREATE TABLE emp( 
id INT(10) NOT NULL, 
NAME VARCHAR(20) NOT NULL, 
sex CHAR(1) [null]
);

建表后

ALTER TABLE emp 
MODIFY sex CHAR(1) NOT NULL;

删除非空约束

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

或

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

举例：

ALTER TABLE emp 
MODIFY sex CHAR(1) NULL;

3、唯一性约束

作用及特点

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

添加唯一约束

建表时

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

或

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

举例：

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

-- 使用表级约束语法 
CREATE TABLE USER( 
id INT NOT NULL, 
NAME VARCHAR(25), 
PASSWORD VARCHAR(16),
CONSTRAINT uk_user_id UNIQUE(id) 
);

建表后

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

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

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

举例：

ALTER TABLE USER 
ADD UNIQUE(NAME);

ALTER TABLE USER 
ADD CONSTRAINT uk_name
UNIQUE(NAME);

ALTER TABLE USER 
MODIFY NAME VARCHAR(20) UNIQUE;

复合唯一约束

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

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

举例：

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

删除唯一约束

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

举例：

ALTER TABLE USER 
DROP INDEX uk_name;

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

4、主键约束

作用及特点

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

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

建表时

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

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

举例：

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

# 表级约束
CREATE TABLE emp5( 
id INT NOT NULL , 
NAME VARCHAR(20), 
pwd VARCHAR(15), 
CONSTRAINT pk_emp5_id PRIMARY KEY(id) 
);

建表后

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

举例：

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

删除主键约束

alter table 表名称 drop primary key;

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

5、自增列

作用及特点

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

指定自增约束

建表时

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

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

建表后

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

举例：

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

alter table employee modify eid int auto_increment;

通过desc employee查看

删除自增约束

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

举例：

alter table employee 
modify eid int;

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

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

6、外键约束

作用及特点

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

使用外键约束

创建

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

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

修改

ALTER TABLE emp1
ADD [CONSTRAINT fk_emp_dept_id] FOREIGN KEY(dept_id) REFERENCES dept(dept_id);

删除

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

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

约束等级

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

举例：

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支持

举例：

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

CREATE TABLE temp( 
id INT AUTO_INCREMENT, 
NAME VARCHAR(20), 
age INT CHECK(age > 20), 
PRIMARY KEY(id) 
);

8、DEFAULT约束

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

举例：

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.com 删除

索引