『mysql篇』：mysql表的相关约束(代码片段)

关键词：

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

---

文章目录

• • • • • 1 . 非空约束 (`not null`)
        • 2 . 唯一性约束(`unique`)
        • 3 . check约束
        • 4 . 默认约束(`default`)
        • 5 . 主键约束(`primary key`)
        • 6 . 外键约束(`foreign key`)

)

• 概述 : 约束是作用于表中字段上的限制,用于限制存储在表中的数据

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

• 分类

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

1 . 非空约束 (not null)

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

create table 表名(列名 类型 not null);

• 案例

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

mysql> create table user(name varchar(10) not null,gender char(1));
Query OK, 0 rows affected (0.03 sec)

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

2 . 唯一性约束(unique)

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

create table 表名(列名 类型 unique);

• 案例

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

mysql> create table user(name varchar(20)not null unique);  
-- name 列不能重复且非空
Query OK, 0 rows affected (0.05 sec)

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

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

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

# 修改user表中的约束
alter table user modify age int not null unique;

3 . check约束

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

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

4 . 默认约束(default)

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

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

• 案例

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

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

mysql> desc user;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id     | int(11) | YES  |     | NULL    |       |
| gender | char(1) | YES  |     | 男      |       |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

5 . 主键约束(primary key)

主键与外键之间的区别 ?

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

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

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

create table 表名(列名 类型 primary key);

• primary key 相当于not null和 unique结合的效果 .
• 在实际开发当中, 每个表通常都会设置一个主键 , 但允许多个列放到一起共同作为一个主键(联合主键)

• 案例

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

mysql> create table user(id int primary key,name varchar(20));
Query OK, 0 rows affected (0.05 sec)

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
# 当插入两条主键相同的数据时,
mysql> insert into user values(12,'张'),(12,'李');
ERROR 1062 (23000): Duplicate entry '12' for key 'PRIMARY'

设置自增主键

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

create table 表名(列名 类型 primary key auto_increment, ...);

• 案例

#将user表中的主键id 设置为自增主键
mysql> create table user(id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.05 sec)

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

mysql> insert into user values(null,'李'),(null,'张'),(null,'王');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
# 自增主键设置成功
mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | 李   |
|  2 | 张   |
|  3 | 王   |
+----+------+
3 rows in set (0.00 sec)

# id列的null并不是将id设置为null, 而是将id的赋值交给数据库来使用自增主键

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

6 . 外键约束(foreign key)

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

create table 表名(
      字段名 数据类型,
      foreign key(本表字段名) renferences 主表名(主键名或unique字段名)
)

alter table 添加外键的表名 add constraint 外键名称 foreign key(从表字段名) renferences 主表名(主表字段名)

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

• 外键约束演示

# 创建主班级表
mysql> create table class(id int primary key comment'班级编号',
                          name varchar(10) not null);
Query OK, 0 rows affected (0.02 sec)

# 创建学生表并建立外键约束
mysql> create table student(id int primary key comment '学生编号',
                            name varchar(20) not null,
                            class_id int, -- 学生所在的班级编号
                            foreign key(class_id) references class(id));
Query OK, 0 rows affected (0.01 sec)

# 主表副表结构
mysql> desc class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(10) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (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    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

#班级表中插入数据
mysql> INSERT INTO class values(100,'java'),
                               (200,'web');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from class;
+-----+------+
| id  | name |
+-----+------+
| 100 | java |
| 200 | web  |
+-----+------+

#学生表插入数据
mysql> insert into student values(1,'tom',100);
Query OK, 1 row affected (0.02 sec)

mysql> insert into student values(2,'jack',200);
Query OK, 1 row affected (0.02 sec)

#添加失败,300号班级在class表中不存在,由于外键约束的作用,插入失败
mysql> insert into student values(3,'frx',300);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mytestdb`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

• 删除更新行为

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

• 格式

alter table 子表 add constraint 外键名 foreign key (字段名) references 父表(字段名) on update 行为 on delete 行为;

• 示例

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

#设置 student 表中的外键
mysql> alter table student add constraint class_id foreign key(class_id) references class(id) on update set null on delete set null;

Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

#更新父表中的 id 
mysql> update class set id = 300 where name = 'web';

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#查询子表中的全部信息
mysql> select * from student;
+----+------+----------+
| id | name | class_id |
+----+------+----------+
|  1 | tom  |      100 |
|  2 | jack |     NULL |
+----+------+----------+
2 rows in set (0.00 sec)

• 删除外键约束

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

ALTER TABLE table_name DROP CONSTRAINT fk_name;

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

ALTER TABLE table_name  DROP FOREIGN KEY fk_name;

示例:

-#删除外键约束
mysql> alter table student drop foreign key class_id;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

#删除外键约束后,主表与从表再无关联,成功插入
mysql> insert into student values(100,'张',550);
Query OK, 1 row affected (0.00 se

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

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

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

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

---

[mysql]数据库的约束与表的设计(代码片段)

专栏简介:MySql数据库从入门到进阶.题目来源:leetcode,牛客,剑指offer.创作目标:记录学习MySql学习历程希望在提升自己的同时,帮助他人,,与大家一起共同进步,互相成长.学历代表过去,能力代表现在,学习能力代表未来! 文章目录1.数据... 查看详情

三战mysql数据库终极篇(代码片段)

...：0.准备工作：1.表结构修改：(关键字alter)2.约束条件：(1)默认约束(default):(2)非空约束(notnull):(3)唯一约束(uniquekey)：(4)主键约束(primarykey):(5)自增长约束(auto_increment):(6)外键约束(foreignk 查看详情

三战mysql数据库终极篇(代码片段)

...：0.准备工作：1.表结构修改：(关键字alter)2.约束条件：(1)默认约束(default):(2)非空约束(notnull):(3)唯一约束(uniquekey)：(4)主键约束(primarykey):(5)自增长约束(auto_increment):(6)外键约束(foreignk 查看详情

三战mysql数据库终极篇(代码片段)

...：0.准备工作：1.表结构修改：(关键字alter)2.约束条件：(1)默认约束(default):(2)非空约束(notnull):(3)唯一约束(uniquekey)：(4)主键约束(primarykey):(5)自增长约束(auto_increment):(6)外键约束(foreignk 查看详情

！(代码片段)

...理(必看)【介绍】什么是数据库MySQL数据库RDBMS术语数据库表的存储位置【数据类型】一.整数类型二.小数类型三.字符串四.日期类型五.复合类型六.二进制类型【约束】一.非空约束(notnull)二.唯一性约束(unique)三.主键约束(primarykey)P... 查看详情

mysql表的约束(代码片段)

文章目录空属性默认值列描述zerofill主键自增长唯一键外键真正约束字段的是数据类型，但是数据类型约束很单一，有时需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。比... 查看详情

『mysql篇』：mysql表的curd操作(代码片段)

📢MySQL系列专栏持续更新中…MySQL专栏​目录目录一、SQL语句-SQL通用语法-注释-SQL语句分类二、基础表操作-创建表-查看库中的表-查看表结构-删除表-重命名表三、MySQL中的增删查改操作-增加（insert语句）-查询(select语... 查看详情

mysql表的约束和表的增删查改(代码片段)

目录一.表的约束    1.1空属性     1.2默认值     1.3列描述     1.4zerofill    1.5主键     1.6自增长     1.7唯一键     1.8外键二.表的增删查改    2.1Create(创建)    2.2Retrieve(读取)     2.2.1全列查询    2.2.... 查看详情

4.mysql表的约束(代码片段)

文章目录Mysql表的约束空属性默认值default列描述commentzerofill主键primarykey主键的删除添加主键复合主键自增长auto_increment唯一键unique外键freignkey…references….Mysql表的约束mysql为上面要有约束？约束本质是MySQL通过限制用户操作... 查看详情

4.mysql表的约束(代码片段)

文章目录Mysql表的约束空属性默认值default列描述commentzerofill主键primarykey主键的删除添加主键复合主键自增长auto_increment唯一键unique外键freignkey…references….Mysql表的约束mysql为上面要有约束？约束本质是MySQL通过限制用户操作... 查看详情

4.mysql表的约束(代码片段)

文章目录Mysql表的约束空属性默认值default列描述commentzerofill主键primarykey主键的删除添加主键复合主键自增长auto_increment唯一键unique外键freignkey…references….Mysql表的约束mysql为上面要有约束？约束本质是MySQL通过限制用户操作... 查看详情

mysql表的约束(代码片段)

文章目录表的约束空属性默认值列描述zerofill主键自增长唯一键外键表的约束真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据... 查看详情

038_mysql表的操作(代码片段)

...类型的数据。表中的一条记录有对应的标题，标题称之为表的字段。　　二，创建表1，创建表createtable表名(字段名1类型[(长度，小数点)约束条件],字段名2类型[(长度，小数点)约束条件],字段名3类型[(长度，小数点)约束条件])engin... 查看详情

mysql(代码片段)

MySQL（三）mysql数据管理1.外键--学生表的gradeid想要引用年纪表的gradeid，就要定义外键key，创建外键约束，执行引用--1.创建表的时候加外键约束key'FK_gradeid'('gradeid'),constraint'FK_gradeid'foreig 查看详情

mysql外键约束-foreignkey(代码片段)

...键约束: 关键字  foreignkey外键：外面的键，一张表的一个字段(非主键)指向另外一个表的主键,那么该字段就称之为外键。外键所在的表称之为子表(附表)； 外键所指向的主键所在的表称之为父表(主表)二、如何加外... 查看详情

mysql初级篇——视图的相关概念及应用举例(代码片段)

...据库对象2.什么是视图？视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊... 查看详情

mysql数据库约束，表的设计(代码片段)

...束notnulluniquedefaultprimarykey主键auto_incrementforeignkey外键check表的设计一对一一对多多对多多对多建表实例插入数据到实例实现多对多数据库约束notnull指定某列的存储不能为null值createtablestudent(idintnotnull,namevarchar(20));QueryOK,0rowsaffected(... 查看详情

mysql表的约束(代码片段)

文章目录MySQL表的约束空属性默认值列描述zerofill主键自增长唯一键外键综合案例MySQL表的约束真正约束字段的是数据类型，如果插入的数据超出了对应数据类型的取值范围，那么数据将会插入失败。但是数据类型的约束... 查看详情