【重学 MySQL】六十一、数据完整性与约束的分类

【重学 MySQL】六十一、数据完整性与约束的分类

在MySQL中，数据完整性是确保数据库中数据的准确性和一致性的关键。为了实现数据完整性，MySQL提供了多种约束类型，这些约束可以根据其功能和作用进行分类。

数据完整性

数据完整性是指数据库中数据的准确性和一致性，它要求数据库中的数据必须满足特定的规则和条件。数据完整性可以分为三类：实体完整性、域完整性和引用完整性。

1. 实体完整性：确保表中的每一行数据都有一个唯一标识，通常通过主键约束来实现。主键的值必须唯一，且不能为NULL。
2. 域完整性：限制表中特定列的数据必须满足的条件，以确保数据的准确性和有效性。域完整性通常通过数据类型、非空约束、默认值约束和检查约束等来实现。
3. 引用完整性：维护两个或多个表之间的关系，确保一个表中的外键值在另一个表的主键中存在，从而防止破坏表之间关系的无效数据。引用完整性通常通过外键约束来实现。

什么是约束

约束（Constraint）在数据库管理系统中，是一种用于限制表中数据类型的规则或条件。这些规则或条件旨在确保数据的准确性和可靠性，防止无效或不一致的数据进入数据库。约束可以应用于单个列或多个列，以及表之间的关系。

在数据库设计中，约束扮演着至关重要的角色，因为它们能够：

1. 保证数据的准确性：通过限制可以输入的数据类型和值，约束确保数据符合业务规则。
2. 维护数据的一致性：约束确保不同表之间的数据关系保持一致，例如通过外键约束来维护参照完整性。
3. 提高数据质量：通过防止无效数据的输入，约束有助于提高整个数据库的数据质量。
4. 简化数据维护：有了约束，数据库管理员可以更容易地管理和维护数据，因为约束会自动执行数据验证。

总之，约束是数据库管理系统中用于确保数据准确性和一致性的重要工具。通过合理应用约束，可以大大提高数据库的质量和可靠性。

约束的分类

MySQL中的约束类型根据其功能和作用可以分为多种，以下是常见的约束类型及其说明：

1. 主键约束（Primary Key Constraint）
   • 定义：唯一标识表中的每一行数据。
   • 特点：主键值必须唯一，且不能为NULL。每个表中只能有一个主键，但可以由一个或多个列组合而成。
2. 外键约束（Foreign Key Constraint）
   • 定义：用于维护两个表之间的关系，确保一个表中的外键值在另一个表的主键中存在。
   • 特点：外键值必须在其所引用的主键表中存在，或者为NULL（如果允许）。外键约束有助于防止破坏表之间关系的无效数据。
3. 唯一约束（Unique Constraint）
   • 定义：确保表中的某一列或一组列的值是唯一的。
   • 特点：唯一约束允许NULL值，但NULL值不被视为重复值。一个表中可以有多个唯一约束。
4. 非空约束（Not Null Constraint）
   • 定义：确保表中的某一列的值不为NULL。
   • 特点：非空约束适用于那些必须有值的字段。即使表中的其他列允许为NULL，也可以通过非空约束保证特定列的值不为NULL。
5. 检查约束（Check Constraint）（注意：MySQL 8.0.16及更高版本支持检查约束）
   • 定义：用于限制表中的某一列的值必须满足特定的条件。
   • 特点：检查约束可以确保列中的值在指定的范围内或满足特定的条件。例如，可以使用检查约束确保年龄必须大于0或日期必须在有效范围内内。
6. 默认值约束（Default Constraint）
   • 定义：为表中的某一列指定默认值，当插入新行时未指定该列的值时，将使用默认值。
   • 特点：默认值约束适用于那些在没有明确指定值时需要一个标准值的字段。
7. 自动递增约束（Auto Increment Constraint）
   • 定义：为表中的某一列指定一个自动递增的值，通常用于主键或ID列。
   • 特点：自动递增约束确保每次插入新行时，该列的值都会自动增加，从而确保主键的唯一性。
8. 级联约束（Cascade Constraint）
   • 定义：当父表中的行被删除或更新时，级联到子表中相应的行。
   • 特点：级联约束有助于维护表之间的数据一致性。例如，当删除一个用户时，可以级联删除该用户所有的订单记录。
9. 索引约束（Index Constraint）
   • 定义：限制在索引列上可以执行的操作，例如唯一性或外键约束。
   • 特点：索引约束可以提高查询性能，并确保数据的唯一性或参照完整性。
10. 空间约束（Spatial Constraint）
    • 定义：指定地理空间列可以容纳的空间类型和维度。
    • 特点：空间约束用于处理地理空间数据，例如地理信息系统（GIS）中的经纬度坐标。

综上所述，MySQL通过提供多种约束类型来确保数据的完整性和一致性。在创建表时，可以根据实际需求添加适当的约束，以提高数据库的可靠性和安全性。

列级约束和表级约束

在MySQL数据库中，列级约束和表级约束是两种重要的约束类型，它们用于确保数据库中数据的准确性和一致性。

列级约束

列级约束是直接在表的列定义时指定的约束。它们作用于单个列，并限制该列的取值范围、规则和限制。常见的列级约束包括：

1. 非空约束（NOT NULL）：
   • 定义：确保列的值不能为空。如果尝试插入或更新一个空值，则会抛出异常。
   • 示例：CREATE TABLE students (id INT NOT NULL, name VARCHAR(50) NOT NULL, age INT); 在这个例子中，id 和 name 字段都被指定为非空字段，而 age 字段没有被指定，所以它可以为空。
2. 唯一约束（UNIQUE）：
   • 定义：确保列的值在表中是唯一的。如果尝试插入或更新一个已经存在的值，则会抛出异常。唯一约束允许存在多个NULL值，因为在数据库中NULL被视为未知，所以两个NULL值并不相等。
   • 示例：CREATE TABLE students (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) UNIQUE, email VARCHAR(100)); 在这个例子中，name 字段被指定为唯一字段，因此每个学生的名字必须是唯一的，而 email 字段没有被指定为唯一字段。
3. 默认值约束（DEFAULT）：
   • 定义：为列指定一个默认值。当插入记录时没有为该列提供值时，将使用默认值。
   • 示例：CREATE TABLE products (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, price DECIMAL(10,2) NOT NULL DEFAULT 0.00); 在这个例子中，如果插入记录时没有为 price 字段提供值，它将默认为0.00。
4. 主键约束（PRIMARY KEY）：
   • 虽然在概念上主键约束通常被视为表级约束的一部分，但实际上它也可以在列级定义。主键约束确保列的值在表中是唯一的，并且不允许为空。一个表只能有一个主键。
   • 示例：CREATE TABLE students (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), age INT); 在这个例子中，id 被指定为主键，并且使用 AUTO_INCREMENT 指令来实现自增长特性。
5. 检查约束（CHECK，MySQL 8.0及以上版本支持）：
   • 定义：对列的值进行条件检查，确保它们满足特定的条件。如果尝试插入或更新一个不满足条件的值，则会抛出异常。
   • 示例：CREATE TABLE students (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), age INT CHECK (age > 0)); 在这个例子中，age 字段被添加了检查约束，确保年龄值大于0。

表级约束

表级约束是在表的列定义完成之后，通过额外的语句来指定的约束。它们通常作用于多个列或整个表，并限制表中数据的取值范围、规则和限制。常见的表级约束包括：

1. 唯一约束（UNIQUE）：
   • 当唯一约束涉及多个列时，它必须在表级定义。这可以确保这些列的组合在表中是唯一的。
   • 示例：CREATE TABLE orders (id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT NOT NULL, order_date DATE NOT NULL, UNIQUE (customer_id, order_date)); 在这个例子中，customer_id 和 order_date 列的组合被指定为唯一约束，确保同一个客户在同一天不能下多个订单。
2. 外键约束（FOREIGN KEY）：
   • 定义：建立两个表之间的关联关系，确保关联数据的一致性。外键约束确保子表的外键列值必须在主表的参照列值范围内，或者为空（如果允许的话）。
   • 示例：CREATE TABLE students (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), age INT, class_id INT, FOREIGN KEY (class_id) REFERENCES classes(id)); 在这个例子中，class_id 被指定为外键，并且使用 REFERENCES 关键字来指定它关联的是 classes 表中的 id 字段。
3. 检查约束（CHECK，同样适用于表级）：
   • 虽然检查约束也可以在列级定义，但在某些情况下，它更适合在表级定义，特别是当约束条件涉及多个列时。
   • 示例：CREATE TABLE orders (id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT NOT NULL, total DECIMAL(10,2) NOT NULL, CONSTRAINT check_total_amount CHECK (total >= 0)); 在这个例子中，使用 CONSTRAINT 关键字来指定了一个表级检查约束，确保订单的总金额必须大于等于0。

总结

列级约束和表级约束在MySQL数据库中扮演着重要的角色，它们共同确保数据的准确性和一致性。列级约束直接在列定义时指定，作用于单个列；而表级约束则在列定义完成之后通过额外的语句指定，作用于多个列或整个表。了解这两种约束的区别和用法，有助于更好地设计和管理数据库。

如何查看、添加和删除约束

在MySQL数据库中，查看、添加和删除约束是数据库管理的重要操作。

查看约束

要查看表中的约束，可以使用以下几种方法：

使用SHOW INDEX语句：

SHOW INDEX IN 表名;

这条语句将显示表中所有的索引，包括主键、唯一键等约束。在结果中，Key_name列的值就是约束名。

使用SHOW CREATE TABLE语句：

SHOW CREATE TABLE 表名;

这条语句将显示表的创建语句，包括所有的约束定义。在结果中，CONSTRAINT后面的值就是系统自动生成的约束名。

添加约束

在MySQL中，可以使用ALTER TABLE语句来添加约束。以下是常见的约束及其添加方法：

添加主键约束：

ALTER TABLE 表名 ADD PRIMARY KEY (列名);

或者在建表时直接添加：

CREATE TABLE 表名 (
    列名 数据类型 PRIMARY KEY,
    ...
);

添加唯一约束：

ALTER TABLE 表名 ADD UNIQUE (列名);

添加外键约束：

ALTER TABLE 表名 ADD FOREIGN KEY (列名) REFERENCES 参照表名(参照列名);

添加非空约束：

ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;

添加默认值约束：

ALTER TABLE 表名 ALTER COLUMN 列名 SET DEFAULT 默认值;

添加检查约束（MySQL 8.0.16及更高版本支持）：

ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK (条件);

删除约束

同样地，可以使用ALTER TABLE语句来删除约束。以下是删除常见约束的方法：

删除主键约束：

ALTER TABLE 表名 DROP PRIMARY KEY;

删除唯一约束：

ALTER TABLE 表名 DROP INDEX 唯一约束名;

注意，这里的唯一约束名是创建唯一约束时指定的名称，或者系统自动生成的名称。

删除外键约束：

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;

同样地，外键约束名是创建外键约束时指定的名称。

删除检查约束：

ALTER TABLE 表名 DROP CHECK 检查约束名;

需要注意的是，在删除约束之前，应该仔细考虑其对数据完整性和业务逻辑的影响。此外，如果表中存在依赖于该约束的数据或索引，删除约束可能会导致错误或数据不一致。因此，在执行删除约束的操作之前，最好先备份数据并谨慎测试。

以上就是在MySQL中查看、添加和删除约束的详细步骤和方法。希望这些信息能帮助你更好地管理数据库中的约束和数据完整性。