MySQL数据完整性约束

棋大约 12 分钟约 3728 字

数据完整性是指数据的统一性和相容性，是为了防止数据库中存在不符合语义的数据，即防止数据库中存在不正确的数据。一旦定义了完整性约束，MySQL服务器会实时检测处于更新状态的数据库内容是否符合相关的完整性约束，从而保证数据的一致性和正确性。这样既能防止对数据库的意外破坏又能减轻数据库管理人员的维护负担。

定义完整性约束

实体完整性

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

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

主键约束

主键可以是一个字段，也可以是多个字段的组合，叫做复合主键。主键约束必须满足以下规则：

每个表只能定义一个主键。
唯一性原则：主键的值，也称键值，必须能够唯一标识表中的某一条数据，且不能为NULL。
最小化原则：复合主键不能包含不必要的多余字段。
一个字段名在复合主键中只能出现一次。

在MySQL中，可以在CREATE TABLE和ALTER TABLE语句中使用PRIMARY KEY关键字创建主键约束，实现方式有以下两种：

第一种，在字段的定义后面声明该字段为主键。如下面将id字段设为主键。

create table tb_user(
id int primary key auto_increment,
user varchar(50) not null,
password varchar(50) not null,
createtime date not null
);

可以看到，此数据表中字段id为主键。

第二种，在最后声明，如将主键设为id和class字段。

create table tb_stu(
    id int not null auto_increment,
    name varchar(50) not null,
    sex enum('男','女') not null,
    class int not null,
    primary key (id,class)
);

可以看到此数据表中字段id和class都为主键，即为复合主键。

候选键约束

若一个属性能完整标识一个元组，那么这个属性就可以设置成候选键，如学号可以唯一地区分不同的学生，那么学号就可以设为候选键。候选键可以是某一列，也可以是多个列组成的一个组合。候选键必须唯一且不能为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或者ALTER TABLE语句中添加关键字NOT NULL修饰。如：

可以看到我们将字段c1设为非空，在插入数据行时若c1为空则会报错，而无论c2是否为空都不会报错。

CHECK约束

对列实施CHECK约束

若要对列实施CHECK约束，只需要在该列定义后加一个CHECK(para)即可，其中para表示判断的表达式，如我们设置c列必须大于10且小于20。

如下面我们定义一个字段，在该字段后面加上CHECK约束，使该字段的值大于10且小于20。

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时插入成功。

命名完整性约束

在MySQL中也可以对完整性约束进行添加、修改和删除等操作，在进行这些操作之前就需要先将完整性约束命名。命名完整性约束的方式是在定义完整性约束的语句前加CONSTRAINT字句，具体语法如下：

CONSTRAINT <symbol>
[PRIMARY KEY 短语|UNIQUE 短语|FOREIGN KEY 短语|CHECK 短语]

其中symbol就为完整性约束的名字。

如我们在创建员工表的同时为id添加一个主键约束，并且将其命名为pri_key。

create table employees (
id int auto_increment unique,
name varchar(50) not null,
sex enum('男','女') not null ,
salary int not null,
constraint pri_key primary key (id)
);

在定义完整性约束时，最好在定义的同时就为其添加名字，这样方便后期对其进行修改、删除等操作。

更新完整性约束

删除完整性约束

在ALTER TABLE语句中使用DROP字句可以单独地删除一个完整性约束，语句如下：

DROP [FOREIGN KEY|INDEX|<SYMBOL>]|[PRIMARY KEY]

其中，FOREIGN KEY表示删除外键约束。PRIMARY KEY表示删除主键约束，但是需要注意在删除主键约束时必须再次创建一个主键，否则会删除失败。INDEX表示删除候选键约束，symbol表示要删除的约束名称。

如我们删除employees表中字段id的唯一索引，就可以使用下面的语法：

alter table employees drop index id;

在删除这个索引后我们可以再次查看此表的索引，可以看到唯一索引已被删除。

修改完整性约束

MySQL中不存在直接修改完整性约束的方法，需要先删除对应的完整性约束然后再创建一个同名的完整性约束完成修改。

在下面的实例中我们将候选键约束name改为id，具体是先删除候选键约束name，然后在添加值为id的候选键约束。

其中删除候选键约束和重新添加候选键约束的语句如下：

alter table employees drop index name;
alter table employees add unique index id(id);

运行结果如下图，可见执行这两条语句之后候选键约束从name改为了id。

再若我们要给表tb1添加一个如下的外键约束（无运行结果）。

alter table tb1 add (constraint 'fri_key'  -- 该外键名为fri_key
foreign key (col1,col2)  -- 参照表为tb1 添加外键约束的字段为col1和col2
    references tb2 (col3,col4) -- 被参照表为tb2 被参照字段为col3和col4
   on delete set null  -- 若删除被参照表中的字段值，则在参照表中对应的位置设成NULL
    on update restrict  -- 若更新被参照表中的字段值，若参照表有对应的值则不允许此操作
);

在设置此外键约束前，被参照表tb2必须具有对应字段为col3和col4的多列索引，创建该索引的部分语法如下：add index (col3,col4)。
设置此外键约束后，参照表中若要添加数据，则参照表中同一数据行的字段col1和字段col2必须同时对应被参照表中某一同一数据行的字段col3和col4，如被参照表中有一数据行，该数据行中col3的值为1，col4的值为2，则向参照表中添加数据时，该行数据的字段col1必须为1，col4必须为2，若col1为1col2为3那么就是不同时对应该数据行的每个字段了，若被参照表中其他字段也不满足此规则，则该数据行不能添加。

MySQL数据完整性约束

# 定义完整性约束

# 实体完整性

# 主键约束

# 候选键约束

# 参照完整性

# 用户定义完整性

# 非空约束

# CHECK约束

# 对列实施CHECK约束

# 对表实施CHECK约束

# 命名完整性约束

# 更新完整性约束

# 删除完整性约束

# 修改完整性约束