SQLite 约束管理

介绍

在这个实验中，你将探索 SQLite 约束管理，以确保数据的完整性。你将从定义外键约束开始，建立表之间的关系，实现 CHECK 约束，创建复合键，最后，测试约束冲突，以了解 SQLite 如何强制执行这些规则。这种实践经验将为你提供一个关于如何在 SQLite 数据库中维护数据一致性的实际理解。

创建包含外键约束的表

在这个步骤中，你将创建两个表，customers（顾客）和 orders（订单），并在它们之间建立一个外键约束。这个约束将确保每个订单都与一个有效的顾客相关联。

首先，通过在终端中运行以下命令来打开 SQLite shell：

sqlite3 /home/labex/project/database.db

这个命令会打开一个到名为 database.db 的 SQLite 数据库文件的连接。如果该文件不存在，SQLite 将会创建它。你现在应该看到 sqlite> 提示符。

现在，使用以下 SQL 命令创建 customers 表：

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,
    last_name TEXT,
    email TEXT
);

这个命令创建了一个名为 customers 的表，包含四个列：customer_id、first_name、last_name 和 email。customer_id 列是主键，并且对于每个新顾客会自动递增。

接下来，创建一个带有外键约束的 orders 表，该约束引用 customers 表：

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER,
    order_date TEXT,
    total REAL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

这个命令创建了一个名为 orders 的表，包含四个列：order_id、customer_id、order_date 和 total。order_id 列是主键。FOREIGN KEY (customer_id) REFERENCES customers(customer_id) 子句建立了一个外键约束，确保 orders 表中的 customer_id 引用 customers 表中有效的 customer_id。

要验证表是否已创建，你可以使用以下命令：

.tables

这个命令将列出数据库中的所有表。你应该在输出中看到 customers 和 orders。

插入数据并测试外键约束

在这个步骤中，你将向 customers（顾客）和 orders（订单）表插入数据，并测试外键约束。

首先，你需要在 SQLite 中启用外键约束（默认情况下是禁用的）：

PRAGMA foreign_keys = ON;

现在，向 customers 表插入一些数据：

INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', 'john.doe@example.com'),
('Jane', 'Smith', 'jane.smith@example.com');

这个命令向 customers 表插入两个新顾客。customer_id 将被自动分配。

现在，向 orders 表插入一个订单，引用其中一个已存在的顾客：

INSERT INTO orders (customer_id, order_date, total) VALUES
(1, '2023-01-01', 100.00);

这个命令向 orders 表插入一个新订单，将其与 customer_id 为 1 的顾客关联起来。

接下来，尝试插入一个 customer_id 在 customers 表中不存在的订单：

INSERT INTO orders (customer_id, order_date, total) VALUES
(99, '2023-01-02', 50.00);

你将会看到一个类似于这样的错误信息：Error: FOREIGN KEY constraint failed（错误：外键约束失败）。这确认了外键约束工作正常，阻止你为一个不存在的顾客创建订单。

要查看表中的数据，你可以使用以下命令：

SELECT * FROM customers;
SELECT * FROM orders;

这些命令将分别显示 customers 和 orders 表的内容。

实现 CHECK 约束

在这个步骤中，你将创建一个新的 customers_with_check 表，其中包含一个 CHECK 约束，以确保所有电子邮件地址都包含 @ 符号。

首先，让我们直接创建一个带有 CHECK 约束的新表：

CREATE TABLE customers_with_check (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,
    last_name TEXT,
    email TEXT CHECK (email LIKE '%@%')
);

这个命令创建了一个名为 customers_with_check 的新表，并在 email 列上添加了一个 CHECK 约束，以确保它包含 @ 符号。LIKE 操作符用于模式匹配，% 是一个通配符，可以匹配任何字符序列。

现在，让我们将现有的顾客数据复制到新表中：

INSERT INTO customers_with_check (customer_id, first_name, last_name, email)
SELECT customer_id, first_name, last_name, email FROM customers;

现在，尝试插入一个带有无效电子邮件地址的新顾客：

INSERT INTO customers_with_check (first_name, last_name, email) VALUES
('Charlie', 'Davis', 'invalid-email');

你应该会看到一个类似于这样的错误信息：Error: CHECK constraint failed: email（错误：CHECK 约束失败：email）。这表明 CHECK 约束阻止了无效电子邮件地址的插入。

为了验证 CHECK 约束是否正常工作，插入一个带有有效电子邮件地址的顾客：

INSERT INTO customers_with_check (first_name, last_name, email) VALUES
('Alice', 'Brown', 'alice.brown@example.com');

这个命令应该会成功执行，因为电子邮件地址包含 @ 符号。

创建包含组合键的表

在这个步骤中，你将创建一个名为 enrollments（注册）的表，它包含一个由 student_id（学生 ID）和 course_id（课程 ID）列组成的组合键（composite key）。

在 SQLite shell 中执行以下 SQL 语句：

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrollment_date TEXT,
    PRIMARY KEY (student_id, course_id)
);

这个命令创建了一个名为 enrollments 的表，包含三列：student_id、course_id 和 enrollment_date（注册日期）。PRIMARY KEY (student_id, course_id) 子句指定该表的主键（primary key）由 student_id 和 course_id 列共同组成。这意味着 student_id 和 course_id 的组合对于表中的每一行都必须是唯一的。

现在，向 enrollments 表插入一些数据：

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 101, '2023-01-01'),
(2, 101, '2023-01-02'),
(1, 102, '2023-01-03');

这将在 enrollments 表中插入三行数据。

接下来，尝试插入一行与现有行具有相同 student_id 和 course_id 的数据：

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 101, '2023-01-04');

你应该会看到一个类似于这样的错误信息：Error: UNIQUE constraint failed: enrollments.student_id, enrollments.course_id（错误：UNIQUE 约束失败：enrollments.student_id, enrollments.course_id）。这表明组合键约束阻止了重复行的插入。

为了验证该表是否已正确创建，你可以在 SQLite shell 中使用 .tables 命令：

.tables

你应该在输出中看到 enrollments。

总结

在这个实验中，你已经学习了如何在 SQLite 中定义和实现约束（constraints），以确保数据的完整性（data integrity）。你创建了包含外键约束（foreign key constraints）的表，实现了 CHECK 约束来验证数据，并创建了包含组合键（composite keys）的表，以基于多个列唯一标识行。通过理解和利用这些约束，你可以构建健壮且可靠的数据库，从而维护数据的一致性（data consistency）。