1.6.
表的约束
1.6.1.
概念
1:为什么需要约束
真正约束字段的是数据类型
,
但是数据类型约束很单一
,
需要有一些额外的约束
,
更好的保证数据
的合法性,从业务逻辑角度保证数据的正确性,如:有一个字段是身份证,
要求是唯一的
2:定义字段的类型无法满足对表的约束:
表字段是否可以为
NULL,
有没有默认值
,
表字段的解释能不能加上
对于数字类型的字段可不可以指定默认表示的位数,可不可以将这个字段设置称为唯一标识该
行的数据
3:本质上
Mysql
是一套数据存储解决方案
,
除了解决基本的数据存储功能之外
,
还要保证数据尽可能
的安全,减少用户的误操作可能性
,
约束的体现
,
不仅仅可以体现在数据库层面
,
在我们进行用户业务
逻辑编码的时候,
我们其实也可以做判断
(
约束
),
约束其实不仅仅只能出现在数据库层而上
,
编码层面
也是可以体现
4:约束是什么:约束是在表上强制执行的数据校验规则,本质上是
Mysql
通过限制用户操作的方
式
,
来达到维护数据本身安全及数据完整性的一套方案
5:数据的完整性要从以下四个方面考虑:
实体完整性(Entity Integrity)
:例如,同一个表中,不能存在两条完全相同无法区分的记录
域完整性(Domain Integrity)
:例如:年龄范围
0-120
,性别范围
“
男
/
女
”
引用完整性(Referential Integrity)
:例如:员工所在部门,在部门表中要能找到这个部门
用户自定义完整性(User-defined Integrity)
:例如:用户名唯一、密码不能为空等,本部门经理
的工资不得高于本部门职工的平均工资的
5
倍。
6:约束作为数据库对象,存放在系统表中,也有自己的名字
7:常见约束:
非空约束 、默认值约束 、主键约束 、外键约束 、唯一约束 、检查约束
8:创建约束的时机:
在建表的同时创建
建表后创建(修改表)
9:约束可定义列级或表级约束
1.6.2.
语法
列级约束:在定义列的同时定义约束
create table 表名 (列名 类型 约束 )
表级约束:在定义了所有列之后定义的约束
create table 表名 constraint 约束名 约束类型 (列信息)
创建表之后添加约束:
alter table 表名 add constraint 约束名 约束类型(要约束的列名)
约束名推荐采用:表名
_
列名
_
约束类型简介
1.6.3.
非空约束
作用:限定某个字段
/
某列的值不允许为空
两个值:
null
(默认的)和
not null(
不为空
)
列字段默认一般都是空
,
但是实际开发时
,
尽可能保证字段不为空
,
因为数据为空没办法参与运算
所有数据类型的值都可以是
NULL
,空字符串不等于
NULL
,
0
也不等于
NULL
示例:
![](https://i-blog.csdnimg.cn/direct/c8e2bf7357794cae8ce57eba54955450.png)
若需要某些字段必须写入,应该怎么处理,如:创建一个班级表,包含班级名和班级所在的教室,
站在正 常的业务逻辑中:如果班级没有名字,你不知道你在哪个班级,如果教室名字可以空,就
不知道在哪上课,所以我们在设计数据库表的时候,一定要在表中进行限制,满足上面条件的数据
就不能插入到表中,这就是“约束”
以上为创建表时的设置,也可以创建之后修改:
#
语法:
alter table
<
数据表名
>
change column
<
字段名
> <
字段名
> <
数据类型
>
not
null
;
mysql>
alter table
student1
change column
id id
int
not
null
;
#
也可以通过
modify
修改
mysql>
alter table
student1
modify
name
varchar
(
30
)
not
null
;
# modify
字句中省略
not null
相当于设置为可以为空
mysql>
desc
student1;
![](https://i-blog.csdnimg.cn/direct/459f2741948241d6a09f407487ee2a21.png)
删除not null
![](https://i-blog.csdnimg.cn/direct/e8f3a532eef642588d59baa5b6e251cd.png)
1.6.4.
默认值约束
作用:给某个字段
/
某列指定默认值,一旦设置默认值,在插入数据时,如果此字段没有显式赋
值,则赋值为默认值。
![](https://i-blog.csdnimg.cn/direct/7c46ce3872f141bca9a1119054236018.png)
![](https://i-blog.csdnimg.cn/direct/0756e4f5239b4bf38bd53b17a72dad03.png)
![](https://i-blog.csdnimg.cn/direct/2389a6d59a53483bb03ca443df326809.png)
默认值的生效:数据在插入的时候不给该字段赋值,就使用默认值
注意:若某字段都设置了
not null
和
default
约束,则按照不插入时会选择默认值,插入时选择插入
值,所以此时not null
毫无意义
![](https://i-blog.csdnimg.cn/direct/6a6f4e0a66364995bf7076bc1fa68571.png)
# not null
和
defalut
一般不需要同时出现
,
因为
default
本身有默认值
,
不会为空
1.6.5.
列描述
--comment
列描述没有实际含义
,
专门用来描述字段
,
会根据表创建语句保存
,
供操作者来查看该列的含义,相当
于 C/C++的注释
![](https://i-blog.csdnimg.cn/direct/01e060e55bce4266bd797913761940f2.png)
1.6.6.
主键约束
1:作用:数据库存储数据不是唯一的目的
,
还要考虑到提取数据,一般要求数据库表中的记录要有一
个特定的唯一标识,来表示唯一性,
这个唯一的特定标识就是主键
,
类似于序号学号这样的唯一标识
,
可以根据主键来唯一地筛选出一条记录
2:主键:primary key,用来唯一的约束该字段里面的数据
3:特点:
主键字段不能为空,不能重复
一张表中最多只能有一个主键
主键所在的列通常是整数类型
主键约束是最频繁的约束
4:注意:当创建主键约束时,系统默认会在所在的列或列组合上建立对应的主键索引(能够根据主
键查询的,就根据主键查询,效率更高)。如果删除主键约束了,主键约束对应的索引就自动删除
了,需要注意的一点是,不要修改主键字段的值。因为主键是数据记录的唯一标识,如果修改了主
键的 值,就有可能会破坏数据的完整性。
示例: 创建时增加主键
mysql>
create table
t1 ( id
int unsigned
primary key comment
'
学号不能为空
'
, name
varchar
(
20
)
not
null
,sex
char
(
2
)
default
'
男
'
);
![](https://i-blog.csdnimg.cn/direct/5d3b0771b3a74cb6b3c8c073016504d5.png)
mysql>
insert into
t1(id,name)
values
(
1
,
'
黎明
'
);
ERROR
1062
(
23000
): Duplicate entry
'1'
for key
't1.PRIMARY'
#
再次插入出错,因为需要保持唯一性
示例:修改表,追加主键
![](https://i-blog.csdnimg.cn/direct/28bea02d3cd84f23a35cef5b22dfd570.png)
注意
:
如果该字段内有重复值
,
则不能以该字段新增为主键
,
必须先把重复的数据去掉然后才能添加该
列为主键
删除主键:alter table 表名 drop primary key;
复合主键:在创建表的时候
,
在所有字段之后
,
使用
primary key(
主键字段列表
)
来创建主键
,
如果有多
个字段 作为主键,
可以使用复合主键
,
这些字段合在一起是一个主键,也就是让多个字段联合在一起
作为唯一标识
, 单个字段主键重复是没有问题的,
只要不是成为复合主键的字段一起冲突就行
![](https://i-blog.csdnimg.cn/direct/754e5a073a034535920bbea9e2f8ad57.png)
1.6.7.
主键自增长
作用:给主键添加自增长的数值
auto_increment:
当对应的字段,不给值,会自动的被系统触发,系统会从当前字段中已经有的
最大值+1 操作,得到一个新的不同的值。通常和主键搭配使用,作为逻辑主键
注意:
1:自增长字段必须是整数
,
自增长字段可以不设置初始值
,
默认从
1
开始递增
.
2:被自增长的字段必须作为主键或者其他具有唯一性的键使用
,(
必须保证这一列字段具有唯一性的
字段)
3:自增长字段也可以插入数据
,
只要不与已有数据重复即可
,
之后新增数据会从最大值开始递增
.
4:任何一个字段要做自增长
,
前提是本身是一个索引(
key
一栏有值)
.
5:一张表当中最多只能有一个自增长的列
6:约束的字段必须具备
NOT NULL
属性
示例
![](https://i-blog.csdnimg.cn/direct/9fd30636fbfb4b4a9e9e224753b469f4.png)
mysql>
insert into
student1(name)
values
(
'
关六
'
);
Query
OK,
1
row
affected (
0.00
sec)
mysql>
select
*
from
student1;
#
发现自动增长
指定自增长的起始值:如果第一条记录设置了该字段的初始值,那么新增加的记录就从这个初始值
开始自 增。例如,如果表中插入的第一条记录的 id
值设置为
5
,那么再插入记录时,
id
值就会从
5
开始往上增加
![](https://i-blog.csdnimg.cn/direct/1a990f52b8cc401092dee5f54edb315e.png)
![](https://i-blog.csdnimg.cn/direct/7ee540a75b1d43f79d42179925ae2195.png)
1.6.8.
唯一性约束
1:产生原因:一张表中有往往有很多字段需要唯一性
,
数据不能重复
,
但是一张表中只能有一个主键
,
唯一键就可以解决表中有多个字段需要唯一性约束的问题
2:作用:限制某个字段
/
某列的值不能重复
3:唯一键和主键的关系:
1):主键更多的是标识唯一性
2):唯一键更多的是保证在业务上
,
不要和别的信息出现重复
3):主键只能有一个
,
唯一键能设置多个
4):主键和唯一键不是对立关系而是补充关系
,
目的是让数据库创建出一个表具有非常强的约束
,
彻底
杜绝用户的误插入,在一张表当中,可以有一个主键,
也可以同时具有唯一键
,
它们不是用来对比的
,
只是负责数据在不同层面的唯一性,既要保证选择成为主键的属性字段不能重复不能为空,
同时也要
保证本身具有唯一性的列的字段不能出现冲突
5):主键用来查找
,
唯一键用来保证数据的完整性
6):如:学生信息中有身份证号和学号两个字段,可以把身份证号设置成为主键
,
而所有学生的学号
也不能重复,此时就可以把学号设置成唯一键
示例:创建表时实现
![](https://i-blog.csdnimg.cn/direct/1b7b6ca2640148e1ba468a708d983ccf.png)
![](https://i-blog.csdnimg.cn/direct/d96c7151e1bc4ff194be9caebd5e60a1.png)
唯一键冲突,自增涨字段值不连续
![](https://i-blog.csdnimg.cn/direct/113857da236844ccb1711c04401ce969.png)
1.6.9.
外键约束
1:作用:限定某个表的某个字段的引用完整性
2:概念
主表(父表):被引用的表、被参考的表
从表(子表):引用别人的表,参考别人的表
外键:从表中的某个字段引用自主表的某个字段或多个字段
引用键:主表被引用的字段
3:外键约束主要定义在从表上
,
主表则必须是有主键约束或唯一键约束
.
当定义外键后
,
要求外键列
数据必须在
主表的主键列存在或为
null.
格式:foreign key (从表的字段名称) references 主表名字(主表的字段名称) # 建立外键关联
示例:
![](https://i-blog.csdnimg.cn/direct/539e296bd20d440896473821177a16d3.png)
![](https://i-blog.csdnimg.cn/direct/1d31eb6432a3463ab645490e7bc1f415.png)
示例:
![](https://i-blog.csdnimg.cn/direct/6aedb0b2f3d3429d847562f995be5ecd.png)
![](https://i-blog.csdnimg.cn/direct/852bf5e172bd4668a88dbccab277406d.png)
![](https://i-blog.csdnimg.cn/direct/935d39d4a7604ab5b5237c434e12d4d4.png)
![](https://i-blog.csdnimg.cn/direct/e41d25a87bf54d0d826ba48b26990a6c.png)
1.6.10.
检查约束
1:作用:检查约束(
check
)是用来检查数据表中字段值有效性的一种手段,一般用于设置值的
范围
2:注意
设置检查约束时要根据实际情况进行设置,这样能够减少无效数据的输入
在默认值和非空约束可看作是特殊的检查约束
注意检查约束在
8.0.16
之前,
MySQL
默认但不会强制的遵循
check
约束(写不报错,但是不生效,
需要通触发器完成),8
之后就开始正式支持这个约束了
示例1:创建表时设置检查约束
![](https://i-blog.csdnimg.cn/direct/e04383a8d3014aefb125540d53303a24.png)
示例2:修改表时添加检查约束
#
格式:
alter table
表名
add constraint
<
检查约束名
>
check
(
检查约束
)
mysql>
alter table
t5
add constraint
check_id
check
(id>
0
);
Query
OK,
1
row
affected (
0.02
sec)
Records:
1
Duplicates:
0
Warnings
:
0
1.6.11.
删除表的约束:
![](https://i-blog.csdnimg.cn/direct/bf026540ea3448b6bfbc196fec2cc619.png)
1.6.12.
存储引擎
![](https://i-blog.csdnimg.cn/direct/58ab5932cbb74cb991b4018f9264bd32.png)
![](https://i-blog.csdnimg.cn/direct/afeef8ed98bc4397801b35a6688eae5a.png)
默认情况下, 创建表不指定表的存储引擎, 则会使用配置文件的
my.ini
中
default-storage-
engine=InnoDB
指定的
InnoDB
查看支持的引擎命令:
show engines
\g
查看当前默认存储引擎
![](https://i-blog.csdnimg.cn/direct/fdd9868dc6034a178c9345c93d8b02e3.png)
在创建表时, 可以指定表的存储引擎:
create table
(...)
engine
=
InnoDB
;
1.6.13.
总结
-
完整的建表语句:
![](https://i-blog.csdnimg.cn/direct/8f8005d905d44581b3fa4b89cfac8439.png)
1.6.14.
练习
创建销售数据库
mydb5_sales
使用该数据库
创建产品表
pro_tb (
产品编号
pro_num int(10)
唯一键
,产品名
pro_name varchar(30)
,产品单价
pro_price float(5,2) ,
产品规格
pro_spe varchar
(
8
) 默认值:件
)
创建订单表
orders (
订单号
id int(6) ,
客户号
customer_id int(4),
产品编号
pro_num int(10)
外键
,
签订
日期 signing_date date,金额
amount float(5,2) ) ,
其中订号单为主键、自增长,金额设置
check
(大
于
0
)
![](https://i-blog.csdnimg.cn/direct/6e449e38a33c449d91ad89441dca3c99.png)
![](https://i-blog.csdnimg.cn/direct/f70a9e7bf7e94f41b1072c573e5c136d.png)