MySQL约束类型与数据完整性保障

MySQL 约束类型概述

在 MySQL 数据库中，约束（Constraints）是一种规则，用于限制表中数据的插入、更新和删除操作，以此确保数据的准确性和一致性，即保障数据完整性。数据完整性分为实体完整性、域完整性、参照完整性和用户定义完整性。MySQL 提供了多种约束类型来满足这些完整性需求。

主键约束（Primary Key Constraint）

主键是表中的一个或多个字段，其值能够唯一地标识表中的每一行记录。主键约束确保了实体完整性，即表中的每一行都具有唯一标识。

主键约束的特点

唯一性：主键的值在整个表中必须是唯一的，不允许出现重复值。
非空性：主键字段不能包含 NULL 值。

创建主键约束的方式

在创建表时定义主键

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50)
);

在上述示例中，employee_id 字段被定义为主键。MySQL 会自动为该字段创建一个唯一索引，以加快基于主键的查询操作。

在已有表上添加主键约束

-- 假设 employees 表已存在，但没有主键
-- 添加单个字段为主键
ALTER TABLE employees
ADD PRIMARY KEY (employee_id);

-- 如果要添加多个字段组成的复合主键
ALTER TABLE employees
ADD PRIMARY KEY (employee_id, department);

复合主键是由多个字段组合而成，它们共同唯一标识表中的每一行。需要注意的是，复合主键中的所有字段组合起来必须唯一，单个字段的值可以重复。

唯一约束（Unique Constraint）

唯一约束用于确保表中指定字段的值具有唯一性，但与主键约束不同的是，唯一约束允许字段值为 NULL，并且一个表中可以有多个唯一约束。

唯一约束的特点

唯一性：除 NULL 值外，指定字段的值在表中必须是唯一的。
可空性：字段可以包含 NULL 值，且多个 NULL 值被视为不同的值。

创建唯一约束的方式

在创建表时定义唯一约束

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50) UNIQUE
);

在这个 users 表中，email 和 username 字段都被定义为具有唯一约束，这意味着在这两个字段中，除了 NULL 值外，不会出现重复的数据。

在已有表上添加唯一约束

-- 假设 users 表已存在，为 phone_number 字段添加唯一约束
ALTER TABLE users
ADD UNIQUE (phone_number);

外键约束（Foreign Key Constraint）

外键约束用于建立两个表之间的关联关系，从而保证参照完整性。它定义了一个表中的字段（外键）必须引用另一个表中的主键或唯一键。

外键约束的特点

关联性：外键字段的值必须在被引用表的主键或唯一键值中存在，或者为 NULL（如果外键允许 NULL 值）。
级联操作：可以定义在主表记录删除或更新时，对从表相关记录的操作，如级联删除（CASCADE DELETE）、级联更新（CASCADE UPDATE）等。

创建外键约束的方式

在创建表时定义外键约束

-- 创建 departments 表
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

-- 创建 employees 表，并定义外键关联到 departments 表
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

在上述示例中，employees 表的 department_id 字段是外键，它引用了 departments 表的 department_id 主键。这意味着 employees 表中 department_id 的值必须在 departments 表的 department_id 中存在，或者为 NULL（如果允许 NULL 值）。

在已有表上添加外键约束

-- 假设 employees 和 departments 表已存在，且 employees 表没有外键
ALTER TABLE employees
ADD FOREIGN KEY (department_id) REFERENCES departments(department_id);

级联操作示例

-- 创建表时定义级联删除和级联更新
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

在上述 orders 表中，当 customers 表中 customer_id 对应的记录被删除时，orders 表中所有相关的订单记录也会被自动删除；当 customers 表中 customer_id 被更新时，orders 表中对应的 customer_id 也会自动更新。

检查约束（Check Constraint）

检查约束用于限制字段值必须满足指定的条件，以此保障域完整性。它允许定义一个条件表达式，插入或更新记录时，该表达式必须为真。

检查约束的特点

条件限制：字段值必须满足定义的条件，否则操作将失败。
灵活性：可以根据业务需求定义各种复杂的条件。

创建检查约束的方式

在创建表时定义检查约束

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    price DECIMAL(10, 2),
    CHECK (price > 0)
);

在 products 表中，price 字段定义了一个检查约束，要求价格必须大于 0。这确保了插入或更新 products 表记录时，price 值是合理的正数。

在已有表上添加检查约束

-- 假设 products 表已存在，添加检查约束确保库存数量为非负数
ALTER TABLE products
ADD CHECK (quantity >= 0);

默认约束（Default Constraint）

默认约束为字段提供一个默认值，当插入记录时，如果没有为该字段指定值，将使用默认值。

默认约束的特点

自动赋值：在插入操作中，若字段值未指定，自动使用默认值。
数据一致性：有助于保持数据的一致性，特别是对于一些有默认业务值的字段。

创建默认约束的方式

在创建表时定义默认约束

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    registration_date DATE DEFAULT CURRENT_DATE
);

在 users 表中，registration_date 字段定义了默认约束，默认值为当前日期（CURRENT_DATE）。当插入新用户记录时，如果没有指定 registration_date 的值，将自动使用当前日期。

在已有表上修改字段添加默认约束

-- 假设 users 表已存在，为 gender 字段添加默认值 'M'
ALTER TABLE users
ALTER COLUMN gender SET DEFAULT 'M';

约束与数据完整性保障的关系

实体完整性与主键约束

主键约束是保障实体完整性的核心机制。通过确保表中每一行记录具有唯一标识，避免了重复记录的插入。例如在 employees 表中，employee_id 作为主键，使得每个员工都有唯一的标识。如果尝试插入一个已存在 employee_id 的记录，MySQL 将抛出错误，从而保证了员工数据的实体完整性。

域完整性与检查约束、默认约束

检查约束：通过定义字段值必须满足的条件，确保了数据在特定域内的合法性。如 products 表中 price 字段的检查约束，保证了价格始终为正数，符合实际业务中产品价格的取值范围，从而保障了域完整性。
默认约束：为字段提供默认值，使得在插入记录时即使未指定值，也能有合理的默认数据。这不仅简化了插入操作，还保证了数据的一致性，维护了域完整性。例如 users 表中 registration_date 的默认值，确保了新用户注册时都有一个合理的注册日期记录。

参照完整性与外键约束

外键约束建立了表与表之间的关联关系，确保了参照完整性。在 employees 和 departments 表的例子中，employees 表的 department_id 作为外键引用 departments 表的 department_id 主键。这意味着只有存在的部门才能关联到员工，避免了无效部门的引用。同时，通过级联操作，可以确保在主表数据变化时，从表相关数据也能正确更新或删除，进一步维护了参照完整性。

数据完整性的综合保障

不同的约束类型相互配合，共同保障了数据库的数据完整性。例如，在一个电子商务系统中，orders 表可能有主键约束确保每个订单的唯一性（实体完整性），外键约束关联到 customers 表和 products 表保证订单与客户、产品的正确关联（参照完整性），检查约束确保订单金额为正数（域完整性），默认约束为订单状态提供默认值（域完整性）。

约束的管理与优化

约束的查看

查看表的约束信息 可以使用 SHOW CREATE TABLE 语句来查看表的详细创建语句，其中包含了约束信息。

SHOW CREATE TABLE employees;

该语句将显示 employees 表的创建语句，包括主键、外键、唯一约束等信息。

通过 INFORMATION_SCHEMA 查看约束 INFORMATION_SCHEMA 数据库存储了 MySQL 数据库元数据信息。可以通过查询 TABLE_CONSTRAINTS 和 KEY_COLUMN_USAGE 表来获取约束相关信息。

-- 查看所有表的约束信息
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'your_database_name';

-- 查看特定表的外键约束信息
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_NAME = 'employees'
  AND CONSTRAINT_TYPE = 'FOREIGN KEY';

约束的修改与删除

修改约束 修改约束通常涉及修改已有约束的定义，例如修改外键的级联操作。

-- 修改 employees 表的外键约束，添加级联删除
ALTER TABLE employees
DROP FOREIGN KEY fk_department_id;

ALTER TABLE employees
ADD FOREIGN KEY (department_id) REFERENCES departments(department_id)
ON DELETE CASCADE;

这里先删除原有的外键约束，然后重新添加并指定级联删除操作。

删除约束 删除约束可以使用 ALTER TABLE 语句。

-- 删除 employees 表的唯一约束
ALTER TABLE employees
DROP INDEX unique_email;

-- 删除 employees 表的外键约束
ALTER TABLE employees
DROP FOREIGN KEY fk_department_id;

-- 删除 employees 表的主键约束
ALTER TABLE employees
DROP PRIMARY KEY;

约束对性能的影响及优化

性能影响
- 插入和更新性能：约束会增加插入和更新操作的开销。例如，主键和唯一约束需要检查唯一性，外键约束需要检查引用的完整性，检查约束需要评估条件表达式。这些操作都需要数据库进行额外的计算和查询，从而影响插入和更新的速度。
- 查询性能：适当的约束可以通过创建索引来提高查询性能。例如，主键和唯一约束会自动创建索引，外键约束也可能会创建索引，这些索引可以加快基于约束字段的查询操作。然而，如果约束过多或不合理，可能会导致索引过多，增加存储开销，并影响查询优化器的决策，从而降低查询性能。
优化建议
- 合理设计约束：只在必要的字段上定义约束，避免过度约束。例如，对于一些不参与关联或唯一性检查的字段，不需要定义主键或唯一约束。
- 索引优化：了解约束自动创建的索引，并根据查询需求进行优化。可以删除不必要的索引，或者创建复合索引来提高查询效率。例如，如果经常根据 employee_id 和 department_id 进行查询，可以考虑创建一个包含这两个字段的复合索引。
- 批量操作：在进行插入或更新操作时，尽量使用批量操作，这样可以减少约束检查的次数，提高性能。例如，使用 INSERT INTO... VALUES (...), (...), (...) 语法一次性插入多条记录。

不同约束类型在实际场景中的应用案例

电商系统中的应用

订单表（orders）
- 主键约束：order_id 作为主键，确保每个订单具有唯一标识，方便订单的跟踪和管理。
```
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);
```
- 外键约束：customer_id 作为外键，关联到 customers 表的 customer_id 主键，确保订单与客户的正确关联。
```
ALTER TABLE orders
ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
```
- 检查约束：total_amount 字段添加检查约束，确保订单总金额为正数。
```
ALTER TABLE orders
ADD CHECK (total_amount > 0);
```
订单详情表（order_items）
- 主键约束：通常由 order_item_id 作为主键，或者由 order_id 和 product_id 组成复合主键，确保每个订单详情记录的唯一性。
```
CREATE TABLE order_items (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);
```
- 外键约束：order_id 关联到 orders 表，product_id 关联到 products 表，保证订单详情与订单和产品的正确关联。
- 检查约束：quantity 字段添加检查约束，确保购买数量为正整数。
```
ALTER TABLE order_items
ADD CHECK (quantity > 0);
```

企业人事管理系统中的应用

员工表（employees）

主键约束：employee_id 作为主键，唯一标识每个员工。

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    hire_date DATE
);

外键约束：department_id 关联到 departments 表的 department_id 主键，确定员工所属部门。

ALTER TABLE employees
ADD FOREIGN KEY (department_id) REFERENCES departments(department_id);

默认约束：hire_date 字段设置默认值为当前日期，方便记录新员工入职日期。

ALTER TABLE employees
ALTER COLUMN hire_date SET DEFAULT CURRENT_DATE;

薪资表（salaries）
- 主键约束：由 employee_id 和 salary_date 组成复合主键，确保每个员工在每个薪资发放日期的记录唯一。
```
CREATE TABLE salaries (
    employee_id INT,
    salary_date DATE,
    salary_amount DECIMAL(10, 2),
    PRIMARY KEY (employee_id, salary_date),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);
```
- 外键约束：employee_id 关联到 employees 表，保证薪资记录与员工的正确关联。
- 检查约束：salary_amount 字段添加检查约束，确保薪资金额为正数。
```
ALTER TABLE salaries
ADD CHECK (salary_amount > 0);
```

通过上述实际场景的应用案例，可以更清晰地看到不同约束类型在保障数据完整性方面的重要作用，以及它们如何协同工作来满足复杂的业务需求。在实际数据库设计和开发中，根据业务场景合理选择和应用约束类型是确保数据库可靠性和高效性的关键步骤。