『 MySQL篇』：MySQL表的相关约束

基础篇 MySQL系列专栏(持续更新中 …)1『 MySQL篇』：库操作、数据类型2『 MySQL篇』：MySQL表的CURD操作3『 MySQL篇』：MySQL表的相关约束

文章目录

概述 : 约束是作用于表中字段上的限制,用于限制存储在表中的数据
目的: 保证数据库中的数据的正确 , 有效性和完整性
分类
约束描述关键字非空约束限制该字段的数据不能为 nullNOT NULL唯一性约束保证该字段的所有数据都是唯一. 不重复的UNIQUE主键约束主键是一行数据唯一的表示 , 要求非空且唯一PRIMARY KEY默认约束保存数据时 ,如果未指定该字段的值 , 则采用默认值DEFAULT检查约束(8.0.16版本之后)保证字段值满足某一个条件CHECK外键约束用来让两张表的数据之间建立连接 , 保证数据的一致性和完整性FOREIGN KEY
1 . 非空约束 (
```
not null
```
)

创建表时进行使用 , 指定某一列的值不为空

createtable 表名(列名 类型 notnull);

案例

创建表中包含字段名为 name 字段类型为 varchar(10) , name 不允许为空值

mysql>createtableuser(name varchar(10)notnull,gender char(1));
Query OK,0rows affected (0.03 sec)

mysql>descuser;+--------+-------------+------+-----+---------+-------+| Field  |Type|Null|Key|Default| Extra |+--------+-------------+------+-----+---------+-------+| name   |varchar(10)|NO||NULL||| gender |char(1)| YES  ||NULL||+--------+-------------+------+-----+---------+-------+2rowsinset(0.01 sec)-- 插入姓名为空值时
mysql>insertintouservalues(null,'男');
ERROR 1048(23000): Column'name' cannot be null
mysql>insertintouservalues('张三','男');
Query OK,1row affected (0.00 sec)

2 . 唯一性约束(

unique

)

在创建表时 , 对某一列使用唯一性约束 , 则该列的值不能重复 .

createtable 表名(列名 类型 unique);

案例

创建表中包含字段名为 name , 字段类型为 varchar(10) , name 不允许为空值 . 且name 为唯一

mysql>createtableuser(name varchar(20)notnullunique);-- name 列不能重复且非空
Query OK,0rows affected (0.05 sec)

mysql>descuser;+-------+-------------+------+-----+---------+-------+| Field |Type|Null|Key|Default| Extra |+-------+-------------+------+-----+---------+-------+| name  |varchar(20)|NO| PRI |NULL||+-------+-------------+------+-----+---------+-------+1rowinset(0.00 sec)#第一次直接插入
mysql>insertintouservalues('张三');
Query OK,1row affected (0.00 sec)#当插入重复值时 , 已存在不能重复添加
mysql>insertintouservalues('张三');
ERROR 1062(23000): Duplicate entry '张三'forkey'name'
mysql>

如果没有指定非空 , 那么unique 字段可以有多个 null , 一张表中可以有多个unique 字段

如果此时user 列已经创建 , 要将唯一性约束添加到 name 列 , 则使用

# 修改user表中的约束altertableusermodify age intnotnullunique;

3 . check约束

用于强制行数据必须满足的条件 .

Oracle 和SQL sever均支持check , 但是mysql 5.7 目前还不支持check , 只进行语法检验 ,并不会生效 .

4 . 默认约束(

default

)

在创建表的时候可以使用 , 可以设置列的默认值

createtable 表名(列名 类型 default 默认值..);

案例

创建表中包含字段名为
sex
, 字段类型为
char( 1 )
, 约束条件为默认值为 ‘男’

mysql>createtableuser(id int, gender char(1)default'男');
Query OK,0rows affected (0.01 sec)

mysql>descuser;+--------+---------+------+-----+---------+-------+| Field  |Type|Null|Key|Default| Extra |+--------+---------+------+-----+---------+-------+| id     |int(11)| YES  ||NULL||| gender |char(1)| YES  || 男      ||+--------+---------+------+-----+---------+-------+2rowsinset(0.00 sec)

5 . 主键约束(

primary key

)

主键与外键之间的区别 ?

主键(主码) ：主键用于唯一标识一个元组，不能有重复，不允许为空。一个表只能有一个主键。

外键(外码) ：外键用来和其他表建立联系用，外键是另一表的主键，外键是可以有重复的，可以是空值。一个表可以有多个外键。

primary key 是主键约束 , 表由列和行组成。通常，表具有一列或多列，列的值唯一地标识表中的每一行。此列或多列称为主键。由两列或更多列组成的主键也称为复合主键。

createtable 表名(列名 类型 primarykey);

primary key 相当于not null和 unique结合的效果 .

在实际开发当中, 每个表通常都会设置一个主键 , 但允许多个列放到一起共同作为一个主键(联合主键)

案例

创建表中包含字段名为 id , 字段类型为
 int
, 其中
id 
为主键 ,不为空且唯一

mysql>createtableuser(id intprimarykey,name varchar(20));
Query OK,0rows affected (0.05 sec)

mysql>descuser;+-------+-------------+------+-----+---------+-------+| Field |Type|Null|Key|Default| Extra |+-------+-------------+------+-----+---------+-------+| id    |int(11)|NO| PRI |NULL||| name  |varchar(20)| YES  ||NULL||+-------+-------------+------+-----+---------+-------+2rowsinset(0.01 sec)# 当插入两条主键相同的数据时,
mysql>insertintouservalues(12,'张'),(12,'李');
ERROR 1062(23000): Duplicate entry '12'forkey'PRIMARY'

设置自增主键

MySQl 当中支持自增主键 , 在插入数据时 ,未设置初始值的情况下 , 会自动从 1 开始自增

createtable 表名(列名 类型 primarykeyauto_increment,...);

案例

#将user表中的主键id 设置为自增主键
mysql>createtableuser(id intprimarykeyauto_increment,name varchar(20));
Query OK,0rows affected (0.05 sec)

mysql>descuser;+-------+-------------+------+-----+---------+----------------+| Field |Type|Null|Key|Default| Extra          |+-------+-------------+------+-----+---------+----------------+| id    |int(11)|NO| PRI |NULL|auto_increment|| name  |varchar(20)| YES  ||NULL||+-------+-------------+------+-----+---------+----------------+2rowsinset(0.00 sec)

mysql>insertintouservalues(null,'李'),(null,'张'),(null,'王');
Query OK,3rows affected (0.01 sec)
Records: 3  Duplicates: 0Warnings: 0# 自增主键设置成功
mysql>select*fromuser;+----+------+| id | name |+----+------+|1| 李   ||2| 张   ||3| 王   |+----+------+3rowsinset(0.00 sec)# id列的null并不是将id设置为null, 而是将id的赋值交给数据库来使用自增主键

MySQL当中数据量较大的时候 , 就需要使用多个主机进行储存, 本质上就是分表然后每个数据库服务器只储存一部分数据 , 这种情况下式就涉及到了"分布式系统中唯一id生成算法" , 利用不同时间下的时间戳和主机编号并结合随机因子 , 就可以得到唯一的id.

6 . 外键约束(

foreign key

)

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

createtable 表名(
      字段名 数据类型,foreignkey(本表字段名) renferences 主表名(主键名或unique字段名))

altertable 添加外键的表名 addconstraint 外键名称 foreignkey(从表字段名) renferences 主表名(主表字段名)

外键约束常用来定义主表和从表的关系 , 外键约束定义在从表上 ,主表上必须具有主键约束或是unique约束

外键约束演示

# 创建主班级表
mysql>createtable class(id intprimarykeycomment'班级编号',
                          name varchar(10)notnull);
Query OK,0rows affected (0.02 sec)# 创建学生表并建立外键约束
mysql>createtable student(id intprimarykeycomment'学生编号',
                            name varchar(20)notnull,
                            class_id int,-- 学生所在的班级编号foreignkey(class_id)references class(id));
Query OK,0rows affected (0.01 sec)# 主表副表结构
mysql>desc class;+-------+-------------+------+-----+---------+-------+| Field |Type|Null|Key|Default| Extra |+-------+-------------+------+-----+---------+-------+| id    |int(11)|NO| PRI |NULL||| name  |varchar(10)|NO||NULL||+-------+-------------+------+-----+---------+-------+2rowsinset(0.00 sec)

mysql>desc student;+----------+-------------+------+-----+---------+-------+| Field    |Type|Null|Key|Default| Extra |+----------+-------------+------+-----+---------+-------+| id       |int(11)|NO| PRI |NULL||| name     |varchar(20)|NO||NULL||| class_id |int(11)| YES  | MUL |NULL||+----------+-------------+------+-----+---------+-------+3rowsinset(0.00 sec)#班级表中插入数据
mysql>INSERTINTO class values(100,'java'),(200,'web');
Query OK,2rows affected (0.01 sec)
Records: 2  Duplicates: 0Warnings: 0

mysql>select*from class;+-----+------+| id  | name |+-----+------+|100| java ||200| web  |+-----+------+#学生表插入数据
mysql>insertinto student values(1,'tom',100);
Query OK,1row affected (0.02 sec)

mysql>insertinto student values(2,'jack',200);
Query OK,1row affected (0.02 sec)#添加失败,300号班级在class表中不存在,由于外键约束的作用,插入失败
mysql>insertinto student values(3,'frx',300);
ERROR 1452(23000): Cannot addorupdate a child row: a foreignkeyconstraint fails (`mytestdb`.`student`,CONSTRAINT`student_ibfk_1`FOREIGNKEY(`class_id`)REFERENCES`class`(`id`))

删除更新行为
行为说明（当在父表中删除／更新对应的记录时）NO ACTION检查该记录是否有对应外籍，如果有则不允许删除／更新RESTRICT检查该记录是否有对应的外键，有则不允许删除／更新CASCADE(级联)检查该记录是否有对应的外键，如果有，则也删除／更新外键在子表中的记录SET　NULL检查该记录是否有对应的外键，如果有则设置子表中该外键的值为NULLSET　DEFAULT父表有变更时,子表将外键列设置成一个默认的值(innodb不支持)
格式

altertable 子表 addconstraint 外键名 foreignkey(字段名)references 父表(字段名)onupdate 行为 ondelete 行为;

示例

将 student 表中的外键删除更新行为设置为 update set null 和 delete set null

#设置 student 表中的外键
mysql>altertable student addconstraint class_id foreignkey(class_id)references class(id)onupdatesetnullondeletesetnull;

Query OK,0rows affected (0.05 sec)
Records: 0  Duplicates: 0Warnings: 0#更新父表中的 id 
mysql>update class set id =300where name ='web';

Query OK,1row affected (0.01 sec)Rowsmatched: 1  Changed: 1Warnings: 0#查询子表中的全部信息
mysql>select*from student;+----+------+----------+| id | name | class_id |+----+------+----------+|1| tom  |100||2| jack |NULL|+----+------+----------+2rowsinset(0.00 sec)

删除外键约束

定义的任何约束可以使用带有DROP CONSTRAINT选项的ALTER TABLE命令删除 ,

ALTERTABLE table_name DROPCONSTRAINT fk_name;

如果使用的是MySQL，则可以使用更清晰的语法，如下所示：

ALTERTABLE table_name  DROPFOREIGNKEY fk_name;

示例:

-#删除外键约束
mysql>altertable student dropforeignkey class_id;
Query OK,0rows affected (0.04 sec)
Records: 0  Duplicates: 0Warnings: 0#删除外键约束后,主表与从表再无关联,成功插入
mysql>insertinto student values(100,'张',550);
Query OK,1row affected (0.00 se

实际开发中 , 为什么不推荐使用外键与级联?

一方面 , 外键的使用保证了数据库中数据的一致性和完整性 , 并且级联操作方便,减少了程序的代码量

另一方面 , 每次进行 update 和 delete 都需要考虑外键约束 , 导致测试数据极不方便 , 主从关系是确定的,如果哪天需求发生变化 , 会增加很多麻烦 .

所以 , 外键与级联更新适用于单机低并发，不适合分布式、高并发集群; 级联更新是强阻塞，存在数据库更新风暴的风险; 外键影响数据库的插入速度 , 并发量不是很高的情况下可以使用外键

标签： mysql 数据库 sql

本文转载自: https://blog.csdn.net/m0_56361048/article/details/128769643
版权归原作者 署前街的少年 所有，如有侵权，请联系我们删除。

『 MySQL篇』：MySQL表的相关约束

基础篇 MySQL系列专栏(持续更新中 …)1『 MySQL篇』：库操作、数据类型2『 MySQL篇』：MySQL表的CURD操作3『 MySQL篇』：MySQL表的相关约束

1 . 非空约束 (

2 . 唯一性约束(

3 . check约束

4 . 默认约束(

5 . 主键约束(

6 . 外键约束(

发表评论

“『 MySQL篇』：MySQL表的相关约束”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航

『 MySQL篇 』：MySQL表的相关约束

基础篇 MySQL系列专栏(持续更新中 …)1『 MySQL篇 』：库操作、数据类型2『 MySQL篇 』：MySQL表的CURD操作3『 MySQL篇 』：MySQL表的相关约束

1 . 非空约束 (

2 . 唯一性约束(

3 . check约束

4 . 默认约束(

5 . 主键约束(

6 . 外键约束(

发表评论

“『 MySQL篇 』：MySQL表的相关约束”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航

『 MySQL篇』：MySQL表的相关约束

基础篇 MySQL系列专栏(持续更新中 …)1『 MySQL篇』：库操作、数据类型2『 MySQL篇』：MySQL表的CURD操作3『 MySQL篇』：MySQL表的相关约束

“『 MySQL篇』：MySQL表的相关约束”的评论: