数据库原理与选型 / 系统课时

创建表结构与初始化数据

创建表结构与初始化数据

本课把上一课的数据模型写成可重复执行的 SQLite 脚本,创建表、索引和外键,并生成一组能验证查询与约束的初始数据。

学习目标

  • 编写可重复执行的建表与初始化脚本
  • 正确启用外键并配置级联删除
  • 通过查询和故意失败的写入验证结构

编写初始化脚本

将以下内容保存为 init.sql。脚本使用事务保证建表与初始化要么全部成功,要么全部回滚。

sql
PRAGMA foreign_keys = ON;

BEGIN;

CREATE TABLE IF NOT EXISTS notes (
    id          INTEGER PRIMARY KEY,
    title       TEXT NOT NULL,
    content     TEXT NOT NULL DEFAULT '',
    status      TEXT NOT NULL DEFAULT 'active'
                CHECK (status IN ('active', 'archived')),
    created_at  TEXT NOT NULL,
    updated_at  TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS tags (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS note_tags (
    note_id  INTEGER NOT NULL,
    tag_id   INTEGER NOT NULL,
    PRIMARY KEY (note_id, tag_id),
    FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_notes_updated_at
    ON notes(updated_at DESC);

CREATE INDEX IF NOT EXISTS idx_note_tags_tag_id
    ON note_tags(tag_id, note_id);

INSERT OR IGNORE INTO notes
    (id, title, content, status, created_at, updated_at)
VALUES
    (1, 'SQLite 外键使用记录', '启用 foreign_keys 后再执行写入。', 'active',
     '2026-07-30 08:00:00', '2026-07-30 08:00:00'),
    (2, '归档测试笔记', '用于验证状态筛选。', 'archived',
     '2026-07-29 18:00:00', '2026-07-29 18:00:00');

INSERT OR IGNORE INTO tags (id, name)
VALUES
    (1, 'sqlite'),
    (2, 'database');

INSERT OR IGNORE INTO note_tags (note_id, tag_id)
VALUES
    (1, 1),
    (1, 2),
    (2, 2);

COMMIT;

PRAGMA foreign_keys = ON 只对当前连接生效,因此应用程序每次建立连接时也要启用。note_tags(tag_id, note_id) 用于从标签反查笔记,联合主键则负责从笔记查标签和防止重复关联。

执行并检查数据

在空目录中执行初始化脚本,再查询三张表的数量。

bash
sqlite3 notes.db < init.sql
sqlite3 -header -column notes.db "
SELECT 'notes' AS table_name, COUNT(*) AS row_count FROM notes
UNION ALL
SELECT 'tags', COUNT(*) FROM tags
UNION ALL
SELECT 'note_tags', COUNT(*) FROM note_tags;
"

预期输出:

text
table_name  row_count
----------  ---------
notes       2
tags        2
note_tags   3

再次执行 sqlite3 notes.db < init.sql 不会重复插入固定测试数据,因为主键和 INSERT OR IGNORE 会保持幂等。

验证外键约束

下面故意把不存在的笔记 999 绑定到标签 1。命令必须在同一个连接中先启用外键。

bash
sqlite3 notes.db "
PRAGMA foreign_keys = ON;
INSERT INTO note_tags(note_id, tag_id) VALUES (999, 1);
"

预期输出:

text
Error: stepping, FOREIGN KEY constraint failed (19)

不同 SQLite CLI 版本的错误前缀可能略有不同,但必须包含 FOREIGN KEY constraint failed,并且 note_tags 行数仍为 3。

检查表结构

使用内置命令确认表、索引和建表语句已经落库。

bash
sqlite3 notes.db ".tables"
sqlite3 notes.db ".indexes notes"
sqlite3 notes.db ".schema note_tags"

预期结果:

text
note_tags  notes  tags
idx_notes_updated_at
CREATE TABLE note_tags (
    ...
    PRIMARY KEY (note_id, tag_id),
    ...
);

.schema 的空格和换行可能因版本不同而变化,关键是联合主键、两个外键和级联删除均存在。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

创建表结构与初始化数据

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通