《数据库原理与应用技术-MySQL》MySQL数据库完整性（1）（举一反三考点练）-讲义

举一反三考点练 《数据库原理与应用技术-MySQL》MySQL数据库完整性-讲义 1. 理解实体完整性的概念与实现 2. 熟悉参照完整性的概念与外键实现 3. 掌握用户定义完整性 知识点一 实体完整性的概念与实现 1.基本术语 （1）实体：客观可区分的事物，对应表中的一行（元组）。 （2）元组：表的一行；字段：表的一列。 （3）实体完整性规则：任何基本关系的主属性（构成主键的列）不得取 NULL，保证行可唯一区分。 2.主键约束（PRIMARY KEY） （1）特征：唯一 + 非空；一张表仅允许一个；可单字段或复合。 （2）创建方式： ① 建表时：CREATE TABLE t(id INT PRIMARY KEY, …); ② 已存在表：ALTER TABLE t ADD PRIMARY KEY(id); ③ 复合主键：ALTER TABLE t ADD PRIMARY KEY(id, name); （3）删除：ALTER TABLE t DROP PRIMARY KEY;（无需名字，因唯一）。 3.唯一约束（UNIQUE） （1）特征：值唯一，但允许一个 NULL；一表可多个。 （2）场景：身份证号、手机号等业务唯一且非主键列。 （3）创建方式： ① 建表：idcard VARCHAR(30) UNIQUE 或 CONSTRAINT uk_idc UNIQUE(idcard) ② 追加：ALTER TABLE stu ADD UNIQUE(idcard); （4）复合唯一：ALTER TABLE employee ADD UNIQUE(ikk_username, username); （5）删除：ALTER TABLE stu DROP INDEX 约束名;（需知道名字，可用 SHOW INDEX 查看）。 4.主键 vs 唯一 （1）主键 = 唯一 + 非空 + 一表一个；唯一可空、可多列组合、可多套。 （2）主键常被外键引用，唯一约束不能作为外键目标（MySQL 允许但极少使用）。 5.图形化操作（Navicat） （1）设计表 → 勾选“主键”或“唯一”复选框 → 保存即生成约束；删除同理。 （2）复合约束需在“索引”页签手动添加组合字段。 6.易错点 （1）已有重复值或 NULL 时无法加主键/唯一；需先清洗数据。 （2）忘记给唯一约束命名，后续删除需查系统名；建议始终 CONSTRAINT 名 UNIQUE(...)。 （多项选择题）下列关于“实体完整性”的描述，正确的有（ ） A. 要求主键列的值不能为 NULL B. 一张表只能建立一个主键约束 C. 唯一约束可以实现实体完整性 D. 实体完整性允许同一表内出现两行完全相同的数据 【答案】ABC 【解析】实体完整性通过主键或唯一约束保证行可区分；主键非空且唯一；主键只能一个；唯一约束也属实体完整性范畴。 【要点】实体完整性的定义与实现方式。 1.（单项选择题）在 CREATE TABLE 时，最简洁地把 id 列设为主键的写法是（ ） A. id INT PRIMARY KEY B. id INT KEY C. id INT UNIQUE KEY D. id INT NOT NULL 【答案】A 【解析】列级直接写 PRIMARY KEY 即可同时完成非空与唯一。 【要点】列级主键声明。 2.（单项选择题）关于主键与唯一约束的区别，以下说法正确的是（ ） A. 唯一约束列不能出现 NULL B. 主键列允许一个 NULL C. 一张表可有多个唯一约束 D. 唯一约束可被外键引用而主键不能 【答案】C 【解析】唯一约束允许多套、每套允许一个 NULL；主键非空且唯一；主键常被外键引用。 【要点】主键 vs 唯一约束特征。 3.（多项选择题）以下哪些场景适合使用唯一约束而非主键（ ） A. 身份证号列 B. 订单明细表的订单号+商品号复合主键下的商品条码列 C. 用户表的自动递增 id D. 学生表的学号列 【答案】AB 【解析】身份证、商品条码业务唯一但非主键；自动递增 id 与学号常作为主键。 【要点】唯一约束业务场景。 1.（填空题）实体完整性规则要求所有______属性都不能取空值。 【答案】主键（或主） 【解析】主键列的值必须唯一且非空。 【要点】实体完整性核心要求。 2.（填空题）在已存在表 t 上把列 phone 升级为唯一约束，应执行 ALTER TABLE t ADD ______ (phone); 【答案】UNIQUE KEY 【解析】ADD UNIQUE KEY (列) 即可建立唯一索引/约束。 【要点】追加唯一约束语法。 3.（判断题）建立复合主键时，只要其中一列非空即可满足实体完整性（ ） 【答案】× 【解析】复合主键的所有组成列均不能为 NULL。 【要点】复合主键的非空规则。 · 每行须唯一区分 · 主键：唯一+非空，一表一个；可单/复合 · 唯一约束：值唯一、可1个NULL，多列多套 · 创建/删除：列级/表级/ALTER TABLE；Navicat勾选项 知识点二 参照完整性的概念与外键实现 1.定义与作用 （1）参照完整性（引用完整性）保证“子表外键值必须来源于父表主键或为空”，防止孤立记录。 （2）相关术语：主表（被引用）、从表（含外键）、外键列、父/子关系。 2.规则归纳 （1）外键值要么匹配父表主键，要么全 NULL（若允许）。 （2）更新/删除父表记录时，系统按指定策略（CASCADE、SET NULL、RESTRICT）同步或拒绝，保持两表同步。 3.创建外键 （1）建表时： （2）已存在表： 4.选项说明 （1）ON DELETE CASCADE：父键被删，子行一起删；ON UPDATE CASCADE：父键修改，子行同步更新。 （2）SET NULL：父键删除/更新，子键设空（列须允许 NULL）。 （3）RESTRICT/NO ACTION：默认，若子表存在引用则拒绝父表操作。 5.删除外键 （1）ALTER TABLE score DROP FOREIGN KEY fk_sc_stu; （2）注意：外键名需与创建时一致，未显式命名可用 SHOW CREATE TABLE 查看系统生成的名字。 6.多表级联与自引用 （1）同一数据库内可链式外键：A→B→C，级联顺序自动处理。 （2）自引用：员工表 manager_id 引用自身 emp_id，实现树形结构。 7.图形化操作 （1）Navicat“外键”选项卡→选父表、列、更新/删除规则→保存即生成。 （2）可视化 ER 图即时显示两表连线，方便梳理关系。 8.性能与注意事项 （1）外键会增加维护开销，高并发写入场景可放宽，由业务层保证一致性。 （2）父表列必须有索引（通常是主键），否则子表 DML 会全表扫描。 （3）导入大量数据前可 SET foreign_key_checks=0; 加快速度，导入完再开启并检查数据。 （多项选择题）下列关于参照完整性的描述，正确的有（ ） A. 外键值必须等于父表主键值或为空 B. 父表记录被删除时，子表对应记录一定会被级联删除 C. 外键约束只能在建表时定义，不能后期追加 D. 父表被引用的列必须有索引（通常是主键） 【答案】AD 【解析】外键值只能为父主键值或 NULL；级联行为取决于 ON DELETE 设置；外键可随时通过 ALTER TABLE 添加；父列必须有索引，否则子表 DML 会全表扫描。 【要点】参照完整性规则及实现细节。 1.（单项选择题）创建外键时，若要求父表更新主键时子表外键同步更新，应选用的级联选项是（ ） A. RESTRICT B. SET NULL C. CASCADE D. NO ACTION 【答案】C 【解析】ON UPDATE CASCADE 会在父键修改时自动同步子表外键值。 【要点】级联选项含义。 2.（单项选择题）在已存在表上添加外键约束，正确的语法是（ ） A. ADD FOREIGN KEY (列) REFERENCES 主表(主列) B. ADD CONSTRAINT fk_name FOREIGN KEY (列) REFERENCES 主表(主列) C. ADD KEY (列) REFERENCES 主表(主列) D. ALTER COLUMN 列 FOREIGN KEY REFERENCES 主表(主列) 【答案】B 【解析】ALTER TABLE 需用 ADD CONSTRAINT 指定外键名和引用关系。 【要点】后期追加外键的完整语法。 3.（多项选择题）删除外键约束时，需要注意的有（ ） A. 必须知道创建时给出的约束名 B. 可用 `SHOW CREATE TABLE` 查看系统生成的外键名 C. 删除外键会自动删除对应的索引 D. 删除后数据不再受参照完整性限制 【答案】ABD 【解析】删除外键需指定名称，系统命名可用 `SHOW CREATE TABLE` 查看；外键索引需手动 DROP；约束删除后不再校验数据一致性。 【要点】外键删除要点。 1.（填空题）设置外键时，若允许父表删除记录并将子表对应外键列置空，应在定义中使用 ______ 选项。 【答案】ON DELETE SET NULL 【解析】SET NULL 策略会把子表外键列更新为 NULL，前提是列允许 NULL。 【要点】级联置空用法。 2.（填空题）导入大量数据前，为提高速度可临时关闭外键检查，对应的会话级命令是 ______。 【答案】`SET foreign_key_checks = 0;` 【解析】关闭后不再校验外键，导入完再置 1 重新开启并检查。 【要点】批量数据导入技巧。 3.（判断题）外键列必须与被引用列的数据类型、长度、字符集完全一致，否则无法创建外键。（ ） 【答案】√ 【解析】MySQL 要求外键与引用列类型、长度、字符集、精度完全匹配，否则报错。 【要点】外键列类型一致性要求。 · 外键值须=父表主键或NULL · 建表/ALTER添加：FOREIGN KEY REFERENCES · 级联选项：CASCADE、SET NULL、RESTRICT · 删除：DROP FOREIGN KEY fk名 · 先索引父列，大数据可临时关外键检查 知识点三 用户定义完整性 1.非空约束（NOT NULL） （1）保证列值不能为空，可重复。 （2）建表：stuname VARCHAR(20) NOT NULL （3）追加：ALTER TABLE t MODIFY stuname VARCHAR(20) NOT NULL; （4）取消：再次 MODIFY 去掉 NOT NULL 关键字即可。 （5）图形化：设计表→“不是 null”复选框。 2.默认值约束（DEFAULT） （1）插入时未显式赋值则自动填充默认值。 （2）建表：sc INT DEFAULT 0 （3）追加/修改：ALTER TABLE score MODIFY sc INT DEFAULT 0; （4）删除：重新 MODIFY 不带 DEFAULT，或 ALTER TABLE score ALTER sc DROP DEFAULT; 3.自增约束（AUTO_INCREMENT） （1）仅整数列可用，常与主键组合；一张表只能一个。 （2）建表：id INT PRIMARY KEY AUTO_INCREMENT （3）修改初值：ALTER TABLE t AUTO_INCREMENT=1001; 4.检查约束（CHECK，MySQL 8.0+ 强制） （1）限定列值范围，如 CHECK(age BETWEEN 0 AND 150)。 （2）5.7 及以前可写但不生效，需升级或在业务层检查。 5.约束命名子句（CONSTRAINT） （1）统一格式：CONSTRAINT <名> <约束类型> (<列>) （2）好处：可读、易维护、可单独删除。 （3）示例： （4）删除：ALTER TABLE student6 DROP INDEX uk_phone; · ALTER TABLE student6 DROP FOREIGN KEY fk_xxx; · ALTER TABLE student6 DROP CHECK ck_xxx; 6.图形化与版本提示 （1）Navicat 在“索引/外键/检查”页签可直接命名约束。 （2）MySQL 8.0 之前 CHECK 仅解析忽略，升级后自动生效。 7.使用原则 （1）把能固化的规则放在数据库层，减少代码重复。 （2）复杂业务规则仍建议放在应用层，保持数据库轻量高效。 （多项选择题）下列约束中，属于“用户定义完整性”范畴的有（ ） A. NOT NULL B. DEFAULT C. PRIMARY KEY D. CHECK 【答案】ABD 【解析】用户定义完整性由业务规则决定，常见 NOT NULL、DEFAULT、CHECK；PRIMARY KEY 属于实体完整性。 【要点】区分三类完整性边界。 1.（单项选择题）若要求成绩列未填写时自动存为 0，应在建表时使用（ ） A. PRIMARY KEY B. AUTO_INCREMENT C. DEFAULT 0 D. NOT NULL 【答案】C 【解析】DEFAULT 约束可在插入缺省值时自动赋指定值。 【要点】默认值约束用途。 2.（单项选择题）下列关于 NOT NULL 的说法正确的是（ ） A. 一张表只能有一个 NOT NULL B. 允许列出现多个 NULL C. 可与 DEFAULT 同时定义 D. 主键列可省略 NOT NULL 【答案】C 【解析】NOT NULL 可与 DEFAULT 并存；主键自带非空；多列都可设 NOT NULL。 【要点】NOT NULL 语法特点。 3.（多项选择题）在 MySQL 8.0 及以上版本，关于 CHECK 约束的说法正确的有（ ） A. 写入非法数据时会被拒绝 B. 5.7 版本写入但忽略检查 C. 一张表只能定义一个 CHECK D. 可用于限制年龄 0-150 【答案】ABD 【解析】8.0 强制检查；5.7 仅解析；可定义多条 CHECK；范围检查是典型用法。 【要点】CHECK 的版本差异。 1.（填空题）给已存在列添加默认值 18 的命令格式为：ALTER TABLE 表名 ALTER 列名 SET DEFAULT ________。 【答案】18 【解析】SET DEFAULT 后直接跟常量值。 【要点】ALTER 修改默认值语法。 2.（填空题）若插入时未给列赋值且该列无 DEFAULT 约束，则其值为__________。 【答案】NULL 【解析】无默认值时系统赋 NULL，除非列定义 NOT NULL 禁止。 【要点】缺省插入行为。 3.（判断题）AUTO_INCREMENT 必须与主键或唯一键一起使用，且一张表只能有一个（ ） 【答案】√ 【解析】MySQL 限制每表仅一个自增列，且必须建有索引（主键/唯一）。 【要点】自增使用前提。 · NOT NULL：禁止空值；DEFAULT：未赋值填默认值 · CHECK限定范围(MySQL8+生效)；AUTO_INCREMENT自增 · 统一命名：CONSTRAINT名便于维护 · 增改删：MODIFY/ALTER；Navicat设计视图一键操作 原创精品资源学科网独家享有版权，侵权必究！2 学科网（北京）股份有限公司 学科网（北京）股份有限公司 $