MySQL 约束

恋喵大鲤鱼

发布于 2023-10-12 16:15:29

1640

发布于 2023-10-12 16:15:29

文章被收录于专栏：C/C++基础C/C++基础

1.简介

在数据库中，约束是对表中数据的一种限制条件，能够确保数据的完整性和一致性。

为了保证数据的完整性，SQL 规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：

实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录。
域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”。
引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

2.分类

根据约束数据列的限制，约束可分为：

单列约束：每个约束只约束一列(字段)
多列约束：每个约束可约束多列数据

根据约束的作用范围，约束可分为：

列级约束：只能作用在一个列上，跟在列的定义后面
表级约束：可以作用在多个列上，不与列一起，而是单独定义

根据约束所起的作用，约束可分为：

主键约束

主键约束确保表中的每一行都具有唯一标识符，能够唯一标识该表中的每条记录。

例如，学生信息表中的学号是唯一的。

唯一约束

唯一约束用于保证指定列或指定列组合不允许出现重复值。

例如，在用户信息表中，要避免表中的用户名重名，就可以把用户名列设置为唯一约束。

外键约束

外键约束用于建立表与表之间的关系，确保引用另一个表中的值时的完整性。

外键约束经常和主键约束一起使用，用来确保数据的完整性，即保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值。

例如，在员工信息表中，员工所属部门是一个外键，因为该字段是部门表的主键。

检查约束

检查约束允许你定义满足特定条件值的范围或规则，用于检查字段值是否有效。

例如，学生信息表中的年龄字段是没有负数的，并且数值也是有限制的。如果是小学生，年龄不低于 6 岁才可入学。在设置字段的检查约束时要根据实际情况设置，这样能够减少无效数据的输入。

默认值约束

默认约束规定了在未提供值时，某一列应采用的默认值。

例如，在录入商品信息，如果不输入上架状态“上架”或“下架”，那么会默认设置状态为“未上架”。

非空约束

指定某列的值不为空，在插入数据的时候必须非空。

例如，在学生信息表中，如果不添加学生姓名，那么这条记录是没有用的。

注意，上述所有约束中，一个数据表中，无论是单一主键还是复合主键，只能有一个主键约束，其它约束可以有多个。

3.创建约束

创建主键约束

建表时在字段后添加 PRIMARY KEY 表明是主键。

如果某个数据列的类型是整型，而且该列作为主键列，则可指定该列为具有自增长功能。指定自增长功能通常用于逻辑主键列，该列没有任何物理意义，仅仅为了标识每一行。MySQl 使用 AUTO_INCREMENT 设置自增长。

CREATE TABLE users(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);

或者是在定义完所有字段之后指定主键，语法格式如下：

[CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (key_part,...)
      [index_option] ...

index_type:
    USING {BTREE | HASH}

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  |ENGINE_ATTRIBUTE [=] 'string'
  |SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

CONSTRAINT [symbol]：这是一个可选部分。你可以为主键约束指定一个名称，以便在将来引用它。symbol 是主键约束的名称，可以根据你的喜好为其指定，如果不指定，则系统会为主键自动生成一个名称。

PRIMARY KEY：这是关键字，指示这是一个主键约束。

index_type：这是可选的部分，用于指定主键的索引类型。主键索引可以是 BTREE（B树索引，通常用于普通主键）或 HASH（哈希索引，通常用于自动递增主键）。大多数情况下，不需要显式指定索引类型，系统会根据上下文自动选择适当的索引类型。

(key_part,…)：这是主键的列列表。在括号中列出了构成主键的一个或多个列。主键是用于唯一标识表中每一行的一个或多个列的组合。这些列的值必须唯一且不为空。

index_option：这是可选的部分，用于指定主键索引的选项。这些选项可以包括 USING（指定索引类型）、KEY_BLOCK_SIZE（指定索引块大小）、COMMENT（为索引添加注释）等。

以下是一个示例，演示如何在定义完所有字段之后指定主键：

CREATE TABLE users(
    id INT AUTO_INCREMENT,
    name VARCHAR(255),
    CONSTRAINT pk_users PRIMARY KEY (id)
);

在上述示例中，我们创建了一个名为 users 的表，然后定义了一个名为 pk_users 的主键约束，它由 id 列组成。这意味着 id 列将唯一标识表中每一行。

创建唯一约束

建表时在字段后使用 UNIQUE 创建唯一约束。

例如，在用户信息表中，要避免表中的用户名重名，就可以把用户名列设置为唯一约束。

CREATE TABLE users (
    id INT,
    name VARCHAR(255) UNIQUE
);

可以创建一个多列唯一约束，以确保多个列的组合值在表中是唯一的。这种约束可以用于确保表中不会出现重复的组合。

以下是创建多列唯一约束的示例：

CREATE TABLE example_table (
    id INT,
    name VARCHAR(255),
    email VARCHAR(255),
    UNIQUE (name, email)
);

创建外键约束

建表时使用 FOREIGN KEY 引用主表创建外键。

例如，在员工信息表中，员工所属部门是一个外键，因为该字段是部门表的主键。

-- 主表：部门表
CREATE TABLE dept(
    id INT AUTO_INCREMENT PRIMARY KEY,		-- 部门编号
    name varchar(64)			-- 部门名称
);

-- 从表：员工表
CREATE TABLE emp(
    id INT AUTO_INCREMENT PRIMARY KEY,  		-- 员工编号
    name varchar(16),     						-- 员工姓名
    dept_id int,		   						-- 员工所在部门
    FOREIGN KEY (dept_id) REFERENCES dept (id)	-- 在从表中指定外键约束
);

创建检查约束

在 MySQL 8.0.16 之前，CREATE TABLE 仅允许以下有限版本的表 CHECK 约束语法，该语法将被解析并忽略：

CHECK (expr)

从 MySQL 8.0.16 开始，CREATE TABLE 允许所有存储引擎使用表和列 CHECK 约束的核心功能。对于表约束和列约束，CREATE TABLE 允许使用以下 CHECK 约束语法：

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

可选 symbol 指定约束的名称。如果省略，MySQL 会根据表名、_chk_ 和序数 (1, 2, 3, …) 生成一个名称。约束名称的最大长度为 64 个字符。它们区分大小写，但不区分重音符号。

expr 将约束条件指定为布尔表达式，对于表的每一行，该表达式的计算结果必须为 TRUE 或 UNKNOWN（对于 NULL 值）。如果条件计算结果为 FALSE，则失败并发生约束冲突。

可选的强制执行子句指示是否强制执行约束：

如果省略或指定为 ENFORCED，则创建并强制执行约束。这意味着在插入、更新或删除数据时，MySQL 将检查约束条件（expr）是否满足，如果条件不满足，则不允许进行相应的操作。
如果指定为 NOT ENFORCED，则创建约束但不强制执行。这意味着约束冲突将被记录下来，但不会影响插入、更新或删除数据的操作。

CHECK 约束可指定为表约束或列约束：

表约束不会出现在列定义中，并且可以引用任何表列。允许对表定义中稍后出现的列进行前向引用。
列约束出现在列定义中，并且只能引用该列。

CREATE TABLE t1(
  CHECK (c1 <> c2),
  c1 INT CHECK (c1 > 10),
  c2 INT CONSTRAINT c2_positive CHECK (c2 > 0),
  c3 INT CHECK (c3 < 100),
  CONSTRAINT c1_nonzero CHECK (c1 <> 0),
  CHECK (c1 > c3)
);

上面的检查约束定义中使用了有名和无名的定义方式。

CHECK (c1 <> c2) 是表约束：它出现在任何列定义之外，因此它可以（并且确实）引用多个表列。此约束包含对尚未定义的列的前向引用。没有指定约束名称，因此 MySQL 生成一个名称。

接下来的三个约束是列约束：每个约束都出现在列定义中，因此只能引用正在定义的列。其中一项约束是明确命名的。 MySQL 为另外两个分别生成一个名称。

最后两个约束是表约束。其中之一已被明确命名。 MySQL 为另一个生成一个名称。

创建默认值约束

建表时在字段后使用 DEFAULT 添加默认值可创建默认值约束。

例如，在录入商品信息，如果不输入上架状态“上架”或“下架”，那么会默认设置状态为“未上架”。

CREATE TABLE products(
	id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    sale_status TINYINT DEFAULT 0 -- 0 未上架 1 上架 2 下架
);

创建非空约束

建表时用 NOT NULL 约束的字段不能为 NULL 值，必须给定具体的数据。

例如，在学生信息表中，如果不添加学生姓名，那么这条记录是没有用的。

CREATE TABLE students(
    id INT AUTO_INCREMENT PRIMARY KEY,	-- 学生学号
    name varchar(64) NOT NULL			-- 学生姓名
);

4.查看约束

通过 SHOW CREATE TABLE 语句可以查看表的创建语句，结果包含了表的所有约束。

SHOW CREATE TABLE students\G

*************************** 1. row ***************************
       Table: students
Create Table: CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

在 MySQL 的 information_schema 数据库里的 table_constraints 表保存了该数据库实例中所有的约束信息，用户可以通过查询该表获取该数据库的约束信息。

SELECT * FROM information_schema.table_constraints;

结果列说明如下：

CONSTRAINT_CATALOG：这是约束所属的目录（catalog）的名称。在 MySQL 中，通常情况下，这个值通常为 def，因为 MySQL 不使用目录的概念。
CONSTRAINT_SCHEMA：这是包含约束的数据库的名称。它指定了约束所属的数据库。
CONSTRAINT_NAME：这是约束的名称。对于主键约束、唯一键约束、外键约束和检查约束，它将是一个用户定义的名称。
TABLE_SCHEMA：这是包含受约束表的数据库的名称。它指定了受约束表所在的数据库。
TABLE_NAME：这是受约束的表的名称。它指定了受约束表的名称。
CONSTRAINT_TYPE：这是约束的类型，它可以是以下值之一：
- “PRIMARY KEY”：主键约束
- “UNIQUE”：唯一键约束
- “FOREIGN KEY”：外键约束
- “CHECK”：检查约束

5.删除约束

要删除 MySQL 表中的约束，可以使用 ALTER TABLE 语句并指定要删除的约束类型和名称。

删除主键约束

ALTER TABLE table_name DROP PRIMARY KEY;

删除唯一约束

ALTER TABLE table_name DROP INDEX unique_constraint_name;

删除外键约束

ALTER TABLE table_name DROP FOREIGN KEY foreign_key_name;

删除检查约束

ALTER TABLE table_name DROP CHECK check_constraint_name;

删除默认值约束

要删除列上的默认值约束，可以使用 ALTER TABLE 语句并使用 ALTER COLUMN 子句将列的默认值更改为 NULL 或其他适当的默认值。

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT NULL;

删除非空约束

删除非空约束表示列允许为空。

ALTER TABLE table_name
MODIFY COLUMN column_name data_type NULL;

6.修改约束

在 MySQL 中，要修改约束，通常需要使用 ALTER TABLE 语句，具体取决于要修改的约束类型以及所需的更改。以下是一些常见的约束类型以及如何修改它们的示例：

修改主键约束

如果要修改表的主键约束，首先需要删除原来的主键约束，然后再添加新的主键约束。

-- 添加新的主键约束
ALTER TABLE table_name ADD PRIMARY KEY (new_primary_key_column);

修改唯一约束

修改唯一约束类似于修改主键约束，首先删除原来的唯一约束，然后添加新的唯一约束。

-- 添加新的唯一约束
ALTER TABLE table_name ADD UNIQUE (new_unique_column);

修改外键约束

若要修改外键约束，通常需要删除原来的外键约束，然后再添加新的外键约束。确保新的外键约束与原始表的关联列和引用表的关联列匹配。

-- 添加新的外键约束
ALTER TABLE table_name
ADD FOREIGN KEY (new_foreign_key_column)
REFERENCES referenced_table(referenced_column);

修改检查约束：

修改检查约束通常需要删除原来的检查约束，然后添加新的检查约束。确保新的检查约束表达式满足你的需求。

-- 添加新的检查约束
ALTER TABLE table_name ADD CHECK (new_check_expression);

修改默认值约束

ALTER TABLE table_name
ALTER COLUMN col_name SET DEFAULT new_default_value;

修改非空约束

如果要修改非空约束，可以将列从允许为空更改为不允许为空，或者从不允许为空更改为允许为空。

ALTER TABLE table_name
MODIFY COLUMN column_name data_type [NOT] NULL;

参考文献

1.6.3 How MySQL Deals with Constraints 13.1.20 CREATE TABLE Statement

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2023-09-25，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度

MySQL 约束

MySQL 约束

1.简介

2.分类

3.创建约束

创建主键约束

创建唯一约束

创建外键约束

创建检查约束

创建默认值约束

创建非空约束

4.查看约束

5.删除约束

6.修改约束

参考文献

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐