考点8 数据完整性约束《数据库原理与应用技术-MySQL》河北省（对口招生）计算机类 知识点讲解

河北省(计算机类)对口招生 《数据库原理与应用技术-MySQL》复习讲义 模块3 MySQL数据库管理 考点 8 数据完整性约束 【考纲要求】 1.理解实体完整性、参照完整性、用户自定义完整性的概念； 2.掌握主键、候选键、外键的概念； 3.掌握完整性约束的命名： CONSTRAINT; 4.掌握用户定义的完整性； 5.掌握删除约束命令： DROP TABLE、 ALTER TABLE<表 名 > DROP {FOREIGN KEY|PRIMARY KEY}、ALTER TABLE<表名>DROP{约束名|候选键字段名}; 6.掌握添加约束的相关命令：CREATE TABLE、ALTER TABLE<表名>ADD [CONSTRAINT <约束名>]PRIMARY KEY ( 主键字段)、ALTER TABLE<表名>ADD [CONSTRAINT< 约束名>] FOREIGN KEY(外键字段名)REFERENCES 被参照表(主键字段名)、ALTER TABLE<表名>ADD [CONSTRAINT<约束名>]UNIQUE KEY ( 字段名)。 【知识网络】 【知识和技能要点】 一、三大完整性概念 数据库完整性是指保证数据库中数据的正确性、有效性、相容性，防止不合语义的数据存入数据库。数据库完整性分为实体完整性、参照完整性、用户定义完整性三类。 1.实体完整性 （1）概念：保证数据表中的每一条记录都是唯一可区分的实体，表中不能存在完全重复的行，主键用来实现实体完整性。 （2）规则：主键字段的值不能为 NULL、不能重复。 （3）实现：主键约束 PRIMARY KEY。 （4）应用：学生表中 sid 学生编号作为主键，每个学生编号唯一、不能为空，区分每一位学生记录。 2.参照完整性（引用完整性） （1）概念：描述两个表之间的引用关系，外键字段的值必须参照另一个表的主键；外键的值要么取被参照表主键中已经存在的值，要么取 NULL。 （2）规则：外键字段取值，允许为空；如果不为空，则必须等于被参照表主键已经存在的值，不能出现不存在的编号。 （3）实现：外键约束 FOREIGN KEY。 （4）应用：成绩表 score 中的sid是外键，参照学生表 student 的主键 sid；成绩表里学生编号，只能是学生表里已经存在的学生编号，或者为空，不能写一个学生表不存在的学号。 3.用户定义完整性（自定义完整性） （1）概念：由用户根据业务实际需求，自己定义的业务规则，实体完整性、参照完整性是系统固有规则，用户定义完整性是针对具体业务额外增加的约束。 （2）实现：非空NOT NULL、唯一UNIQUE KEY、默认值DEFAULT、检查约束等。 （3）应用：学生性别只能填男 / 女；成绩取值 0100；年龄大于 0。 二、主键、候选键、外键的概念 1. 候选键（候选码） 可以唯一标识表中每一行记录的一个或者一组字段；候选键的值不能重复，也不能为 NULL。 一张表可以有多个候选键。 例子：学生表，学号、身份证号，都可以唯一确定一个学生，二者都属于候选键。 2. 主键（主码 PRIMARY KEY） 在多个候选键中，人为选定其中一个候选键作为主键。 特点：①值唯一；②不允许 NULL；③一张表只能有一个主键，主键可以是单个字段，也可以是多个字段组合（复合主键）。 作用：用来实现实体完整性。 3. 外键（外码 FOREIGN KEY） 如果表 A 中的某个字段，引用了另外一张表 B 的主键，那么这个字段在 A 表中就称为外键。A 表称为参照表（子表）；B 表称为被参照表（父表）。 特点：外键自身不一定是本表主键；外键可以为 NULL；非 NULL 值必须来自父表主键的值。 作用：实现参照完整性。 3、 完整性约束命名 CONSTRAINT 关键字CONSTRAINT作用：给约束起自定义名字。 （1）好处：给约束命名之后，后续删除约束的时候，可以直接使用约束名删除；如果不写CONSTRAINT，MySQL 自动生成随机约束名，删除时不方便。 （2）使用位置： ①建表CREATE TABLE内部定义约束； ②ALTER TABLE ADD追加约束的时候。 （3）语法格式：CONSTRAINT 约束名 约束类型(字段) 4、 添加完整性约束的命令 有两种方法：（1）建表时 CREATE TABLE 直接定义约束；（2）表已经建好，用 ALTER TABLE ADD 追加约束。 1.CREATE TABLE 建表时定义约束 可以在建表字段后面写列级约束，也可以在字段列表之后写表级约束（支持 CONSTRAINT 命名，适合主键、外键、唯一键）。 语法格式： CREATE TABLE student( sid INT, sname VARCHAR(20), CONSTRAINT pk_stu_sid PRIMARY KEY(sid) ); 2. ALTER TABLE ADD 添加约束 （1）ALTER TABLE ADD 添加主键约束 语法格式：ALTER TABLE 表名 ADD [CONSTRAINT 约束名] PRIMARY KEY (主键字段); （2）ALTER TABLE ADD 添加外键约束 语法格式： ALTER TABLE 表名 ADD [CONSTRAINT 约束名] FOREIGN KEY(外键字段名) REFERENCES 被参照表(主键字段名); （3） ALTER TABLE ADD 添加唯一（候选键）约束 语法格式：ALTER TABLE 表名 ADD [CONSTRAINT 约束名] UNIQUE KEY (字段名); 5、 删除完整性约束命令 1. 删除主键约束 语法格式：ALTER TABLE 表名 DROP PRIMARY KEY; 说明：一张表只有一个主键，不需要写约束名字，直接DROP PRIMARY KEY。 2. 删除唯一约束（UNIQUE KEY） 语法格式：ALTER TABLE 表名 DROP INDEX 约束名; 3. 删除外键约束 FOREIGN KEY 必须使用约束名删除，在建表 / 添加时通过 CONSTRAINT 定义的名称。 语法格式：ALTER TABLE 表名 DROP FOREIGN KEY 约束名; 【练习题】 一、填空题 1.数据库三大完整性分别是实体完整性、________、用户定义完整性。 2.实体完整性依靠________约束实现。 3.参照完整性依靠________约束实现。 4.________可以唯一标识表中每一行记录，一张表可以存在多个。 5.一张数据表最多可以有________个主键。 6.________完整性保证数据表当中每一条记录是唯一可区分。 二、选择题 1.下列不属于数据库三大完整性的是（ ） A. 实体完整性 B. 参照完整性 C. 更新完整性 D. 用户定义完整性 2.关于实体完整性规则描述正确的是（ ） A. 主键可以为 NULL，允许重复值 B. 主键字段不能为 NULL，不允许出现重复值 C. 一张表可以设置多个主键实现实体完整性 D. 实体完整性用来维护多张表之间引用关系 3.关于参照完整性外键取值规则，说法正确的是（ ） A. 外键绝对不允许为 NULL B. 外键非空值必须等于被参照表主键已存在的值，允许取 NULL C. 外键的值可以随意填写不受限制 D. 外键的值必须和本表主键保持一致 4.下列属于用户定义完整性的是（ ） A. 主键非空唯一 B. 外键引用父表主键 C. 规定成绩字段取值范围 0100 D. 主键区分每一条实体记录 5.下面关于候选键描述正确的是（ ） A. 一张表只能有一个候选键 B. 候选键可以唯一标识表中每一行记录，值不能重复、不能为 NULL C. 候选键的值允许重复 D. 候选键的值可以为 NULL 6.关于外键，子表（参照表）和父表（被参照表）关系描述正确的是（ ） A. 外键字段引用子表的主键 B. 外键字段引用被参照父表的主键 C. 外键必须是本表的主键 D. 外键的值不允许为 NULL 7.如果创建约束时没有写 CONSTRAINT 自定义约束名，MySQL 会（ ） A. 不创建该约束 B. 自动生成随机约束名称 C. 约束名称为空 D. 直接报错无法创建 8.复合主键指的是（ ） A. 一张表设置两个独立主键 B. 多个字段组合在一起共同作为主键 C. 主键同时又是外键 D. 候选键同时充当主键 9.下面不属于主键特点的是（ ） A. 字段值不能重复 B. 不允许为 NULL C. 一张表可以设置多个主键 D. 可以是多个字段组成复合主键 10.主键的来源是（ ） A. 随意指定任意字段 B. 从多个候选键当中选定一个 C. 必须由多个字段组合而成 D. 必须是数值类型字段 3、 判断题 1. 数据库完整性作用是保证数据的正确性、有效性、相容性，阻止不合语义数据入库。（） 2. 实体完整性要求主键字段不能为 NULL，并且不能存在重复值。（） 3. 参照完整性用来维护同一张表内部各行记录的唯一性。（） 4. 限制成绩字段 0100 取值范围属于用户定义完整性。（） 5. 候选键要求字段不能重复，也不能为 NULL。（） 6. 一张数据表可以同时设置多个 PRIMARY KEY 主键约束。（） 7. 主键只能够由单个字段构成，不能使用多个字段组成复合主键。（） 8. CONSTRAINT 关键字既可以在 CREATE TABLE 建表时使用，也可以在 ALTER TABLE ADD 添加约束时使用。（） 9. 删除主键约束命令 ALTER TABLE 表名 DROP PRIMARY KEY;，不需要书写约束名称。（） 10. NOT NULL 非空约束属于用户定义完整性的实现方式。（） 四、命令题 1.创建学生表student，字段：sid 整型，sname VARCHAR (20)；使用表级约束方式，自定义约束名pk_stu_sid，将 sid 设置为主键。 2.数据表student已经存在，表内 sid 字段适合作为主键。写出 MySQL命令，追加主键约束，约束命名为pk_stu_sid。 3.已有学生表student(sid 为主键)，成绩表score存在字段 sid；请为 score 表的 sid 字段添加外键约束，约束名fk_score_sid，参照 student 表的 sid 主键。 4.已有学生表 student，idcard 字段存储身份证号，要求身份证号不能重复；追加唯一约束，约束命名uk_stu_idcard。 5.删除 student 表的主键约束。 【答案】 一、填空题 1.参照完整性 2. PRIMARY KEY（主键） 3. FOREIGN KEY（外键） 4. 候选键（候选码） 5. 1 6. 实体 二、选择题 1. C 2. B 3.B 4.C 5. B 6. B 7. B 8. B 9. C 10. B 三、判断题 1. √ 2. √ 3. × 4. √ 5. √ 6. × 7. × 8. √ 9.√ 10. √ 四、命令题 1.CREATE TABLE student( sid INT, sname VARCHAR(20), CONSTRAINT pk_stu_sid PRIMARY KEY(sid) ); 2.ALTER TABLE student ADD CONSTRAINT pk_stu_sid PRIMARY KEY(sid); 3.ALTER TABLE score ADD CONSTRAINT fk_score_sid FOREIGN KEY(sid) REFERENCES student(sid); 4. ALTER TABLE student ADD CONSTRAINT uk_stu_idcard UNIQUE KEY(idcard); 5. ALTER TABLE student DROP PRIMARY KEY; 原创精品资源学科网独家享有版权，侵权必究 学科网（北京）股份有限公司 学科网（北京）股份有限公司 $