Mysql基础7-约束

这篇具有很好参考价值的文章主要介绍了Mysql基础7-约束。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一、约束的基本概念

  1、概念:约束是作用于表中字段上的规则,用于限制储存在表中的数据

  2、目的:保证数据库中的数据的正确性,有效性和完整性

  3、分类

    • 非空约束(not null):限制该字段的数据不能为null
    • 唯一约束(unique):保证该字段的所有数据都是唯一,不重复的
    • 主键约束(primary key):主键是一行数据的唯一标识,要求非空且唯一
    • 默认约束(default):保存数据时,如果未指定该字段的值,则采用默认值
    • 检查约束(check 8.0以后的新约束):保证字段满足某一个条件
    • 外键约束(foreign key):用来让两张变的数据建立连接,保证数据的一致性和完整性

二、约束的案例实践

  需求1:创建一个表id、name、age、address、stu_num五个字段。

  需求2:id字段为主键,且设置为自动递增。

  需求3:name字段长度为10个字符并且不能为空。

  需求4:age字段要大于0并且小于150.

  需求5:address字段如果不设,默认为广州。

  需求6:stu_num唯一且不能为空。

mysql> create table stu_table(
    -> id int primary key auto_increment comment "id主键",
    -> name varchar(10) not null comment "姓名",
    -> age int check(age>0 && age<150) comment "年龄",
    -> address varchar(10) default "广州" comment "地址",
    -> stu_num int not null unique comment "学号"
    -> ) comment "学生表";
Query OK, 0 rows affected, 1 warning (0.03 sec)

  stu_table的表结构如下

mysql> desc stu_table;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int         | NO   | PRI | NULL    | auto_increment |
| name    | varchar(10) | NO   |     | NULL    |                |
| age     | int         | YES  |     | NULL    |                |
| address | varchar(10) | YES  |     | 广州    |                |
| stu_num | int         | NO   | UNI | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

  说明:

    • Type 是字段的类型
    • Null 是是否允许为空
    • key 是标记主键,外键和唯一的
    • Default 是该字段的默认值
    • Extra 是一些额外信息的展示

  验证1:添加一组正常数据

mysql> insert into stu_table (name, age, address, stu_num) values ("张三", 18, "深圳",10001);
Query OK, 1 row affected (0.00 sec)

mysql> select * from stu_table;
+----+--------+------+---------+---------+
| id | name   | age  | address | stu_num |
+----+--------+------+---------+---------+
|  1 | 张三   |   18 | 深圳    |   10001 |
+----+--------+------+---------+---------+
1 row in set (0.00 sec)

  说明1:各个字段都复合各个字段的要求,添加正常没有问题

  验证2:添加一个name 超过10位的异常数据

mysql> insert into stu_table (name, age, address, stu_num) values ("ABCDEFGHIJK", 18, "深圳",10002);
ERROR 1406 (22001): Data too long for column 'name' at row 1

  说明2:直接报错,提示 name  data too long 太长了

  验证3:验证age 大于150的异常情况

mysql> insert into stu_table (name, age, address, stu_num) values ("李四", 152, "深圳",10003);
ERROR 3819 (HY000): Check constraint 'stu_table_chk_1' is violated.

  说明3:这里提示了一个验证错误

  验证4:验证address不填写,默认值的设置

mysql> insert into stu_table (name, age, stu_num) values ("李四", 19,10002);
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu_table;
+----+--------+------+---------+---------+
| id | name   | age  | address | stu_num |
+----+--------+------+---------+---------+
|  1 | 张三   |   18 | 深圳    |   10001 |
|  2 | 李四   |   19 | 广州    |   10002 |
+----+--------+------+---------+---------+
2 rows in set (0.00 sec)

  说明4:在上面的insert 语句中只设置了name,age,stu_num三个字段,所以adderss就自动设置了默认值广州

  验证5:验证stu_num字段的唯一性

mysql> insert into stu_table (name, age, address, stu_num) values ("王五", 21, "上海",10002);
ERROR 1062 (23000): Duplicate entry '10002' for key 'stu_table.stu_num'

  说明5:提示10002已经重复了

 三、外键约束介绍

  1、什么是外键

    • 首先外键是表中一个字段
    • 外键是两张表之间的纽带
    • 设置外键的表称之为子表,外键对应的表称之为父表

  2、外键的介绍

    Mysql基础7-约束

    说明1:《学生表》和《辅导员》表示两张相互独立的表。

    说明2:在《学生表》中的辅导员编号,和《辅导员表》中的辅导员编号是一一对应的

    说明3:这种情况下就可以通过辅导员编号这个字段将《学生表》和《辅导员表》联系起来了

    说明4:这是辅导员编号字段,就符合设置为外键的条件

    说明5:如果将《学生表》中的辅导员编号字段设置为外键,则《学生表》为子表,《辅导员表》为父表

    说明6:外键在父表中是唯一,不可重复的。

  3、多外键展示

    Mysql基础7-约束

    说明1:通过上图发现《学生表》中的班级id和《班级表》中的班级id也存在一一对应的关系

    说明2:班级id也符合设置外键的标准。

    说明3:例如:辅导员编号,班级id都符合外键的设置标准,所以一个表中可以有多个外键,但是每个外键对应不同的表

  4、不符合外键的展示

    Mysql基础7-约束

    说明1:在《学生表》班级评级字段和《班级考核与平级对照表》中的班级平级字段也存在着关系。

    说明2:但是这个班级评级字段就不存在外键的特征,因为班级评级在《班级考核与评级对照表》中不是惟一的。

    说明3:在子表中的四星,对应父表中有三种情况这样就会出现子表中的四星到底对应父表的哪一个四星的情况。

四、外键约束展示

  1、原始数据:student表结构及其数据

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  1 | stu1     |          1 |    98 |
|  2 | stu2     |          1 |    88 |
|  3 | stu3     |          2 |    79 |
|  4 | stu4     |          2 |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
6 rows in set (0.00 sec)

  2、原始数据:teacher表结构及其数据

mysql> select * from teacher;
+------------+--------------+
|         id | teacher_name |
+------------+--------------+
|          1 | 张三         |
|          2 | 李四         |
|          3 | 王五         |
+------------+--------------+
3 rows in set (0.00 sec)

  3、添加外键的语法

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 父表 (父表字段)on update 更新行为 on delete 删除行为

    说明1:alter table 是DML语法,修改表的意思,在之前的文章中已经介绍过

    说明2:add constraint 是添加约束的意思

    说明3:foreign key 是外键约束的关键字

    说明4:references 后面跟上父表和父表中字段

  4、需求:给student表中的teacher_id设置为teacher表的外键,并且对应id字段的数据

mysql> alter table student add constraint fk_teacher foreign key (teacher_id) references teacher (id);
Query OK, 6 rows affected (0.22 sec)
Records: 6  Duplicates: 0  Warnings: 0

    说明1:外键一旦设置成功,将会保持子表和父表的数据一致性和完整性。

    说明2:这个时候,如果我删除《teacher》表中的id=1的张三老师,就会出错,因为,如果张三在《teacher》表中删除了,则在《student》中的辅导员编号这列数据就找不到对应的值

    说明3:从而这样就破坏了数据的完整性和一致性

mysql> delete from teacher where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mysql_test`.`student`, CONSTRAINT `fk_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`))

    说明1:这个时候就会提示,不能删除或者修改父表中的数据,因为有外键存在

  5、外键数据的更新和删除行为

    • no action:当在父表中删除/更新对应记录时,首先检查该记录是否有对应的外键,如果有则不允许删除/更新(与restrict一致)
    • restrict:当在父表中删除/更新对应记录时,首先检查该记录是否有对应的外键,如果有则不允许删除/更新(与 no action一致)
    • cascade:当在父表中删除/更新对应记录时,首先检查该记录是否有对应的外键,如果有,则也删除/更新外键在子表中的记录
    • set null:当在父表中删除/更新对应记录时,首先检查该记录是否有对应的外键,如果有则设置子表中该外键的值为null,这就要求该外键记录允许null
    • set default:父表有变更时,子表将外键列设置成一个默认的值(Innodb不支持)

  6、重新创建《student》和《teacher》表并添加外键

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  1 | stu1     |          1 |    98 |
|  2 | stu2     |          1 |    88 |
|  3 | stu3     |          2 |    79 |
|  4 | stu4     |          2 |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
6 rows in set (0.00 sec)

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  1 | 张三         |
|  2 | 李四         |
|  3 | 王五         |
+----+--------------+
3 rows in set (0.00 sec)

mysql> alter table student add constraint fk_teacher foreign key (teacher_id) references teacher(id) on update cascade on delete cascade;
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

  7、验证cascade级联行为

    验证1:我修改《teacher》表中id=1的数据改为id=4

mysql> update teacher set id=4 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  2 | 李四         |
|  3 | 王五         |
|  4 | 张三         |
+----+--------------+
3 rows in set (0.01 sec)

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  1 | stu1     |          4 |    98 |
|  2 | stu2     |          4 |    88 |
|  3 | stu3     |          2 |    79 |
|  4 | stu4     |          2 |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
6 rows in set (0.00 sec)

    说明1:这个时候我们会发现,当我修改了《teacher》表中辅导员编号的id字段是,在《student》表中teacher_id 原本等于1的也都改为了4,这就是cascade的作用

    验证2:cascade的删除行为

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  2 | 李四         |
|  3 | 王五         |
+----+--------------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  3 | stu3     |          2 |    79 |
|  4 | stu4     |          2 |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
4 rows in set (0.00 sec)

    说明2:和更新一样,cascade的删除也是级联的。

  8、验证set null的更新和删除行为

    需求1:同样先删除《student》和《teacher》表然后重新建立新的表,重新建立外键约束测试

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  1 | 张三         |
|  2 | 李四         |
|  3 | 王五         |
+----+--------------+
3 rows in set (0.00 sec)

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  1 | stu1     |          1 |    98 |
|  2 | stu2     |          1 |    88 |
|  3 | stu3     |          2 |    79 |
|  4 | stu4     |          2 |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
6 rows in set (0.00 sec)

mysql> alter table student add constraint fk_teacher foreign key (teacher_id) references teacher(id) on update set null on delete set null;
Query OK, 6 rows affected (0.04 sec)
Records: 6  Duplicates: 0  Warnings: 0

    验证1:更新《teacher》表中id=1的数据,改为id=4

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

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  2 | 李四         |
|  3 | 王五         |
|  4 | 张三         |
+----+--------------+
3 rows in set (0.00 sec)

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  1 | stu1     |       NULL |    98 |
|  2 | stu2     |       NULL |    88 |
|  3 | stu3     |          2 |    79 |
|  4 | stu4     |          2 |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
6 rows in set (0.01 sec)

    说明1:对应更新的数据都改为了null

    验证2:删除《teacher》表中id=2的数据

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

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  3 | 王五         |
|  4 | 张三         |
+----+--------------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  1 | stu1     |       NULL |    98 |
|  2 | stu2     |       NULL |    88 |
|  3 | stu3     |       NULL |    79 |
|  4 | stu4     |       NULL |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
6 rows in set (0.00 sec)

    说明1:当删除数据的时候,子表中对应的外键数据也会变成了null

五、删除外键约束

  1、删除外键的语法

alter table 表名 drop foreign key 外键名称;
mysql> alter table student drop foreign key fk_teacher;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

  2、删除外键后,数据一致性和完整性的验证

mysql> delete from teacher where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  2 | 李四         |
|  3 | 王五         |
+----+--------------+
2 rows in set (0.00 sec)

    说明1:这个时候就可以在《teacher》表中删除id=1的张三老师了,但是这样《student》表和《teacher》表的数据的一致性就破坏了

 

    文章来源地址https://www.toymoban.com/news/detail-584053.html

到了这里,关于Mysql基础7-约束的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL:表的约束和基本查询

    表的约束——为了让插入的数据符合预期。 表的约束很多,这里主要介绍如下几个: null/not null,default, comment, zerofill,primary key,auto_increment,unique key 。 两个值:null(默认的)和not null(不为空) 数据库默认字段基本都是字段为空,但是实际开发时,尽可能保证字段不为空,因

    2024年02月13日
    浏览(38)
  • 密码基础知识——密码的概念与作用

    密码是指采用 特定变换 的方法对 信息 等进行 加密保护、安全认证 的 技术、产品和服务 。 在我国,密码分为 核心密码、普通密码和商用密码 ,其中商用密码用于保护 不属于国家秘密 的信息。 从内容上看,密码技术包括密码编码、实现、协议、安全防护、分析破译,以

    2023年04月26日
    浏览(54)
  • MySQL基础(十三)约束

    1.1 为什么需要约束 数据完整性(Data Integrity)是指数据的精确性(Accuracy)和可靠性(Reliability)。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。 为了保证数据的完整性,SQL规范以约束的方式对 表数据进行额外

    2024年02月03日
    浏览(70)
  • Mysql基础7-约束

    1、概念:约束是作用于表中字段上的规则,用于限制储存在表中的数据 2、目的:保证数据库中的数据的正确性,有效性和完整性 3、分类 非空约束(not null):限制该字段的数据不能为null 唯一约束(unique):保证该字段的所有数据都是唯一,不重复的 主键约束(primary key):主键是

    2024年02月17日
    浏览(42)
  • MySQL基础3-约束

    约束是作用于表中字段上的规则,用于限制存储在表中的数据 保证数据库中数据的正确、有效性和完整性 约束 描述 非空约束 限制该字段的数据不能为null NOT NULL 唯一约束 保证该字段的所有数据都是唯一、不重复的 UNIQUE 主键约束 主键是一行数据的唯一标识,要求

    2024年02月09日
    浏览(40)
  • Node.js的基本概念&&node -v 和npm -v 这两个命令的作用

    Node.js 是一个开源且跨平台的 JavaScript 运行时环境,它可以让你在服务器端运行 JavaScript 代码。Node.js 使用了 Chrome 的 V8 JavaScript 引擎来执行代码,非常高效。 在 Node.js 出现之前,JavaScript 通常只在浏览器中运行,用于控制网页行为和与用户交互。Node.js 的出现让 JavaScript 能够

    2024年02月08日
    浏览(57)
  • Mysql基础篇(二)之函数和约束

    Mysql中的函数主要分为四类:字符串函数、数值函数、日期函数、流程函数 常用函数如下: 函数 功能 CONCAT(S1, S2, ......Sn) 字符串拼接,将S1,S2,.....Sn拼接成一个字符串 LOWER(str) 将字符串str全部转为小写 UPPER(str) 将字符串str全部转为大写 LPAD(str, n, pad) 左填充,用字符串pad对

    2024年02月11日
    浏览(41)
  • MySQL数据库基础(九):SQL约束

    文章目录 SQL约束 一、主键约束 二、非空约束 三、唯一约束 四、默认值约束 五、外键约束(了解) 六、总结 PRIMARY KEY 约束唯一标识数据库表中的每条记录。 主键必须包含唯一的值。 主键列不能包含 NULL 值。 每个表都应该有一个主键,并且每个表只能有一个主键。 遵循原

    2024年02月19日
    浏览(60)
  • Mysql基础(下)之函数,约束,多表查询,事务

    👂 回到夏天(我多想回到那个夏天) - 傲七爷/小田音乐社 - 单曲 - 网易云音乐 截图自 劈里啪啦 -- 黑马Mysql,仅学习使用 👇原地址 47. 基础-多表查询-表子查询_哔哩哔哩_bilibili 目录 🦂函数 🌳字符串函数 🌳数值函数 🌳日期函数 🌳流程函数 🌳小结 🦂约束  🍈概述 🍈

    2024年02月16日
    浏览(42)
  • 【MySQL】1、MySQL数据库的基本概念

    数据库的基本概念 1.数据 2.表         将不同的记录组织在一起;用来存储具体数据 3.数据库         表的集合,是存储数据的仓库;以一定的组织方式存储的相互有关的数据集合 4.数据库管理系统         是实现对数据库资源有效组织、管理和存取的系统软件

    2024年02月11日
    浏览(63)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包