定义完整性约束

实体完整性

实体是一个数据对象，是指客观存在并且可以相互区分的事物。如一个教师，一个学生，一个桌子，一个椅子。一个实体在数据中表示为一条记录，通常它必须遵循实体完整性约束。

在MySQL中实体完整性是由主键约束和候选键约束实现的。

候选键约束

若一个属性能完整标识一个元组，那么这个属性就可以设置成候选键，如学号可以唯一地区分不同的学生，那么学号就可以设为候选键。候选键可以是某一列，也可以是多个列组成的一个组合。候选键必须唯一且不能为NULL。候选键使用CREATE TABLE或ALTER TABLE语句创建，其实现方法与主键约束类似，也是作为列的完整性约束和表的完整性约束两种格式。

在MySQL中，候选键与主键有以下两种区别：

一个表只能创建一个主键，但是可以创建多个候选键；
定义主键约束时，系统会自动创建PRIMARY KEY索引，而定义候选键约束时，系统会自动创建UNIQUE索引。

如我们创建一个员工表，将id和姓名作为候选键。

create table tb_employees (
id int auto_increment unique,
    name varchar(50) not null unique,
    sex enum('男','女'),
    tele varchar(50)
);

参照完整性

参照完整性规则就是定义外码（外键）和主码（主键）之间的引用规则，它是对关系间引用数据的一种限制。

InnoDB引擎定义外键约束的语法如下：

[CONSTRAINT[SYMBOL]] FOREIGN KEY (index_col_name) reference_definition;

reference_definition主要用于定义外键所参照的表、列、参照动作的声明和实施策略四部分内容。它的基本语法如下：

REFERENCES tbl_name [(index_col_name,...)] [MATCH FULL|MATCH PARTIAL| MATCH SIMPLE] [ON DELETE reference_option] [ON UPDATE reference_option]

index_col_name的语法格式如下：

col_name [(length)] [ASC|DESC]

reference_option的语法格式如下：

RESTRICT|CASCADE|SET FULL|NO ACTION

参数说明如下：

index_col_name：用于指定被设置为外键的字段。
tbl_name：用于指定外键所参照的表名。这个表称为被参照表（或父表），而外键所在的表为参照表（或子表）。
col_name：用于指定被参照的列名，外键可以引用被参照表中的主键或候选键，也可以引用被参照表中某一些字段的组合，但这个组合不能是被参照表中随机的一组列，必须保证该组合的取值在被参照表中是唯一的。外键中所有列值在被参照表中必须全部存在，也就是通过外键来对参照表中某些列的取值进行限定和约束。
ON DELETE|ON UPDATE指定参照动作相关的SQL语句。可为每个外键指定对应于DELETE语句和UPDATE语句的参照动作.
reference_option：指定参照完整性约束的实现策略。其中当没有明确指定参照完整性约束的实现策略时，两个参照动作都会使用RESTRICT，具体策略见下表：

可选值	说明
RESTRICT	限制策略：当要删除或更新被参照表中被参照表列上、在外键中出现的值时，系统拒绝对被参照表的删除或更新操作
CASCADE	级联策略：从被参照表中删除或更新记录行时，自动删除或更新参照表中的记录行
SET FULL	置空策略：当从被参照表中删除或更新记录行时，设置参照表中与之对应的外键的值为NULL，这个记录需要被参照表中的外键列没有声明为NOT NULL
NO ACTION	不采取实施策略：当一个相关的外键值在被参照表中时，删除或更新被参照表中键值的动作不被允许。该策略的动作语言与RESTRICT相同

如我们定义一个存储学生信息的数据库，在这里面存在两个数据表：tb_stu和tb_class。tb_stu中我们定义五个字段：id、name、sex、classid（班级id）、remark（备注），将id设为主键；tb_class中定义两个字段：id（班级id）、classname，在此数据表中，我们约定班级id一一对应班级名称，那么我们就可以通过将tb_stu中的classid定义为外键，与tb_class中的主键id相连接，这就是参照完整性。在向学生数据表中添加信息时，若我们添加的班级id在班级数据表中不存在则添加失败。

实现上述功能的步骤如下：

创建学生信息表和班级id转换表。

班级id转换表
运行结果
学生信息表（创建的同时将classid的外键连接到tb_class的主键id上）

create table tb_stu(
id int auto_increment primary key,
name varchar(50) not null,
classid int not null,
remark varchar(50),
foreign key (classid)
references tb_class (id)
on delete restrict
on update restrict
);

注意在创建学生信息表的时候我们指定了on delete restrict on update restrict，这个代表在尝试删除参照表也就是学生信息表时若该数据行的classid字段的值等于被参照表班级id参照表中id的某一个值就会弹出删除失败的信息，若尝试添加一个不存在的班级id的学生时也会报出添加失败的信息。如我们先录入几个班级id和班级名称对应的关系：

insert into tb_class (classname) values ('一年一班'),('一年二班'),('一年三班'),('一年四班');

我们尝试添加一个不存在的班级id的学生：

insert into tb_stu (name,classid) values ('张三',0);

可见0不存在于班级id中，下面我们添加一个班级id为1的学生：

insert into tb_stu (name,classid) values ('张三',1);

可见添加成功。

我们再尝试班级id参照表中classid为1的数据行。

delete from tb_class where id=1;

可见由于外键约束，学生信息表中还存在着classid为1的数据行，因此删除失败，解决方式就是先删除学生信息表中classod为1的数据行再删除此数据。

设置外键时，必须遵守如下规则：

被参照表必须是存在的，或者是当前正在创建的表。若为当前正在创建的表，也就是说，被参照表与参照表是一个表，这样的表称为自参照表，这种结构称为自参照完整性。
必须为被参照表定义主键。
必须在被参照表名后指定列名或列名的组合。这个列或者列组合必须是这个被参照表的主键或候选键。
外键中列的数据类型必须与被参照表中主键（或候选键）的数据类型相同。
外键中列的数目必须与被参照表中列的数据相同。
尽管主键不能包含空值，但允许在外键中出现一个空值。这意味着，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

create table demo (c int check(c>10 and c<20));

可以看到我们设置CHECK约束之后当c等于1时插入失败，而当c等于15时可以成功插入。

对表实施CHECK约束

若要对表实施完整性约束，可以在CREATE TABLE语句或ALTER TABLE语句中定义一个CHECK语句即可，语法如下：

CREATE TABLE table_name(
col_definitions ...
    CHECK(para)
)

其中para代表检查条件，如CHECK(classid in(2,3))代表classid必须在2和3里面，CHECK(classid in(select id from tb_class))代表classid必须为tb_class表中的某一个值。如：

create table demo(
c int not null,
    check(c>0 and c<100)
)

可以看到我们定义c必须大于0且小于10，在插入的值为-1时插入失败，在插入的值为1时插入成功。

版权所有

许可证：署名 4.0 国际 (CC-BY-4.0)

定义完整性约束

实体完整性

主键约束

候选键约束

参照完整性

用户定义完整性

非空约束

CHECK约束

对列实施CHECK约束

对表实施CHECK约束

版权所有