mysql开发和 与实践 第8章 数据完整性约束.pdf

本章要求:第8章数据完整性约束 MySQL中对数据库完整性三项规则的设置和实现方式 如何设置主键约束及必须遵守的规则 如何设置候选键约束 参照完整性的定义及规则 如何添加用户定义的完整性 实现命名完整性约束的国式 删除完整性约束 修改完整性约束主要内容第8章数据完整性约束1.定义完整性约束2.命名完整性约束3.更新完整性约束4.综合实例在创建表时添加命名外键 完整性约束8.1定义完整性约束8.1.18.1.28.1.3实体完整性参照完整性 用户定义的完整性8.1.1实体完整性实体（Entity）是一个数据对象，是指客观存在并可以相互区分的事物，例如，一个教师、.个学生、一个雇员等。一个实体在数据库中表现为表中的一条记录。通常情况下，它必须遵守实体.完整性规则。.实体完整性规则（Entity Integrity Rule）是指关系的主属性，即主码（主键）的组成不能为空，也就是关系的主属性不能是空值（NUL L）。关系对应于现实世界中的实体集，而现实世界中的 实体是可区分的，即说明每个实例具有唯一性标识。在关系模型中，是使用主码（主键）作为唯一性标识的，若假设主码（主键）取空值，则说明这个实体不可标识，即不可区分，这个假设显然不 正确，与现实世界应用环境相矛盾，因此不能存在这样的无标识实体，从而在关系模型中引入实体 完整性约束。例如，学生关系（学号、姓名，性别）中，“学号”为主码（主键），则“学号”这 个属性不能为空值，否则就违反了实体完整性规则。在MySQL中，实体完整性是通过主键约束和候选键约束来实现的。1.主键约束主键可以是表中的某一列，也可以是表中多个列所构成的一个组合；其中，由多个列组合而成的 主键也称为复合主键。在MySQL中，主键列必须遵守以下规则。每一个表只能定义一个主键。唯一性原则。主键的值，也称键值，必须能够唯一标识表中的每一行记录，且不能为NUL L。也 就是说一张表中两个不同的行在主键上不能具胡相同的值。最小化规则。复合主键不能包含不必要的多余列。也就是说，当从一个复合主键中删除一列后,如果剩下的列构成的主键仍能满足唯一性原则，那么这个复合主键是不正确的。一个列名在复合主键的列表中只能出现一次。在MySQL中，可以在CREATE TABL E或者AL TER TABL E语句中，使用PRIMARY K EY子句来创建主键约 束，其实现方式有以下两种。作为列的完整性约束 在表的某个列的属性定义时，加上关键字PRIMARY K EY实现。【例8-1】在创建用户信息表tb user时，将id字段设置为主键，代码如下:create table tb_user(id int auto increment primary key,user varchar(30)not null,password varchar(30)not null,createtime datetime);.运行上述代码，其结果如图8T所示。_mysql use db_database08 Database changed mysql create table tb_user id int auto_increnent primary key,-user uaichai*not null,password uai*chai*not null,createtime datetine；Query OK,0 rows affected mysql).图8-1将id字段设置为主键.作为表的完整性约束在表的所有列的属性定义后，加上PRIMARY K EY(index_col_name,)子句实现。【例8-2】在创建学生信息表tb student时，将学号(id)和所在班级号(classid)字段设置为主键，代码如下：create table tb student(id int auto increment,name varchar(30)not null,sex varchar(2),classid int not null,birthday date,PRIMARY KEY(id,classid)运行上述代码,其结果如图8-2所示。it;sql use db_database08 iatabase changed iysql create table tb_student id int auto_increment,name uarchat not null,-sex varchai*,-classid int not null,-birthday date,-PRIMARY KEY id.classid-；luery OK,0 rows affected iysql说明:图8-2将id字段和classid字段设置为主键如果主键仅由表中的某一列所构成，那么以上两种方法均可以定义主键约束；如果主键由表中 多个列所构成，那么只能用第二种方法定?超约枣一枭外，定义主键约束后，MySQL会自动为主键.创建一个唯一索引，默认名为PRIMARY,也窠以修改码如名称。2.候选键约束如果一个属性集能唯一标识元组，且又不含有多余的属性，那么这个属性集称为关系的候选键 O例如，在包含学号、姓名、性别、年龄、院系、班级等列的“学生信息表”中，“学号”能 够标识一名学生，因此，它可以作为候选键，而如果规定，不允许有同名的学生，那么姓名也 可以作为候选键。候选键可以是表中的某一列，也可以是表中多个列所构成的一个组合。任何时候，候选键的值 必须是唯一的，且不能为空（NUL L）。候选键可以在CREATE TABL E或者AL TER TABL E语句中使用 关键字UNIQUE来定义，其实现方法与主键约束类似，也是可作为列的完整性约束或者表的完整 性约束两种方式。在MySQL中，候选键与主键之间存在以下两点区别。一个表只能创建一个主键，但可以定义若干个候选键。定义主键约束时，系统会自动创建PRIMARY K EY索引，而定义候选键约束时，系统会自动创建 UNIQUE索引。【例8-3】在创建用户信息表tb_userl时，将id字段和user字段设置为候选键，代码如下:create table tb_userl(id int auto_increment UNIQUE,user varchar(30)not null UNIQUE,password varchar(30)not null,createtime TIMESTAMP default CURRENT_TIMESTAMP);运行上述代码，其结果如图8-3所示。mysql use db_database08Database changed mysql create table tb_userl id int auto-increment UNIQUE,-user uarchar not null UNIQUE,-password uarchar not null,-createtime TIMESTAMP default CURRENT_TIMESTAMP；Query OK,0 rows affected mysql.图8-3将id字段和user字段设置为候选键8.1.2参照完整性现实世界中的实体之间往往存在着某种联系，在关系模型中，实体及实体间的联系都是用关系 来描述的，那么自然就存在着关系与关系间的引用。例如，学生实体和班级实体可以分别用下面的 关系表示，其中主码（主键）用下划线标识。学生（学生证号,姓名，性别，生日，班级编号，备注）班级（班级编号,班级名称，备注）在这两个关系之间存在着属性的引用，即“学生”关系引用了“班级”关系中的主码（主键）“班级编号”。在两个实体间，“班级编号”是“班级”关系的主码（主键），也是“学生”关系的 外部码（外键）。显然，“学生”关系中的“班级编号”的值必须是确实存在的班级的“班级编号”，即“班级”关系中的该班级的记录。也就是说，“学生”关系中某个属性的取值需要参照“班 级”关系的属性和值。参照完整性规则（Referential Integrity Rule）就是定义外码（外键）和主码（主键）之间的 引用规则，它是对关系间引用数据的一种限制。参照完整性的定义为：若属性（或属性组）F是基本关系R的外码，它与基本关系S的主码K相对 应，则对于R中每个元组在F上的值只允许两种可能，即要么取空值（F的每个属性值均为空值），要 么等于S中某个元组的主码值。其中，关系R与S可以是不同的关系，也可以是同一关系，而F与F是定 义在同一个域中。例如，在“学生”关系中每个学生的“班级编号”一项，要么取空值，表示该学 生还没有分配班级；要么取值必须与“班级”关系中的某个元组的“班级编号”相同，表示这个学 生分配到某个班级学习。这就是参照完整性。如果“学生”关系中，某个学生的“班级编号”取值 不能与“班级”关系中任何一个元组的“班级编号”值相同，表示这个学生被分配到不属于所在学 校的班级学习，这与实际应用环境不相符，显然是错误的，这就需要在关系模型中定义参照完整性进行约束。与实体完整性一样，参照完整性也是由系统自动支持的，即在建立关系(表)时，只要定义了“谁是主码、“谁参照于认证”，系统将自动进行此类完整性的检查。在MySQL中，参照完整性可 以通过在创建表(CREATE TABL E)或者修改表(AL TER TABL E)时定义一个外键声明来实现。MySQL有两种常用的引擎类型(MylSAM和InnoDB),目前，只有InnoDB引擎类型支持外键约束。InnoDB引擎类型中声明外键的基本语法格式如下：CONSTRAINT SYMBOLFOREIGN KEY(index_col_name,.)reference_definitionreference_definition主要用于定义外键所参照的表、歹!、参照动作的声明和实施策略等4部分内容 O它的基本语法格式如下：REFERENCES tbl_name(index_col_name,.)MATCH FULL|MATCH PARTIAL|MATCH SIMPLEON DELETE reference_optionON UPDATE reference_optionindex_col_naine 的语法格式：col_name(length)ASC|DESC referenceoption 的语法格式：RESTRICT|CASCADE|SET NULL|NO ACTION参数说明如下：index_col_name：用于指定被设置为外键的列。tbL name：用于指定外键所参照的表名。这个表称为参照表（或父表），而外键所在的表称作 参照表（或子表）。Unaiiie；用于指定被参照的列名。外键可以引用被参照表中的主键或候选键，也可以引用被 参照表中某些列的一个组合，但这个组合不能是被参照表中随机的一组列，必须保存该组合的 取值在被参照表中是唯一的。外键中的所有列值被参照表的列中必须全部存在，也就是通过外 键来对参照表某些列（外键）的取值进行限定与约束。ON DEL ETE ON UPDATE：指定参照动作相关的SQL语句。可为每个外键指定对应于DEL ETE语句 和UPDATE语句的参照动作。reference_option：指定参照完整性约束的实现策略。其中，当没有明确指定参照完整性的实 现策略时，两个参照动作会默认使用RESTRICT。具体的策略可选值如表8-1所示。表8-1 策略可选值可选值说明RESTRICT限制策略：当要删除或更新被参照表中被参照列上，并在外键中出现 的值时，系统拒绝对被参照表的删除或更新操作CASCADE级联策略：从被参照表中删除或更新记录行时，自动删除或更新参照 表匹配的记录行SET NUL L置空策略：当从被参照表中删除或更新记录行时，设置参照表中与之 对应的外键列的值为NUL L。这个策略需要被参照表中的外键列没有声明限 定词NOT NUL LNO ACTION不采取实施策略：当一个相关的外键值在被参照表中时，删除或更新 被参照表中键值的动作不被允许A该策略的动作语言与RESTRICT相同J-【例8-4】在创建学生信息表tb_studentl,并为其设置参照完整性约束(拒绝删除或更新被参照.表中被参照列上的外键值)，即疮classid字段设置为外键，代码如下：create table tb_studentl(.id int auto_increment,name varchar(30)not null,sex varchar(2),classid int not null,birthday date,remark varchar(lOO),primary key(id),FOREIGN KEY(classid)REFERENCES tb_class(id)ON DELETE RESTRICT ON UPDATE RESTRICT仁口运行上述代码，其结果如图8-4所示。iysql create table tb_studentl id int auto_increment,-name uarchar not null,-sex uarchar,-classid int not null,-birthday date,-remark uarchar,-primary key,-FOREIGN KEY classid)-REFERENCES tb_class-ON DELETE RESTRICT-ON UPDATE RESTRICTQuery OK,0 rows affected 图8-4将classid字段设置为外键注意：要设置为主外键关系的两张数据表必须具有相同的存储引擎，例如，都是InnoDB,并且相关联 的两个字段的类型必须一致。设置外键时，通常需要遵守以下规则：被参照表必须是已经存在的，或者是当前正在创建的表。如果是当前正在创建的表，也就是说被 参照表与参照表是同一个表，这样的表称为自参照表(self-referencing table),这种结构称 为自参照完整性(sei为referential integrity)。必须为被参照表定义主键。必须在被参照表名后面指定列名或列名的组合。这个列或列组合必须是这个被参照表的主键或候 选键。外键中列的数目必须和被参照表中的列的数据相同。外键中列的数据类型必须和被参照表的主键(或候选键)中的对应列的数据类型相同。尽管主键是不能够包含空值的，但允许在外键中出现一个空值。这意味着，只要外键的每个非空 值出现在指定的主键中，这个外键的内容就是正确的。多二二二8.1.3用户定义的完整性用户定义完整性规则(User-defined Integrity Rule)是针对某一应用环境的完整性约束条件,它找反映了某一具体应用所涉及的数据应满足的要求。关系模型提供定义和检验这类完整性规则 的机制，其目的是由系统来统一处理，而不再由应用程序来完成这项工作。在实际系统中，这类完 整性规则一般是在建立数据表的同时进行定义，应用编程人员不需要再做考虑，如果某些约束条件 没有建立在库表一级，则应用编程人员应在各模块的具体编程中通过程序进行检查和控制。MySQL支持非空约束、CHECK约束和触发器3种用户自定义完整性约束。其中，触发器将在第10章 进行详细介绍。这里主要介绍非空约束和CHECK约束。1.非空约束在MySQL中，非空约束可以通过在CREATE TABL E或AL TER TABL E语句中，某个列定义后面加上关 键字NOT NUL L来定义，用来约束该列的取值不能为空。仁口【例8-5】在创建班级信息表tb_classl,并为其name字段添加非空约束，代码如下:CREATE TABLE tb_classl(id int(ll)NOT NULL AUTOJNCREMENT,name varchar(45)NOT NULL,remark varchar(lOO)DEFAULT NULL,PRIMARY KEY(id)X运行上述代码，其结果如图8-5所示。niFsql use db_database08Database changed nysql CREATE TABLE tb_classi id int NOT NULL AUTO_INCREMENT,name uarchai*NOT NULL,-remark uarchar DEFAULT NULL,-PRIMARY KEY id)-；Query OK,0 rows affected nysql图8-5为name字段添加非空约束2.CHECK约束与非空约束一样，CHECK约束也可以通过在CREATE TABL E或AL TER TABL E语句中，根据用户的实 际完整性要求来定义。它可以分别对列或表实施CHECK约束，其中使用的语法如下：CHECK(expr)其中，expr是一个SQL表达式，用于指定需要检查的限定条件。在更新表数据时，MySQL会检查 更新后的数据行是否满足CHECK约束中的限定条件。该限定条件可以是简单的表达式，也可以复 杂的表达式(如子查询)。下面将分别介绍如何对列和表实施CHECK约束。对列实施CHECK约束将CHECK子句置于表的某个列的定义之后就是对列实施CHECK约束。下面将通过一个具体的实例 来说明如何对列实施CHECK约束。【例8-6】在创建学生信息表tb_student2,限制其age字段的值只能是7T8之间(不包括18)的数，代码如下：create table tb_student2(id int auto_increment,name varchar(30)not null,sex varchar(2),age int not null CHECK(age6 and age use db_database08Database changed create table tb_student2 id int auto_inci*ernent,-name varchai*not null,-sex uarchai*,age int not null CHECK6 and age,-remark varchar,-primary key -；Query 0K_ 0 rows affected 图8-6对列实施CHECK约束说明：目前的MySQL版本只是对CHECK约束进行了分析处理，但会被直接忽略，并不会报错。仁Q 对表实施CHECK约束将CHECK子句置于表中所有列的定义以及主键约束和外键定义之后就是对表实施CHECK约束。下 面将通过一个具体的实例来说明如何对表实施CHECK约束。【例8-7】在创建学生信息表tb_student3,限制其2必551字段的值只能是1_(315$表中1(1字段的 某一个id值，代码如下：create table tb_student3(id int auto_increment,name varchar(30)not null,sex varchar(2),classid int not null,birthday date,remark varchar(lOO),primary key(id),CHECK(classid IN(SELECT id FROM tb_class)；4三运行上述代码，其结果如图8-7所示。use db_database08 e changed create table tb_student3(id int auto_increnent,name uarchar not null,sex uarchar,classid int not null,birthday date,renark uarchar,primary key,CHECKCclassid IN 0K,0 rows affected 图8-7对表实施CHECK约束仁口8.2命名完整性约束二;二在MySQL中，也可以对完整性约束进行添加、修改和删除等操作。其中，为了删除和修改完整.性约束，需要在定义约束的同时对其进行命名。命名完整性约束的方式是在各种完整性约束的 定义说明之前加上CONSTRAINT子句实现的。CONSTRAINT子句的语法格式如下：.CONSTRAINT PRIMAR KEY 短语|FOREIGN KEY 短语|CHECK 短语.参数说明如下：symbol：用于指定约束名称。这个名字是在完整性约束说明的前面被定义，在数据库里必须是.唯一的。如果在创建时没有指定约束的名字，贝UMySQL将自动创建一个约束名字。PRIMAR K EY短语：主键约束。.FOREIGN K EY短语：参照完整性约束。CHECK 短语：CHECK约束。说明；在MySQL中，主键约束名称只能是PRIMARY。例如，对雇员表添加主键约束，并为其命名为PRIMARY,可以使用下面的代码。ALTER TABLE 雇员表 ADD CONSTRAINT PRIMARY PRIMARY KEY（雇员编号）仁口【例8-8】修改例8-4的代码，重新创建学生信息表tb_studentl,命名为tb_studentla,并为其参照完整性约束命名，代码如下：create table tb_studentla(id int auto_increment PRIMARY KEY,name varchar(30)not null,sex varchar(2),classid int not null,birthday date,remark varchar(lOO),CONSTRAINT ik_classid FOREIGN KEY(classid)REFERENCES tb_class(id)ON DELETE RESTRICTON UPDATE RESTRICT)；仁口运行上述代码，其结果如图8-8所示。nysql use db_database08Database changed m create table tb_studentla id int auto-increment PRIMARY KEY,-name varchar not null,-sex uarchar,classid int not null,-birthday date,-renark uarchar,-CONSTRAINT fk_classid F OREIGN KEY classid-REF ERENCES tb_class-ON DELETE RESTRICT-ON UPDATE RESTRICT-；Query OK,0 rows affected mysql).图8-8命名完整性约束说明：在定义完整性约束时，应该尽可能为其指定名字，以便在需要对完整性约束进行修改或删除时,可以很容易的找到它们。注意：只能给基于表的完整性约束指定名字,“无法给基于洌的完整性约束指定名字。仁m，二二&更新完整性约束1删除完整性约束2修改完整性约束,”I，.”，8.3.1删除完整性约束 在MySQL中，使用AL TER TABL E语句，可以独立地删除完整性约束，而不会删除表本身。如果使.用DROP TABL E语句删除一个表，那么这个表中的所有完整性约束也会自动被删除。删除完整性 约束需要在AL TER TABL E语句中使用DROP关键字来实现，具体的语法格式如下：.DROP FOREIGN KEY|INDEX|(PRIMARY KEY参数说明：.FOREIGN K EY：用于删除外键约束。PRIMARY K EY：用于删除主键约束。需要注意的是：在删除主键时，必须再创建一个主键，否则 不能删除成功。INDEX：用于删除候选键约束。*symbol：要删除的约束名称。【例8-9】要删除例8-8中的名称为fk_classid的外键约束，可以使用下面的代码。ALTER TABLE tb_studentla DROP FOREIGN KEY fk_classid;运行上述代码，其结果如图8-9所示。mysql ALTER TABLE tb_studentla DROP F OREIGN KEY k_classid；Query OK,0 rows affected Records：0 Duplicates：0 Warnings：0musal _图8-9删除名称为fk_classid的外键约束8.3.2修改完整性约束在MySQL中，完整性约束不能直接被修改，若要修改只能是用AL TER TABL E语句先删除除该约束,然后再增加一个与该约束同名的新约束。由于删除完整性约束的语法在8.3.1节已经介绍了，这里只给出在AL TER TABL E语句中添加完整性约束的语法格式。具体语法格式如下：ADD CONSTRAINT 各种约束参数说明：symbol：为要添加的约束指定一个名称。各种约束：定义各种约束的语句，具体内容请参见8.1和8.2节介绍的各种约束的添加语法。【例8-10】更新例8-8中的名称为fk_classid的外键约束为级联删除和级联更新，可以使用下面的代码。ALTER TABLE tb_studentla DROP FOREIGN KEY fk_classid;ALTER TABLE tb_studentlaADD CONSTRAINT fk_classid FOREIGN KEY(classid)REFERENCES tb_class(id)ON DELETE CASCADEON UPDATE CASCADE仁口运行上述代码，其结果如图8-10所示。mysql use db_database08 Database changed mysql create table tb_studentla id int auto_inci*enent,-name uarchai*not null,-sex uai*chai*,-classid int not null,-birthday date,-remark uarchar,-CONSTRAINT pk_id PRIMARY KEY,-CONSTRAINT fk_classid FOREIGN KEY -REFERENCES tb_class-ON DELETE RESTRICT-ON UPDATE RESTRICTQuery OK,0 rows affected mysql ALTER TABLE tb_studentla DROP FOREIGN KEY fk_classid；Query OK.0 rows affected Records：0 Duplicates：0 Warnings：0mysql ALTER TABLE tb_studentla-ADD CONSTRAINT fk_classid FOREIGN KEY-REFERENCES tb_class-ON DELETE CASCADE-ON UPDATE CASCADE-；Query OK.0 rows affected Records：0 Duplicates：0 Warnings：0mysql.图8-1 0更新外键约束8.4综合实例在创建表时添加命名外键完整性约束在本实例中，首先创建的一个图书类别信息表，然后再创建一个图书信息表，并为图书信息表设 置命名外键约束，实现删除参照表中的数据时，级联删除图书信息表中相关类别的图书信息，具体步 骤如下：(1)创建名称为tb_type的图书类别信息表，具体代码如下：CREATE TABLE tb_type(id int(ll)NOT NULL AUTOJNCREMENT,name varchar(45)DEFAULT NULL,remark varchar(lOO)DEFAULT NULL,PRIMARY KEY(id)；(2)创建不添加任何外键的教材信息表tb_teacher,代码如下:Create table tb_book(id int(11)not null primary key auto_increment,name varchar(20)not null,publishingho varchar(20)not null,author varchar(20),typeid int(l 1),CONSTRAINT fk_typeidFOREIGN KEY(typeid)REFERENCES tb_type(id)ON DELETE CASCADEON UPDATE CASCADE)；仁口运行结果如图8T1所示。njF sql use db_database08Database changed nysql CREATE TABLE tb_type id NOT NULL AUTO.INCREMENT,-name uarchar DEF AULT NULL.-remark uarchar DEF AULT NULL,-PRIMARY KEV-；Query OK,0 rows affected Create table tb_bookid int not null primary key auto_increment,-name uai*char not null-publishingho uarchar not null-author varchar,-ti/peid int,-CONSTRAINT fk.typeid-F OREIGN KEY typeid-REF ERENCES tb_type-ON DELETE CASCADE-ON UPDATE CASCADE-；Query OK.0 rows affected 图8-1 1在创建表时添加命名外键完整性约束 g知销点梃落(1)在关系模型中，提供了实体完整性、参照完整性和用户定义的完整性等三项规则。(2)实体(Entity)是一个数据对象，是指客观存在并可以相互区分的事物，例如，一个教师、个学生、一个雇员等。一个实体在数据库中表现为表中的一条记录。(3)实体完整性规则(Entity Integrity Rule)是指关系的主属性，即主码(主键)的组成不能 为空，也就是关系的主属性不能是空值(NUL L)。(4)主键可以是表中的某一列，也可以是表中多个列所构成的一个组合；其中，由多个列组合而成 的主键也称为复合主键。(5)在MySQL中，可以在CREATE TABL E或者AL TER TABL E语句中，使用PRIMARY K EY子句来创建主键 约束。(6)候选键可以在CREATE TABL E或者AL TER TABL E语句中使用关键字UNIQUE来定义。(7)参照完整性规则(Referential Integrity Rule)就是定义外码(外键)和主码(主键)之间 的引用规则，它是对关系间引用数据的一种限制。(8)命名完整性约束的方式是在各种完整性约束的定义说明之前加上CONSTRAINT子句实现的。(9)在MySQL中，完整性约束不能直接被修改，若要修改只能是用AL TER TABL E语句先删除除该约束,然后再增加一个与该约束同名的新约束。仁口行散0 1-1简述在MySQL中，主键列必须遵守的规则。.1-2简述候选键与主键之间的区别。，1-3参照完整性的定义是什么。.1-4简述设置外键时，通常需要遵守的规则。仁口