MySQL 中的约束

约束是对表中数据施加的限制规则用来保证数据的完整性和一致性。在 MySQL 中约束可以在创建表时定义也可以后期通过ALTER TABLE添加或删除。一、约束的类型约束类型作用作用范围PRIMARY KEY唯一标识每一行列级/表级FOREIGN KEY维护表与表之间的引用完整性表级UNIQUE确保列值唯一列级/表级NOT NULL禁止列值为空列级CHECK自定义条件检查列级/表级DEFAULT为列提供默认值列级二、主键约束PRIMARY KEY主键是唯一标识表中每一行记录的字段一个表只能有一个主键但可以包含多个列联合主键。-- 单列主键 CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100) ); -- 联合主键多个字段组合唯一 CREATE TABLE user_roles ( user_id INT, role_id INT, PRIMARY KEY (user_id, role_id) ); -- 后期添加主键 ALTER TABLE users ADD PRIMARY KEY (id); -- 删除主键 ALTER TABLE users DROP PRIMARY KEY;特点不允许重复唯一性不允许为NULL一个表只能有一个主键联合主键的多个字段共同组成唯一标识三、外键约束FOREIGN KEY外键用于维护两张表之间的引用完整性确保一张表中的数据在另一张表中存在对应的引用。-- 创建主表 CREATE TABLE departments ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL ); -- 创建从表添加外键约束 CREATE TABLE employees ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, dept_id INT, CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments(id) ON DELETE SET NULL ON UPDATE CASCADE );说明1、CONSTRAINT的主要目的是为了‌给约束命名‌。命名后的约束便于后续管理如修改或删除且在某些约束类型中是必须的。2、ON DELETE SET NULL父表记录删除时自动将子表外键列置为NULL‌该列必须允许 NULL‌‌3、ON UPDATE CASCADE‌父表被引用列通常为主键更新时自动同步更新子表对应外键值仅当显式修改了被引用的键列时才生效修改其他字段不触发。外键的级联操作选项说明ON DELETE CASCADE删除主表记录时自动删除从表相关记录ON DELETE SET NULL删除主表记录时将从表外键设为 NULLON DELETE RESTRICT如果从表有引用拒绝删除主表默认ON UPDATE CASCADE更新主表主键时自动更新从表外键-- 后期添加外键约束 ALTER TABLE employees ADD CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments(id) ON DELETE CASCADE; -- 删除外键约束 ALTER TABLE employees DROP FOREIGN KEY fk_emp_dept;四、唯一约束UNIQUE确保列中的值不重复但允许有多个NULL值MySQL 中NULL被视为不同值。-- 创建表时添加唯一约束 CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) UNIQUE ); -- 联合唯一约束多个字段组合唯一 CREATE TABLE user_roles ( user_id INT, role_id INT, UNIQUE KEY uk_user_role (user_id, role_id) ); -- 后期添加唯一约束 ALTER TABLE users ADD UNIQUE INDEX uk_email (email); -- 删除唯一约束 ALTER TABLE users DROP INDEX uk_email;五、非空约束NOT NULL确保列中不能存储 NULL 值。-- 创建表时添加 CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL, phone VARCHAR(20) ); -- 修改列添加非空约束 ALTER TABLE users MODIFY email VARCHAR(100) NOT NULL; -- 删除非空约束允许 NULL ALTER TABLE users MODIFY email VARCHAR(100) NULL;六、检查约束CHECK用于定义自定义的验证条件MySQL 8.0.16 开始支持CHECK约束。-- 列级检查约束 CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, price DECIMAL(10,2) CHECK (price 0), stock INT CHECK (stock 0) ); -- 表级检查约束 CREATE TABLE employees ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, age INT, salary DECIMAL(10,2), CONSTRAINT chk_age CHECK (age 18 AND age 65), CONSTRAINT chk_salary CHECK (salary 0) ); -- 后期添加检查约束 ALTER TABLE employees ADD CONSTRAINT chk_age CHECK (age 18 AND age 65); -- 删除检查约束 ALTER TABLE employees DROP CHECK chk_age;七、默认值约束DEFAULT为列指定默认值当插入数据时如果未指定该列的值则使用默认值。-- 创建表时指定默认值 CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, status TINYINT DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); -- 修改列默认值 ALTER TABLE users MODIFY status TINYINT DEFAULT 0; -- 删除默认值改为无默认值 ALTER TABLE users MODIFY status TINYINT;