MySQL约束

标签：COMMENT 外键约束 MySQL TABLE 主键 id

约束

概述：约束可以保证 "数据完整性"，"数据的精确性" 和 "可靠性"。它会了防止数据库中会存在不符合语义规定的数据以及因错误输入从而导致数据错乱而提出的。

在 SQL规范中 "约束" 是对表中数据进行额外的条件限制。

实体完整性：同一个表中，不能存在两条完全相同无法区分的记录。
域完整性：例如：年龄范围 0 ~ 120，性别范围 "男/女"。
引用完整性：员工表引用的部门，在部门表中必须存在。
用户自定义完整性：用户名唯一，密码不能为空等.....。

什么是约束？是对表中数据的强制规定（就是对表中的列设定的限制）

1. 约束的分类

约束的列个数：

单列约束。
多列约束。

约束的作用范围：

列级约束：将此约束声明在列的后面，作用于某一个列。
表级约束：在表中所有列声明都后再声明的该约束，作用于整张表。

约束的作用-功能：

not null （非空约束）
unique （唯一性约束）
primary key （主键约束）
foreign key （外键约束）
check （检查约束）
default （默认值约束）

如何操作约束：

在创建表时（create table）规定约束。
在修改表时（alter table）规定约束。

查看表中的约束

# 查看表信息
desc 表名;

# 数据字典查询-约束表
SELECT
	*
FROM
	information_schema.TABLE_CONSTRAINTS
WHERE
	TABLE_NAME = '表名'

# 数据字典查询-列表
SELECT
	*
FROM
	information_schema.COLUMNS
WHERE
	TABLE_NAME = '表名'

2. 非空约束

该约束属于：单列，列级约束。

作用：限定列的值不允许为空。

关键字：NOT NULL

特点：

默认，所有类型的值都不可以是 null，包含数字类型。

"非空约束" 只能出现在表对象的列上，只能为某个列单独限定非空，不能组合非空。

一个表可以有很多列限定非空，但都要分别限定。

注意：空字符串不等于NULL。

为列添加非空约束：

CREATE TABLE
IF NOT EXISTS supconit_not_null (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	c_name VARCHAR (20) NOT NULL COMMENT '名字'
)
# 修改时添加 非空约束
ALTER TABLE supconit_not_null MODIFY c_name VARCHAR(15) NOT NULL COMMENT '名字';

删除列的非空约束：

# ?
# 提示：这是一个 DDL 语句。
ALTER TABLE supconit_not_null MODIFY c_name VARCHAR (20) NOT NULL COMMENT '名字';

3. 唯一性约束

该约束属于：单列，多列，表级约束。

作用：限定列的值不能重复。

关键字：UNIQUE

特点：

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

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

唯一性约束允许列值为空，但只能有一个 null 值。

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

MySQL 会给 "唯一约束" 的列，默认创建一个 "唯一索引"（MySQL 给你做的优化）。

添加唯一约束：

CREATE TABLE
IF NOT EXISTS supconit_unique (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	c_code VARCHAR (30) UNIQUE COMMENT '唯一编码',
	c_name VARCHAR(15) NOT NULL COMMENT '名字'
)

CREATE TABLE
IF NOT EXISTS supconit_unique_01 (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	c_code VARCHAR (30) '唯一编码',
	c_name VARCHAR (15) NOT NULL COMMENT '名字',
    # 多列组合值唯一
	CONSTRAINT uniqun_column UNIQUE(c_code,c_name)
)
# 修改表时添加唯一约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名称 UNIQUE (列,列 ,...);

删除唯一约束（删除唯一索引）：

由于在添加唯一约束时，会自动创建唯一索引，所以删除唯一约束只能通过删除 "唯一索引" 的方式删除。

说明：删除时需要指定索引名，"索引名" 和 "约束名" 相同。如果创建唯一约束时未指定名称，单列默认和列名相同，组合列默认为第一列列名。

ALTER TABLE 表名 DROP INDEX 索引名称;
#
ALTER TABLE supconit_unique DROP INDEX c_code;

扩展 - 查看表的索引：
SHOW INDEX FROM 表名;
#
SHOW INDEX FROM supconit_unique_01;

4. 主键约束

该约束属于：单列，多列，表级约束。

作用：用来唯一标识表中的一行记录。

关键字：PRIMARY KEY

特点：

主键约束是唯一约束 + 非空约束（主键约束不允许重复，也不允许出现空值）。

一个表最多只能有一个主键约束。

主键约束可以对应着表中的一列或者多列（复合主键），那么这些列都不允许为空值，并且组合的值不允许重复。

MySQL 的主键约束名总是 PRIMARY，自定义的名字无效。

当创建主键约束时，系统默认会所在的列或列组合上建立对应的 "主键索引"（能够根据主键查询的，就根据主键查询，效率是最高的）。

主键列的值是不允许修改的。因为主键是数据记录的唯一标识，如果修改了，就有可能破坏 "数据的完整性"。

添加主键约束：

CREATE TABLE
IF NOT EXISTS supconit_primary (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	c_code VARCHAR (30) COMMENT '编码',
	c_name VARCHAR (15) COMMENT '名字'
)

CREATE TABLE
IF NOT EXISTS supconit_primary_01 (
	id INT UNSIGNED auto_increment COMMENT '主键-1',
	id_ext INT UNSIGNED COMMENT '主键-2',
	c_code VARCHAR (30) COMMENT '编码',
	c_name VARCHAR (15) COMMENT '名字',
	PRIMARY KEY (id, id_ext)
)
# 修改表时添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(列,列 ,...);

删除主键约束（这是不允许的）：

ALTER TABLE 表名 DROP PRIMARY KEY;
#
ALTER TABLE supconit_primary_01 DROP PRIMARY KEY;
# 错误：
# [Err] 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
# [Err] 1075 - 表定义不正确；只能有一个自动列，并且必须将其定义为键（主键）

注意：增长列必须为主键，并且在表中只能有一个。删除主键约束之前，要先删除增长约束。

5. 自增列

该约束属于：单列，表级约束。

作用：列的值（按照预设的数）自增长。

关键字：AUTO_INCREMENT

特点：

一个表最多只能有一个自增长列，并且该列必须是主键列。

自增约束的列的数据类型必须是整数类型。

如果自增列指定了 0 和 null，会在当前最大值的基础上自增。如果自增列手动指定了具体的值，则使用指定的值（注意：不能重复）。

添加自增列：

CREATE TABLE
IF NOT EXISTS 表名 (
	id INT PRIMARY KEY auto_increment COMMENT '主键', # 必须在主键约束和唯一约束配合使用
	NAME VARCHAR (10),
	email VARCHAR (25)
);
# 修改表时设置自增列（该列必须主键）
ALTER TABLE 表名 MODIFY id INT auto_increment COMMENT '注释';

删除自增长：

ALTER TABLE 表名 MODIFY id INT;

设置表自增列的初始值：

ALTER TABLE 表名 auto_increment = 0;

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

MySQL 8.0 之前，自增主键 auto_increment 的值如果大于 max(primary key)+1，在 MySQL 重启后，会重置 auto_increment = max(primary key)+1，这种现象在某些情况下会导致主键冲突或者其他难以发现的问题。

例如：使用了：1，2，4，5，6 ，此时下一条应该使用的是 7。然后删除了 5，6，此时重启 MySQL 服务器之后，下一条使用的是 5。

说明：自增值是有 InnoDB 的数据字典内部一个计数器决定的，而该计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化为：auto_increment = max(primary key)+1。

MySQL 8.0 之后，将自增主键的计数器持久化到重做日志中。每次计数器发生改变，都会将其写入到重做日志中。如果数据库重启，InnoDB 会根据重做日志中的信息来初始化计数器的内存值。

例如：使用了：1，2，4，5，6 ，此时下一条应该使用的是 7。然后删除了 5，6，此时重启 MySQL 服务器之后，下一条使用的是依然是 7。

6. 检查约束

该约束属于：单列，表级约束。

作用：检查一个列或多个列的值，是否符合预先设定要求（一般指的是值的范围）。

关键字：CHECK

说明：MySQL 8.0 之前不支持检查约束，可以定义，但是不起作用

标签：COMMENT,外键,约束,MySQL,TABLE,主键,id
From： https://www.cnblogs.com/xiaolindang/p/17848837.html

约束

1. 约束的分类

2. 非空约束

3. 唯一性约束

4. 主键约束

5. 自增列

6. 检查约束

相关文章

赞助商

阅读排行