项目6 MySQL数据库完整性（课件）-《数据库MYSQL》同步教学（河北科技出版社）

数据库原理与应用技术MySQL 项目6 数据库完整性 1 数据库完整性 任务1 实体完整性 任务2 参照完整性 任务3 用户自定义的完整性 任务4 完整性约束命名子句 1 任务1 实体完整性 子任务1 实体和实体完整性概念 子任务2 实体完整性的实现 1 子任务1 实体和实体完整性概念 实体（Entity）是一个数据对象，是指客观存在并可以相互区分的事物，如一个教师、一个学生或一个雇员等。一个实体在数据库中表现为表中的一条记录。通常情况下，它必须遵守实体完整性规则。 元组（tuple）是关系数据库中的基本概念，关系是一张表，表中的每行（即数据库中的每条记录）就是一个元组，每列就是一个属性。在二维表里，元组也称为行。 字段（field）表示与对象或类关联的变量。在数据库中，大多数时，表的“列”称为“字段”，每个字段包含某一专题的信息。就像“通讯录”数据库中，“姓名”，“联系电话”这些表中所有行共有的属性，所以把这些列称为“姓名”字段和“联系电话”字段。 1 子任务1 实体和实体完整性概念 1、实体完整性 关系数据库中每个元组应该是可区分的，是唯一的。这样的约束条件用实体完整性来保证。 实体完整性（Entity Integrity）指的是表中行的完整性。要求表中的所有行都有唯一的标识符，称为主关键字。主关键字是否可以修改，或整个列是否可以被删除，取决于主关键字与其他表之间要求的完整性。 1 子任务1 实体和实体完整性概念 2、实体完整性规则 基本关系中的所有主关键字（或主键）对应的主属性都不能取空值(null value)。所谓空值就是“不知道”或“不存在”或“无意义”的值。例如，学生选课的关系表（学号，课程号，成绩）中，学号和课程号共同组成为主关键字，则学号和课程号两个属性都不能为空。因为没有学号的成绩或没有课程号的成绩都是不存在的。 1 子任务1 实体和实体完整性概念 3、实体完整性规则如下： 实体完整性规则是针对基本关系而言的。一个基本关系表通常对应一个实体集。例如，学生关系对应学生集合。 现实世界中的实体是可以区分的，它们具有一种唯一性质的标识。例如，学生的学号，教师的职工号等。 在关系模型中，主关键字作为唯一的标识，且不能为空。 1 子任务2 实体完整性的实现 约束（restrain）： 是作用于表中字段上的规则，用于限制存储在表中的数据，目的是为了保证数据库中数据的正确性，有效性和完整性。我们可以通过主键约束和唯一约束来实现实体完整性。 1 子任务2 实体完整性的实现 1、主键约束(PRIMARY KEY) 主键约束是指在表中定义一个主键来唯一确定表中每一行数据的标识符。在每一个表中，只能有一个列被设置为主键。通常用来标记表中数据的唯一存在，主键约束要求当前的列值是唯一存在的且不能重复且不能为空值。 1 (1)创建表时添加主键： 语法格式如下 CREATE TABLE 表名（字段1 数据类型 … ） PRIMARY KEY(约束列) 1 【例6-1】 创建数据表STUDENT_1,共包含stuid,name,ssex三个字段，并将stuid字段设置为主键并查看表结构。 CREATE TABLE STUDENT_1( stuid int,name varchar(20), ssex varchar(10), PRIMARY KEY(stuid) ) 1 子任务2 实体完整性的实现 主键约束(PRIMARY KEY) 我们设置主键的方法其实有两种： 一种方法是可以通过创建表时通过命令设置主键； 另一种方法如果是已创建的表也可以通过设计表的时候点击“主键”选项卡进行主键的添加和修改。 1 子任务2 实体完整性的实现 主键约束(PRIMARY KEY) 已创建的表除了可以通过设计表时进行添加主键，当未设置主键时还可以通过修改表结构进行添加主键，如若表中已有主键，那么不能设置第二个主键。因为主键保证记录的唯一性，它唯一地标识了每一条数据。在将某个字段指定为主键后，该字段自动为非空即不允许存在空值，并且每张数据表只能存在一个主键，该字段不能为空值，不允许表中任何两条记录在该字段上有相同的值。一个数据表最多可以创建一个主键。 1 （2）为已创建的表添加主键， 语法格式如下： ALTER TABLE 表名 ADD [CONSTRAINT <约束名>] PRIMARY KEY（约束列） 【例6-2】 在已创建的数据表teacher中，把Teaid设置为主键，再查看表结构的变化。 ALTER TABLE teacher ADD PRIMARY KEY(Teaid) 1 （3）删除主键约束 语句格式： ALTER TABLE 表名 [CONSTRAINT <约束名>] DROP PRIMARY KEY 【例6-3】 把teacher表中已设置好的Teaid主键删除。 ALTER TABLE teacher DROP PRIMARY KEY 1 （4)联合主键（添加多列主键） 所谓的联合主键，就是这个主键是由一张表中多个字段组成的。当主键是由多个字段组成时，不能直接在字段名后面声明主键约束；一张表只能有一个主键，联合主键也是一个主键。 语法格式如下： ALTER TABLE 表名 ADD [CONSTRAINT <约束名>] PRIMARY KEY (字段1，字段2，…，字段n) 1 【例6-4】 在数据表student中，添加Stuid和Stuname这两个字段为联合主键（多字段主键）。此时数据表student表中仍然只有一个主键，只是这个主键由两个字段组合而成。 ALTER TABLE student ADD PRIMARY KEY(Stuid, Stuname) 1 子任务2 实体完整性的实现 Stuid,Stuname这两个字段组合起来成为student表的主键，与仅有Stuid一个字段作为主键的方式相比，这种组合形式生成的主键比仅有单一字段做主键更加灵活。例如，在student表中，以Stuid为主键，那么Stuid字段则不允许出现任意两个记录在该字段上的值重复；如果以Stuid和Stuname两个字段形成复合主键，则Stuid字段就允许出现两个或多个记录在该字段上的值重复，但不允许任何两条记录在Stuid和Stuname两个字段上都重复。 1 子任务2 实体完整性的实现 2.唯一约束（UNIQUE KEY） 由于一张表中只能有一个主键，但是对于某些字段，例如身份证号这样的字段，肯定也是唯一的，我们又不能将其设置为主键（身份证字段属于有意义的字段），那么怎么保证它的唯一性呢？这就是唯一约束。 唯一约束是为表中的某一列添加唯一约束。唯一约束与主键类似。为了列中所有数据各不相同。唯一约束可以保证记录的唯一性，任何两行都不能有相同的列值，唯一约束的字段可以为空值但该列中空值只能出现一次，而且一个表中可以为多个列设置唯一约束。 1 (1)创建表时设置唯一约束 语法格式如下 CREATE TABLE 表名 （ … ）[CONSTRAINT <约束名>] UNIQUE KEY（约束列） （UNIQUE KEY语句在建表的时候直接写到该设置唯一约束的字段后也可以） 1 【例6-5】 创建数据表STUDENT_2,共包含stuid,name,idcard三个字段，并将idcard字段设置为唯一约束。 ALTER TABLE student ADD PRIMARY KEY(Stuid, Stuname) 1 （2）对已经存在的表设置和删除唯一约束 设置唯一约束 语法格式如下： ALTER TABLE 表名[CONSTRAINT <约束名>] ADD 约束名 UNIQUE KEY(约束列) 删除唯一约束 语法格式如下： ALTER TABLE 表名 [CONSTRAINT <约束名>] DROP INDEX UNIQUE(约束列) 1 【例6-6】 给数据表student中的Stuid字段添加唯一约束。 【例6-7】 给数据表student中的Stuid字段和Stuname字段添加唯一约束。 1 【例6-8】 创建数据表STU,在表中添加id和phone字段，并把phone设置为唯一约束，然后删除唯一约束。 1 设置唯一约束 在设置约束的时候可以给所添加的约束取名字，以唯一约束为例，我们可以对比看看有无约束名字的语法格式的区别。 没有设置约束名字时： CREATE TABLE STU(id int,name VARCHAR(20),phone VARCHAR(20) UNIQUE) 这条语句只是确定了phone列设置为了唯一约束，但并没有起约束名。 ALTER TABLE STU ADD CONSTRAINT PG UNIQUE (phone) 此时设置phone为唯一约束，并取名为PG。 1 任务2 参照完整性 子任务1 参照完整性 子任务2 参照完整性的实现 1 子任务1 参照完整性 参照完整性：数据库的参照完整性是指表与表之间的一种对应关系，一般是通过MySQL外键(foreign key)实现的。有对应参照完整性的两张表格，在对他们进行数据插入、更新、删除的过程中，系统都会将被修改表格与另一张对应表格进行对照，从而阻止一些不正确的数据的操作。 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。参照完整性又称引用完整性。参照完整性是关系模型的完整约束之一，属于数据完整性的一种。 参照完整性属于表间规则。对于永久关系的相关表，在更新、插入或删除记录时，如果只改其一不改其二，就会影响数据的完整性：例如修改父表中关键字值后，子表关键字值未做相应改变；删除父表的某记录后，子表的相应记录未删除，致使这些记录成为孤立记录；对于子表插入的记录，父表中没有相应关键字值的记录等等。对于这些涉及表间数据的完整性，统称为参照完整性。 1 子任务1 参照完整性 1、参照完整性规则： 若某一属性或属性组F是基本表R（关系）的外键，它与另一个基本表S（或本表）的主键相对应，则对于基本表R中的每个元组在属性组F上的值必须为： （1）空值，属性组F的每个属性值均为空值； （2）S基本表中某个元组的主键值； 即参照的关系中的属性值必须能够在被参照关系中找到或者取空值，否则不符合数据库的语义。在实际操作时如更新、删除、插入一个表中的数据，通过参照引用相互关联的另一个表中的数据，来检查对表的数据操作是否正确，不正确则拒绝操作。 1 子任务1 参照完整性 2、引用关系 同一关系 在参照完整性规则中，R和S可以是同一个关系。 在学生(学号，姓名，性别，专业，地址，班长)关系表中，“学号”属性是主键，“班长”属性表示该学生所在班级的班长的学号，因此它应用的本关系“学号”属性。按照参照完整性规则，“班长”属性可以取以下两类值: (1)空值，表示该学生所在班级尚未选出班长。 (2)非空值，该值必须是本关系表中某个元组的学号值，即某个当选班长的学生的学号值。 1 子任务1 参照完整性 2、引用关系 两个关系间 若学生实体和专业实体可以用下列关系模式来表示，其中学号是学生的主键，专业号是专业的主键： 学生成绩表(学号，专业号，教师号，成绩) 专业表(专业号，专业名，课时) 1 子任务1 参照完整性 2、引用关系 以上这两个关系之间存在着属性的引用(含有相同的属性“专业号”)，学生成绩关系引用了专业关系的主键“专业号”，专业号则是学生关系的外键。而且按照参照完整性规则，学生关系(并非专业关系)中的每个元组的“专业号”属性只能取两种值： (1)空值，表示尚未给学生分配专业。 (2)非空值，这时该值必须是专业关系中某个元组的“专业号”值，表示该学生不可能分配到一个不存在的专业中去。就是说学生关系中的某个属性的取值需要参照专业关系的属性取值。 1 子任务2 实体完整性的实现 2、引用关系 两个以上的关系 两个以上的关系也可以存在引用关系，学生表、课程表、学生成绩表(学生与课程成绩)之间的联系可以用下列三个关系来表示: （1）学生表(学号，姓名，性别，专业，教师姓名，地址) （2）专业表(专业号，专业名，课时) （3）学生成绩表(学号，专业号，教师号，成绩) 1 子任务1 参照完整性 1 子任务1 参照完整性 2、引用关系 这里的“学号”和“专业号”属性都是学生成绩关系的外键，但只有“学号”和“专业号”联合起来才能确定学生成绩的关系，所以这里存在一个复合主键关系，(学号，专业号)是成绩关系的主键。虽然按照参照完整性可以取两类值，但是由于实体完整性，复合主键中的每个属性均不能取空值。所以成绩关系中的“学号”和“专业号”属性实际上只能取被参照关系(学生关系和专业关系)中已经存在的主键值，即成绩关系中的“学号”值必须是确实存在的学生的学号，学生关系中有该学生的记录；成绩关系中的“专业号”值也必须是确定存在的专业的专业号，即专业关系中有该专业的记录。 1 子任务2 实体完整性的实现 1、外键约束 外键就是关键字，如果公共关键字在一个关系表中是主关键字，那么这个公共关键字被称为另一个关系表的外键。如果表A中的主键是表B中的字段，则该字段称为表B的外键；另外，表A称为主表，表B称为从表。外键表示了两个关系表之间的相关联系。外键能够保持数据一致性、完整性，主要目的是控制存储在外键表中的数据。 外键的作用是能够保持数据一致性，完整性，主要目的是控制存储在外键表中的数据，使两张表形成关联，外键只能引用外表中的列的值或使用空值。一个数据表中可以有多个外键。 1 (1)创建表时添加外键约束 语法格式如下 CREATE TABLE 表名（ 列名 数据类型； … [CONSTRAING][外键名称] FOREIGN KEY {外键列名} REFERENCES 主表{主表列名} ） 1 【例6-9】 创建STUDENT_3(学号，姓名，性别，地址)关系表和STUDENT_4(学号，专业号，班委职务)，并把学号设置为外键。 1 (2)建完表后，修改表结构时添加外键约束 语法格式如下 ALTER TABLE 表名 ADD [CONSTRAINT 外键名称] FOREING KEY (外键字段名称) REFERENCES 主表名称（主表列名称） 1 【例6-10】 修改score表结构，并给它的Stuid字段添加外键约束和student表的主键Stuid字段连接起来。 1 (3)删除外键约束 语法格式如下 ALTER TABLE 表名DROP FOREIGN KEY 外键名称 【例6-11】 删除score表的外键fk_sc_stu 1 (3)删除外键约束 （1）从以上实例可以看出，我们在进行外键约束设置的时候，既可以添加外键名称，也可以不添加。 （2）我们不仅可以通过外键约束建立两个表的连接，还可以建立两个表以上的连接，自己可以动手尝试。 1 任务3 用户定义的完整性 子任务1 用户自定义的完整性 子任务2 默认值约束 1 子任务1 用户自定义的完整性 用户自定义完整性是根据用户实际的完整性要求来定义，在执行数据插入、更新等操作时，DBMS会检查数据是否满足限定条件，避免不符合条件的操作，以确保数据存储的准确性。它包含非空约束，自增约束和默认值约束等。 1 子任务1 用户自定义的完整性 1、非空约束（not null） 非空约束：属于域完整性约束，字段（列）的值不能为空，但可以重复； （1）在建表时添加非空约束 语法格式： CREATE TABLE 表名（ 列名 数据类型 NOT NULL ） 1 【例6-12】 创建数据表student5,添加姓名字段，姓名字段不能为空。 此时student5数据表内的姓名字段不能取空值，若取空值则会出现错误。 1 子任务1 用户自定义的完整性 1、非空约束（not null） 给已建立的数据表添加非空约束，有两种方法：第一种是通过命令设置；第二种是在设计表时设置 （2）为已创建的表添加非空约束 语法格式： ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 NOT NULL 设计表时右击student表，在弹出的快捷菜单中选择设计表选项，然后Stuname字段的不是null选项单击，选择或者取消非空约束。 1 【例6-13】 给已建立的student数据表的Stuname字段设置非空约束。 1 子任务1 用户自定义的完整性 （3）在建表之后取消非空约束 语法格式： ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 【例6-14】 取消student数据表的Stuname字段的非空约束。 1 子任务2 默认值约束 默认值约束（DEFAULT）：给某个字段（某列）指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显示赋值，则赋值为默认值。 （1）建表时添加默认值约束 语法格式： CREATE TABLE 表名（ 字段名1 数据类型，字段名2 数据类型， … 字段n,数据类型 DEFAULT 字段默认值 ） 1 【例6-15】 建立一个成绩表score_2，包含字段id ,stuname,sco,给sco字段添加默认值为0。 由此可见，当给成绩sc字段设置默认值后，意思为当该学生没有给成绩赋值的时候，则默认为0分。 1 (2)在建表之后，添加默认值约束 语法格式如下 ALTER TABLE 表名 MODIFY 字段名 数据类型 DEFAULT 默认值 【例6-16】 给已建立的数据表course的classhour字段设置默认值约束，若未赋值，则默认40课时。 1 (2)删除默认值约束 语法格式如下 ALTER TABLE 表名 MODIFY 字段名 数据类型 【例6-17】 删除course数据表的classhour字段的默认值约束。 1 (2)删除默认值约束 非空约束和默认值约束的添加和删除方式类似，如果需要删除则把约束语句删掉即可。 在添加非空约束或默认值约束后可以在数据表中输入相应数据观察表结构的变化。 1 任务4 完整性约束命名子句 子任务1 完整性约束命名子句 1 子任务1 完整性约束命名子句 完整性约束命名子句：我们通过前三个任务的学习，了解了约束，在MySQL中还可以使用完整性约束命名子句CONSTRAINT，来对完整性约束条件命名，从而可以灵活地增加一个完整性约束条件。 1 子任务1 用户自定义的完整性 1、完整性约束条件 完整性约束条件包括主键约束PRIMARY KEY ,外键约束FOREIGN KEY,非空约束NOT NULL,唯一约束UNIQUE,默认值约束DEFAULT短语等。 ； （1）完整性约束命名子句 语法格式： CONSTRAINT <完整性约束条件名><完整性约束条件> 1 【例6-18】 新建一个学生表STUDENT_6,要求学号stuid字段为主键，姓名stuname字段不能取空值，成绩sc字段默认为0,phone字段为唯一约束。 完整性约束的命名可以通过命令进行添加，完整性约束的删除可以通过DROP语句，也可以直接不加约束子句再执行一遍命令可以删除。 1 本项目主要介绍了实体完整性，参照完整性，用户自定义的约束类型和完整性约束命名子句。通过本项目的学习，能掌握数据库完整性的概念，了解实体完整性，参照完整性的概念和规则，约束类型以及约束的添加和修改的方式，对完整性约束的命名子句灵活的学以致用，结合上机操作对本项目有深刻的理解。 1 $$