SQLite NOT NULL 与 DEFAULT：可靠的列约束

两个物有所值的约束

表结构设计得马虎时，大多数 bug 都源于两件事之一：某一列在没人预料的时候是 NULL，或者某一列缺少应用以为一定存在的值。NOT NULL 和 DEFAULT 能同时解决这两个问题，而且加上它们几乎没有成本。

CREATE TABLE users (
    id       INTEGER PRIMARY KEY,
    email    TEXT    NOT NULL,
    name     TEXT    NOT NULL DEFAULT 'anonymous',
    joined   TEXT    NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

INSERT INTO users (email) VALUES ('rosa@example.com');

SELECT * FROM users;

一列是必填且没有兜底值，另外两列有兜底值。插入时只需要提供 email，其余的由 SQLite 补上。整个功能用一个例子就讲完了，这一页剩下的内容讲的都是边界情况。

NOT NULL 的意思是“拒绝 NULL，没有例外”

NOT NULL 说到做到。任何把 NULL 放进这一列的尝试都会失败，不管是在没有默认值时 INSERT 省略了这一列，还是显式写了 NULL：

CREATE TABLE posts (
    id    INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);

-- This fails: title is missing.
INSERT INTO posts (id) VALUES (1);

错误信息如下：

Runtime error: NOT NULL constraint failed: posts.title

直接传入 NULL 也是同样的结果：

INSERT INTO posts (id, title) VALUES (1, NULL);
-- Runtime error: NOT NULL constraint failed: posts.title

这就是约定。如果一列在逻辑上是必填的，就把它标成 NOT NULL，这样就消灭了一整类 bug：任何应用代码都没法偷偷把 NULL 塞进数据库。

调用方没提供值时，DEFAULT 来补

只有当 INSERT 完全没有提到这一列时，DEFAULT 才会生效。它不会替你挽救显式写出的 NULL：

CREATE TABLE tasks (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending'
);

INSERT INTO tasks (title) VALUES ('Write docs');
INSERT INTO tasks (title, status) VALUES ('Ship it', 'done');

SELECT * FROM tasks;

第一次插入用了默认值，第二次覆盖了它。如果你写的是 INSERT INTO tasks (title, status) VALUES ('x', NULL)，就会得到 NOT NULL constraint failed 错误：列名已经写出来了，所以默认值根本不会触发。

值得记住的理解方式是：DEFAULT 填补的是缺失的列，NOT NULL 拒绝的是不管以什么方式到来的 NULL。它们是两个独立的功能，组合起来效果很好。

默认值可以是表达式

字面量默认值是最常见的情况（DEFAULT 0、DEFAULT ''、DEFAULT 'pending'），但 SQLite 也接受括号里的表达式。你可以用它给每行盖上创建时间戳，或者生成一个随机 ID：

CREATE TABLE events (
    id         INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    occurred   TEXT NOT NULL DEFAULT (CURRENT_TIMESTAMP),
    token      TEXT NOT NULL DEFAULT (lower(hex(randomblob(8))))
);

INSERT INTO events (name) VALUES ('login');
INSERT INTO events (name) VALUES ('logout');

SELECT * FROM events;

有几点需要知道：

• 表达式在每次插入时都会计算，而不是在建表时只算一次。每一行都会得到自己的时间戳和自己的令牌。
• CURRENT_TIMESTAMP、CURRENT_DATE 和 CURRENT_TIME 这三个特殊关键字不需要括号，其他表达式都需要。
• 表达式不能引用其他列，也不能使用子查询，它必须是自包含的。

如果你希望一列是可选的，但没给值时自动盖上时间戳，就去掉 NOT NULL，保留默认值。如果你希望它既必填又能自动盖时间戳，两个都用。

DEFAULT NULL 是合法的（有时正是重点）

写 DEFAULT NULL 和完全没有默认值是一样的：不提供值时这一列就是 NULL。当你想在表结构里明确表达“没有值”就是预期的初始状态时，值得这样写：

CREATE TABLE profiles (
    id        INTEGER PRIMARY KEY,
    user_id   INTEGER NOT NULL,
    bio       TEXT DEFAULT NULL,
    avatar    TEXT
);

INSERT INTO profiles (user_id) VALUES (1);

SELECT * FROM profiles;

这里的 bio 和 avatar 行为完全相同。bio 上的 DEFAULT NULL 是一条以代码形式写成的注释：它告诉阅读表结构的人，没有简介是正常状态，而不是疏忽。

给已有的表加上 NOT NULL

这里就开始变得麻烦了。SQLite 的 ALTER TABLE 故意设计得很有限，你不能像在 Postgres 里那样执行 ALTER COLUMN ... SET NOT NULL。你能做什么，取决于这一列是否已经存在。

对于全新的列，ADD COLUMN ... NOT NULL 可以用，但你必须提供默认值，否则已有的行会突然在一个 NOT NULL 列里出现 NULL，这是不可能的：

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO products (name) VALUES ('widget'), ('gizmo');

-- Works: existing rows get the default.
ALTER TABLE products ADD COLUMN price REAL NOT NULL DEFAULT 0;

SELECT * FROM products;

去掉默认值再试一次，就会报错：

ALTER TABLE products ADD COLUMN sku TEXT NOT NULL;
-- Runtime error: Cannot add a NOT NULL column with default value NULL

对于已经存在的列，没法原地修改。标准做法是重建表的那一套流程：新建一张带你想要的约束的表，把数据复制过去，删掉旧表，再把新表改名。我们会在 drop-and-alter-table 那一页详细介绍；现在你只需要知道这个限制确实存在，设计表结构时要考虑到它。

一个贴近实际的组合

大多数生产环境的表会同时使用这两个约束，来表达“应用认为一定成立的事情”：

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer    TEXT    NOT NULL,
    total_cents INTEGER NOT NULL DEFAULT 0,
    currency    TEXT    NOT NULL DEFAULT 'USD',
    status      TEXT    NOT NULL DEFAULT 'pending',
    created_at  TEXT    NOT NULL DEFAULT (CURRENT_TIMESTAMP),
    notes       TEXT
);

INSERT INTO orders (customer) VALUES ('Ada');
INSERT INTO orders (customer, total_cents, currency) VALUES ('Boris', 4999, 'EUR');

SELECT id, customer, total_cents, currency, status, created_at FROM orders;

从上到下读一遍这个表结构，不用看一行代码就能猜出应用在做什么。customer 必填且没有兜底值：调用方必须知道订单是谁的。金额、币种和状态都有合理的默认值，所以即使是最简单的插入也能得到一条完整的记录。notes 是可选的。created_at 由数据库填写，而这也是它唯一应该被填写的地方。

这就是这些约束的价值：它们把假设变成了由数据库自己强制执行的规则。

常见陷阱

几个经常让人踩坑的地方：

• 显式的 NULL 会让 DEFAULT 失效。 INSERT INTO t (col) VALUES (NULL) 不会使用默认值。这一列必须完全不出现在列清单里。
• 表达式默认值需要括号。 DEFAULT CURRENT_TIMESTAMP 可以用（它是三个特殊关键字之一）。DEFAULT lower(hex(randomblob(8))) 不行，要把它包起来：DEFAULT (lower(hex(randomblob(8))))。
• NOT NULL 和空字符串不是一回事。 '' 是合法的 TEXT 值，不会触发这个约束。如果你还想禁止空字符串，那是 CHECK 的工作（下一页）。
• ADD COLUMN ... NOT NULL 需要一个 DEFAULT，而且不能是 NULL。没有的话，SQLite 会拒绝这次修改。

下一篇：CHECK 约束

NOT NULL 和 DEFAULT 解决的是“必须存在”和“缺失时补上”。更进一步的校验，比如“必须是正数”“必须是这几个值之一”“结束日期必须晚于开始日期”，SQLite 提供了 CHECK 约束，让你写出每一行都必须满足的任意布尔表达式。这就是下一页的内容。