（十六）MySQL约束

魔镜er

已于 2023-06-27 01:51:42 修改

阅读量47

点赞数

分类专栏： # MySQL：菜鸟的进化文章标签： mysql 数据库 sql

于 2023-06-27 01:07:56 首次发布

本文链接：https://blog.csdn.net/lvmealn/article/details/131407711

版权

MySQL：菜鸟的进化专栏收录该内容

21 篇文章 0 订阅

订阅专栏

约束

MySQL中的约束是数据库设计模式和实现数据完整性的重要工具。其主要作用如下：

1. 确保数据的一致性和完整性，防止错误数据的插入和修改。

2. 简化数据操作，避免进行复杂的数据验证和校验。

3. 提高查询效率，通过约束可以建立索引，提升查询性能。

4. 降低系统开发成本，通过设置约束可以避免在代码层面进行数据验证和校验。

5. 提高数据安全性，通过约束可以限制表中数据的访问和修改。

总之，MySQL中的约束在确保数据完整性、数据安全性和查询效率方面具有重要的作用，是数据库设计和管理中必不可少的一部分。

约束作用于列上，可以在创建表或修改表的时候添加。约束可以保证数据库中数据的正确性、有效性和完整性。

MySQL中的约束
约束	关键字	说明
非空约束	NOT NULL	限制该列的数据不能为null
唯一约束	UNIQUE	限制该列的数据是唯一的，不能重复
主键约束	PRIMARY KEY	行数据的唯一标识，非空且唯一
默认约束	DEFAULT	如果不指定该列值，则采用默认值
检查约束	CHECK	保证字段满足某个条件
外键约束	FOREIGN KEY	让两张表之间的数据之间建立联系，保证数据的一致性和完整性

举个栗子

创建一个新数据库test，在test中创建一张学生表student，包含以下列：

学生表student表结构
列名	列的含义	列的类型	约束	约束关键字
id	ID唯一标识	int	主键，并自动增长	PRIMARY KEY, AUTO_INCREMENT
name	姓名	varchar(10)	不为空，且唯一	NOT NULL, UNIQUE
age	年龄	int	大于0小于等于120	CHECK
gender	性别	varchar(6)	无
country	国籍	varchar(30)	如果没有指定，默认为’China’	DEFAULT

use test;
create database if not exists test;
use test;
drop table if exists student;
create table if not exists student (
	id int primary key auto_increment comment 'ID'
    , name varchar(10) unique not null comment '姓名'
    , age int check (age > 0 and age <= 120) comment '年龄'
    , gender varchar(6) comment '性别'
    , country varchar(30) default 'China' comment '国籍'
) comment '学生表';
desc student;

插入数据进行验证：

delete from student;  -- 确保是空表
insert into student (name, age, gender, country) values ('mojing_1', 18, 'female', 'China');  -- 正常运行
insert into student (name, age, gender, country) values ('mojing_2', 20, 'male', 'China');  -- 正常运行。id默认唯一且自增 
insert into student (name, age, gender, country) values (null, 20, 'male', 'China');  -- 报错。name不能为null，不会占用id
insert into student (name, age, gender, country) values ('mojing_2', 20, 'male', 'China');  -- 报错。name不能重复，但会占用一个id
insert into student (name, age, gender, country) values ('mojing_3', -1, 'female', 'China');  -- 报错。age>0且age<120，不会占用id
insert into student (name, age, gender, country) values ('mojing_4', 18, null, 'China');  -- 正常运行。gender没有约束
insert into student (name, age, gender) values ('mojing_5', 18, 'female');  -- 正常运行。country默认为'China'
select * from student;

外键约束

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

比如，MySQL自带的数据库中的两张表：city和country，countrycode与code之间可以建立外键约束。

语法

-- 添加外键
-- 方法1：建表时添加
CREATE TABLE 表名(
	列名1 数据类型
	, …
	, [CONSTRAINT] [外键名] FOREIGN KEY (外键列名) REFERENCES 主表 (主表列名)
);

-- 方法2：先建表，再添加
ALTER TABLE 表名 
ADD CONSTRAINT 外键名 
FOREIGN KEY (外键列名) 
REFERENCES 主表 (主表列名);


-- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;


-- 外键的删除/更新行为
ALTER TABLE 从表名 
ADD CONSTRAINT 外键名 
FOREIGN KEY (外键列) 
REFERENCES 主表名 (主表列名) 
ON UPDATE 更新行为名 
ON DELETE 删除行为名;

注：多个外键名不能相同！

外键的删除/更新行为
行为	说明
NO ACTION	当在主表中删除/更新对应记录时，首先检查记录是否有对应的外键，若有则不允许删除/更新。（与RESTRICT一致）
RESTRICT	当在主表中删除/更新对应记录时，首先检查记录是否有对应的外键，若有则不允许删除/更新。（与NO ACTION一致）
CASCADE	当在主表中删除/更新对应记录时，首先检查记录是否有对应的外键，若有则允许删除/更新外键在从表中的记录。
SET NULL	当在主表中删除/更新对应记录时，首先检查记录是否有对应的外键，若有则设置从表中该外键值为null（需要该外键允许null）。
SET DEFAULT	主表有变更时，从表将外键列设置成一个默认的值（Innodb不支持）

举个栗子

在test数据库中创建学生表student（从表）和班级表class（主表），表结构如下。并为主表的id与从表的class_id建立外键约束。

class（主表）
列名	列的含义	列的类型	约束	约束关键字
id	ID唯一标识	int	主键，并自动增长	PRIMARY KEY, AUTO_INCREMENT
name	班级名称	char(2)	不为空，且唯一	NOT NULL, UNIQUE

student（从表）
列名	列的含义	列的类型	约束	约束关键字
id	ID唯一标识	int	主键，并自动增长	PRIMARY KEY, AUTO_INCREMENT
name	姓名	varchar(10)	不为空，且唯一	NOT NULL, UNIQUE
age	年龄	int	大于0小于等于120	CHECK
gender	性别	varchar(6)	无	-
country	国家	varchar(30)	如果没有指定，默认为’China’	DEFAULT
class_id	班级	int	无	-

-- 添加外键 
-- 方法一：建表时直接添加外键 
use test;
drop table if exists student;
drop table if exists class;

create table class(  -- 主表（要先建主表再建从表，否则会报错）
	id int primary key auto_increment comment '班级id'
    , name varchar(2) not null comment '班级名称'
) comment '班级表';  

create table student(  -- 从表
	id int primary key auto_increment comment '学生id'
    , name varchar(10) unique not null comment '姓名'
    , age int check (age > 0 and age <= 120) comment '年龄'
    , gender varchar(6) comment '性别'
    , country varchar(30) default 'China' comment '国籍'
    , class_id int comment '班级id'
    , constraint fk_cls_stu_1 foreign key(class_id) references class(id)  -- 添加外键 
) comment '学生表';  



-- 方法二：先建表，再添加外键约束
use test;
drop table if exists student;
drop table if exists class;

create table class(  -- 主表  
	id int primary key auto_increment comment '班级id'
    , name varchar(2) not null comment '班级名称'
) comment '班级表';  

create table student(  -- 从表
	id int primary key auto_increment comment '学生id'
    , name varchar(10) unique not null comment '姓名'
    , age int check (age > 0 and age <= 120) comment '年龄'
    , gender varchar(6) comment '性别'
    , country varchar(30) default 'China' comment '国籍'
    , class_id int comment '班级id'
) comment '学生表';  

alter table student add constraint fk_cls_stu_1 foreign key(class_id) references class(id);

添加上数据看看：

delete from class;
insert into class(name) values('a');
insert into class(name) values('b');
insert into class(name) values('c');
select * from class;

delete from student;
insert into student(name, age, gender, country, class_id) values('mojing_1', 18, 'female', 'China', 1);  
insert into student(name, age, gender, country, class_id) values('mojing_2', 20, 'male', 'USA', 2);  
insert into student(name, age, gender, country, class_id) values('mojing_3', 19, 'male', 'UK', 2);  
insert into student(name, age, gender, country, class_id) values('mojing_4', 20, 'female', 'China', 3);  
insert into student(name, age, gender, country, class_id) values('mojing_5', 21, 'male', 'China', 1); 
insert into student(name, age, gender, country, class_id) values('mojing_6', 18, 'female', 'China', 3); 
select * from student;

接下来试试添加外键的删除/修改行为：

注意：在运行下列每一段代码之前，都要先重新运行一遍下面的代码。

-- 创建表
use test;
drop table if exists student;
drop table if exists class;

create table class(  -- 主表  
	id int primary key auto_increment comment '班级id'
    , name varchar(2) not null comment '班级名称'
) comment '班级表';  

create table student(  -- 从表
	id int primary key auto_increment comment '学生id'
    , name varchar(10) unique not null comment '姓名'
    , age int check (age > 0 and age <= 120) comment '年龄'
    , gender varchar(6) comment '性别'
    , country varchar(30) default 'China' comment '国籍'
    , class_id int comment '班级id'
) comment '学生表';  


-- 添加数据 
insert into class(name) values('a');
insert into class(name) values('b');
insert into class(name) values('c');
select * from class;

insert into student(name, age, gender, country, class_id) values('mojing_1', 18, 'female', 'China', 1);  
insert into student(name, age, gender, country, class_id) values('mojing_2', 20, 'male', 'USA', 2);  
insert into student(name, age, gender, country, class_id) values('mojing_3', 19, 'male', 'UK', 2);  
insert into student(name, age, gender, country, class_id) values('mojing_4', 20, 'female', 'China', 3);  
insert into student(name, age, gender, country, class_id) values('mojing_5', 21, 'male', 'China', 1); 
insert into student(name, age, gender, country, class_id) values('mojing_6', 18, 'female', 'China', 3); 
select * from student;

1. no action、restrict以及啥也不设置得到的结果是一样的，删除主表数据时会报错（不能删除）：

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `fk_cls_stu_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

alter table student -- 从表
add constraint fk_cls_stu_1  -- 外键名
foreign key(class_id) references class(id);  -- 外键约束 

delete from class where id = 1;  -- 报错

2. cascade

alter table student -- 从表
add constraint fk_cls_stu_1  -- 外键名
foreign key(class_id) references class(id)  -- 外键约束 
on update cascade  -- 删除行为
on delete cascade;  -- 修改行为

delete from class where id = 1;
select * from class;  -- 主表特定数据已删除 
select * from student;  -- 从表外键约束数据已删除

3. set null

alter table student -- 从表
add constraint fk_cls_stu_1  -- 外键名
foreign key(class_id) references class(id)  -- 外键约束 
on update set null 
on delete set null;  -- 外键的删除/修改行为set null

delete from class where id = 1;
select * from class;  -- 主表特定数据已删除 
select * from student;  -- 从表外键约束数据设置为null