SQLite 约束

SQLite 的约束（Constraints）用于强制数据完整性，确保表中数据符合业务规则。约束可以在 CREATE TABLE 或 ALTER TABLE 时定义。

---

常见约束类型

约束	说明	示例
NOT NULL	列不能为 NULL	name TEXT NOT NULL
UNIQUE	列值唯一（允许多个 NULL）	email TEXT UNIQUE
PRIMARY KEY	主键：唯一 + NOT NULL	id INTEGER PRIMARY KEY
FOREIGN KEY	外键：引用另一表的主键	user_id INTEGER REFERENCES users(id)
CHECK	自定义条件	age INTEGER CHECK(age >= 18)
DEFAULT	默认值	status TEXT DEFAULT 'active'

---

示例表结构

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT UNIQUE,
    age INTEGER CHECK(age >= 0 AND age <= 150),
    status TEXT DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    product TEXT NOT NULL,
    amount REAL CHECK(amount > 0),
    order_date DATE DEFAULT (DATE('now')),
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

---

1. NOT NULL 约束

-- username 必须填写
INSERT INTO users (username) VALUES ('alice');  -- 成功
INSERT INTO users (username) VALUES (NULL);    -- 错误！

---

2. UNIQUE 约束

-- 邮箱唯一（允许多个 NULL）
INSERT INTO users (username, email) VALUES ('bob', 'bob@example.com');
INSERT INTO users (username, email) VALUES ('charlie', 'bob@example.com'); -- 冲突！
INSERT INTO users (username) VALUES ('dave'); -- email=NULL，允许

注意：SQLite 允许多个 NULL 在 UNIQUE 列中（与其他数据库不同）

---

3. PRIMARY KEY 约束

-- 自动唯一 + NOT NULL
CREATE TABLE products (
    id INTEGER PRIMARY KEY,  -- 简写，等价于 PRIMARY KEY AUTOINCREMENT
    name TEXT
);

• INTEGER PRIMARY KEY → 自动成为 行ID（rowid）别名
• 加 AUTOINCREMENT → 防止 ID 重用（性能略低）

-- 推荐写法（自增）
id INTEGER PRIMARY KEY AUTOINCREMENT

---

4. FOREIGN KEY 约束（外键）

-- 必须先启用外键支持（默认关闭！）
PRAGMA foreign_keys = ON;

-- orders.user_id 必须存在于 users.id 中
INSERT INTO orders (user_id, product, amount) VALUES (1, 'Laptop', 1200); -- 成功（若用户存在）
INSERT INTO orders (user_id, product, amount) VALUES (999, 'Mouse', 25);   -- 失败！

外键行为

FOREIGN KEY(user_id) REFERENCES users(id)
    ON DELETE CASCADE     -- 删除用户时，级联删除订单
    ON UPDATE RESTRICT    -- 禁止修改用户 ID

---

5. CHECK 约束

-- 金额必须 > 0
amount REAL CHECK(amount > 0)

-- 状态只能是特定值
status TEXT CHECK(status IN ('pending', 'paid', 'shipped'))

INSERT INTO orders (amount) VALUES (-100); -- 违反 CHECK，报错

---

6. DEFAULT 默认值

status TEXT DEFAULT 'pending',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP

INSERT INTO users (username) VALUES ('eve');
-- status 自动为 'active'，created_at 自动为当前时间

---

7. 复合主键（多列联合唯一）

CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER DEFAULT 1,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY(order_id) REFERENCES orders(order_id),
    FOREIGN KEY(product_id) REFERENCES products(id)
);

---

添加/删除约束（ALTER TABLE）

SQLite 对 ALTER TABLE 支持有限：

操作	支持？	方法
添加列	支持	ALTER TABLE table ADD COLUMN col TYPE
删除列	不支持	需重建表
添加约束	部分支持	只能加 CHECK、UNIQUE 等，需重建表

示例：重建表添加约束

-- 1. 创建新表（带新约束）
CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE  -- 新增 NOT NULL + UNIQUE
);

-- 2. 复制数据
INSERT INTO users_new SELECT * FROM users;

-- 3. 删除旧表，重命名
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

---

约束冲突处理

-- INSERT OR IGNORE：冲突时跳过
INSERT OR IGNORE INTO users (username) VALUES ('alice');

-- INSERT OR REPLACE：冲突时替换
INSERT OR REPLACE INTO users (id, username) VALUES (1, 'new_name');

---

最佳实践

建议	说明
显式启用外键	PRAGMA foreign_keys = ON;
主键用 INTEGER PRIMARY KEY AUTOINCREMENT	清晰自增
业务状态用 CHECK	防止非法值
唯一字段用 UNIQUE	防重复
重要时间用 DEFAULT CURRENT_TIMESTAMP	自动记录

---

快速参考表

约束	语法
非空	col TEXT NOT NULL
唯一	col TEXT UNIQUE
主键	id INTEGER PRIMARY KEY
外键	FOREIGN KEY(col) REFERENCES table(id)
检查	CHECK(col > 0)
默认	DEFAULT 'value'

---

需要我帮你为你的表设计完整约束吗？
请提供 CREATE TABLE 语句或业务需求！