MySQL十三：约束

最新推荐文章于 2023-06-09 21:33:10 发布

e_nanxu

最新推荐文章于 2023-06-09 21:33:10 发布

阅读量281

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/N_ZSX/article/details/122517110

版权

MySQL 专栏收录该内容

22 篇文章 1 订阅

订阅专栏

约束

一、约束概述

引入原因

为了保证数据库中的数据完整性，包括数据的精确性和可靠性，防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息。
定义

对数据表中字段的强制规定
考虑约束的方面

实体完整性：例如不能存在完全无法区分的两条记录

域完整性：例如规定其取值范围

引用完整性：关联性表之间的引用关系要完整

用户自定义完整性：根据制定表的实际情况，进行的其它规定
约束的分类

① 根据数据列的限制

单列约束：每个约束只约束一列

多列约束：每个约束可约束多列数据

② 根据作用范围

列级约束：将此约束声明在对应字段的后面

表级约束：在表中所有字段都声明完后，在所有字段的后面声明的约束

创建约束

# 1.创建表时列级约束
CREATE TABLE 表名称(
	字段名1 数据类型 约束1，
    字段名2 数据类型 约束2，
    . . . 
)
# 2.创建表时表级约束
CREATE TABLE 表名称(
	字段名1 数据类型，
    字段名2 数据类型，
    . . . 
    字段名n 数据类型，
    # 表级约束时，可以省略CONSTRAINT 约束名
    CONSTRAINT 约束名 约束类型(字段名x, 字段名y, ...)
)
# 3.在表内规定约束
# 3.1
ALTER TABLE 表名称
MODIFY 字段名 数据类型 新增约束名;
# 3.2
ALTER TABLE 表名称
ADD 约束名 key(字段名x, 字段名y, ...);

删除约束

# 一般不会进行删除约束的操作
ALTER table 表名称
MODIFY 字段名 数据类型; (后面不再跟约束，即是删除约束)

查看表约束

SELECT * 
FROM information_schema.table_constraints
WHERE table_name = 表名称;

二、非空约束

关键字：NOT NULL
特点：

① 默认值：所有的类型的值都可以是NULL

② 只能出现在列上，且只能某个列单独限定，不能组合非空

③ 一个表内可以很多列分别限定非空

④ 空字符串不等于NULL，0 也不等于NULL

⑤ NOT NULL只能是列级约束，不可以是表级约束

操作

# 创建表时设定非空约束
CREATE TABLE emp(
	id INT NOT NULL,
    name VARCHAR(20) NOT NULL,
    sex CHAR NULL
);
# 建表后设定非空约束
ALTER TABLE emp
MODIFY sex VARCHAR(30) NOT NULL;
# 删除非空约束
ALTER TABLE emp
MODIFY sex VATCHAR(30) NULL;

注意

如果表中该字段的值是NULL，则会出现修改不成功的报错。

三、唯一性约束

关键字：UNIQUE
特点

同一个表可以有多个唯一约束

唯一约束可以是某个列的值唯一，也可以是多个列组合的值唯一

唯一性约束允许列值为空

在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同

可以向声明为unique的字段上添加NULL值

MySQL会给唯一约束的列上默认创建一个唯一索引

操作

# 1.创建表时设立约束
# 1.1 列级约束
CREATE TABLE student(
	id INT UNIQUE,
    name VARCHAR(20),
    tel CHAR(11) UNIQUE
)
# 1.2 表级复合约束
CREATE TABEL t_course(
	cid INT NOT NULL,
    cname VARCHAR(25),
    password VARCHAR(30),
    # 只要两个字段中有一个不一样就算不同
    CONSTRAINT uk_name_pwd UNIQUE(cname, password)
);

# 2.建表后指定约束
# 2.1 方式1
ALTER TABLE user1
ADD UNIQUE(name, password);
# 2.2 方式2
ALTER TABLE user1
MODIFY name VARCHAR(20) UNIQUE;

删除唯一约束

① 添加唯一性约束的列上也会自动创建唯一索引

② 删除唯一约束只能通过删除唯一索引的方式

③ 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样

④ 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和（）中排在第一个的列名相同，也可以自定义唯一性约束名。
```
# 查看表的索引
SHOW INDEX FROM 表名；
# 删除唯一约束
ALTER TABLE 表名
DROP INDEX 唯一索引名/列名;
```

四、主键约束

关键字：PRIMARY KEY
特点

相当于唯一约束＋非空约束的组合，该列数据不允许重复，也不允许出现空值

一个表最多只有一个主键约束，对应一个列或复合主键

MySQL的主键名总是PRIMARY，自己命名无效

当创建主键约束时，系统默认会在所在的列或组合上建立主键索引

不要修改主键字段的值。因为主键是数据记录的唯一标识，修改或删除主键或破环数据完整性。

操作

# 列级约束/表级约束与上述相同

# 删除，实际开发中，是不会删除主键约束的
ALTER TABLE student
DROP PRIMARY KEY;

五、自增列

关键字：AUTO_INCREMENT
特点

一个表最多一个自增列，且该列必须是键列（主键列 / 唯一键列），必须是整数类型。

如果自增列指定了0或NULL，则会在当前最大值的基础上自增。如果手动指定具体值，则直接赋给具体值。
操作

创建，跟在主键或唯一键的声明后即可。

删除，与NOT NULL的删除方式一样，在MODIFY后不要再写自增列的关键字即可
MySQL8.0 新特性：自增列的持久化

MySQL5.7：对于自增主键的分配，是由InnoDB数据字典内部的一个计数器决定的，是内存层面的，当数据库重启后，该计数器会初始化。（重启前如果有删除逻辑操作，那么重启后只会按照现在的主键值后自增）

MySQL8.0 ：将自增主键的计数器持久化到重做日志。（重启后，依然会遵循重启前的删除等逻辑操作，如重启前自增到了6，虽然删除了6，但是新自增会从7开始。）

六、外键约束

关键字：FOREIGN KEY
主表和从表

主表：被引用（参考）的表

从表：引用（参考）别人的表
特点

① 从表的外键，必须引用主表的主键或唯一约束列

② 创建外键未命名时，系统会自动生成一个外键名

③ 在创建表时就指定外键的话，要先创建主表，再创建从表

④ 删除表时，先删除从表（或外键约束），后删除主表

⑤ 当主表的记录被从表参照时，该记录不允许删除。

⑥ 在从表中指定外键约束，并且一个表可以建立多个外键约束

⑦ 从表的外键列与主表被参照的列名字可不同，但是数据类型必须一样，逻辑意义一致。

⑧ 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引，删除外键约束后，必须手动删除对应的索引。

操作

# 主表
CREATE TABLE dept(
	did INT PRIMARY KEY,
    dname VARCHAR(50)
);
# 从表
CREATE TABLE emp(
	eid INT PRIMARY KEY,
    ename VARCHER(5),
    did INT,
    FOREIGN KEY(did) references dept(did)
);

外键的约束方式

CASCADE：在主表上update/delete记录，同步update/delete 子表的匹配记录

SET NULL：在主表上update/delete记录，将子表上匹配记录的列设为NULL

NO ACTION：如果从表中有匹配记录，不允许对主表对应候选键进行update/delete操作

RESTRICT：同上no action

SET DEFAULT：主表有变更，从表将外键列设为默认值
对于外键约束，最好采用：
```
ON UPDATE CASADE ON DELETE RESTRICT
```

删除外键

# 1.查看约束名
SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名称';
# 2.删除外键约束
ALTER TABLE 从表名 DROP FOREIGN KEY 外键名;
# 3.查看索引名
SHOW INDEX FROM 表名称;
# 4.删除索引
ALTER TABLE 从表名 DROP INDEX 索引名;

阿里开发规范

不得使用外键与级联，一切外键概念必须在应用层解决。

七、默认约束

关键字：DEFAULT
作用：相当于Java中的初始化，一旦设置默认值，如果此字段没有显示赋值，则赋值为默认值

操作

# 创建表和创建表之后都可以添加默认约束
CREATE TABLE 表名(
	字段名1 数据类型 DEFAULT 默认值,
    字段名2 数据类型 NOT NULL DEFAULT 默认值,
    ...
)
# 删除默认值约束，同删除NOT NULL一样

八、CHECK约束

关键字：CHECK
作用：检查某个字段的值是否符合要求，一般指的是值得范围

操作

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

九、注意

建表时，加 NOT NULL DEFAULT ‘’ 或DEFAULT 0，是不想让表中出现null值
为什么不想要null值

不好比较：比较null需专门的IS NULL语句

效率不高：
AUTO_INCREMENT约束的字段值是从 1 开始的

e_nanxu

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL十三：约束

约束一、约束概述引入原因为了保证数据库中的数据完整性，包括数据的精确性和可靠性，防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息。定义对数据表中字段的强制规定考虑约束的方面实体完整性：例如不能存在完全无法区分的两条记录域完整性：例如规定其取值范围引用完整性：关联性表之间的引用关系要完整用户自定义完整性：根据制定表的实际情况，进行的其它规定约束的分类① 根据数据列的限制单列约束：每个约束只约束一列多列约束：每个约束可约束多列数据
复制链接

扫一扫