数据库原理与选型 / 系统课时
创建表结构与初始化数据
创建表结构与初始化数据
本课把上一课的数据模型写成可重复执行的 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 的空格和换行可能因版本不同而变化,关键是联合主键、两个外键和级联删除均存在。
按自己的节奏,确认真正理解。