游牧学院

5.1 SQLite数据类型
5.3 UNIQUE与NOT NULL约束

SQLite 数据类型与约束：主键与外键

在 SQLite 中，数据类型和约束是数据库设计的核心部分。它们不仅定义了数据的结构，还确保了数据的完整性和一致性。在本节中，我们将深入探讨主键（Primary Key）和外键（Foreign Key）的概念、用法、优缺点以及注意事项。

1. 主键（Primary Key）

1.1 定义

主键是一个表中用于唯一标识每一行数据的字段或字段组合。主键的值必须是唯一的，且不能为 NULL。每个表只能有一个主键。

1.2 创建主键

在创建表时，可以通过 PRIMARY KEY 约束来定义主键。以下是一个示例：

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

在这个示例中，id 字段被定义为主键。SQLite 会自动为 id 字段生成一个唯一的整数值。

1.3 主键的优点

  • 唯一性：主键确保每一行数据的唯一性，避免重复数据。
  • 索引：SQLite 会自动为主键创建索引，从而提高查询性能。
  • 数据完整性：主键约束确保数据的完整性，防止插入无效数据。

1.4 主键的缺点

  • 性能开销：在插入、更新或删除数据时，维护主键的唯一性可能会带来性能开销。
  • 设计限制：一旦定义了主键，修改主键字段的类型或约束可能会比较复杂。

1.5 注意事项

  • 主键字段的选择应谨慎，通常选择不易变化的字段作为主键。
  • 在设计数据库时，尽量避免使用自然键（如电子邮件地址）作为主键，因为这些值可能会发生变化。

2. 外键（Foreign Key）

2.1 定义

外键是一个表中的字段，它引用另一个表的主键。外键用于建立表之间的关系，确保数据的参照完整性。

2.2 创建外键

在创建表时，可以通过 FOREIGN KEY 约束来定义外键。以下是一个示例：

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    user_id INTEGER,
    order_date TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

在这个示例中，user_id 字段是一个外键，它引用了 users 表中的 id 字段。

2.3 外键的优点

  • 数据完整性：外键约束确保了数据的参照完整性，防止孤立记录的出现。
  • 关系建模：外键使得表之间的关系更加清晰，便于理解和维护。
  • 级联操作：可以设置级联删除或更新，自动维护相关数据的一致性。

2.4 外键的缺点

  • 性能开销：外键约束可能会导致性能下降，特别是在进行大量插入、更新或删除操作时。
  • 复杂性：在设计数据库时，外键关系可能会增加复杂性，特别是在涉及多个表的情况下。

2.5 注意事项

  • 在启用外键约束之前，确保 SQLite 数据库的外键支持已开启。可以通过以下命令启用外键支持：
PRAGMA foreign_keys = ON;
  • 外键引用的表必须在外键定义之前创建。
  • 在删除或更新主键时，需考虑外键的级联操作，以避免数据不一致。

3. 示例：综合应用主键与外键

以下是一个综合示例，展示如何在 SQLite 中使用主键和外键：

-- 创建用户表
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

-- 创建订单表
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    order_date TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 插入数据
INSERT INTO users (username, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (username, email) VALUES ('Bob', 'bob@example.com');

INSERT INTO orders (user_id, order_date) VALUES (1, '2023-10-01');
INSERT INTO orders (user_id, order_date) VALUES (2, '2023-10-02');

-- 查询数据
SELECT * FROM users;
SELECT * FROM orders;

-- 删除用户
DELETE FROM users WHERE id = 1;

-- 查询订单，验证级联删除
SELECT * FROM orders;

在这个示例中，我们创建了两个表：users 和 orders。users 表的 id 字段是主键，而 orders 表的 user_id 字段是外键，引用了 users 表的 id 字段。通过设置 ON DELETE CASCADE，当删除用户时，相关的订单也会被自动删除。

结论

主键和外键是 SQLite 数据库设计中不可或缺的部分。它们不仅确保了数据的完整性和一致性，还帮助我们建立了表之间的关系。在设计数据库时，合理使用主键和外键可以提高数据的可维护性和查询性能。然而，开发者也需要注意它们可能带来的性能开销和复杂性。通过深入理解这些概念，您将能够更有效地设计和管理 SQLite 数据库。

5.1 SQLite数据类型
5.3 UNIQUE与NOT NULL约束