4.2 MySQL数据的完整性全解，看不懂你来找我（三大完整性规则、七种约束控制）

在MySQL数据库中，数据完整性是指确保数据库中的数据准确、有效、一致且无歧义。这些约束在数据库设计中起着至关重要的作用，它们帮助开发者维护数据的完整性和准确性。通过这些约束，我们确保了图书馆管理系统中的数据完整性和一致性。如果尝试插入违反这些约束的数据，MySQL将拒绝这些操作并返回错误。通过MySQL代码来演示如何创建上述案例中的表，并应用实体完整性、参照完整性和域完整性的规则。在MySQL中

Argonaut_

1770人浏览 · 2024-09-13 14:10:00

Argonaut_ · 2024-09-13 14:10:00 发布

文章目录

MySQL数据的完整性

MySQL数据的完整性

在MySQL数据库中，数据完整性是指确保数据库中的数据准确、有效、一致且无歧义。为了维护数据完整性，MySQL提供了多种完整性规则，这些规则可以分为以下三类：

4.2.1 3类完整性规则

实体完整性（Entity Integrity）
- 主键约束（PRIMARY KEY）：确保表中每一行数据都有一个唯一标识，通常通过一个或多个列组成的主键来实现。主键的值必须唯一，且不能为NULL。
- 候选键约束：候选键是能够唯一标识表中每行数据的列或列组合，除了主键之外，还可以有多个候选键，它们都通过UNIQUE约束来实现。
参照完整性（Referential Integrity）
- 外键约束（FOREIGN KEY）：用于维护两个表之间的关系，确保一个表中的外键值必须在另一个表的主键中存在，或者为NULL（如果外键列允许NULL值）。外键约束用于防止破坏表之间关系的无效数据。
域完整性（Domain Integrity）
- 非空约束（NOT NULL）：确保列中的值不能为NULL，适用于那些必须有值的字段。
- 唯一约束（UNIQUE）：确保列中的所有值都是唯一的，可以有多个列组合使用此约束，以确保数据的唯一性。
- 检查约束（CHECK）：用于确保列中的值满足特定的条件，例如年龄必须大于0，日期必须在有效范围内等。
- 默认值约束（DEFAULT）：当没有为列指定值时，使用默认值。这适用于那些在没有明确指定值时需要一个标准值的字段。

案例说明

以一个简单的图书馆管理系统为例，可以创建三个表：Authors（作者）、Books（书籍）和Borrowers（借阅者），以及一个关联表Borrowing（借阅记录）。

Authors表包含作者的ID和姓名，其中作者ID是主键。
Books表包含书籍的ID、标题、作者ID和ISBN号，其中书籍ID是主键，ISBN号是唯一约束。
Borrowers表包含借阅者的ID和姓名，其中借阅者ID是主键。
Borrowing表包含借阅记录的ID、借阅者ID、书籍ID和借阅日期，其中借阅记录ID是主键，借阅者ID和书籍ID是外键，分别引用Borrowers表和Books表。

通过这些表和约束的设计，可以确保图书馆管理系统中的数据完整性，例如：

每本书和每位作者都有一个唯一标识。
借阅记录中的每本书和每位借阅者都必须在对应的表中存在。
书籍的ISBN号是唯一的，不会出现重复。

这些完整性规则共同作用，确保了数据库中数据的准确性和一致性。

通过MySQL代码来演示如何创建上述案例中的表，并应用实体完整性、参照完整性和域完整性的规则。

-- 创建Authors表，包含作者ID和姓名
CREATE TABLE Authors (
    AuthorID INT AUTO_INCREMENT PRIMARY KEY, -- 主键约束，确保实体完整性
    Name VARCHAR(255) NOT NULL -- 非空约束，确保域完整性
);

-- 创建Books表，包含书籍ID、标题、作者ID和ISBN号
CREATE TABLE Books (
    BookID INT AUTO_INCREMENT PRIMARY KEY, -- 主键约束，确保实体完整性
    Title VARCHAR(255) NOT NULL, -- 非空约束，确保域完整性
    AuthorID INT, -- 外键约束，确保参照完整性
    ISBN VARCHAR(20) UNIQUE NOT NULL -- 唯一约束，确保域完整性
);

-- 创建Borrowers表，包含借阅者ID和姓名
CREATE TABLE Borrowers (
    BorrowerID INT AUTO_INCREMENT PRIMARY KEY, -- 主键约束，确保实体完整性
    Name VARCHAR(255) NOT NULL -- 非空约束，确保域完整性
);

-- 创建Borrowing表，包含借阅记录ID、借阅者ID、书籍ID和借阅日期
CREATE TABLE Borrowing (
    BorrowingID INT AUTO_INCREMENT PRIMARY KEY, -- 主键约束，确保实体完整性
    BorrowerID INT NOT NULL, -- 非空约束，确保域完整性
    BookID INT NOT NULL, -- 非空约束，确保域完整性
    BorrowDate DATE NOT NULL, -- 非空约束，确保域完整性
    ReturnDate DATE, -- 可以为空，如果书籍尚未归还
    FOREIGN KEY (BorrowerID) REFERENCES Borrowers(BorrowerID), -- 外键约束，确保参照完整性
    FOREIGN KEY (BookID) REFERENCES Books(BookID) -- 外键约束，确保参照完整性
);

-- 将Books表的AuthorID设置为外键，引用Authors表的AuthorID
ALTER TABLE Books
ADD CONSTRAINT fk_author
FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID);

在上述代码中，我们创建了四个表，并为它们设置了相应的完整性约束：

Authors表：
- AuthorID：为主键，确保每个作者都有一个唯一标识。
- Name：设置了非空约束，确保作者名必须提供。
Books表：
- BookID：为主键，确保每本书都有一个唯一标识。
- Title：设置了非空约束，确保书名必须提供。
- ISBN：设置了唯一约束，确保每本书的ISBN号是唯一的。
- AuthorID：设置了外键约束，引用Authors表的AuthorID，确保参照完整性。
Borrowers表：
- BorrowerID：为主键，确保每个借阅者都有一个唯一标识。
- Name：设置了非空约束，确保借阅者姓名必须提供。
Borrowing表：
- BorrowingID：为主键，确保每条借阅记录都有一个唯一标识。
- BorrowerID和BookID：设置了非空约束，并作为外键引用Borrowers表和Books表，确保参照完整性。
- BorrowDate：设置了非空约束，确保借阅日期必须提供。
- ReturnDate：可以为空，表示书籍可能尚未归还。

通过这些约束，我们确保了图书馆管理系统中的数据完整性和一致性。如果尝试插入违反这些约束的数据，MySQL将拒绝这些操作并返回错误。

4,2,2MySQL约束控制（7种约束）

在MySQL中，约束是用来确保数据准确性和完整性的重要工具。以下是MySQL中常见的7种约束。

主键约束（PRIMARY KEY）
- 定义：唯一标识表中每条记录的字段。
- 特点：表中只能有一个主键，主键值必须唯一且非空。
- 示例代码：
```
CREATE TABLE Users (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    Username VARCHAR(50) NOT NULL,
    Email VARCHAR(100)
);
```
- 描述：UserID 作为主键，每条用户记录都可以通过这个唯一标识符来识别。
外键约束（FOREIGN KEY）
- 定义：用于建立两个表之间的关联关系，确保参照完整性。
- 特点：外键值必须在其所引用的主键表中存在或为NULL（如果允许）。
- 示例代码：
```
CREATE TABLE Orders (
    OrderID INT AUTO_INCREMENT PRIMARY KEY,
    UserID INT,
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);
```
- 描述：Orders 表中的 UserID 是外键，它引用 Users 表的主键 UserID，确保订单总是关联到一个有效的用户。
唯一约束（UNIQUE）
- 定义：确保列中的所有值都是唯一的。
- 特点：可以有空值，但每个值只能出现一次。
- 示例代码：
```
CREATE TABLE Users (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE
);
```
- 描述：Email 字段设置了唯一约束，确保同一个邮箱地址不会被不同的用户使用。
非空约束（NOT NULL）
- 定义：确保列中的值不能为NULL。
- 特点：插入或更新记录时，该列必须有值。
- 示例代码：
```
CREATE TABLE Users (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    Username VARCHAR(50) NOT NULL
);
```
- 描述：Username 字段设置了非空约束，用户必须提供用户名才能创建记录。
默认值约束（DEFAULT）
- 定义：当没有为列指定值时，使用默认值。
- 特点：适用于在没有明确指定值时需要一个标准值的字段。
- 示例代码：
```
CREATE TABLE Users (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    IsActive BOOLEAN DEFAULT TRUE
);
```
- 描述：IsActive 字段设置了默认值为 TRUE，如果创建用户时未指定该字段，系统会自动将其设置为 TRUE。
检查约束（CHECK）（MySQL 8.0.16及以上版本支持）
- 定义：用于确保列中的值满足特定条件。
- 特点：可以定义复杂的条件，确保数据的一致性。
- 示例代码：
```
CREATE TABLE Users (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    Age INT CHECK (Age > 0 AND Age < 130)
);
```
- 描述：Age 字段设置了检查约束，确保年龄在1到129岁之间。
自增长约束（AUTO_INCREMENT）
- 定义：用于自动为字段值递增。
- 特点：通常用于主键字段，每当插入新记录时，字段值自动加1。
- 示例代码：
```
CREATE TABLE Users (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    Username VARCHAR(50) NOT NULL
);
```
- 描述：UserID 字段设置了自增长约束，每次添加新用户时，UserID 会自动递增。