MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下产品.MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件.

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言.MySQL 软件采用了双授权政策,分为社区版和商业版,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择 MySQL 作为网站数据库.

由于其社区版的性能卓越,搭配 PHP 和 Apache 可组成良好的开发环境.

今天,尚硅谷的老师就来讲一讲MySQL的约束.

相关资料可访问尚硅谷下载：http://www.atguigu.com/opensource.shtml

什么是约束?

约束即不随意,有制约,管束.

数据完整性(Data Integrity)是指数据的精确性(Accuracy)和可靠性(Reliability).它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的.数据的完整性要从以下四个方面考虑：

l实体完整性(Entity Integrity)：例如,同一个表中,不能存在两条完全相同无法区分的记录

l域完整性(Domain Integrity)：例如：年龄范围0-120,性别范围“男/女”

l引用完整性(Referential Integrity)：例如：员工所在部门,在部门表中要能找到这个部门

l用户自定义完整性(User-defined Integrity)：例如：用户名唯一、密码不能为空等,本部门经理的工资不得高于本部门职工的平均工资的5倍.

约束的分类

为了达到这个目标,我们要对数据库表的设计增加约束,根据约束的作用范围分,主要是表级约束和列级约束.

l列级约束只能作用在一个列上,跟在列的定义后面定义,与列的定义用空格分隔

l表级约束可以作用在多个列上,不与列一起,而是单独定义,格式：[ CONSTRAINT <约束名> ] <约束类型>

根据约束的特点,分为几种：

l键约束：主键约束、外键约束、唯一键约束

lNot NULL约束：非空约束

lCheck约束：检查约束

lDefault约束：缺省约束

键约束之主键约束

1、主键：Primary key,简称PK,数据库主键作用保证实体的完整性,可以是一个列或多列的组合.

l主键约束相当于唯一约束+非空约束的组合,主键约束列不允许重复,也不允许出现空值,如果是多列组合的主键约束,那么这些列都不允许为空值,并且组合的值不允许重复.

l每个表最多只允许一个主键约束,建立主键约束可以在列级别创建,也可以在表级别上创建.

lMySQL的主键名总是PRIMARY.

l当创建主键约束时,MySQL默认会在所在的列和列组合上建立对应的主键索引.

l删除主键时,也会直接删除主键索引

键约束之唯一键约束

2、唯一键：Unique key,简称UK,

l同一个表可以有多个唯一约束.

l唯一约束可以是某一个列的值唯一,也可以多个列组合值的唯一.

l建立唯一约束可以在列级别创建,也可以在表级别上创建.

l在创建唯一约束的时候,如果不给唯一约束名称,会有默认名.

lMySQL会给唯一约束的列上默认创建一个唯一索引.

l删除唯一键只能通过删除唯一索引的方式删除

主键约束与唯一键约束的区别?

一个表只能有一个主键,可以有0~n个唯一键

主键必须非空,而唯一键可以为空

键约束之外键约束

3、外键：Foreign key,简称FK

l外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系.

l建立外键约束只能在表级别上创建.

l在创建外键约束时,如果不给外键约束名称,外键也有默认名,也可以指定外键名.

l当创建外键约束时,系统默认会在所在的列上建立对应的普通索引.

l删除外键时,关于外键列上的普通索引需要单独删除.

注意：

l外键是在从表中声明

l从表的外键列,在主表中引用的只能是主键或唯一键约束的列.

l从表的外键列与主表被参照的列名字可以不相同,但是数据类型必须一样

l从表的外键值必须"在主表中能找到"或者为空,从而约束了从表的外键值.

l当主表的记录被从表参照时,主表中被参考记录的删除和更新也会受到限制.

n(1)默认情况下,主表和从表是严格依赖关系RESTRICT.当主表的记录被从表参照时,主表的记录将不允许删除,如果要删除数据,需要先删除从表中依赖该记录的数据,然后才可以删除主表的数据.

n(2)但是有一种是级联删除：

nON DELETE SET NULL(级联置空)：当外键设置了SET NULL,当主表的相关记录删除时,从表对应的字段改为NULL.

nON DELETE CASCADE(级联删除)：当外键设置了CASCADE(级联),当主表的相关记录删除时,从表对应的行都删除了.

n对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式

例如：

在成绩表中有两个外键,学号外键参考学生表的学号,课程编号外键参考课程表.

例如：

请点击此处输入图片描述请点击此处输入图片描述

在t_emp员工表中也有两个外键,t_emp员工表的department_id(员工所属部门)参考t_dept部门表的did,t_emp员工表的manager_id(领导编号)的参考t_emp员工表的eid(员工编号).

非空约束

lNOT NULL 非空约束,规定某个字段不能为空

l非空约束只能出现在表对象的列上,即只能是列级约束.

检查约束

注意： MySQL不支持check约束,但可以使用check约束,而没有任何效果；

例如：age tinyint check(age >20) 或 sex char(2) check(sex in(‘男’,’女’))

Default缺省约束

default：默认值,在插入数据时某列如果没指定其他的值,那么会将默认值添加到所有的新记录.

约束的创建与删除

(一)建表时声明约束

#主键和唯一键可以使用列级约束声明方式,但外键只能使用表级约束声明方式

create table 表名(

字段1 数据类型 【primary key】,

字段2 数据类型【unique】【not null】【default 默认值】,

字段3 数据类型【unique】【not null】【default 默认值】,

【constraint FK_从表_主表1】foreign key (从表的字段) references 主表1(对应字段) 【on delete cascade/set null/RESTRICT】【on update cascade/set null/RESTRICT】,

【constraint FK_从表_主表2】foreign key (从表的字段) references 主表2(对应字段) 【on delete cascade/set null/RESTRICT】【on update cascade/set null/RESTRICT】

);

#主键和唯一键可以使用表级约束声明方式

create table 表名(

字段1 数据类型,

字段2 数据类型【not null】【default 默认值】,

字段3 数据类型【not null】【default 默认值】,

【constraint PK_字段】 primary key (字段),

【constraint uk_字段2】 unique(字段2),

【constraint uk_字段3】 unique(字段3),

【constraint FK_从表_主表1】foreign key (从表的字段) references 主表1(对应字段) 【on delete cascade/set null/RESTRICT】【on update cascade/set null/RESTRICT】,

【constraint FK_从表_主表2】foreign key (从表的字段) references 主表2(对应字段) 【on delete cascade/set null/RESTRICT】【on update cascade/set null/RESTRICT】

);

#注意：如果是复合主键,复合唯一键,必须使用表级约束定义

create table 表名(

字段1 数据类型,

字段2 数据类型,

字段3 数据类型,

字段4 数据类型,

字段5 数据类型【not null】【default 默认值】,

【constraint PK_字段1_字段2】 primary key (字段1,字段2),

【constraint uk_字段3_字段4】 unique(字段3,字段4),

【constraint uk_字段5】 unique(字段5),

【constraint FK_从表_主表1】foreign key (从表的字段) references 主表1(对应字段) 【on delete cascade/set null/RESTRICT】【on update cascade/set null/RESTRICT】,

【constraint FK_从表_主表2】foreign key (从表的字段) references 主表2(对应字段) 【on delete cascade/set null/RESTRICT】【on update cascade/set null/RESTRICT】

);

(二)如果表已经创建完毕,需要建立约束、删除约束

(1)增加主键约束

alter table 表名称 add 【constraint 约束名】 primary key (字段名);

alter table 表名称 add 【constraint 约束名】 primary key (字段名1,字段名2);#复合主键

(2)增加唯一键约束

alter table表名称 add 【constraint 约束名】 unique 【key】 (字段名);

alter table表名称 add 【constraint 约束名】 unique 【key】 (字段名1,字段名2);

(3)增加外键约束

alter table表名称 add 【constraint 约束名】 foreign key (从表字段名) references 主表名(主表被参照字段名);

(4)增加/删除缺省约束或非空约束

alter table 表名 modify 【column】 列名 数据类型 【default 默认值】 【not null】;

(三)删除约束

#键约束只能通过这种方式删除,不能通过modify/change列来删除

(1)如何查看一个表的约束信息：

#如果不知道要删除的约束名,可以通过SHOW CREATE TABLE 表名;查看 或

#select * from information_schema.table_constraints where table_name = '表名称';

(2)如何查看一个表的索引

show index from表名称;

(3)删除主键约束,不需要指定主键名,一个表只有一个主键

alter table表名称drop primary key;

删除主键约束,也会把主键上的索引删除

(4)删除唯一键约束

#唯一键只能通过删除索引的方式删除

alter table 表名称 drop index 唯一键的索引名;

(5)删除外键约束

alter table 表名称 drop foreign key 约束名;

删除外键约束,不会把外键上的索引删除,需要再单独删除索引

约束与索引

索引：索引是对数据库表中一列或多列的值进行排序的一种结构.

索引是一个单独的、物理的数据库结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单.由此可知,索引是要消耗数据库空间的.而约束是一种逻辑概念.

Mysql索引