MySQL：表的约束

非空约束

• 确保列的值不能为空。

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,  -- 名称不能为空
    price DECIMAL(10,2) NOT NULL  -- 价格不能为空
);

应用：

• 用户信息表：用户名、密码加非空约束，确保系统正常识别和验证用户。
• 商品信息表：商品名称、价格加非空约束，保证商品信息完整。

默认值约束

• 在插入数据时，如果未指定该列的值，则使用默认值。

• DEFAULT 只能用于非主键列。

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'pending',  -- 默认状态为 'pending'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 默认当前时间
);

# 插入
INSERT INTO orders (order_id) VALUES (1); -- `status` 自动填充 'pending'

应用：

• 订单管理系统：订单状态默认设为 ‘pending’，便于统一管理新订单。
• 日志记录表：记录创建时间默认用 CURRENT_TIMESTAMP，保证记录及时准确。

主键约束

• 唯一标识表中的每一行记录，不能重复，不能为NULL。

• 每个表只能有一个主键。

• 由一个或多个列组成（复合主键）。

• 默认自动创建唯一索引。

单列主键

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
# 两者等价
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    PRIMARY KEY (id)   -- 单独定义主键
);

复合主键

CREATE TABLE orders (
    order_id INT,
    user_id INT,
    PRIMARY KEY (order_id, user_id)  -- 组合唯一标识
);

应用：

• 用户表：用户 ID 作主键，快速准确管理用户信息，避免重复混淆。
• 订单表：订单 ID 作主键，处理订单操作时提高效率和准确性。

自增约束

• 让某列的值自动增长，常用于主键。

• 只能用于 INT 或 BIGINT 类型的列。

• 默认从 1 开始，每次递增 1。

• 只能有一个 AUTO_INCREMENT 列。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO users (name) VALUES ('Alice'), ('Bob');	-- Alice id 1, Bob id 2

应用：

• 用户注册系统：用户 ID 设自增约束，自动分配唯一连续 ID，方便管理。
• 论坛帖子表：帖子 ID 用自增约束，自动生成递增 ID，便于排序查询。

唯一约束

• 确保列的值唯一，但允许NULL值（NULL不认为是重复）。

• 一个表可以有多个 UNIQUE 约束。

• UNIQUE 可以在单列或多列组合上定义。

单列唯一

CREATE TABLE employees (
    emp_id INT UNIQUE,  -- emp_id 不能重复
    email VARCHAR(100) UNIQUE  -- email 不能重复
);

复合唯一

CREATE TABLE course_enrollment (
    student_id INT,
    course_id INT,
    UNIQUE (student_id, course_id)  -- 确保一个学生只能选同一课程一次
);

应用：

• 员工信息表：员工工号、邮箱加唯一约束，避免数据冲突。
• 课程选课表：学生 ID 和课程 ID 组合加唯一约束，防止重复选课。

外键约束

• 确保一个表中的数据引用另一个表中存在的值。
• 保持数据的引用完整性。
• 被引用的列必须是主键或唯一键。

基本外键

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)  -- 约束user_id必须是users表的id
);

级联操作

级联操作	说明
CASCADE	级联更新或删除
SET NULL	关联数据变更时，外键列设为NULL
RESTRICT	阻止删除/更新

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

• 当 users 表中 id 被删除时，orders 表中 user_id 相关的记录也会被删除。

应用：

• 订单与用户系统：订单表用户 ID 作外键关联用户表，保证订单对应有效用户，方便查询统计。
• 图书管理系统：借阅记录图书 ID、读者 ID 作外键，确保借阅信息有效。

检查约束

• MySQL 8.0+ 支持 CHECK 约束。

• 确保列的值符合特定条件。

CREATE TABLE students (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 18)  -- 限制年龄不能小于18岁
);

应用：

• 学生信息表：学生年龄加检查约束（≥18 岁）。