MySQL-约束Constraint详解

文章目录

    • 约束简介
    • 非空约束
    • 检查约束
    • 唯一约束
      • 列级约束与表级约束
      • 给约束起名字
    • 主键约束
      • 主键概念以及注意事项
    • 外键约束
      • 外键概念以及注意事项
      • 外键使用场景
      • 约束的删除与添加
      • 级联相关操作
        • 级联删除(on delete cascade)
        • 级联更新(on update cascade)
        • 级联置空(on delete set null)

约束简介

约束是我们在创建表的时候, 我们可以给表中的字段添加约束确保我们的数据的完整性和有效性, 比如大家平时上网时注册用户常见的 : 用户名不能为空, 对不起, 用户名已经存在等提示信息
约束通常包括下面的这6种

约束类型约束关键字
非空约束not null
默认约束default
检查约束check
唯一约束unique
主键约束primary key
外键约束foreign key

下面我们会详细的剖析这几种约束(默认约束省略, 就是一个简单的在不插入这个字段的数据时插入默认值)

非空约束

这个约束比较好理解, 就是插入的数据不能为空, 当我们设置这一个约束的时候, 我们使用desc展示表结构的时候, 表的Null那一行就会设置为NO, 允许为空就会为YES, 下面是我们的一个实例

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int         | NO   | PRI | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

尝试完成下面的一个需求, 创建一个学校表, 有编号, 学校名称(不能为空), 建校时间, 这时候就需要给name字段加上一个非空约束not null
我们尝试执行下面的SQL语句

-- 创建一个学校表
create table school(sno int comment '学校编号',name varchar(255) not null comment '学校名称',est_time date comment '建校时间'
);-- 插入几条数据来进行测试(关于日期类型的插入, 其实底层会进行str_to_date函数的调用, 把字符串转化为日期)
insert into school (sno, name, est_time) values (1, '北京101中学', '1910-08-11');
insert into school (sno, name, est_time) values (2, '南开附中', '1912-04-13');
insert into school (sno, name, est_time) values (3, '清华附中', '1915-03-12');-- select 查询一下当前的信息
select * from school;-- 执行结果如下
+------+-----------------+------------+
| sno  | name            | est_time   |
+------+-----------------+------------+
|    1 | 北京101中学     | 1910-08-11 |
|    2 | 南开附中        | 1912-04-13 |
|    3 | 清华附中        | 1915-03-12 |
+------+-----------------+------------+

由于我们添加了非空约束, 也就是我们的name不可以为null, 如果插入一条数据没有name就会报错

-- 尝试执行下面的SQL
insert into school (sno, est_time) values (4, '1899-11-06');
-- 会直接报错, 报错信息如下
ERROR 1364 (HY000): Field 'name' doesn't have a default value

检查约束

其实就是在创建一张表的时候添加一定的检查信息, 这个约束时MySQL8版本之后新添加的, 在先前的版本中是不存在的
基础语法

create table [表名](...字段信息check(约束条件)
);

我们尝试建一张学生表, 要求添加学生的年龄必须大于18岁, 这种情况就可以使用检查约束

-- 创建一张学生表
create table t_stu(sno int,name varchar(255),age int,check(age > 18)
);-- 执行DML语句
insert into t_stu(sno, name, age) values (1, 'hh', 19);
insert into t_stu(sno, name, age) values (2, 'xx', 18);
-- 第一条是执行成功的, 但是第二条 18 == 18, 不满足check约束, 所以失败

唯一约束

对一个字段添加unique约束, 这个字段就具有了唯一性, 唯一性的字段是可以为null, 但不可以重复, 如果是null的话, 是可以重复的, 我们拿下面的t_stu表作为一个例子说明

# 创建了一个学生表, 这个表的email字段是唯一的, 不可以重复
create table t_stu(no int,name varchar(255),email varchar(255) unique
);
# 我们尝试执行下面的DML语句, 执行成功成功的标明 √, 否则为 ×
insert into t_stu (no, name, email) values (1, 'hh', 'hh@163.com');
insert into t_stu (no, name, email) values (2, 'xx', 'xx@163.com');
insert into t_stu (no, name) values (3, 'xx'), (4, 'wx');
# 查看一下当前的信息
select * from t_stu;
+------+------+------------+
| no   | name | email      |
+------+------+------------+
|    1 | hh   | hh@163.com |
|    2 | xx   | xx@163.com |
|    3 | xx   | NULL       |
|    4 | wx   | NULL       |
+------+------+------------+
# 尝试插入一条重复的数据
insert into t_stu (no, name, email) values (5, 'sf', 'hh@163.com');
# 直接报错, 因为email的位置重复了

unique约束也可以和not null 同时使用表示非空且唯一

create table t_stu(no int,name varchar(255),email varchar(255) not null unique
);

列级约束与表级约束

如果一个约束紧紧的跟在字段后面, 那这个字段仅仅作用于这一个字段, 我们称之为列级约束, 如果一个约束位于表定义的结尾位置, 那么这个约束我们称之为表级约束, 这种约束可以约束多种字段

unique, primary key, foreign key 可以作为表级约束存在, not null 不可以作为表级约束

我们比较一下下面的SQL语句

# unique的列级约束
create table t_stu(no int,name varchar(255),email varchar(255),unique(email)
);
# unique的表级约束
create table t_stu(no int,name varchar(255),email varchar(255),unique(name,email)
);

那么上述列级和表级的约束有什么区别呢?
答案是 : 表级可以联合联合多个字段而列级只能约束一个字段

给约束起名字

约束是以对象的形式存在的, 所有的约束对象对象都存在一个系统表中
information_schema(四个系统数据库之一)中的table_constraints这个表中, 这张表保存的所有的约束名称信息
这里注意, 列级约束是不能起名字的, 但是有系统默认分配的名字, 只有表级别才可以起名字, 如果不起名字的话, 也会自动分配一个
找到约束的名字我们就可以对约束进行删除, 从而消除对某些字段的约束
首先找到这张表
在这里插入图片描述

使用这个information_schema数据库
在这里插入图片描述
这里面有79张表, 找到这个TABLE_CONSTRAINTS表, 这个表存储的所有的约束对象的信息
在这里插入图片描述
desc查看一下表结构, 我们找到刚才的 t_stu 学生表的约束名称
先用DDL语句查看一下创建 t_stu 这张表时的建表语句
在这里插入图片描述
我们可以看到, 我们创建表的时候对email字段进行了unique约束, 但是没有给约束起名字, 所以系统会自动分配名字
下面我们查看一下这个约束的名称
在这里插入图片描述
这里可以看到这个约束的名称时email

下面我们创建一张新的表, 从新添加一个约束并给约束起名字
基础的语法如下

# 表级约束起名的语法
constraint [约束名称] [表级约束的主体];
# 起约束名的标准
表名_约束的字段_约束的简称(unique/pk/fk)

创建一个班级表进行测试


-- 创建一个班级表(设置班级编号为主键, 班级名称为唯一键)
create table class(cno int comment '班级编号',cname varchar(255) comment '班级名称',constraint class_cno_pk primary key(cno),constraint class_cname_unique unique(cname)
);

用上面我们找到约束的名称的流程进行演示…

# 使用一下这个系统库
use information_schema;
# 找到class表的约束名称
select constraint_name from table_constraints where table_name = 'class';

在这里插入图片描述
这里可能会有疑问为什么给主键起的名字没有生效呢?
下面是我查阅的资料

MySQL版本限制:从MySQL8.0版本开始，主键的名字不再可以直接修改。这是由于 MySQL的内部存储引擎(如InnoDB)实现方式导致的，InnoDB存储引擎中主键的名字是以索引的形式存储的，修改主键名字实际上是修改索引的名字，会对存储引擎的内部数据结构产生影响，因此MySQL禁止直接修改主键的名字。

但是经过测试, 给外键起名字是生效的

主键约束

主键概念以及注意事项

主键约束是一个比较重要的内容

  1. 主键 : primary key, 简称pk
  2. 主键约束的字段不能为NULL, 并且不可以重复
  3. 任何一张表都应该有主键(第一范式), 没有主键的表可以被视为无效表
  4. 主键值是这行记录的身份证号，是唯一标识。在数据库表中即使两条数据一模一样，但由于主键值不同，我们也会认为是两条完全的不同的数据。
  5. 主键分类：
    在这里插入图片描述
  6. 单一主键(建议使用这种方式)
create table t_student(id bigint primary key,sno varchar(255) unique,sname varchar(255) not null
)
  1. 联合主键(很少用, 了解)
create table t_user(no int,name varchar(255),age int,primary key(no,name)
);
  1. 主键自增
    既然主键值是一个自然的数字，mysql为主键值提供了一种自增机制，不需要我们程序员维护，mysql自动维护该字段键自增
create table t_vip(no int primary key auto_increment,name varchar(255)
);

外键约束

外键概念以及注意事项

外键约束

  1. 外键约束 : foreign key, 简称fk
  2. 添加了外键约束的字段的数据必须来源于其他的其他字段, 不可以随便设置
  3. 比如我们给a字段添加了外键约束, 要求a字段中的数据必须来源于b字段
    b字段不一定是主键, 但一定具有唯一性
  4. 外键约束可以给单个字段添加，叫做单一外键。也可以给多个字段联合添加，叫做复合外键。复合外键 很少用。
  5. 如果a表引用了b表的数据(a是外键, b具有唯一性被引用)
操作类型执行顺序
创建表时先创建b, 再创建a
插入数据时先插入b, 再插入a
删除数据时先删除a, 再删除b
删除表时先删除a, 再删除b

外键使用场景

有下面一个需求, 我们想要创建一个学生表, 能够存储学生的信息
我们的第一种设计是这样的
在这里插入图片描述
很明显, 这样创建表的方法会导致数据冗余(实质上是违法了我们第三范式)
这张表是一种典型的一对多的情况, 所以根据创建表的设计(后面会讲)我们创建为两张表
一张学生表, 单独存储学生的信息, 另一张是学校表, 专门存储学校的相关信息, 返回用外键进行关联
在这里插入图片描述
在这里插入图片描述
为什么要设置外键约束, 因为对于学生来说, 学校编号这一个条件不是随便设置的, 要用学校表中的信息进行约束

有了上面的铺垫, 我们尝试创建一个学生表, 和一个学校表

-- 创建一个学校表(把学校编号设置为主键)
create table t_school(sch_no int primary key auto_increment,sch_name varchar(255) unique
);-- 创建一个学生表(把学生编号设置为主键, 然后名字添加default约束, 年龄添加check约束, 学校名称为外键)
create table t_student(stu_no int primary key auto_increment,stu_name varchar(255) default '无名氏',age int,sch_no int,check(age >= 18),constraint t_student_sch_no_fk foreign key(sch_no) references t_school(sch_no)
);-- 插入几条测试数据(按照我们举出来的例子)
insert into t_school (sch_name) values ('清华大学'), ('北京大学'), ('浙江大学'), ('复旦大学');
insert into t_student (stu_name, age, sch_no) values ('hxh', 19, 1), ('dwv', 18, 1), ('cac', 19, 1), ('fqe', 20, 1);
insert into t_student (stu_name, age, sch_no) values ('ger', 18, 2), ('he', 21, 2);
insert into t_student (stu_name, age, sch_no) values ('few', 20, 3), ('rhr', 22, 3), ('wgh', 22, 3);
insert into t_student (stu_name, age, sch_no) values ('rhre', 22, 4), ('wg', 21, 4);

在这里插入图片描述

约束的删除与添加

由于我们下面要介绍级联的相关操作, 所以要删除之前的外键约束条件, 我们之前就说过, 可以通过找到约束的名字从而删除约束, 这种操作其实是DDL, 对表层面的一种操作, 我们首先找到t_student表的约束的名字
在这里插入图片描述
删除约束的基础语法如下

# 基础语法就是
alter table [表名] drop constraint [约束名称];
# 删除学生表的外键约束
alter table t_student constraint t_student_sch_no_fp;

约束添加的语法与删除的语法类似 , 都是DDL语句那一套逻辑

# 基础语法
alter table [表名] add constraint [约束名称] [约束主体];
# 比如我们从新把刚才删除外键约束添加回来
alter table t_student add constraint stu_pk
foreign key(sch_no) references t_school(sch_no);

级联相关操作

我们上面介绍外键概念的时候提到过, 如果删除表或者删除表中的数据的时候要注意先后的顺序
那有没有一种方法, 可以在操纵主表的同时, 同时修改副表里面关联的数据呢

级联删除(on delete cascade)

我们从新把上面创建的学校表和学生表拿出来
在这里插入图片描述

现在存在外键约束, 我们直接删除学校编号为4的复旦大学是不能操作的, 因为有副表的数据引用
但是通过级联删除就可以实现这一效果(删除主表信息的同时删除副表内容)
基础语法

-- 只需要在创建约束的时候在末端加上一个 (on delete cascade)
[创建约束主体] on delete cascade;

下面我们进行测试

-- 首先删除一下之前的外键约束
alter table t_student drop constraint stu_pk;
-- 创建一个新的外键约束(并加上一个级联删除的信息)
alter table t_student add constraint t_student_sch_no_fk foreign key(
sch_no) references t_school(sch_no) on delete cascade;
-- 下面我们从新进行测试
delete from t_school where sch_no = 4;
-- 此时就会显示执行成功, 我们从新查看一下学生表中的数据, 执行结果如下图

在这里插入图片描述
注意, 级联删除其实是一个相当危险的操作

级联更新(on update cascade)

作用就是当更新主表数据的同时修改副表中的相关数据
基础语法

[约束主体] on update cascade;

下面是测试内容

-- 首先还是一下上一个约束
alter table t_student drop constraint t_student_sch_no_fk;
-- 创建一个级联更新约束
alter table t_student add constraint t_student_sch_no_fk 
foreign key(sch_no) references t_school(sch_no) on update cascade;
-- 把学校编号为3改为学校编号为5
update t_school set sch_no = 5 where sch_no = 3;
-- 查看一下当前的学生信息
select * from t_student;
-- 执行结果见下图

在这里插入图片描述

级联置空(on delete set null)

作用就是当在主表中删除一个数据的时候, 副表中跟这条数据关联的外键位置设置为NULL
基础语法

[约束主体] on delete set null;

下面是我们的测试代码

--还是先删除约束
alter table t_student drop constraint t_student_sch_no_fk;
-- 加一个级联置空的约束
alter table t_student add constraint t_student_sch_no_fk 
foreign key(sch_no) references t_school(sch_no) on delete set null;
-- 上一个级联更新我们不是3编号更新为5了么, 我们现在删除5数据对应的编号学校
delete from t_school where sch_no = 5;
-- 执行结果见下图

在这里插入图片描述
谢谢观看

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/447934.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

从SQL Server过渡到PostgreSQL：理解模式的差异

从SQL Server过渡到PostgreSQL：理解模式的差异

前言 随着越来越多的企业转向开源技术，商业数据库管理员和开发者也逐渐面临向PostgreSQL迁移的需求。 虽然SQL Server和PostgreSQL共享许多数据库管理系统（RDBMS）的基本概念，但它们在处理某些结构上的差异可能会让人感到困惑&…
阅读更多...
首届公安影视文化融创发展活动暨龙虎山警察文化交流季圆满收官！

首届公安影视文化融创发展活动暨龙虎山警察文化交流季圆满收官！

为推动形成公安题材文学创作的全链条发展机制，搭建文化交流的平台，由公安部新闻传媒中心联合江西省鹰潭市人民政府举办的“首届公安影视文化融创发展活动暨龙虎山警察文化交流季”，于10月12日在江西省鹰潭市龙虎山举行专题总结仪式&#xff0…
阅读更多...
如何通过构建对应的api服务器使Vue连接到数据库

如何通过构建对应的api服务器使Vue连接到数据库

一、安装数据库驱动 在后端安装 MySQL 数据库驱动，比如在 Node.js 环境中可以使用 mysql2 包来连接 MySQL 数据库。在项目目录下运行以下命令安装： npm install mysql2或者使用 yarn： yarn add mysql2二、创建数据库连接模块 创建一个专门…
阅读更多...
Linux shellcheck工具

Linux shellcheck工具

安装工具 通过linux yum源下载，可能因为yum源的问题找不到软件包，或者下载的软件包版本太旧。 ShellCheck的源代码托管在GitHub上(推荐下载方式)： GitHub - koalaman/shellcheck: ShellCheck, a static analysis tool for shell scripts 对下…
阅读更多...
STM32传感器模块编程实践(八) HX711压力传感器称重模块简介及驱动源码

STM32传感器模块编程实践(八) HX711压力传感器称重模块简介及驱动源码

文章目录 一.概要二.HX711主要技术指标三.HX711模块参考原理图四.模块接线说明五.模块工作原理介绍六.模块通讯协议介绍七.STM32单片机与HX711模块实现重量测量实验1.硬件准备2.软件工程3.软件主要代码4.实验效果 八.小结 一.概要 电子秤是将检测与转换技术、计算机技术、信息…
阅读更多...
安装Node.js环境，安装vue工具

安装Node.js环境，安装vue工具

一、安装Node.js 去官方网站自行安装自己所需求的安装包 这是下载的官方网站 下载 | Node.js 中文网 给I accept the terms in the License Agreement打上勾然后点击Next 把安装包放到自己所知道的位置,后面一直点Next即可 等待它安装好 然后winr打开命令提示符cmd 二、安装…
阅读更多...
mybatis-plus saveOrUpdate详细解析

mybatis-plus saveOrUpdate详细解析

mybatis-plus saveOrUpdate详细解析 saveOrUpdate() 方法介绍 插入新记录：当对象的所有字段都为新值且对象的主键字段未设置或设置为默认值时，saveOrUpdate将执行插入操作。更新现有记录：如果对象的主键字段设置了有效的值，并且…
阅读更多...
MySQL表的基本查询上

MySQL表的基本查询上

1，创建表 前面基础的文章已经讲了很多啦，直接上操作： 非常简单！下一个！ 2，插入数据 1，全列插入 前面也说很多了，直接上操作： 以上插入和全列插入类似，全列…
阅读更多...
一台电脑轻松接入CANFD总线-来可CAN板卡介绍

一台电脑轻松接入CANFD总线-来可CAN板卡介绍

在工业控制领域，常常使用的总线技术有CAN(FD)、RS-232、RS-485、Modbus、Profibus、Profinet、EtherCAT等。RS-485以其长距离通信能力著称，Modbus广泛应用于PLC等设备，EtherCAT则以其低延迟和高实时性在自动化系统中备受青睐。 其中&#xf…
阅读更多...
10.9QT对话框以及QT的事件机制处理

10.9QT对话框以及QT的事件机制处理

MouseMoveEvent(鼠标移动事件) widget.cpp #include "widget.h" #include "ui_widget.h"Widget::Widget(QWidget *parent): QWidget(parent), ui(new Ui::Widget) {ui->setupUi(this);// 设置窗口为无边框，去掉标题栏等装饰this->setWi…
阅读更多...
Springboot整合抖音小程序获取access-token图片检测V3

Springboot整合抖音小程序获取access-token图片检测V3

抽取配置文件 appId以及secret需要自行在抖音开放平台获取 dy:appId: ttb0xxxxxsecret: 12a19a426xxxxxxxxxxxxx获取access-token 参照文档我们调用此接口需要先获取access-token 获取access-token官方文档地址 注意事项 client_token 的有效时间为 2 个小时，重复获…
阅读更多...
CMake 教程（二）添加库

CMake 教程（二）添加库

目录 一、实例一——创建库1、add_library2、target_include_directories()、target_link_libraries()2.1 target_include_directories()2.2 target_link_libraries() 3、实例操作 二、实例二——添加选项1、option()2、实例操作 在第一节 CMake 教程（一&#xff09…
阅读更多...
fastadmin 列表页表格实现动态列

fastadmin 列表页表格实现动态列

记录：fastadmin 列表页表格实现动态列 后端代码 /*** 商品库存余额表*/public function kucunbalance(){$houseList (new House)->where([shop_id>SHOP_ID])->order(id desc)->field(name,id)->select();//设置过滤方法$this->request->filte…
阅读更多...
LeetCode209.长度最小的子数组

LeetCode209.长度最小的子数组

题目链接：209. 长度最小的子数组 - 力扣（LeetCode） 1.常规解法（会超时） 可以先将数组的所有子数组求出来，计算其中元素的值，判断与目标值的大小关系，代码如下： public …
阅读更多...
Ubuntu里彻底卸载UHD

Ubuntu里彻底卸载UHD

查看已经安装的UHD版本uhd_find_devices，展示的是当前安装的 UHD 库版本所支持的设备信息，下载了多个版本的uhd但是又记不住安装的位置，想要把所有的uhd相关环境全都删掉，用下边这个命令看一下所有的uhd信息： apt lis…
阅读更多...
在 Spring 中使用 @EhCache 注解作为缓存

在 Spring 中使用 @EhCache 注解作为缓存

文章目录 项目概况项目设置一个简单的 RESTful Web 服务Spring 整合 EhCache第 1 步：更新依赖项以使用 EhCache Spring 注解第 2 步：设置自定义缓存管理器第 3 步：配置 EhCache第 4 步：测试缓存 刷新缓存总结推荐阅读文章 EhCache…
阅读更多...
Visual Studio的实用调试技巧总结

Visual Studio的实用调试技巧总结

对于很多学习编程的老铁们来说，是不是也像下面这张图一样写代码呢？ 那当我们这样编写代码的时候遇到了问题？大家又是怎么排查问题的呢？是不是也像下面这张图一样，毫无目的的一遍遍尝试呢？ 这篇文章我就以 V…
阅读更多...
k8s的微服务

k8s的微服务

ipvs模式 Service 是由 kube-proxy 组件，加上 iptables 来共同实现的 kube-proxy 通过 iptables 处理 Service 的过程，需要在宿主机上设置相当多的 iptables 规则，如果宿主机有大量的Pod，不断刷新iptables规则，会消耗…
阅读更多...
QT：计算点到线段的垂线段的距离

QT：计算点到线段的垂线段的距离

描述 在Qt中，要计算一个点到一条线段的垂线段的长度（即点到线段上最近点的距离，且这个点是垂直于线段的），你不能直接使用QVector2D::distanceToLine，因为这个方法计算的是点到直线的垂直距离，而…
阅读更多...
2024-09-22 进程优先级，进程切换

2024-09-22 进程优先级，进程切换

一、僵尸状态 & 孤儿进程 进程退出：内核数据结构（task_struct 维护） 代码和数据（直接释放） 代码不会执行了首先可以立即释放的就是进程对应的程序信息数据。进程退出要有退出信息（进程的退出码&#…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023