约束与完整性

问题

数据库中有哪些约束？它们分别起什么作用？

答案

约束（Constraint） 是数据库用来保证数据质量和一致性的规则。它们就像是数据的"守门员"——不符合规则的数据不允许进入数据库。

数据库完整性分为四类，每类都有对应的约束来保障：

完整性类型	含义	对应约束
实体完整性	每行记录必须可唯一区分	PRIMARY KEY
域完整性	列的值必须在合法范围内	NOT NULL、CHECK、DEFAULT、数据类型
参照完整性	外键引用必须有效	FOREIGN KEY
用户自定义完整性	业务规则约束	CHECK、UNIQUE、触发器

---

六种核心约束

1. PRIMARY KEY 主键约束

确保每行记录都能被唯一标识。自动包含 NOT NULL + UNIQUE。

-- 单列主键
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

-- 复合主键
CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

2. NOT NULL 非空约束

确保列的值不能为 NULL。

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,    -- 商品名不能为空
    price DECIMAL(10,2) NOT NULL,  -- 价格不能为空
    description TEXT               -- 描述可以为空（默认允许 NULL）
);

NULL 是什么？

NULL 不是空字符串 ''，也不是数字 0，而是**"未知"或"不存在"** 的意思。NULL 参与任何运算结果都是 NULL：

SELECT NULL = NULL;    -- 结果是 NULL（不是 TRUE）
SELECT NULL + 1;       -- 结果是 NULL
SELECT NULL > 0;       -- 结果是 NULL

判断 NULL 必须用 IS NULL / IS NOT NULL，不能用 =。

3. UNIQUE 唯一约束

确保列（或列的组合）的值不重复。

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,     -- 邮箱不能重复
    phone VARCHAR(20) UNIQUE,      -- 手机号不能重复
    -- 联合唯一：同一部门下名称不能重复
    dept_id INT,
    position VARCHAR(50),
    UNIQUE KEY uk_dept_position (dept_id, position)
);

4. FOREIGN KEY 外键约束

确保一张表中的外键值在另一张表中确实存在。

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT NOT NULL,
    -- 外键约束 + 级联操作
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE     -- 删除用户时，自动删除其所有订单
        ON UPDATE CASCADE     -- 更新用户 ID 时，自动更新订单中的 user_id
);

级联操作选项：

操作	说明
CASCADE	级联操作：父表记录删除/更新时，子表对应记录也删除/更新
SET NULL	父表记录删除/更新时，子表外键设为 NULL
RESTRICT	阻止操作：如果有子表引用，不允许删除/更新父表记录
NO ACTION	类似 RESTRICT
SET DEFAULT	父表记录删除/更新时，子表外键设为默认值

5. CHECK 检查约束

自定义数据的合法性条件。

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 18 AND age <= 65),        -- 年龄 18~65
    salary DECIMAL(10,2) CHECK (salary > 0),        -- 薪资必须大于 0
    gender CHAR(1) CHECK (gender IN ('M', 'F')),    -- 只能是 M 或 F
    
    -- 也可以用 CONSTRAINT 给约束命名
    CONSTRAINT chk_hire_date CHECK (hire_date <= CURRENT_DATE)
);

MySQL 5.7 注意

MySQL 5.7 及更早版本语法上接受 CHECK 但不强制执行。从 MySQL 8.0.16 开始才真正强制执行 CHECK 约束。PostgreSQL 一直支持。

6. DEFAULT 默认值约束

当插入数据时未指定某列的值时，使用默认值。

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    status VARCHAR(20) DEFAULT 'draft',             -- 默认状态为草稿
    view_count INT DEFAULT 0,                        -- 默认阅读量为 0
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 默认为当前时间
    is_deleted TINYINT(1) DEFAULT 0                  -- 默认未删除（软删除）
);

-- 插入时不指定 status 和 view_count，自动填充默认值
INSERT INTO articles (title) VALUES ('我的第一篇文章');
-- 结果：status='draft', view_count=0, created_at=当前时间, is_deleted=0

---

约束的管理

添加约束

-- 添加唯一约束
ALTER TABLE users ADD UNIQUE (email);

-- 添加 CHECK 约束
ALTER TABLE employees ADD CONSTRAINT chk_age CHECK (age >= 18);

-- 添加外键约束
ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);

删除约束

-- 删除唯一约束（MySQL）
ALTER TABLE users DROP INDEX email;

-- 删除外键约束（MySQL 需要先知道约束名）
ALTER TABLE orders DROP FOREIGN KEY orders_ibfk_1;

-- 删除 CHECK 约束
ALTER TABLE employees DROP CHECK chk_age;

查看约束

-- MySQL：查看表的建表语句（包含所有约束）
SHOW CREATE TABLE orders;

-- MySQL：查看约束信息
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'orders';

---

常见面试问题

Q1: 主键约束和唯一约束有什么区别？

答案：

对比	PRIMARY KEY	UNIQUE
每表数量	只能有 1 个	可以多个
NULL 值	不允许	允许（通常只允许一个 NULL）
索引类型	聚簇索引	非聚簇索引
语义	行的唯一身份标识	保证业务字段不重复

Q2: 为什么很多公司不在数据库层使用外键？

答案：

虽然外键能保证数据完整性，但在大规模互联网应用中常见的做法是不在数据库层设置外键约束，原因：

1. 性能问题：每次 INSERT/UPDATE 时需要验证外键，对写入性能有影响
2. 锁竞争：外键校验需要对引用的表加共享锁，增加锁等待
3. 分库分表：跨库外键无法实现
4. 运维困难：修改、删除有外键的表结构时，需要先处理外键关系
5. 灵活性：有时候需要先插入子表再插入父表（如双向依赖）

替代方案：在应用代码层面保证数据一致性，通过代码逻辑来检查引用关系。

Q3: NULL 值在 SQL 中有什么特殊的行为？

答案：

NULL 在 SQL 中是一个特殊值，表示"未知"，有三大特殊行为：

-- 1. NULL 不等于 NULL
SELECT NULL = NULL;      -- 返回 NULL（不是 TRUE）
SELECT NULL <> NULL;     -- 返回 NULL（不是 TRUE）
-- 正确写法：
SELECT NULL IS NULL;     -- 返回 TRUE

-- 2. NULL 参与运算结果都是 NULL
SELECT 1 + NULL;         -- NULL
SELECT 'hello' || NULL;  -- NULL（PostgreSQL）
SELECT CONCAT('hi', NULL); -- NULL

-- 3. 聚合函数忽略 NULL
-- 假设 scores 表有值：90, 80, NULL, 70
SELECT COUNT(*) FROM scores;      -- 4（统计所有行）
SELECT COUNT(score) FROM scores;  -- 3（忽略 NULL）
SELECT AVG(score) FROM scores;    -- 80（(90+80+70)/3，忽略 NULL）

陷阱：NOT IN 与 NULL

-- 如果子查询结果包含 NULL，NOT IN 的结果可能是空的！
SELECT * FROM users WHERE id NOT IN (1, 2, NULL);
-- 等价于：id != 1 AND id != 2 AND id != NULL
-- 因为 id != NULL 永远是 NULL，所以整个条件永远不为 TRUE
-- 结果：返回 0 行！

-- 安全做法：用 NOT EXISTS 替代 NOT IN
SELECT * FROM users u 
WHERE NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.user_id = u.id);

Q4: 什么是软删除？怎么实现？

答案：

软删除（Soft Delete） 是指不物理删除数据，而是通过一个标记字段表示数据已被"删除"。

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    is_deleted TINYINT(1) DEFAULT 0,   -- 0=正常, 1=已删除
    deleted_at TIMESTAMP NULL           -- 删除时间
);

-- "删除"操作：只更新标记
UPDATE articles SET is_deleted = 1, deleted_at = NOW() WHERE id = 1;

-- 查询时过滤已删除的记录
SELECT * FROM articles WHERE is_deleted = 0;

软删除的优势：数据可恢复、方便审计跟踪、不影响外键引用。

软删除的注意点：

• 所有查询都需要加 WHERE is_deleted = 0 条件
• 唯一约束需要把 is_deleted 加进去（否则删除后无法创建同名记录）
• 数据量会越来越大，需要定期清理

Q5: 约束应该在数据库层还是应用层实现？

答案：

约束方式	优点	缺点
数据库层（SQL 约束）	绝对可靠，任何入口都受约束	灵活性低、写入性能有开销
应用层（代码校验）	灵活、错误信息更友好	多入口时可能遗漏校验

最佳实践：两层都做。

• 数据库层：设置 NOT NULL、CHECK、UNIQUE 等基础约束——作为最后一道防线
• 应用层：做完整的参数校验和业务规则检查——提供友好的错误提示
• 外键：小型系统用数据库外键，大型系统在应用层保证

Q6: 如何给已有数据的表添加约束？

答案：

如果表中已有不满足约束的数据，直接添加约束会失败：

-- 1. 先查找不合规的数据
SELECT * FROM employees WHERE age < 18 OR age > 65;

-- 2. 修复或清理这些数据
UPDATE employees SET age = 18 WHERE age < 18;
DELETE FROM employees WHERE age > 65 AND status = 'inactive';

-- 3. 然后再添加约束
ALTER TABLE employees ADD CONSTRAINT chk_age CHECK (age >= 18 AND age <= 65);

大表加约束的注意事项

在大表（百万级以上）上添加约束时，要注意：

1. CHECK 约束需要全表扫描验证，可能会锁表
2. UNIQUE 约束需要创建索引，耗时可能很长
3. 建议在低峰期操作，或使用 pt-online-schema-change 等工具

Q7: CREATE TABLE 中约束的完整写法是什么？

答案：

CREATE TABLE employees (
    -- 列级约束（直接写在列定义后面）
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT DEFAULT 25,
    
    -- 表级约束（写在所有列定义之后，可以命名、可以多列组合）
    dept_id INT NOT NULL,
    position VARCHAR(50),
    
    CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(id),
    CONSTRAINT uk_dept_pos UNIQUE (dept_id, position),
    CONSTRAINT chk_age CHECK (age BETWEEN 18 AND 65)
);

命名约束的好处：删除、修改约束时可以按名引用；报错信息更有意义。

---

相关链接

• MySQL 约束
• PostgreSQL 约束
• MySQL CHECK 约束