MySQL数据完整性约束

数据的完整性约束：

数据完整性指的是数据的一致性和正确性,完整性约束是指数据库的内容必须随时遵守的规则。如果定义了数据完整性约束，MySQL会负责数据的完整性，每次更新数据时，MySQL都会测试新的数据内容是否符合相关的完整性约束条件，只有符合完整性约束条件的更新才被接受。
例如，为保证数据的完整性，需要对输入的数据进行以下检查。

●输入的类型是否正确? --年龄必须是数字。
输入的格式是否正确? -E-Mail 必须包含@符号。·是否在允许的范围内?
--性别只能是“男”或者“女”。

●是否存在重复输入?
一一同一员工信息只能输入一次。

●是否符合其他特定要求?
-信誉值大于5的客户才能够加入客户表。
......
数据完整性约束分为实体完整性(entity integrity)、域完整性(domain integrity )、引用完整性(referential integrity)及用户定义的完整性(user - defined integrity )，

其含义如图所示：

表的约束简介（关系图）：

在DBMS中，为了防止数据表中插入错误数据，定义了一些维护数据完整性的规则，即为表的约束。

在DBMS中，为了防止数据表中插入错误数据，定义了一些维护数据完整性的规则，即为表的约束。
约束的创建和管理有三种常用方法:
1.使用表设计器创建和管理约束。
2.使用CREATE TABLE语句创建约束。
3.使用ALTER TABLE语句创建和管理约束。

主键约束：

1.定义主键：

主键就是表中的一列或多个列的一组，其值能唯一地标识表中的每一
V3-7主键    行。MySQL为主键列创建了唯一性索引，实现了数据的唯一性，在查询中    
约束    使用主键时，该索引可用来对数据进行快速访问。MySQL通过定义    
PRIMARY KEY 约束来创建主键，而且PRIMARYKEY约束中的列不能取空值。如果 PRIMARY KEY 约束是由多列组合定义的，则某一列的值可以重复，但PRIMARY KEY约束定义中所有列的组合值必须唯一。
用户可以用两种方式定义主键，作为列或表的完整性约束，作为列的完整性约束时，只需在列定义的时候加上关键字PRIMARYKEY;作为表的完整性约束时，需在语句最后加上一条PRIMARYKEY(列名，…)语句。

创建表book_copy,将"书名"定义为主键（例）：

主键定义于NOT NULL之后，也可以在主键之后指定NOT NULL。如果作为主键的一部分的一个列没有定义为NOT NULL，MySQL就自动把这个列定义为NOT NULL。例3-14中的书名列可以没有NOT NULL声明，为了清楚起见，最好包含这个空指定。
当表中的主键为复合主键时，只能将其定义为表的完整性约束。

创建course表来记录每门课程的学生的“学号”“姓名”“课程号”“学分”“毕业日期”。其中“学号”“课程号”“毕业日期”构成复合主键（例）：

（接上表）

2.定义主键的原则：

原则上，任何列或者列的组合都可以充当一个主键，但是主键列必须遵守一些规则。这些规则源于关系模型理论和MySQL所制定的规则。
(1) 每个表必须定义一个主键
关系模型理论要求必须为每个表定义一个主键。然而，MySQL并不要求这样，它可以创建一个没有主键的表。但是，从安全角度考虑，应该为每个基础表指定一个主键。主要原因在于，没有主键，可能在一个表中存储两个相同的行，当两个行不能彼此区分时，查询过程中将会满足同样的条件，更新的时候也总是一起更新，这样容易造成数据库崩溃。

(2)唯一性规则
表中两个不同的行在主键上不能具有相同的值。

(3)最小化规则
如果从一个复合主键中删除一列，而剩下的列构成的主键仍然满足唯一性原则，那么这个复合主键是不正确的。这条规则称为最小化规则，也就是说，复合主键不应该包含一个不必要的列。一个列名在一个主键的列表中只能出现一次。
MySQL自动地为主键创建一个索引，通常这个索引名为PRIMARY，用户可以重新给这个索引起名。

course表创建course1表，将索引命名为“INDEX_course”（例）：

3.替代健约束：

替代键像主键一样，是表的一列或一组列，它们的值在任何时候都是唯一的。因为一个表只能有一个主键，所以当一个表有多个列需要建立唯一性约束时，替代键是没有被选
做主键的候选键。定义替代键的关键字是UNIQUE。

在表book_copy1中，将“图书编号”作为主键，将“书名”列定义为一个替代健（例）：

关键字UNIQUE表示“书名”是一个替代键，其列值必须是唯一的。         

替代键也可以定义为表的完整性约束，上述语句也可以进行如下定义（例）：

在MySQL中，替代键和主键的区别主要有以下几点：

(1)数量不同
一个数据表只能创建一个主键，但可以有若干个UNIQUE键,并且它们甚至可以重合。例如，在C1和C2列定义了一个替代键，并且在C2和C3列定义了另一个替代键，这两个替代键在C2列上重合，MySQL允许这样。

(2)NULL设置不同
主键字段的值不允许为NULL，而UNIQUE字段的值可取NULL，但是必须使用NULL或NOT NULL 声明。

(3)索引不同
创建 PRIMARY KEY约束时，系统自动产生PRIMARY KEY索引。创建UNIQUE 约束时，系统自动产生UNIQUE 索引。

对于已经创建好的表，可以使用ALTER TABLE语句向表中添加约束。语法格式如下：

假设book 表中主键未设定，为book表建立主键约束为“图书编号”，“书名”为替代键约束。

本例中既包括主键约束，也包括替代键约束，说明MySQL可以同时创建多个约束。注意，使用PRIMARY KEY的列必须是一个具有NOT NULL属性的列。

删除book表中的主键和替代键约束（例）：

4.参照完整性约束的概念：

在数据库中，有很多规则是和表之间的关系有关的。例如，学生只有注册后才可以参与考试，才可以录入成绩。因此，成绩表中的所有学生(由学号来标识)必须是学生注册表中的学生，也就是说存储在成绩表中的所有学号必须是学生注册表学号列中的学号。这种类型的关系就是参照完整性约束(referential integrity constraint )，

如图 ：

参照完整性约束语法定义:
参照完整性约束可以在创建表或修改表时定义一个外键声明。定义外键的语法格式如下：

外键被定义为表的完整性约束，语法中包含了外键所参照的表和列，还可以声明参照动作。

语法说明如下 ：
引用主键和替代键，不能引用被参照表中随机的一组列，它必须是被参照表的列的一个我。

外键:参照表的列名。外键中的所有列值在引用的列中必须全部存在。外键可以及
合，且其中的值是唯一的。

父表表名:外键所参照的表名。这个表叫作被参照表，外键所在的表叫作参照表在例3-20中，book_ref是参照表或子表，book是被参照表或父表。

父表列名:被参照的列名。外键可以引用一个或多个列。
ON DELETE | ON UPDATE:可以为每个外键定义参照动作。参照动作包含两部分，第一部分指定这个参照动作应用哪一条语句，有UPDATE和DELETE语句;第二部分指定采取哪个动作，可能采取的动作有RESTRICT、CASCADE、SET NULL、NO ACTION和 SET DEFAULT。

RESTRICT:当要删除或更新父表中被参照列上在外键中出现的值时，拒绝对父表的删除或更新操作。

CASCADE:从父表删除或更新行时，自动删除或更新子表中匹配的行。

SETNULL:从父表删除或更新行时，设置子表中与之对应的外键列为NULL。如果外键列没有指定NOT NULL限定词，这就是合法的。

NO ACTION: NO ACTION意味着不采取动作，就是如果有一个相关的外键值在被参考的表里，则删除或更新父表中该值的企图将不被允许，其作用和RESTRICT 命令一样。

SET DEFAULT:作用和SET NULL一样，只不过SET DEFAULT是指定子表中的外键列为默认值。
如果没有指定动作，两个参照动作就会默认地使用RESTRICT 命令。

创建外键：

在创建表或修改表时可以创建外键，方法如下。

(1)创建表的同时创建外键

语法格式如下：

（2）对已有表创建外键

语法格式如下：

创建book_ref表，book_ref表中的所有“图书编号”都必须出现在book表中，假设已经使用“图书编号”列作为book表主键（例）：

（接上表）

在这条语句中，定义一个外键的实际作用是，在这条语句执行后，确保MySQL插入外键中的每一个非空值都已经在被参照表中作为主键出现。这意味着，对于book_ref 表中的每一个图书编号，都执行一次检查，看这个编号是否已经出现在book表的“图书编号”列(主键)中。如果情况不是这样，用户或应用程序会接收到一条出错消息，并且更新被拒绝。这种检查也适用于使用UPDATE语句更新bookref表中的图书编号列，即MySQL确保了 book_ref表中“图书编号”列的内容总是book表中“图书编号”列的内容的一个子集。也就是说，

如下SELECT语句不会返回任何行：

当指定一个外键的时候，要受到下列条件的制约。
①父表必须是已经创建的表或者是正在创建的表。在后一种情况下，父表和子表是同一个表，表中的某列参照另一列。
② 必须在父表的表名后面指定列名(或列名的组合)。这个列(或列组合)必须是这个表的主键或替代键。
③外键中列的数目必须和父表中的主键(或替代键)中列的数目相同。④ 外键中列的数据类型必须和父表中主键中列的数据类型相同。
⑤ 父表和子表必须使用相同的存储引擎，并且不能将它们定义为临时表。

创建带有参照动作“CASCADE”的book_ref1 表（例）：

这个参照动作的作用是在主表更新时，子表产生连锁更新动作，有些人称它为“级联操作。例如，如果book表中有一个图书编号为“TP.2525”的值被修改为“TP.2525-1”，则 book_refl表中“图书编号”列中的值“TP.2525”相应地改为“TP.2525-1”。
同样，如果例3-21中的参照动作为ON DELETE SET NULL，则表示如果删除了book表中图书编号为“TP.2525”的一行，会同时将book_ref1表中所有图书编号为“TP.2525”的列值改为NULL。

在网络图书销售系统中，只有会员才能下订单。因此sell表中的所有“用户号”也必须出现在members表的“用户号”列中。这种约束通过定义参照完整性约束来实现（例）：

CHECK 完整性约束：

主键、替代键、外键都是常见的完整性约束实例。但是，每个数据库还有一些专用的完整性约束、例如，se表中的订购册数要在1~5000内，book表中的出版时间必须大于1986年1月1日。这样的规则可以使用CHECK完整性约束来指定。

CHECK 完整性约束在创建表的时候定义,可以定义为列完整性约束,也可以定义为表完整性约束。

语法格式如下：

语法说明如下：

表达式:指定需要检查的条件，在更新表数据的时候，MySQL会检查更新后的数据行是否满足 CHECK 的条件。

创建表student，只考虑学号和性别两列，性别只能包含“男”或“女两项（例）：

这里，CHECK 完整性约束指定了性别只允许输入“男”或“女”，由于CHECK包含在列自身的定义中，因此 CHECK 完整性约束被定义为列完整性约束。

创建表 student1，只考虑“学号”“出生日期”“学分”列，出生日期必须大于2000年1月1日（例）：

如果指定的完整性约束中要相互比较一个表的两个或多个列，那么该列完整性约束必须定义表完整性约束。

创建表 student3、有“学号”“最好成绩”“平均成绩”3列，要求最好成绩必须大于平均成绩（例）：

CHECK 完整性约束可以同时定义多个，中间用逗号隔开。

创建表 student4，有“学号”“最好成绩”“平均成绩”3列，要求最好成绩必须大于平均成绩，且最好成绩不得超过100分（例）：

如果需要查看表的所有信息，包括一些字段类型、字段的约束、外键、主键、索引及字符编码等，可以使用如下命令。

如果使用一条DROPTABLE语句删除一个表,则所有完整性约束都自动被删除了，被参照表的所有外键也都被删除了。使用ALTERTABLE语句，可以独立地删除完整性约束、而不会删除表本身。

删除 book 表的主键，删除 book_ref表的外 book_refibk_1，删除 student 表的 CHECK 完性约束 student chk_1。修改 student! 表的 CHECK 完整性约束 studentl_chk 1属性，暂时不强制执行（例）：

注意:MySQL可以以使用check约束，但check约束对数据验证没有任何作用。