MySQL中的数据完整性约束

AiryData 2017-03-27 00:02

预备阅读：MySQL中常用的数据类型 MySQL中表的操作

前言

前面已经学习了常用数据类型和表的操作，其中表的操作中提到了数据完整性约束条件，今天来详细说一下各种完整性约束条件。

关系模型的完整性规则是对关系的某种约束条件。对关系模型施加完整性约束，是为了在数据库应用中保障数据的正确性和一致性，防止数据库中存在不符合语义、不正确的数据，这也是数据库服务器最重要的功能之一。

完整性约束既能防止对MySQL数据库的意外破坏和非法存取，又能提高完整性检测效率，也能减轻我们的负担。下面说一下关系模型中的三类完整性约束。

---

定义实体完整性

实体完整性规则（Entity Integrity Rule）是指关系的主属性不能取空值，即主键和候选键在关系中所对应的属性都不能取空值。MySQL中实体完整性就是通过主键约束和候选键约束来实现的。

1、主键约束

主键是表中某一列或某些列所构成的一个组合。能够唯一地标识表中的一条记录。主键约束（Primary Key Constraint）要求主键列的数据唯一，且不允许为空。

主键约束实现的两种方式：

• 一种是列级完整性约束，在表中某个字段定义后加上关键字PRIMARY KEY即可。如studentNo char(10) PRIMARY KEY,。

• 一种是表级完整性约束，需要在表中所有字段定义最后添加一条PRIMARY KEY（index_col_name, ...）。如PRIMARY KEY(studentNo)。

比如我们重新创建学生表：

CREATE TABLE tb_student(

studentNo CHAR(10) PRIMARY KEY,

studentName VARCHAR(20) NOT NULL,

sex CHAR(2),

birthday DATE,

native VARCHAR(20),

nation VARCHAR(20),

classNo CHAR(6)

)ENGINE=InnoDB;

2、完整性约束的命名

与数据库中的表一样，可以对完整性约束进行添加、删除和修改等操作。首先需要在定义约束时对其进行命名，命名完整性约束的方法是，在各种完整性约束的定义说明之前加关键字CONSTRAINT和该约束的名字，语法格式如下：

CONSTRAINT<symbol>

{PRIMARY KEY (主键字段列表)

|UNIQUE (候选键字段列表)

| FOREIGN KEY (外键字段列表) REFERENCES tb_被参照关系(主键字段列表)

|CHECK(约束条件表达式)};

说明：symbol为指定的约束名字，必须是唯一的，默认MySQL会自动创建一个约束名字。

3、候选键约束

与主键一样，候选键可以是表中的某一列，也可以是表中某些列构成的一个组合。任何时候，候选键的值必须是唯一的，且不能为NULL，候选键可在CREATE TABLE 或ALTER TABLE语句中使用关键字UNIQUE来定义，实现方法与主键类似。看一个例子，创建班级表。

CREATE TABLE tb_class(

classNo CHAR(6) PRIMARY KEY,

className VARCHAR(20) NOT NULL UNIQUE,

department VARCHAR(30) NOT NULL,

grade SMALLINT,

classNum TINYINT

)ENGINE=InnoDB;

或者直接放在所有字段之后：CONSTRAINT UQ_class UNIQUE(className)。

MySQL中PRIMARY KEY 和UNIQUE之间的区别：

• 一个表只能创建一个PRIMARY KEY，但是可以定义多个UNIQUE。

• 定义PRIMARY KEY的列不允许有空值，定义UNIQUE的字段允许空值的存在。

• 定义PRIMARY KEY约束时，系统会自动产生PRIMARY KEY索引，而定义UNIQUE约束时，系统自动产生UNIQUE索引。

---

定义参照完整性

关系模型中实体与实体间的联系都是用关系来描述，因此可能存在着关系与关系间的引用。外键是一个表中的一个或一组属性，他不是这个表的主键，但是它对应另一个表的主键，外键的主要作用就是保证数据引用的完整性，保持数据的一致性。

参照完整性规则（Referential Integrity Rule）定义的是外键与主键之间的引用规则，即外键的取值或者为空，或者等于被参照关系中某个主键的值。

下面修改一下前面的学生表。

DROP TABLE tb_student;

CREATE TABLE tb_student(

studentNo CHAR(10),

studentName VARCHAR(20) NOT NULL,

sex CHAR(2),

birthday DATE,

native VARCHAR(20),

nation VARCHAR(20),

classNo CHAR(6) REFERENCES tb_class(classNo),

CONSTRAINT PK_student PRIMARY KEY(studentNo)

)ENGINE=InnoDB;

前面已经创建了表tb_class，而且classNo是主键，这里学生表里可以设置为外键，这个值参照班级表的主键classNo的值。

PS.外键只能引用主键和候选键。外键只可以用在使用存储引擎InnoDB创建的表中，其他的存储引擎不支持外键。

---

用户定义的完整性

除了前面两种完整性之外，还有一种特殊的约束条件，即用户定义的完整性规则（User-definedIntegrity Rule），它反映了某一具体应用所涉及的数据应满足的语义要求。

MySQL支持以下几种用户自定义完整性约束：

1、设置非空约束

非空约束是指设置的字段的值不能为空。比如之前我们添加的字段：studentNameVARCHAR(20) NOT NULL,中就设置了非空约束。

2、CHECK约束

与非空约束一样，也是在创建表或修改表的同时来定义的。CHECK约束需要指定限定条件，CHECK的语法是CHECK(expr);下面看一下实例，我们来创建一个课程表tb_course，并且设置约束，每16个课时对应1学分。

CREATE TABLE tb_course(

courseNo CHAR(10),

courseName VARCHAR(20) NOT NULL,

credit INT NOT NULL,

courseHour INT NOT NULL,

term CHAR(2),

priorCourse CHAR(6),

CONSTRAINT PK_course PRIMARY KEY(courseNo),

CONSTRAINT FK_course FOREIGN KEY(priorCourse)

REFERENCES tb_course(courseNo),

CONSTRAINT CK_course CHECK(credit= courseHour/16)

)ENGINE=InnoDB;

---

更新完整性约束

1、删除约束

DROP TABLE会删除所有完整性，但是并不经常这样做。看一下单独删除的语法。

1. 删除外键约束

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

1. 删除主键约束

ALTER TABLE <表名> DROP PRIMARY KEY;

1. 删除候选键约束

ALTER TABLE <表名> DROP {约束名|候选键字段名};

2、添加约束

创建表时一般会添加约束，如果没有添加的话，后期添加也是可以的。

1. 添加主键约束

ALTER TABLE <表名> ADD [CONSTRAINT<约束名>] PRIMARY KEY(主键字段);

1. 添加外键约束

ALTER TABLE <表名> ADD [CONSTRAINT<约束名>] FOREIGNKEY (外键字段) REFERENCES 被参照表(主键字段名);

1. 添加候选键约束

ALTER TABLE <表名> ADD [CONSTRAINT<约束名>] UNIQUE KEY(字段名);

大家可以手动测试一下。

---

小结

今天主要讲一下MySQL中的数据完整性约束，方便下面的学习，熟悉之后，我们才能更好的应用于我们的数据库，用合适的完整性约束来规范我们的数据，这样不仅方便查找和操作，也方便后期的维护和优化。

希望通过上面的操作能帮助大家。如果你有什么好的意见，建议，或者有不同的看法，我都希望你留言和我们进行交流、讨论。

如果想快速联系我，欢迎关注微信公众号：AiryData。