约束与完整性：主键、外键、唯一、非空

引言

提出问题

前面我们学会了增删改查，但遇到一些问题：

如何防止插入重复的用户名？
如何保证用户年龄不能为负数？
如何保证订单必须关联到存在的用户？
如何保证邮箱字段不能为空？

这就需要数据库约束（Constraints）来保证数据完整性！

数据完整性的三个层次

1. 实体完整性（Entity Integrity）

定义：每一行数据必须是唯一可识别的

实现方式：主键约束（PRIMARY KEY）

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键，唯一标识
  username VARCHAR(50)
);

2. 域完整性（Domain Integrity）

定义：列的值必须符合定义的类型和约束

实现方式：

数据类型（INT、VARCHAR等）
NOT NULL约束
DEFAULT默认值
CHECK约束（MySQL 8.0.16+）

CREATE TABLE users (
  id INT PRIMARY KEY,
  age INT NOT NULL CHECK (age >= 0 AND age <= 150),  -- 年龄范围
  status TINYINT DEFAULT 1  -- 默认值
);

3. 参照完整性（Referential Integrity）

定义：表之间的关联必须一致

实现方式：外键约束（FOREIGN KEY）

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)  -- 外键，关联users表
);

主键约束（PRIMARY KEY）

特点

✅ 唯一性：值不能重复
✅ 非空性：值不能为NULL
✅ 每张表只能有一个主键
✅ 主键可以是单列或多列（复合主键）

单列主键

-- 方式1：列级定义
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50)
);

-- 方式2：表级定义
CREATE TABLE users (
  id INT AUTO_INCREMENT,
  username VARCHAR(50),
  PRIMARY KEY (id)
);

复合主键

-- 学生选课表（学生ID + 课程ID作为主键）
CREATE TABLE student_courses (
  student_id INT,
  course_id INT,
  score INT,
  PRIMARY KEY (student_id, course_id)  -- 复合主键
);

-- 插入数据
INSERT INTO student_courses VALUES (1, 101, 90);  -- 成功
INSERT INTO student_courses VALUES (1, 102, 85);  -- 成功
INSERT INTO student_courses VALUES (1, 101, 95);  -- 失败：主键重复

添加/删除主键

-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

-- 删除主键
ALTER TABLE users DROP PRIMARY KEY;

-- 修改主键（先删除再添加）
ALTER TABLE users DROP PRIMARY KEY;
ALTER TABLE users ADD PRIMARY KEY (new_id);

唯一约束（UNIQUE）

特点

✅ 值不能重复
✅ 可以为NULL（但只能有一个NULL）
✅ 一张表可以有多个唯一约束

基础用法

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50) UNIQUE,  -- 用户名唯一
  email VARCHAR(100) UNIQUE,    -- 邮箱唯一
  phone VARCHAR(20)
);

-- 插入数据
INSERT INTO users VALUES (1, 'zhangsan', 'zhang@example.com', '13800138000');
INSERT INTO users VALUES (2, 'zhangsan', 'li@example.com', '13900139000');
-- ERROR 1062: Duplicate entry 'zhangsan' for key 'username'

复合唯一约束

-- IP访问日志（同一IP在同一天只能记录一次）
CREATE TABLE access_logs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  ip VARCHAR(15),
  access_date DATE,
  UNIQUE KEY uk_ip_date (ip, access_date)  -- 复合唯一约束
);

添加/删除唯一约束

-- 添加唯一约束
ALTER TABLE users ADD UNIQUE (username);
ALTER TABLE users ADD UNIQUE KEY uk_email (email);  -- 指定约束名

-- 删除唯一约束
ALTER TABLE users DROP INDEX username;
ALTER TABLE users DROP INDEX uk_email;

非空约束（NOT NULL）

特点

✅ 字段不能为NULL
✅ 必须提供值

基础用法

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,  -- 不能为空
  email VARCHAR(100) NOT NULL,
  nickname VARCHAR(50)  -- 可以为空
);

-- 插入数据
INSERT INTO users (id, username, email) VALUES (1, 'zhangsan', 'zhang@example.com');  -- 成功
INSERT INTO users (id, username) VALUES (2, 'lisi');  -- 失败：email不能为空

NULL vs 空字符串

-- NULL：未知、不存在
INSERT INTO users (id, username, email, nickname) VALUES (1, 'zhang', 'zhang@example.com', NULL);

-- 空字符串：已知但为空
INSERT INTO users (id, username, email, nickname) VALUES (2, 'li', 'li@example.com', '');

-- 查询
SELECT * FROM users WHERE nickname IS NULL;  -- 查到id=1
SELECT * FROM users WHERE nickname = '';  -- 查到id=2

重要区别：

NULL：未知，需要用IS NULL判断
''：空字符串，用= ''判断

添加/删除非空约束

-- 添加非空约束
ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) NOT NULL;

-- 删除非空约束
ALTER TABLE users MODIFY COLUMN phone VARCHAR(20);

默认值约束（DEFAULT）

基础用法

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  status TINYINT DEFAULT 1,  -- 默认值1
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 默认当前时间
);

-- 插入数据（不指定status和created_at）
INSERT INTO users (username) VALUES ('zhangsan');

-- 查询结果
SELECT * FROM users;
-- id=1, username=zhangsan, status=1, created_at=2024-01-15 10:30:00

常用默认值

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) DEFAULT 0.00,  -- 默认价格0
  stock INT DEFAULT 0,  -- 默认库存0
  status TINYINT DEFAULT 1,  -- 默认状态1
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 创建时间
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  -- 自动更新
);

检查约束（CHECK，MySQL 8.0.16+）

基础用法

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50),
  age INT CHECK (age >= 0 AND age <= 150),  -- 年龄范围
  email VARCHAR(100) CHECK (email LIKE '%@%'),  -- 邮箱格式
  status TINYINT CHECK (status IN (0, 1, 2))  -- 状态枚举
);

-- 插入数据
INSERT INTO users VALUES (1, 'zhangsan', 25, 'zhang@example.com', 1);  -- 成功
INSERT INTO users VALUES (2, 'lisi', 200, 'li@example.com', 1);  -- 失败：年龄超出范围
INSERT INTO users VALUES (3, 'wangwu', 30, 'invalid', 1);  -- 失败：邮箱格式错误

表级CHECK约束

CREATE TABLE orders (
  id INT PRIMARY KEY,
  amount DECIMAL(10, 2),
  discount DECIMAL(5, 2),
  final_amount DECIMAL(10, 2),
  CHECK (final_amount = amount - discount)  -- 金额一致性检查
);

注意事项

MySQL 8.0.16之前的版本不支持CHECK约束
CHECK约束在插入和更新时验证
复杂逻辑建议在应用层验证

外键约束（FOREIGN KEY）

什么是外键？

外键：一个表中的列，引用另一个表的主键，建立表之间的关联。

基础示例

-- 父表（被引用表）
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL
);

-- 子表（引用表）
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  order_no VARCHAR(32),
  FOREIGN KEY (user_id) REFERENCES users(id)  -- 外键约束
);

-- 插入数据
INSERT INTO users VALUES (1, 'zhangsan');
INSERT INTO orders VALUES (1, 1, 'ORDER001');  -- 成功：user_id=1存在
INSERT INTO orders VALUES (2, 999, 'ORDER002');  -- 失败：user_id=999不存在

外键的作用

保证参照完整性：订单的user_id必须存在于users表
防止删除被引用的数据：无法删除有订单的用户

-- 尝试删除有订单的用户
DELETE FROM users WHERE id = 1;
-- ERROR 1451: Cannot delete or update a parent row: a foreign key constraint fails

级联操作

CASCADE（级联）

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE  -- 删除用户时，自动删除其所有订单
    ON UPDATE CASCADE  -- 更新用户ID时，自动更新订单的user_id
);

-- 删除用户
DELETE FROM users WHERE id = 1;
-- 用户被删除，其所有订单也被自动删除

SET NULL

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE SET NULL  -- 删除用户时，订单的user_id设为NULL
    ON UPDATE SET NULL
);

RESTRICT（默认，拒绝）

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE RESTRICT  -- 有订单的用户无法删除
    ON UPDATE RESTRICT
);

NO ACTION（与RESTRICT类似）

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
);

外键的优缺点

优点：

✅ 保证数据一致性
✅ 数据库层面约束，更可靠
✅ 自动级联操作

缺点：

❌ 性能开销（每次插入/更新都要检查）
❌ 影响分库分表（外键跨库无法使用）
❌ 影响高并发（增加锁竞争）

生产环境建议

互联网公司通常不使用外键：

数据一致性在应用层保证
提升性能和扩展性
便于分库分表

-- 推荐做法：不加外键约束，通过应用层保证一致性
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,  -- 不加FOREIGN KEY
  INDEX idx_user_id (user_id)  -- 只加索引，提升查询性能
);

实战案例：电商订单系统

表设计

-- 用户表
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE,
  balance DECIMAL(10, 2) DEFAULT 0.00 CHECK (balance >= 0),
  status TINYINT DEFAULT 1 CHECK (status IN (0, 1)),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 商品表
CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
  stock INT DEFAULT 0 CHECK (stock >= 0),
  status TINYINT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 订单表
CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  order_no VARCHAR(32) NOT NULL UNIQUE,
  total_amount DECIMAL(10, 2) NOT NULL CHECK (total_amount > 0),
  status TINYINT DEFAULT 0 CHECK (status IN (0, 1, 2, 3, 4)),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_id (user_id),
  INDEX idx_order_no (order_no)
);

-- 订单详情表
CREATE TABLE order_items (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL CHECK (quantity > 0),
  price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
  subtotal DECIMAL(10, 2) NOT NULL,
  INDEX idx_order_id (order_id),
  INDEX idx_product_id (product_id)
);

约束总结

表	约束类型	作用
users	PRIMARY KEY	id唯一
users	UNIQUE	username、email不重复
users	NOT NULL	必填字段
users	CHECK	balance>=0, status枚举
users	DEFAULT	默认值
orders	索引	代替外键，提升性能

最佳实践

约束命名规范

-- 主键：pk_表名
ALTER TABLE users ADD CONSTRAINT pk_users PRIMARY KEY (id);

-- 唯一约束：uk_表名_列名
ALTER TABLE users ADD CONSTRAINT uk_users_username UNIQUE (username);

-- 外键：fk_表名_引用表名
ALTER TABLE orders ADD CONSTRAINT fk_orders_users
  FOREIGN KEY (user_id) REFERENCES users(id);

-- 检查约束：ck_表名_列名
ALTER TABLE users ADD CONSTRAINT ck_users_age
  CHECK (age >= 0 AND age <= 150);

选择建议

场景	推荐约束
唯一标识	PRIMARY KEY
不允许重复	UNIQUE
不允许为空	NOT NULL
数值范围	CHECK
表关联（小项目）	FOREIGN KEY
表关联（大项目）	索引 + 应用层保证

总结

核心要点

主键约束：唯一 + 非空，每表一个
唯一约束：不能重复，可为NULL
非空约束：不能为NULL
默认值：未提供值时使用默认值
检查约束：限制值的范围（MySQL 8.0.16+）
外键约束：保证参照完整性（大项目不推荐）

记忆口诀

约束六兄弟：主唯非默查外

主：PRIMARY KEY（主键）
唯：UNIQUE（唯一）
非：NOT NULL（非空）
默：DEFAULT（默认值）
查：CHECK（检查）
外：FOREIGN KEY（外键）

下一步学习

💡 本文是 “MySQL从入门到精通” 系列的第 7 篇（共86篇）。
📚 建议动手练习各种约束，理解其作用和限制。

引言#

提出问题#

数据完整性的三个层次#

1. 实体完整性（Entity Integrity）#

2. 域完整性（Domain Integrity）#

3. 参照完整性（Referential Integrity）#

主键约束（PRIMARY KEY）#

特点#

单列主键#

复合主键#

添加/删除主键#

唯一约束（UNIQUE）#

特点#

基础用法#

复合唯一约束#

添加/删除唯一约束#

非空约束（NOT NULL）#

特点#

基础用法#

NULL vs 空字符串#

添加/删除非空约束#

默认值约束（DEFAULT）#

基础用法#

常用默认值#

检查约束（CHECK，MySQL 8.0.16+）#

基础用法#

表级CHECK约束#

注意事项#

外键约束（FOREIGN KEY）#

什么是外键？#

基础示例#

外键的作用#

级联操作#

CASCADE（级联）#

SET NULL#

RESTRICT（默认，拒绝）#

NO ACTION（与RESTRICT类似）#

外键的优缺点#

生产环境建议#

实战案例：电商订单系统#

表设计#

约束总结#

最佳实践#

约束命名规范#

选择建议#

总结#

核心要点#

记忆口诀#

下一步学习#

参考资料#

引言

提出问题

数据完整性的三个层次

1. 实体完整性（Entity Integrity）

2. 域完整性（Domain Integrity）

3. 参照完整性（Referential Integrity）

主键约束（PRIMARY KEY）

特点

单列主键

复合主键

添加/删除主键

唯一约束（UNIQUE）

特点

基础用法

复合唯一约束

添加/删除唯一约束

非空约束（NOT NULL）

特点

基础用法

NULL vs 空字符串

添加/删除非空约束

默认值约束（DEFAULT）

基础用法

常用默认值

检查约束（CHECK，MySQL 8.0.16+）

基础用法

表级CHECK约束

注意事项

外键约束（FOREIGN KEY）

什么是外键？

基础示例

外键的作用

级联操作

CASCADE（级联）

SET NULL

RESTRICT（默认，拒绝）

NO ACTION（与RESTRICT类似）

外键的优缺点

生产环境建议

实战案例：电商订单系统

表设计

约束总结

最佳实践

约束命名规范

选择建议

总结

核心要点

记忆口诀

下一步学习

参考资料