登录  /  注册
首页 > 数据库 > mysql教程 > 正文

深入浅析MySQL中常见的6种约束类型

青灯夜游
发布： 2021-09-16 19:55:03
转载
2966人浏览过

深入浅析MySQL中常见的6种约束类型

约束的字面意思就是规定或者限制某个事该如何去做，在MySQL中，约束就是对数据表中数据指定规则，也就是对数据进行限制，以此来确保可靠性，比如不允许某列出现Null值，实际中我们会遇到以下类型的约束。

  • NOT NULL ： 确保列不能有NULL值
  • CHECK ： 确保列中的值满足特定条件
  • UNIQUE ： 确保一列中的所有值都不同
  • PRIMARY KEY： NOT NULL和UNIQUE组合，唯一标识表中的每一行
  • FOREIGN KEY ： 外键约束
  • DEFAULT ： 如果未指定值，则为列设置默认值

【相关推荐：mysql视频教程">mysql视频教程】

约束

1.NULL

MySQL中通过使用NOT NULL确保列中不会出现Null值，创建表时候格式如下：

mysql> create table user(name varchar(255)not null);
Query OK, 0 rows affected (0.06 sec)
登录后复制

如果试图插入一个null值，则会抛出异常。

mysql> insert user values(null);
ERROR 1048 (23000): Column 'name' cannot be null
登录后复制

或者在现有表上新增NOT NULL约束。

mysql> alter table user modify name varchar(255) not null;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
登录后复制

删除NOT NULL约束。

mysql> alter table user modify name varchar(255)  null;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0
登录后复制

2.CHECK

如果想在列上定义条件约束，可以使用CHECK，比如下面，强制让年龄字段大于18，小于80，否则将会报错。

mysql> create table user(age int(11) check(age>18 and age <80));
Query OK, 0 rows affected, 1 warning (0.06 sec)
登录后复制

插入测试，可以发现9、81在插入的时候抛出异常。

mysql> insert user values(9);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.

mysql> insert user values(19);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(81);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql>
登录后复制

也可以进行多列约束，如年龄必须大于18，城市必须为中国。

mysql> create table user(age int(11),city varchar(255) ,check(age>18 and city=&#39;中国&#39;));
Query OK, 0 rows affected, 1 warning (0.05 sec)
登录后复制

插入测试。

mysql> insert user values(81,&#39;2&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values(8,&#39;2&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values(20,&#39;2&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values(20,&#39;中国&#39;);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(20,&#39;中国1&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values(85,&#39;中国&#39;);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(9,&#39;中国&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
登录后复制

还可以让列值必须在指定集合中，如性别必须在男、女、未知、人妖集合中。

mysql> create table user(sex varchar(255) check (sex in (&#39;男&#39;,&#39;女&#39;,&#39;未知&#39;,&#39;人妖&#39;)));
Query OK, 0 rows affected (0.05 sec)
登录后复制

插入测试。

mysql> insert user values("男");
Query OK, 1 row affected (0.02 sec)

mysql> insert user values("男男");
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values("女");
Query OK, 1 row affected (0.01 sec)

mysql> insert user values("人妖");
Query OK, 1 row affected (0.00 sec)
登录后复制

为约束命名并删除约束。

mysql> create table user (age int(11) ,constraint CHK_AGE check(age>18));
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> insert user values(5);
ERROR 3819 (HY000): Check constraint &#39;CHK_AGE&#39; is violated.

mysql> alter table user drop check CHK_AGE;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> insert user values(5);
Query OK, 1 row affected (0.01 sec)
登录后复制

但是，这样的写法你见过吗？

猜猜下面的作用是什么。

这其实是一个case when条件判断，让其仅仅可以插入>=18，或者是在0-10之间的数。

CREATE TABLE `user` (`age` int(11) CHECK 
(((case when (`age` >=18) then 1 
else 
(case when age<10 and age >0 then 1 else 2 end) end) =1)));
登录后复制

3.UNIQUE

UNIQUE约束确保列中的没有重复的值，UNIQUE和 PRIMARY KEY约束都为一列值的唯一性提供保障，但是UNIQUE每个表可以出现多次，而PRIMARY KEY只能出现一个。

如下面name字段不能重复。

mysql> create table user (name varchar(255),unique(name));
Query OK, 0 rows affected (0.07 sec)
登录后复制

插入测试。

mysql> insert user values("张三");
Query OK, 1 row affected (0.02 sec)

mysql> insert user values("张三");
ERROR 1062 (23000): Duplicate entry &#39;张三&#39; for key &#39;user.name&#39;mysql>
登录后复制

对此约束进行起名，并删除。

mysql> create table user (name varchar(255),constraint name_un unique(name));
Query OK, 0 rows affected (0.07 sec)

mysql> insert user values("张三");
Query OK, 1 row affected (0.02 sec)

mysql> insert user values("张三");
ERROR 1062 (23000): Duplicate entry &#39;张三&#39; for key &#39;user.name_un&#39;
mysql> alter table user drop index name_un;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert user values("张三");
Query OK, 1 row affected (0.02 sec)
登录后复制

插入后可以用以下语句查看创建语句。

mysql> show create table user;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  UNIQUE KEY `name_un` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
登录后复制

要删除UNIQUE约束，可以使用DROP INDEX或ALTER TABLE语句：

mysql> DROP INDEX name_un ON user;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table user;
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
登录后复制

在现有表上添加。

mysql> alter table user add constraint name_un unique(name);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
登录后复制

4.PRIMARY KEY

通常每个表中包含一个用于唯一标识每一行的值，这个列就被称为PRIMARY KEY。

mysql> create table user (id int(11) ,age int(11),primary key (id));
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> insert user values(1,2);
Query OK, 1 row affected (0.02 sec)

mysql> insert user values(1,2);
ERROR 1062 (23000): Duplicate entry &#39;1&#39; for key &#39;user.PRIMARY&#39;mysql>
登录后复制

5.FOREIGN KEY

FOREIGN KEY用于约束表中的一个字段必须是另一个表中某个字段所存在的值，但是在另一个表中，这个列不一定是主键，但必须是唯一性索引，否则会创建失败。

比如orders表中的userId必须参考user表中的id，如果插入的userId在user表中不存在，则无法插入。

mysql> create table orders (id int(11) primary key ,userId int(11) ,  FOREIGN KEY (userId) REFERENCES user(id) );
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> insert orders values(1,3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`t`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`))

mysql> insert orders values(1,1);
Query OK, 1 row affected (0.01 sec)
登录后复制

但是存在一个问题，如果主表(user)中记录被删除或者更新，那orders中的记录该怎么办？，如下面的例子，可以发现直接报错了。

mysql> update user set id =2 where id =1;

Cannot delete or update a parent row: a foreign key constraint fails (`t`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
登录后复制

MySQL提供了几个约束可以帮助我们解决这类问题，比如在user表更新时，orders也相继更新。

  1. RESTRICT：如果子表中有记录，则拒绝更新或删除父表中的记录。

  2. CASCADE：更新或删除父表中的记录时，自动更新或删除子表中的记录。

  3. SET NULL：在更新或删除父表记录时，将子表中字段的值设置为空。

可以发现，默认采用的是RESTRICT，下面来修改一下，让在更新时候也同样更新，在删除时候设置null。

mysql> alter table orders add constraint orders_ibfk_1  FOREIGN KEY (`userId`) REFERENCES `user` (`id`) on update cascade on
delete set null;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0
登录后复制

测试更新

mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
+----+--------+
1 row in set (0.00 sec)

mysql> select * from orders;
Empty set (0.00 sec)

mysql> insert orders values (1,1);
Query OK, 1 row affected (0.01 sec)

mysql> update user set id =2 where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from orders;
+----+--------+
| id | userId |
+----+--------+
|  1 |      2 |
+----+--------+
1 row in set (0.01 sec)
登录后复制

测试删除。

mysql> delete from user where id =2;
Query OK, 1 row affected (0.02 sec)

mysql> select * from orders;
+----+--------+
| id | userId |
+----+--------+
|  1 |   NULL |
+----+--------+
1 row in set (0.00 sec)
登录后复制

6.DEFAULT

DEFAULT约束用于为列设置默认值，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值，没有赋值指的是在insert插入数据时没有指明这个字段，如果指定null值，最终存放的还是null值。

mysql> create table user(age int(11) default 18);
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> insert user values();
Query OK, 1 row affected (0.02 sec)

mysql> select * from user;
+------+
| age  |
+------+
|   18 |
+------+
1 row in set (0.00 sec)
登录后复制
原文地址：https://juejin.cn/post/7000352993572814885作者：i听风逝夜

更多编程相关知识，请访问：编程视频！！

以上就是深入浅析MySQL中常见的6种约束类型的详细内容，更多请关注php中文网其它相关文章！

智能AI问答
PHP中文网智能助手能迅速回答你的编程问题，提供实时的代码和解决方案，帮助你解决各种难题。不仅如此，它还能提供编程资源和学习指导，帮助你快速提升编程技能。无论你是初学者还是专业人士，AI智能助手都能成为你的可靠助手，助力你在编程领域取得更大的成就。
我要提问
相关标签：
mysql
来源：掘金--i听风逝夜网
收藏 点赞
上一篇：教你怎么在线安装Mysql 下一篇：详细解读MySql都有哪些顽疾
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
作者最新文章
最新问题
function_exists()无法判定自定义函数 function test()    {        return true;    } ...
凡人来自于2024-04-29 11:01:01
0 19 1374
google 浏览器 手机版显示的怎么实现 老师您好，google 浏览器怎么变成手机版样式的？
艾尼宛尔.亚森来自于2024-04-23 00:22:19
0 6 1244
子窗口操作父窗口，输出没反应 前两句可执行，最后一句没法应
P粉722478067来自于2024-04-19 15:37:47
0 1 1271
父窗口没有输出 document.onclick = function(){ window.opener.document.write('我是子窗口的输出');  &nb...
P粉722478067来自于2024-04-18 23:52:34
0 0 1060
关于CSS思维导图的课件在哪？ 课件
凡人来自于2024-04-16 10:10:18
0 0 1173
PX自动转换为REM错误  <style>html {   font-size: calc(100vw / 3.75);      }...
凡人来自于2024-04-16 09:34:16
0 0 5976
PHP数组从URL参数中获取的行为不如预期 我有一个包含类别ID的URL参数，我想将其视为一个数组，如下所示：http://example.com?cat[]=3,9,13在PHP中，我使用它从URL参数获取数组：$catI...
P粉785905797来自于2024-04-06 22:09:02
0 1 1464
通过添加 Width 属性将内容向左移动 我已经为主体提供了边距。主要{左边缘：200px;右边距：200px；文本对齐：居中}由于我想以两行而不是一行显示文本，因此我在样式中添加了width属性。.p{字体大小：12px...
P粉738046172来自于2024-04-06 22:01:35
0 3 578
我应该在 apache 中哪里放置 CustomLog 指令 我正在使用php:7.2-apachedocker。我需要禁用运行状况检查url登录访问日志。基于此链接，他们提到了有关修改Customlog指令的信息。我不是关于需要更改Cust...
P粉573809727来自于2024-04-06 22:03:59
0 1 658
返回值中变量的格式是什么？ 我是php的新学习者。我发现有一段代码：if($x<time()){return[false,'error'];}逻辑或变量并不重要，但我不明白[false,'error']...
P粉757556355来自于2024-04-06 21:55:20
0 1 441
相关专题
更多>
热门推荐
热门教程
更多>
相关推荐
热门推荐
最新课程
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责申明 意见反馈 讲师合作 广告合作 最新更新
php中文网：公益在线php培训，帮助PHP学习者快速成长！
关注服务号 技术交流群
app下载
PHP中文网订阅号
每天精选资源文章推送
PHP中文网APP
随时随地碎片化学习
PHP中文网抖音号
发现有趣的

Copyright 2014-2024 //m.sbmmt.com/ All Rights Reserved | php.cn | 湘ICP备2023035733号

  • 精品班

  • 技术支持

  • 技术咨询

  • 学习群

  • 会员优惠

  • 返回顶部