Software Systems Atlas

中文

English

中文

English

主题

第 5 卷 / 第 1 章 / 第 1 课

1.1 关系模型、约束与安全写入 ​

关系数据库把数据组织成表，并通过约束、事务和恢复机制维护合法状态。手工编辑 CSV 时容易漏掉的重复键、无效引用和半次更新，可以交给数据库检查。本课用 SQLite 建一个设备目录，验证合法写入、约束拒绝和事务回滚；数据库很认真，但它只会认真执行你写下来的规则。

本课目标 ​

  • 区分 table、row、column、key 与 constraint；
  • 用 SQLite 建立一组可复用的练习表；
  • 正确执行 INSERT、UPDATE 与 DELETE；
  • 理解 NULL、动态类型和外键的边界；
  • 用事务和参数绑定降低数据损坏风险。

为什么不直接维护 CSV/JSON ​

平面文件适合交换、小型配置和 append-only 记录，但一旦需要多人并发更新、跨记录约束、条件查询和崩溃恢复，就必须自行实现许多数据库能力。

数据库管理系统通常提供：

  • schema 与约束，拒绝不合法状态；
  • transaction，把一组变更作为一个逻辑单位；
  • concurrency control，协调并发读写；
  • index 与 optimizer，避免每次都手写扫描流程；
  • log、checkpoint、backup 等恢复机制；
  • 权限、审计和运维接口。

数据库也不会自动保证业务正确。错误 schema、缺失 constraint、错误 transaction boundary 和拼接 SQL，仍会制造一致性与安全问题。

建立可重复实验 ​

本章使用 SQLite 3.37.0 或更新版本的 CLI，示例从一个空的练习数据库开始。先确认版本：

bash
sqlite3 --version

从官方 SQLite 下载页或操作系统包管理器安装。课程不把某条安装命令写成跨平台通用答案。

打开一个专用练习数据库：

bash
sqlite3 catalog-lab.db

在 CLI 中启用便于发现错误的设置：

sql
.bail on
.headers on
.mode box
PRAGMA foreign_keys = ON;

这些以点开头的行是 SQLite CLI 命令，不能原样传给驱动的 SQL 执行接口。每个新连接都应在事务外执行 PRAGMA foreign_keys = ON，再用 PRAGMA foreign_keys; 确认返回 1；事务中切换此设置不会生效。SQLite 外键文档 说明了这个连接边界。

用约束表达不变量 ​

sql
CREATE TABLE item_types (
    item_type_id INTEGER PRIMARY KEY,
    code         TEXT NOT NULL UNIQUE,
    display_name TEXT NOT NULL
) STRICT;

CREATE TABLE catalog_items (
    item_id       INTEGER PRIMARY KEY,
    item_type_id  INTEGER NOT NULL,
    name          TEXT NOT NULL UNIQUE,
    value_cents   INTEGER NOT NULL CHECK (value_cents >= 0),
    discovered_on TEXT,
    FOREIGN KEY (item_type_id)
        REFERENCES item_types(item_type_id)
        ON UPDATE RESTRICT
        ON DELETE RESTRICT,
    CHECK (
        discovered_on IS NULL
        OR discovered_on GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
    )
) STRICT;

约束各自承担不同责任：

  • PRIMARY KEY：唯一标识一行；
  • NOT NULL：不允许 unknown/missing；
  • UNIQUE：候选值不得重复；
  • CHECK：限制行内条件；
  • FOREIGN KEY：引用的父行必须满足参照完整性。

示例把金额保存为最小货币单位的整数，避免二进制浮点用于精确金额。真实财务系统还要定义币种、舍入和会计规则。

discovered_on 的 CHECK 只检查文本形状，不证明日期真实存在。SQLite 没有独立 DATE storage class；生产应用可使用更强的约束、trigger 或应用验证，并统一时区与格式。

SQLite 的类型边界 ​

SQLite 默认采用 type affinity，同一列在某些情况下仍可能保存不同 storage class。PostgreSQL、MySQL 等也不是简单的“绝对严格”：它们各有 implicit cast、mode 与 domain 行为。

本课两张表都使用 STRICT，因此 value_cents 不能接收无法无损转换为整数的文本。仅声明 INTEGER CHECK (value_cents >= 0) 的普通表仍可能接受非数字文本，不能据此证明金额类型正确。STRICT 仍允许无损类型转换；日期真实性和业务状态还需要各自的约束。SQLite STRICT Tables

明确列名地插入 ​

sql
INSERT INTO item_types (item_type_id, code, display_name)
VALUES
    (1, 'device', '设备'),
    (2, 'spare_part', '备件'),
    (3, 'consumable', '消耗品');

INSERT INTO catalog_items (
    item_id,
    item_type_id,
    name,
    value_cents,
    discovered_on
)
VALUES
    (1, 1, '传感器套件',   120000, '2024-01-15'),
    (2, 3, '清洁液',   8000, '2024-03-01'),
    (3, 1, '校准仪', 180000, '2024-02-20'),
    (4, 2, '备用外壳',  95000, '2024-05-10'),
    (5, 3, '密封胶',  15000, NULL);

显式列名可以避免 schema 增加列后发生位置错配，也让 code review 看得出每个值的含义。

一次 multi-row INSERT 是一条 statement。若其中一行违反约束，这条 statement 的效果受 conflict resolution 和 transaction context 影响；不要把不同数据库的错误处理细节想当然地混用。

UPDATE 与 DELETE 先确认目标集 ​

先在 transaction 中预览范围：

sql
BEGIN IMMEDIATE;

SELECT item_id, name, value_cents
FROM catalog_items
WHERE item_type_id = 1
ORDER BY item_id;

UPDATE catalog_items
SET value_cents = (value_cents * 110 + 50) / 100
WHERE item_type_id = 1;

SELECT changes() AS changed_rows;

ROLLBACK;

初始数据中设备有两行，因此 changed_rows 应为 2；回滚后金额仍为 120000 与 180000。这个实验使用 integer arithmetic 做 10% 调整并显式舍入。ROLLBACK 让练习不修改最终数据。确认结果后可在新的 transaction 中执行并 COMMIT。

SQLite 的 BEGIN IMMEDIATE 会较早获取写事务所需的锁，减少执行到中途才发现无法开始写的情况；它不应机械替换所有 BEGIN，并发策略要根据工作负载选择。

删除同样应明确 predicate：

sql
BEGIN;

DELETE FROM catalog_items
WHERE item_id = 5;

SELECT changes() AS deleted_rows;
ROLLBACK;

DELETE 删除行，DROP TABLE 删除 schema object。二者都可能在 transaction 内产生持久变更，是否能回滚以及 DDL transaction 语义要以具体数据库为准。

NULL 表示 unknown，不是空字符串 ​

sql
SELECT item_id, name
FROM catalog_items
WHERE discovered_on IS NULL;

column = NULL 的结果是 UNKNOWN，不会像普通 true predicate 那样选中行。SQL predicate 有 TRUE、FALSE、UNKNOWN 三种逻辑结果；WHERE 只保留 TRUE。

NULL 与 ''、0 都不同。是否允许 NULL 是建模决策：如果“尚未发现日期”是合法状态，可以允许；如果业务要求每件物品必有日期，就应使用 NOT NULL。

不拼接用户输入 ​

下面是错误示例：

python
query = f"SELECT item_id, name FROM catalog_items WHERE name = '{user_input}'"

它把数据当成 SQL syntax，存在 SQL injection 和 quoting 错误。使用 driver parameter binding：

python
import sqlite3
from contextlib import closing

with closing(sqlite3.connect("catalog-lab.db")) as connection:
    connection.execute("PRAGMA foreign_keys = ON")
    rows = connection.execute(
        "SELECT item_id, name FROM catalog_items WHERE name = ?",
        ("传感器套件",),
    ).fetchall()

print(rows)

placeholder 只能绑定 value，不能安全替代表名、列名或 SQL keyword。动态 identifier 需要 allowlist 与显式映射。

约束失败是一份有价值的测试 ​

以下两条语句应分别失败。逐条在交互式 CLI 中执行；若用 .read 或批处理且启用了 .bail on，第一条失败会停止后续执行，需分开运行。

sql
-- negative money violates CHECK
INSERT INTO catalog_items (
    item_id, item_type_id, name, value_cents, discovered_on
) VALUES (6, 1, '错误样本', -1, '2024-01-01');

-- missing parent violates FOREIGN KEY when enforcement is enabled
INSERT INTO catalog_items (
    item_id, item_type_id, name, value_cents, discovered_on
) VALUES (7, 999, '孤儿样本', 100, '2024-01-01');

预期分别看到 CHECK constraint failed 与 FOREIGN KEY constraint failed；错误样本都不应留在表中。再把第一条的 -1 改成非数字文本，确认 STRICT 拒绝类型错误。测试要覆盖允许与拒绝的状态。

本课小结 ​

关系 schema 是可执行的数据契约。安全写入依赖明确列名、constraint、transaction boundary 和 parameter binding，而不是依赖开发者“每次都记得”。下一课在这组表上学习 SELECT、JOIN、aggregation、subquery 与 execution plan。

资料入口 ​

Built with VitePress | Software Systems Atlas

Copyright 2026 Steven Yang