数据库原理与选型 / 系统课时
非空、唯一与检查约束
非空、唯一与检查约束
本课将把“必填、不可重复、取值范围和默认状态”四类业务规则落实到表结构中。你将创建一张会员表,并验证非法数据如何被 SQLite 拦截或忽略。
学习目标
- 使用
NOT NULL和UNIQUE限制空值与重复值 - 使用
CHECK表达范围和枚举规则 - 理解
DEFAULT的触发条件与约束边界
四类规则的职责
NOT NULL 要求列不能保存 NULL;UNIQUE 要求指定列或列组合不重复;CHECK 要求表达式结果不能为 0;DEFAULT 在插入时为未提供的列补值,但它本身不负责判断数据是否合法。
UNIQUE 通常允许多行 NULL,因为 NULL 不被视为彼此相等。CHECK 表达式结果为 NULL 时也不会判定失败,所以必填字段应把 NOT NULL 与 CHECK 组合使用。
创建并验证会员表
下面使用 INSERT OR IGNORE 演示冲突处理。违反非空、唯一或检查约束的行会被忽略,changes() 返回 0。
sql
DROP TABLE IF EXISTS members;
CREATE TABLE members (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
age INTEGER NOT NULL CHECK (age BETWEEN 18 AND 120),
status TEXT NOT NULL DEFAULT 'ACTIVE'
CHECK (status IN ('ACTIVE', 'LOCKED'))
);
INSERT INTO members (email, age)
VALUES ('alice@example.com', 20);
INSERT OR IGNORE INTO members (email, age)
VALUES ('alice@example.com', 30);
SELECT changes() AS duplicate_inserted;
INSERT OR IGNORE INTO members (email, age)
VALUES ('bob@example.com', 15);
SELECT changes() AS invalid_age_inserted;
SELECT id, email, age, status
FROM members;预期输出
text
0
0
1|alice@example.com|20|ACTIVE第一条数据省略 status,因此使用默认值 ACTIVE。第二条数据违反邮箱唯一约束,第三条数据违反年龄检查约束,两次写入都没有新增记录。
列级与表级约束
单列规则通常紧跟列定义,便于阅读。涉及多列的规则应写成表级约束,例如同一租户内用户名唯一:
sql
DROP TABLE IF EXISTS tenant_users;
CREATE TABLE tenant_users (
id INTEGER PRIMARY KEY,
tenant_id INTEGER NOT NULL,
username TEXT NOT NULL,
UNIQUE (tenant_id, username)
);
INSERT INTO tenant_users (tenant_id, username)
VALUES (10, 'alice'), (20, 'alice');
SELECT COUNT(*) FROM tenant_users;预期输出
text
2两个租户都可以使用 alice,但同一租户不能重复。复合唯一约束的列顺序还会影响底层索引的查询利用方式,应把常用过滤前缀放在前面。
常见错误
不要把 DEFAULT 当成非空保护:显式插入 NULL 仍会触发 NOT NULL 错误。不要在 CHECK 中编写子查询;SQLite 的 CHECK 表达式不允许子查询。对于大小写不敏感的邮箱或用户名,还需要统一归一化,或设计合适的排序规则和唯一索引。
按自己的节奏,确认真正理解。