6.8.8 约束与外键使用规范

约束与外键使用规范#

约束类型与使用原则（含示例）#

  • 主键（PRIMARY KEY）：每表必须有主键，推荐使用自增 BIGINT 或雪花ID
  • 唯一约束（UNIQUE）：确保业务唯一性（如账号、订单号）
  • 非空约束（NOT NULL）：关键字段必须非空
  • 默认值（DEFAULT）：为非关键字段提供合理默认值
  • 检查约束（CHECK）：MySQL 8.0可用，适用于范围与枚举校验

示例：创建含多类约束的表

CREATE DATABASE IF NOT EXISTS ops_demo DEFAULT CHARSET=utf8mb4;
USE ops_demo;

CREATE TABLE user_account (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(120) NOT NULL,
  status TINYINT NOT NULL DEFAULT 1,
  age INT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_user_account_username (username),
  UNIQUE KEY uk_user_account_email (email),
  CHECK (age BETWEEN 1 AND 120)
) ENGINE=InnoDB;

预期效果：用户名与邮箱唯一、age范围受限、未填status默认1。

外键使用规范（含示例与风险）#

  • 优先逻辑外键：高并发与分库分表场景采用应用层一致性
  • 谨慎使用物理外键：仅限单库、数据量可控、强一致性场景
  • 禁止跨库外键：影响扩展性与迁移
  • 级联策略需评估：生产默认禁用级联删除

外键示例（不建议跨库）

CREATE TABLE department (
  dept_id INT NOT NULL AUTO_INCREMENT,
  dept_name VARCHAR(50) NOT NULL,
  PRIMARY KEY (dept_id),
  UNIQUE KEY uk_department_name (dept_name)
) ENGINE=InnoDB;

CREATE TABLE employee (
  emp_id BIGINT NOT NULL AUTO_INCREMENT,
  emp_name VARCHAR(50) NOT NULL,
  dept_id INT NOT NULL,
  PRIMARY KEY (emp_id),
  KEY idx_employee_dept (dept_id),
  CONSTRAINT fk_employee_department
    FOREIGN KEY (dept_id) REFERENCES department(dept_id)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT
) ENGINE=InnoDB;

原理草图（外键校验路径）#

文章图片

约束设计最佳实践#

  • 命名规范：pk_表名、uk_表名_字段、fk_表名_关联表_字段
  • 避免冗余约束：主键字段无需重复加唯一约束
  • 软删除冲突处理：唯一键加入状态字段或采用分区策略
  • 批量导入控制：合理分批与事务大小，减少锁冲突

关键命令与排错#

1）查看表约束与索引

SHOW CREATE TABLE user_account\G
SHOW INDEX FROM user_account;

2）常见错误与定位

-- 1) 唯一约束冲突
INSERT INTO user_account(username,email,age) VALUES('alice','a@b.com',30);
INSERT INTO user_account(username,email,age) VALUES('alice','a2@b.com',25);
-- 预期：Duplicate entry 'alice' for key 'uk_user_account_username'

-- 2) 外键约束失败
INSERT INTO employee(emp_name,dept_id) VALUES('Tom',999);
-- 预期：Cannot add or update a child row: a foreign key constraint fails

-- 3) 检查约束失败
INSERT INTO user_account(username,email,age) VALUES('bob','b@b.com',200);
-- 预期：Check constraint 'user_account_chk_1' is violated

3）约束变更（在线DDL建议）

# 使用 pt-osc 变更约束（示例：增加唯一约束）
pt-online-schema-change --alter "ADD UNIQUE KEY uk_user_account_phone (phone)" \
  D=ops_demo,t=user_account --execute

安装与环境准备（排错工具）#

# 安装 Percona Toolkit（CentOS/RHEL）
yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
percona-release enable-only tools
yum install -y percona-toolkit

# 验证工具
pt-online-schema-change --version

练习#

  1. 创建一张 order 表，包含 order_no 唯一约束与 status 默认值。
  2. 在 order_item 表中添加外键引用 order，测试外键失败场景。
  3. 删除外键后，使用逻辑校验在应用层模拟约束检查流程。

推荐落地策略#

  • 以唯一约束与非空约束为基础保障数据质量
  • 以逻辑外键为默认，物理外键仅限特定场景
  • 约束设计与监控告警结合，形成闭环治理