MySql中的约束

约束

概述

约束（Constraints）是用于限制表中数据的规则，确保数据的完整性和一致性。

安全性约束
完整性约束：确保数据的准确和可靠
- 实体完整性约束（主键约束）
- 域完整性约束（数据类型）
- 引用完整性约束（外键约束）
- 用户自定义完整性约束（check 约束）

主键约束（PRIMARY KEY）

作用：唯一标识表中的每一行，确保数据的唯一性

基本特性

唯一性：关系中任意元组的主键值必须唯一
- 主键列的值必须唯一且不能为 NULL。
- 一个表只能有一个主键，但主键可以由多个列组成（复合主键）。
- 关系中任意元组的主键值必须唯一（unique）
非空性：关系中任意元组在主键值必须非空
- 关系中任意元组在主键值必须非空（not null）
不可更改性：主键值一旦设定，通常不应修改

-- 创建表时指定主键
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- 添加复合主键
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- 修改表添加主键
ALTER TABLE employees ADD PRIMARY KEY (emp_id);

-- 删除主键约束
ALTER TABLE employees DROP PRIMARY KEY;

主键选择策略

自然主键：使用业务中有意义的字段（如身份证号）
代理主键：使用无意义的自增ID（推荐）
复合主键：多个字段组合作为主键

域完整性约束

规则：关系中某属性取值必须在合法的范围内

实现：

一般通过数据类型来实现域完整性约束。不同的数据类型限制了属性可以接受的值的类型和范围。

CREATE TABLE products (
    price DECIMAL(10,2) NOT NULL,  -- 价格必须为数字
    in_stock BOOLEAN DEFAULT TRUE   -- 布尔类型约束
);

用户自定义完整性约束。

CREATE TABLE employees (
    age INT CHECK (age >= 18 AND age <= 65),
    email VARCHAR(100) CHECK (email LIKE '%@%.%')
);

为列设置默认值，当插入数据时如果没有指定该列的值，数据库将自动使用默认值。

ENUM 类型

1
2
3

CREATE TABLE orders (
    status ENUM('pending', 'processing', 'shipped', 'delivered') DEFAULT 'pending'
);

作用：用于确保表中列（属性）取值有效性和正确性的一种机制

唯一约束（UNIQUE）

作用：确保列中的值唯一，但允许 NULL 值。
特点：
- 一个表可以有多个唯一约束。
- 唯一约束可以作用于单列或多列（复合唯一约束）。

与主键的区别

特性	主键约束	唯一约束
NULL值	不允许	允许（但只能有一个NULL）
数量	每表一个	每表多个
索引类型	聚集索引	非聚集索引

CREATE TABLE users (
    email VARCHAR(100) UNIQUE,  -- 单列唯一约束
    username VARCHAR(50),
    CONSTRAINT uc_username_email UNIQUE (username, email)  -- 复合唯一约束
);

非空约束（NOT NULL）**

作用：确保列中的值不能为 NULL。
特点：
- 非空约束只能作用于单列。

-- 创建表时指定
CREATE TABLE customers (
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL
);

-- 修改现有列
ALTER TABLE customers MODIFY COLUMN email VARCHAR(100) NOT NULL;

-- 配合DEFAULT使用
CREATE TABLE orders (
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

默认约束（DEFAULT）

作用：为列设置默认值，当插入数据时未指定该列的值时，使用默认值。
特点：
- 默认值可以是常量、表达式或函数。

常用默认值：

CREATE TABLE logs (
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'active',
    counter INT DEFAULT 0
);

动态默认值：

-- 使用函数作为默认值
CREATE TABLE documents (
    doc_number VARCHAR(50) DEFAULT CONCAT('DOC-', UUID()),
    created_date DATE DEFAULT CURDATE()
);

外键约束（FOREIGN KEY）

作用：确保表之间的引用完整性，用于关联两个表。
规则：设F是关系R的外键，则R中任意元组的F属性值必须满足：F 为空或F不为空，其值必须在 S 中存在
特点：
- 外键列的值必须存在于被引用表的主键或唯一键中。
- 外键可以为 NULL。

完整语法

CREATE TABLE child_table (
    id INT PRIMARY KEY,
    parent_id INT,
    FOREIGN KEY (parent_id) 
        REFERENCES parent_table(id)
        ON DELETE action_type
        ON UPDATE action_type
);

检查约束（CHECK）

作用：确保列中的值满足指定条件。
特点：
- MySQL 8.0.16 及以上版本支持 CHECK 约束。
- 条件可以是逻辑表达式。

CREATE TABLE employees (
    salary DECIMAL(10,2) CHECK (salary > 0),
    hire_date DATE CHECK (hire_date >= '2000-01-01'),
    department VARCHAR(50),
    CONSTRAINT chk_dept CHECK (department IN ('IT', 'HR', 'Finance'))
);

-- 表级检查约束
ALTER TABLE products 
ADD CONSTRAINT chk_price CHECK (price > cost * 1.2);

动递增约束（AUTO_INCREMENT）

作用：自动为列生成唯一的递增值，通常用于主键列。
特点：
- 只能用于整数类型的列。
- 每个表只能有一个 AUTO_INCREMENT 列。

复合约束

作用：将多个列组合在一起作为约束条件。
常见类型：
- 复合主键：多个列共同作为主键。
- 复合唯一约束：多个列共同确保唯一性。

CREATE TABLE orders(
    order_id INT,
    product_id INT,
    PRIMARY KEY (order_id, product_id)
);

约束操作

约束的添加

1	ALTER TABLE 表名 ADD CONSTRAINT 约束名约束类型 (列名);

约束的删除

1	ALTER TABLE 表名 DROP CONSTRAINT 约束名;

约束的命名

CREATE TABLE 表名 (
    列名 数据类型,
    CONSTRAINT 约束名 约束类型 (列名)
);

查看约束

-- 从information_schema.TABLE_CONSTRAINTS系统表中获取信息
-- TABLE_SCHEMA用于指定数据库名，需替换为实际的数据库名称
-- TABLE_NAME用于指定表名，需替换为实际的表名称
SELECT * 
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table';

修改约束

1
2
3

-- 修改检查约束（MySQL中需要先删除再添加）
ALTER TABLE employees DROP CHECK chk_salary;
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

约束演示

-- 约束建表
CREATE TABLE Bin_user(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',   -- 多个约束空格分开
    name VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',
    age INT CHECK ( age >= 0 AND age <= 120 ) COMMENT '年龄',
    status CHAR(1) DEFAULT '0' COMMENT '状态',
    gender CHAR(1)  COMMENT '性别'
)COMMENT '用户表';

-- 主键约束的自动增长
INSERT INTO Bin_user (name, age, status, gender) VALUES ('Tom1', 18, '1', 'M'), ('SJD', 23, '1', 'W');

-- name的非空约束
INSERT INTO Bin_user (name, age, status, gender) VALUES (NULL, 18, '1', 'M');
-- name的唯一约束
INSERT INTO Bin_user (name, age, status, gender) VALUES ('Tom1', 18, '1', 'M');
-- age的检查约束
INSERT INTO Bin_user (name, age, status, gender) VALUES ('Tom1', -18, '1', 'M');
-- status的默认约束
INSERT INTO Bin_user (name, age, gender) VALUES ('Tom1', 18, 'M');

外键约束

添加外键

CREATE TABLE 表名(
	字段名 数据类型，
    。。。
    [CONSTRAIN] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
)

示例

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY, -- 客户ID，主键
    name VARCHAR(100) NOT NULL,        -- 客户姓名
    email VARCHAR(100) UNIQUE          -- 客户邮箱，唯一
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY, -- 订单ID，主键
    order_date DATE NOT NULL,               -- 订单日期
    amount DECIMAL(10, 2) NOT NULL,         -- 订单金额
    customer_id INT,                        -- 客户ID，外键
    FOREIGN KEY (customer_id) REFERENCES customers(id) -- 外键约束
);


INSERT INTO customers (name, email) VALUES
('张三', 'zhangsan@example.com'),
('李四', 'lisi@example.com');

INSERT INTO orders (order_date, amount, customer_id) VALUES
('2023-10-01', 100.50, 1), -- 订单属于客户ID为1的张三
('2023-10-02', 200.75, 2); -- 订单属于客户ID为2的李四


-- 测试外键约束
INSERT INTO orders (order_date, amount, customer_id) VALUES
('2023-10-03', 150.00, 99); -- 99 不存在于 customers 表中

DELETE FROM customers WHERE id = 1;   -- 尝试删除 customers 表中被 orders 表引用的数据

外键约束的级联操作

可以通过 ON DELETE 和 ON UPDATE 子句定义外键的级联行为。例如：

ON DELETE CASCADE：当主表中的记录被删除时，自动删除从表中的相关记录。
ON UPDATE CASCADE：当主表中的记录被更新时，自动更新从表中的相关记录。

示例：使用 `ON DELETE CASCADE`

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

测试：

删除 customers 表中的一条记录：
1
DELETE FROM customers WHERE id = 1;
检查 orders 表：
1
SELECT * FROM orders;
结果：customer_id 为 1 的订单也会被自动删除。

外键约束的删除和更新行为

NO ACTION：父表中删除/更新对应记录时，先检查该记录是否有对应外键，有则不允许删除/更新
RESTRICT ：父表中删除/更新对应记录时，先检查该记录是否有对应外键，有则不允许删除/更新

NO ACTION/RESTRICT（默认）会阻止执行会破坏引用完整性的操作
CASCADE 父表中删除/更新对应记录时，先检查该记录是否有对应外键，有则也删除/更新外键在子表中的记录
SET BULL 父表中删除/更新对应记录时，先检查该记录是否有对应外键，有则设置子表中该外键值为null（外键允许null）
SET DEFAULT ：父表变更的时候，子表将外键设置成一个默认的值
NO ACTION和RESTRICT是默认的

如何设置

1	ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE；

约束实践

学校管理系统约束

students 表：用于存储学生的基本信息，如学生编号、姓名、出生日期、所属专业编号等。学校可以通过该表管理学生档案，了解学生基本情况。
courses 表：用于记录课程相关信息，包括课程代码、课程名称、学分、所属系部编号等。学校利用此表进行课程管理，如排课、课程信息维护等。
enrollments 表：用于记录学生的选课情况，包括选课记录编号、学生编号、课程代码、学期、成绩等。通过该表可以了解学生的选课动态，统计课程选修人数，核算学生成绩等。

mysql语句：

CREATE TABLE students (
    student_id CHAR(10) PRIMARY KEY CHECK (student_id REGEXP '^[A-Z]{2}[0-9]{8}$'),
    name VARCHAR(100) NOT NULL,
    birth_date DATE NOT NULL CHECK (birth_date <= DATE_SUB(CURRENT_DATE, INTERVAL 16 YEAR)),
    major_id INT,
    FOREIGN KEY (major_id) REFERENCES majors(major_id)
);

student_id 列：数据类型为 CHAR(10) ，作为学生的唯一标识。设置为主键，同时通过 CHECK 约束确保其值符合正则表达式 ^[A-Z]{2}[0-9]{8}$ ，即学生编号必须是 2 个大写字母开头，后面跟着 8 位数字。
name 列：数据类型为 VARCHAR(100) ，用于存储学生姓名，NOT NULL 约束表示该字段不能为空。
birth_date 列：数据类型为 DATE ，用于记录学生出生日期，NOT NULL 约束要求不能为空。CHECK 约束限定出生日期必须是当前日期往前推至少 16 年以前，确保录入的出生日期符合正常入学年龄逻辑。
major_id 列：数据类型为 INT ，用于存储学生所属专业的编号。通过 FOREIGN KEY 约束引用 majors 表中的 major_id ，建立与专业表的关联关系，保证学生所属专业编号在专业表中存在。

CREATE TABLE courses (
    course_code VARCHAR(10) PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    credit TINYINT NOT NULL CHECK (credit BETWEEN 1 AND 5),
    department_id INT NOT NULL,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

course_code 列：数据类型为 VARCHAR(10) ，作为课程的唯一标识，设置为主键。
title 列：数据类型为 VARCHAR(100) ，用于存储课程名称，NOT NULL 约束表示不能为空。
credit 列：数据类型为 TINYINT ，用于记录课程学分，NOT NULL 约束要求不能为空。CHECK 约束限定学分在 1 到 5 之间，符合常见课程学分设置范围。
department_id 列：数据类型为 INT ，用于存储课程所属系部的编号，NOT NULL 约束表示不能为空。通过 FOREIGN KEY 约束引用 departments 表中的 department_id ，建立与系部表的关联，保证课程所属系部编号在系部表中存在。

CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id CHAR(10) NOT NULL,
    course_code VARCHAR(10) NOT NULL,
    semester VARCHAR(20) NOT NULL,
    grade CHAR(2) CHECK (grade IN ('A', 'B', 'C', 'D', 'F', NULL)),
    UNIQUE KEY (student_id, course_code, semester),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_code) REFERENCES courses(course_code)
);

enrollment_id 列：数据类型为 INT ，设置为自增长（AUTO_INCREMENT），作为选课记录的唯一标识，是该表的主键。
student_id 列：数据类型为 CHAR(10) ，用于记录选课学生的编号，NOT NULL 约束表示不能为空。通过 FOREIGN KEY 约束引用 students 表中的 student_id ，确保选课学生编号在学生表中存在。
course_code 列：数据类型为 VARCHAR(10) ，用于记录所选课程的代码，NOT NULL 约束表示不能为空。通过 FOREIGN KEY 约束引用 courses 表中的 course_code ，保证所选课程代码在课程表中存在。
semester 列：数据类型为 VARCHAR(20) ，用于记录选课的学期，NOT NULL 约束表示不能为空。
grade 列：数据类型为 CHAR(2) ，用于记录学生该课程的成绩。CHECK 约束限定成绩只能是 'A'、'B'、'C'、'D'、'F' 或空值，符合常见成绩记录规则。
UNIQUE KEY (student_id, course_code, semester)：组合唯一键约束，确保每个学生在每个学期对每门课程的选课记录唯一，防止重复选课记录出现。