SQLite - 约束

SQLite - 约束 (Constraints)

约束 (Constraints) 是应用于表列的规则，用于强制执行数据完整性。它们限制了可以插入的数据类型，确保数据库的准确性和可靠性。约束可以在列级别或表级别定义。

SQLite 中的主要约束包括：

• NOT NULL：确保列不能存储 NULL 值。
• UNIQUE：确保列（或一组列）中的所有值都是唯一的。
• PRIMARY KEY：NOT NULL 和 UNIQUE 的组合，用于唯一标识表中的每一行。
• FOREIGN KEY：在两个表之间创建链接，强制执行引用完整性。
• CHECK：确保列中的值满足特定条件。
• DEFAULT：如果未指定值，则为列提供默认值。

PRIMARY KEY 约束 (主键约束)

主键对于识别记录至关重要。虽然任何唯一列都可以作为主键，但 SQLite 对 INTEGER PRIMARY KEY 类型的列有特殊行为。

一个 INTEGER PRIMARY KEY 列成为内部 rowid 的别名。这会创建一个高性能、自动递增的 64 位整型键。这是在 SQLite 中创建代理键的推荐方法。

-- 推荐的创建带自动递增 ID 的表的方法
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY, -- 自动 NOT NULL、UNIQUE 和自动递增
    email TEXT NOT NULL UNIQUE,
    created_at TEXT DEFAULT (datetime('now'))
);

AUTOINCREMENT 关键字是可选的。它会增加少量开销，但能保证新生成的键将始终大于该表中曾经使用的任何键，即使已删除的行也是如此。仅当您不允许 rowid 值被重用时才使用它。

FOREIGN KEY 约束 (外键约束)

外键是关系型数据库的基石。它们在表之间创建父子关系，确保子表中的值在父表中有一个对应的条目。

注意：为了兼容性，最佳实践是为每个连接启用外键支持，使用 PRAGMA foreign_keys = ON;。

示例

让我们来建模一个 authors 表和一个 books 表。每本书都必须与一个有效的作者关联。

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER NOT NULL,
    FOREIGN KEY (author_id) REFERENCES authors (author_id)
        ON DELETE CASCADE -- 如果作者被删除，其书籍也将被删除。
        ON UPDATE CASCADE -- 如果 author_id 发生变化，在 books 表中也进行更新。
);

-- 这将成功
INSERT INTO authors (name) VALUES ('J.R.R. Tolkien');
INSERT INTO books (title, author_id) VALUES ('The Hobbit', 1);

-- 这将失败，因为 author_id 99 不存在
INSERT INTO books (title, author_id) VALUES ('The Silmarillion', 99);

其他常见约束

• UNIQUE：适用于不允许重复值的列，例如电子邮件地址或用户名。
• CHECK：对于业务逻辑很有用，例如确保价格为正数或评级在 1 到 5 之间。
• DEFAULT：通过自动填充值（例如注册时间戳）来简化 INSERT 语句。

组合示例

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    sku TEXT NOT NULL UNIQUE, -- 库存单位必须唯一
    name TEXT NOT NULL,
    price REAL NOT NULL CHECK(price > 0), -- 价格必须为正数
    stock_count INTEGER DEFAULT 0 -- 如果未提供库存，则默认为 0
);

修改约束

SQLite 的 ALTER TABLE 命令是有限的。您不能直接删除或修改现有列上的约束。标准且安全的程序涉及在事务内部重新创建表。

示例：添加 NOT NULL 约束

假设我们有一个 products 表，并且希望将 name 列设置为 NOT NULL。

BEGIN TRANSACTION;

-- 1. 创建一个具有所需模式的新表
CREATE TABLE products_new (
    product_id INTEGER PRIMARY KEY,
    sku TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL, -- 新的约束在此处添加
    price REAL NOT NULL CHECK(price > 0),
    stock_count INTEGER DEFAULT 0
);

-- 2. 将旧表中的数据复制到新表
INSERT INTO products_new (product_id, sku, name, price, stock_count)
SELECT product_id, sku, name, price, stock_count FROM products;

-- 3. 删除旧表
DROP TABLE products;

-- 4. 将新表重命名为原始名称
ALTER TABLE products_new RENAME TO products;

COMMIT;