【Mysql】约束

一、前言

1.1 约束概述

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。
它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

为了保证数据的完整性，SQL规范以约束的方式对 表数据进行额外的条件限制。从以下四个方面考虑：

• 实体完整性（Entity Integrity） ：例如，同一个表中，不能存在两条完全相同无法区分的记录
• 域完整性（Domain Integrity） ：例如：年龄范围0-120，性别范围“男/女”
• 引用完整性（Referential Integrity） ：例如：员工所在部门，在部门表中要能找到这个部门
• 用户自定义完整性（User-defined Integrity） ：例如：用户名唯一、密码不能为空等
   

约束是表级的强制规定。
可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后 （通过 ALTER TABLE）语句规定
约束。

 

1.2 约束的分类

1.根据约束数据列的限制，约束可分为：

• 单列约束：每个约束只约束一列
• 多列约束：每个约束可约束多列数据
   

2.根据约束的作用范围，约束可分为：

• 列级约束：只能作用在一个列上，跟在列的定义后面
• 表级约束：可以作用在多个列上，不与列一起，而是单独定义
   

3.根据约束起的作用，约束可分为：

• NOT NULL 非空约束，规定某个字段不能为空
• UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
• PRIMARY KEY 主键(非空且唯一)约束
• FOREIGN KEY 外键约束
• CHECK 检查约束
• DEFAULT 默认值约束

 

二、按约束的作用分类

添加约束时可以对约束进行命名，分别在建表时，和修改表时：

-- 创建表时：
create table table_01
id int,
content char(5),
[constraint 名字] unique (字段表));
-- 修改表时
alter table table_01
add [constraint 名字] unique (字段表));

Constraint关键字可以省略，不命名，默认字段名.
查看表中的约束：

select * from information_schema.table_constraints where table_name = 'table_02';

 

2.1 非空约束[NOT NULL]

作用：限制某个字段不能为空
 
说明:

• 1.同一个表中可以有多个非空约束
• 2.所有类型的值都可以为 NULL
• 3.非空约束只能作用在表中某个列上，不能组合非空
• 4.空字符串 “” 不等于 NULL，0也不等于 NULL

1.建表时(create table)添加非空约束：

create table table_01(
id int not null,
salary double);

2.建表后(alter table)添加非空约束:

alter table table_01
modify salary double not null; 

3.删除(alter table)非空约束:

alter table table_01
modify salary double;

 

2.2 唯一性约束[UNIQUE KEY]

作用：用来限制某个字段(列)的值不能重复
 
说明：

• 1.同一个表中可以有多个唯一约束
• 2.唯一性约束可以是某一个列的值唯一，也可以是多个列(2个或以上)的值唯一
• 3.唯一性约束列值可以为空，并且空值与空值之间不构成重复
• 4.创建唯一性约束时，如果没有给唯一性约束命名，则默认和列名相同
• 5.创建唯一性约束时，默认在列或者列组合创建对应的唯一索引

1.建表时(create table)添加唯一性约束：

create table table_01(
id int unique, // 列级
name char(5),
password char(10),
unique (name,password) // 表级
);

2.建表后(alter table)添加唯一性约束:

// 列级
alter table table_01
modify id int unique;
--
alter table table_01
add unique(id);

// 表级
alter table table_01
add unique (id1,id2,...);

3.删除(alter table)唯一性约束(删除索引的方式):

alter table table_01
drop index 索引名;

 

2.3 主键约束[PRIMARY KEY]

作用：用来唯一标识表中的一行记录，相当于 唯一性约束 + 非空约束.
 
说明:

• 1.一个表最多只能有一个主键约束，可以在列级别创建，也可以在表级别创建
• 2.表级的主键约束，任意一列都不能为空，并且这些列的组合不能重复
• 3.MySQL的主键名总是PRIMARY，命名也无法更改
• 4.创建主键约束时，默认在列或者列组合创建对应的主键索引

1.建表时(create table)添加主键约束:

// 列级
create table table_01(
name char(5) primary key,
password char(10));
// 表级
create table table_01(
name char(5),
password char(10),
primary key (name,password));

2.建表后(alter table)添加主键约束:

// 列级
alter table table_01
modify id int primary key;
--
alter table table_01
add primary key(id);
-- 表级
alter table table_01
add primary key (name,password);

3.删除(alter table)主键约束(主键索引):

alter table table_01
drop primary key;

 

2.4 外键约束[FOREIGN KEY]

作用：限制某个表中的某个字段的引用完整性，用于定义主表和从表之间的关系.
 
说明:

• 1.同一个表可以有多个外键约束
• 2.类型为 innodb的表，才支持外键
• 3.外键指向的表(主表)的字段，要求是 primary 或 unique
• 4.外键字段的类型要和主键字段的类型一致(长度可以不一样)
• 5.外键字段的值，必须在主表中依赖字段存在过，也可以为空(unique)
• 6.删除主表被依赖的记录时，需要先删除从表中依赖该记录的数据
• 7.删除表时，先删除从表(或外键约束)后，再删除主表

1.建表时(create table)添加外键约束:

create table table_02(
id int primary key,
content char(5),
t1_id int,
foreign key (t1_id) references table_01(t1_id));

2.建表后(alter table)添加外键约束:

alter table table_02
add foreign key (t1_id) references table_01(t1_id);

3.删除(alter table)外键索引(删除索引的方式):

alter table t01 
drop index 索引名;

 

2.5 检查约束[CHECH]

作用：检查某个字段的值是否符合xx要求，一般指的是值的范围(mysql5.7不支持)

create table table_01(
id int primary key,
sex char(2) check (sex	in ('max', 'woman')),
salary double check (salary >=10000 and salary <=20000));

 

2.6 自增长约束[AUTO_INCREMENT]

作用：某个字段值自增长
 
说明:

• 1.同一个表最多只有一个自增长列
• 2.自增长字段必须是有主键约束(primary key)或者唯一性约束(unique key)
• 3.自增长字段一般都为整数类型(几乎不用小数)
• 4.自增长默认从1开始，可以修改(一般不会对其修改)
• 5.添加值数据时如果没有填写自增长字段，或者指定 0或 null ,就会在原添加过的最大值的基础上自增1 对其设置值

1.建表时(create table)添加自增长约束:

create table t01(
id int primary key auto_increment,
content char(2));
--
create table t01(
id int primary,
number int unique auto_increment);

2.建表后(alter table)添加自增长约束:

alter table t01
modify number int auto_increment;

3.删除(alter table)自增长约束:

alter table t01
modify id int; // 修改字段的方式

4.修改(alter table)自增长的初始值:

alter table t01
auto_increment  = 100;

 

2.7 默认值约束[DEFAULT]

作用：给某个字段指定默认值
 
说明：

• 1.若没有显示赋值，则赋值为默认值
• 2.添加DEFAULT约束后，若没有显示赋值，则为默认值(可以显示赋值NULL)

1.建表时(create table)添加默认值约束:

create table t01(
id int primary key auto_increment,
content char(5) not null default '');

2.建表后(alter table)添加默认值约束:

alter table t01
modify content char(5) not null default ''; // 原有 not null约束不能省略，不然视为删除；在原有 default约束上添加 not null约束也一样

3.删除(alter table)默认值约束:

alter table t01
modify content char(5) not null; // 保留 not null约束