SQLite 表结构设计与管理

本文档详细介绍 SQLite 表的设计原则、创建方法和管理技巧。

表设计原则

1. 遵循关系型数据库设计原则

• 范式化设计：尽量遵循第三范式，减少数据冗余
• 主键设计：为每个表设计合适的主键
• 数据类型选择：选择合适的数据类型，优化存储空间和性能
• 索引设计：合理设计索引，提高查询性能

2. 考虑 SQLite 特性

• 动态类型系统：利用 SQLite 的动态类型特性，保持灵活性
• 类型亲和性：考虑列的类型亲和性，优化存储和查询
• 无类型约束：虽然 SQLite 允许任何类型的数据存入任何列，但建议在应用层进行类型检查

表创建

基本语法

CREATE TABLE [IF NOT EXISTS] table_name (
    column1 datatype [CONSTRAINT constraint_name] [PRIMARY KEY] [AUTOINCREMENT] [NOT NULL] [UNIQUE] [DEFAULT default_value],
    column2 datatype [CONSTRAINT constraint_name] [NOT NULL] [UNIQUE] [DEFAULT default_value],
    ...
    [CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ...)],
    [CONSTRAINT constraint_name FOREIGN KEY (column) REFERENCES parent_table (parent_column)],
    [CONSTRAINT constraint_name CHECK (condition)]
);

示例

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    age INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    product TEXT NOT NULL,
    quantity INTEGER DEFAULT 1,
    total REAL NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id)
);

表管理

查看表列表

.tables

查看表结构

.schema table_name

修改表结构

添加列

ALTER TABLE table_name ADD COLUMN column_name datatype [CONSTRAINT] [DEFAULT default_value];

修改列

-- SQLite 不支持直接修改列，需要通过以下步骤：
-- 1. 创建新表
CREATE TABLE new_table (
    -- 新的表结构
);
-- 2. 复制数据
INSERT INTO new_table SELECT * FROM old_table;
-- 3. 删除旧表
DROP TABLE old_table;
-- 4. 重命名新表
ALTER TABLE new_table RENAME TO old_table;

删除列

-- SQLite 不支持直接删除列，需要通过创建新表的方式

重命名表

ALTER TABLE old_table_name RENAME TO new_table_name;

删除表

DROP TABLE [IF EXISTS] table_name;

约束管理

主键约束

-- 在创建表时添加
CREATE TABLE table_name (
    id INTEGER PRIMARY KEY
);

-- 或使用 CONSTRAINT 子句
CREATE TABLE table_name (
    id INTEGER,
    CONSTRAINT pk_table_name PRIMARY KEY (id)
);

外键约束

-- 启用外键约束
PRAGMA foreign_keys = ON;

-- 在创建表时添加
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY (user_id) REFERENCES users (id)
);

唯一约束

CREATE TABLE table_name (
    email TEXT UNIQUE
);

检查约束

CREATE TABLE table_name (
    age INTEGER CHECK (age >= 0 AND age <= 120)
);

常见问题（FAQ）

Q: SQLite 支持 AUTO_INCREMENT 吗？

A: 是的，SQLite 支持 AUTOINCREMENT 关键字，但仅适用于 INTEGER PRIMARY KEY 列。

Q: 如何查看表的索引信息？

A: 使用 .indices table_name 命令。

Q: SQLite 支持哪些数据类型？

A: SQLite 支持 NULL、INTEGER、REAL、TEXT、BLOB 五种基本数据类型，并具有灵活的类型亲和性。

Q: 如何修改表的主键？

A: SQLite 不支持直接修改表的主键，需要通过创建新表的方式实现。