一、约束概述
1、为什么需要约束:为了保证数据的完整性
2、什么是约束:对表中字段的限制
3、约束的分类
角度1:约束字段个数
单列约束和多列约束
角度2:约束的作用范围
列级约束和表级约束
角度3:约束作用(功能)
- not null非空约束
- unique唯一性约束
- foreign key 外键约束
- primary key 主键约束
- check 检查约束
- default 默认值约束
4、如何添加约束
CREATE TABLE时添加约束
AlTER TABLE时添加 、删除约束
5、如何查看表中的约束
select * from information_schema.table_constraints where table_name = '表名称'
二、非空约束
用于约束某列的值不能为空
关键字 NOT NULL
创建表时添加约束
//创建时添加
create table test{
id int not null,
title varchar(23) not null
}
//alter时添加 如果原有数据已经有null的 执行不成功
alter table type modify name varchar(25) not null;
后续不管insert还是update数据,title字段都不能为null
三、唯一性约束
用于约束某列的值唯一
关键字 unique
创建表时添加约束
create table test1(
#列级约束
id INT unique,
last_name VARCHAR(15),
email VARCHAR(25),
salary DECIMAL(10,2),
#表级约束
constraint uk_test_email unique(email)
);
//alter时候创建
方式1
alter table test add constraint uk_test_email unique(email)
方式2
alter table test modify 字段名 字段类型 unique
多个字段创建唯一约束
create table test1(
#列级约束
id INT unique,
last_name VARCHAR(15),
email VARCHAR(25),
salary DECIMAL(10,2),
#表级约束
constraint uk_test_email unique(email,last_name)
);
特点:
- 唯一约束 允许多个值为NULL
- 创建唯一约束,不给约束名,默认和列名相同
- mysql会给唯一约束默认创建一个唯一索引
四、主键约束
用于约束行记录的唯一性,相当于唯一约束+非空约束组合
关键字 primary key
特点:
- 一个表最多只能有一个主键约束,建立主键约束可以在列上创建,也可以在表级上创建
- 主键约束对应表中一个或者多个列(复合主键)
- 如果是多列组合的复合主键,这几列都不能为null,并且组合的值不能重复
- mysql的主键名总是primary 就算自己命名了也没用
- 创建主键约束时默认会创建主键索引,删除主键约束同时会删除主键索引
创建表时添加约束
复合主键
create table test4(
id INT unique,
last_name VARCHAR(15),
email VARCHAR(25),
salary DECIMAL(10,2),
primary key(id,email)
);
alter创建或者删除
alter table 表名称 add primary key(字段名);//添加主键
alter table 表名称 drop primary key;//删除主键
五、自增列 auto_increment
某个字段的值自增
特点:
(1)表最多只能有一个自增长列
(2) 当需要产生唯一标识符或顺序值时,可设置自增长
(3)自增长列约束的列必须是键列(主键列,唯一键列)
(4)自增约束的列的数据类型必须是整数类型
(5)自增列值为0和 null,会在当前最大值的基础上自增,如果自增列手动指定了具体值,直接赋值为具本值
自增值存储说明
1.1 MyISAM 引擎的自增值保存在数据文件中。
1.2 InnoDB 引擎的自增值,其实是保存在了内存里,并且到了 MySQL 8.0 版本后,才有了“自增值持久化”的能力,也就是才实现了“如果发生重启,表的自增值可以恢复为 MySQL 重启前的值”,具体情况是:
- 在 MySQL 5.7 及之前的版本,自增值保存在内存里。每次重启后,第一次打开表的时候,都会去找自增值的最大值 max(id),然后将 max(id) + 1 作为这个表当前的自增值。
- 在 MySQL 8.0 版本,将自增值的变更记录在了 redo log 中,重启的时候依靠 redo log 恢复重启之前的值。
六、外键约束
限制某个表某个字段引用的完整性
关键字
foreign key
特点:
说明:
在 MySOL 里,外键约束是有成本的,需要消耗系统资源。对于大并发的 SOL 操作,有可能会不适合。比如大型网站的中央数据库,可能会 因为外键约束的系统开销而变得非常慢 。所以,MySQL 允许你不使用系统自带的外键约束,在 应用层面 完成检查数据一致性的逻辑。也就是说,即使你不用外键约束,也要想办法通过应用层面的附加逻辑,来实现外键约束的功能,确保数据的一致性。
七、check约束
限制某个值是否满足某个要求、一般指范围
关键字 check
mysql5.7不支持check约束 ,可以添加check约束,但是没有作用,但是8.0可以使用
八、DEFAULT 约束
指定某字段/列默认值
关键字 DEFAULT
创建或者alter
CREATE TABLE test(
id int,
name varchar(25),
salary DECIMAL(10,2) DEFAULT 2000
);
ALTER TABLE test12
MODIFY salary DECIMAL(8,2) DEFAULT 2500;
九、备注
1、为什么建表时,加 not null default"或 default 0
答: 不想让表中出现null值。
2、为什么不想要 null 的值
答: (1)不好比较。nul是一种特殊值,比较时只能用专门的is null 和 s not null来比较。碰到运算符,通常返null。
(2)效率不高。影响提高索引效果。因此,我们往往在建表时 not null default"或 default 0
3、带AUTO_INCREMENT约束的字段值是从1开始的吗?在MySQL中,默认AUTO_INCREMENT的初始值是1,每新增一条记录,字段值自动加1。设置自增属性(AUTO_INCREMENT)的时候,还可以指定第一条插入记录的自增字段的值,这样新插入的记录的自增字段值从初始值开始递增,如在表中插入第一条记录,同时指定id值为5,则以后插入的记录的id值就会从6开始往上增加。添加主键约束时,往往需要设置字段自动增加属性。
4、并不是每个表都可以任意选择存储引擎?
外键约束 (FOREIGN KEY) 不能跨引擎使用。
MySOL支持多种存储引擎,每一个表都可以指定一个不同的存储引擎,需要注意的是:外键约束是用来保证数据的参照完整性的,如果表之间需要关联外键,却指定了不同的存储引擎,那么这些表之间是不能创建外键约束的。所以说,存储引擎的选择也不完全是随意的。