约束

引言 提出问题 前面我们学会了增删改查，但遇到一些问题： 如何防止插入重复的用户名？ 如何保证用户年龄不能为负数？ 如何保证订单必须关联到存在的用户？ 如何保证邮箱字段不能为空？ 这就需要数据库约束（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 重要区别： ...