MySQL约束

约束介绍

概念：

约束英文：constraint
约束实际上就是表中数据的限制条件

作用：

表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性，比如用户表有些列的值（手机号）不能为空，有些列的值（身份证号）不能重复。

分类：

1. 主键约束(primary key)PK
2. 自增长约束(auto increment)
3. 非空约束(not null)
4. 唯一性约束(unique)
5. 默认约束(default)
6. 零填充约束(zerofill)
7. 外键约束(foreign key)FK

一. 主键约束

概念：

1. MySQL主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行,方便在RDBMS中尽快的找到某一行。
2. 主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。
3. 每个表最多只允许一个主键。
4. 主键约束的关键字是：primary key。
5. 当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

操作：

· 添加单列主键
· 添加多列联合主键
· 删除主键

1.1 添加单列主键

  创建单列主键有两种方式，一种是在定义字段的同时指定主键，一种是定义完字段之后指定主键。

语法格式：

方式1：在create table 语句中，通过primary key关键字来指定主键。
在定义字段的同时指定主键

Create table tableName(
	…
	<colName> <dataType> primary key
	…
);

举例：

create table emp1(
	eid int primary key,
	name varchar(20),
	deptId int,
	salary double
);

方式2：在定义字段后在指定主键

Create table tableName(
	…
	[constraint <约束名>] primary key [字段名]
);

举例：

create table emo2(
	eid int,
	name varchar(20),
	deptId int,
	salary double,
	constraint pk1 primary key(eid)
);

1.2添加多列主键(联合主键)

  所谓的联合主键，就是这个主键是由一张表中多个字段组成的。
注意：

1. 当主键是由多个字段组成时，不能直接在字段名后面声明主键约束。
2. 一张表只能由一个主键，联合主键也是一个主键。

语法格式：

Create table tableName(
	…
Primary key(colName1,colName2,colName3,...,colNamen)
…
);

举例：

create table emp3( 
  name varchar(20), 
  deptId int, 
  salary double, 
  primary key(name,deptId) 
);

1.3通过修改表结构添加主键

主键约束不仅可以在创建表的同时创建，也可以在修改表时添加。

语法格式：

Alter table <tableName> add primary key(colName);

举例：

create table emp4(
	eid int,
	name varchar(20),
	deptId int,
	salary double
);
alter table emp4 add primary key(eid);

1.4删除主键约束

  一个表中不需要主键约束时，就需要从表中将其删除。删除主键约束的方法要比创建主键约束容易的多。

语法格式：

Alter table tableName emp1 drop primary key;

实现：删除单列主键

Alter table emp1 drop primary key;

删除联合主键

Alter table emp3 drop primary key;

二． 自增长约束

概念：

在MySQl中，当主键定义为自增长后，这个主键的值就不需要用户输入数据了，而由数据库根据定义自动赋值了，每增加一条记录，主键会自动以相同的步数增长。
我们通过给字段添加auto_increment属性来实现主键自增长。

语法：

 colName dataType auto_increment;

操作：

Create table t_user1(
	id int primary key auto_increment,
	name varchar(20)
);

特点：

· 默认情况下，auto_increment的初始值是1，每新增一条记录，字段值自动加1。
· 每个表中只能有一个字段使用auto_increment约束，且该字段必须有唯一索引，以避免重复(即主键或主键的一部分)。
· auto_increment约束字段必须具备NOT NULL属性。
· auto_increment约束字段只能是整数类型(int,tinyint,smallint,bigint)。
· auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上线，auto_increment就会失效。

2.1 指定自增字段初始值

  如果我们设置该字段的初始值，那么新增的记录就会从这个初始值开始自增。

方式1：创建表时指定

Create table t_user2(
	id int primary key auto_increment,
	name varchar(20)
)auto_increment=100;

我们插入两个数据查看一下

use mydb1;
insert into t_user2(name) values('张三');
insert into t_user2(name) values('李四');

可以看到，我们并没有对id进行赋值，系统自动从100开始自增赋值。

方式2：创建表后指定

create table t_user3(
	id int primary key auto_increment,
	name varchar(20)
);

alter table t_user3 auto_increment=100;

其结果和方法1一样。

2.2 delete和truncate在删除后自增列的变化

delete删除数据后自动增长从断点开始
truncate删除数据后自动增长从默认起始值开始

举例：
Delete

insert into t_user2(name) values('张三');
insert into t_user2(name) values('李四');
delete from t_user2 where id = 100;
insert into t_user2(name) values('张三');

我们可以发现，他是从101开始自增的。
继上面的数据我们继续
Truncate

truncate table t_user2;
insert into t_user2(name) values('张三');
insert into t_user2(name) values('李四');

我们可以看到它的id从默认起始值1开始了。
由此可知 delete删除后，是从上次自增后的最大值开始，而truncata则是从1开始。

三. 非空约束

概念：
  MySQL非空约束(NOT NULL)是指字段的值不能为空，对使用了非空约束的字段，如果用户再给他们赋值为空时，即没有指定的数据时，数据库系统就会报错。

语法：

方式1： not null;
方式2：alter table tableName modify column dateType not null;

3.1添加非空约束

举例：
– 方式1,创建表时的指定

create table t_user6(
	id int,
	name varchar(20) not null,
	address varchar(20) not null
);

– 方式2

create table t_user7(
	id int,
	name varchar(20),
	address varchar(20)
);
alter table t_user7 modify name varchar(20) not null;
alter table t_user7 modify address varchar(20) not null;

3.2删除非空约束

语法格式：

Alter table tableName modify colName dateType;

举例：

alter table t_user7 modify name varchar(20);
alter table t_user7 modify address varchar(20);

四.唯一约束

  唯一约束(Unique Key)是指所有记录中字段的值不能重复出现。例如，为字段id加上唯一约束后，每条记录的id值都是唯一的，不能重复出现。

语法格式：
方式1：

<colName> <dateType> unique;

方式2：

Alter table tableName add constraint 约束名 unique(列);

举例：

– 1.创建表时指定

create table t_user8(
	id int,
	name varchar(20),
	phone_number varchar(20) unique -- 指定唯一约束
);

insert into t_user8 values(1001, '12345678');
insert into t_user8 values(1002, '12345678');

第二次插入的时候，数据库系统报错了，因为第二次的phone_number数据和第一个一样。

– 2：创建表后指定

create table t_user9(
	id int,
	name varchar(20),
	phone_number varchar(20)
);

alter table t_user9 add constraint unique_k unique(phone_number); 

删除唯一约束：

Alter table <tableName> drop index <唯一约束名>;

五.默认约束

概念：
MySQL默认值的约束用来指定某列的默认值。

语法格式：

方式1：<字段名> <数据类型> default <默认值>;
方式2：alter table 表名 modify 列名 类型 default 默认值;

举例：
– 创建表时进行默认值约束

create table t_user10(
	id int,
	name varchar(20),
	address varchar(20) default '北京'
);

插入两个数据:

insert into t_user10(id, name) value(1, '张三');
insert into t_user10(id, name) value(1, '李四');

用alter指定列进行默认约束

create table t_user11(
	id int,
	name varchar(20),
	address varchar(20)
);
alter table t_user11 modify address varchar(20) default '北京';

5.1 删除约束：

语法公式：

Alert table <表名> modify column <字段名> <类型> default null;

举例：

Alter table t_user11 modify column address varchar(20) default null;

六.零填充约束

概念：

1. 插入数据时，当该字段插入的值长度小于给定类型的长度，会在该值的前面补上相应的0。
2. zerofill默认为int(10)
3. 当使用zerofill时，默认会自动加unsigned(无符号)属性，使用unsigned属性后，数值范围是原来的两倍，例如，有符号-128 ~ +127，无符号为0 ~ 256。

操作：

create table t_user12 ( 
  id int zerofill , -- 零填充约束
  name varchar(20)   
);

删除：

alter table t_user12 modify id int;