5.3 数据约束与表的参照完整性

数据库技术5.3数据约束与表的参照完整性5.3数据约束与表的参照完整性

在数据库表设计中，数据约束与参照完整性是确保数据准确性和一致性的基石。它们不仅有助于防止数据冗余、错误和冲突，还能维护数据库的完整性和可靠性。通过实施适当的数据约束，我们可以定义数据的规则和结构，从而确保数据在插入、更新和删除时满足特定的条件。参照完整性则进一步强化了表之间的关系，确保了数据在跨表操作时的一致性和关联性。这些内容都要求数据库设计者在表设计阶段予以完成，是数据库表设计的一个重要组成内容。PRIMARYKEY主键约束5.3.15.3.1PRIMARYKEY主键约束

PRIMARYKEY主键约束用于唯一标识表中的每一行记录。一个表只能有一个主键，它可以由一个或多个字段组成。如果主键由多个字段组成则称之为复合主键，复合主键只能通过表定义中的PRIMARYKEY子句定义为表的完整性约束。主键字段的值必须是唯一的，且不允许为空。PRIMARYKEY不仅提供了数据的唯一性保证，还自动为表创建了索引，从而提高了查询性能。

主键的设置可以在创建表时直接指定一个字段或多个字段作为主键，也可以针对已有的表添加主键或修改主键。主键设置的SQL实现语句如下：5.3.1PRIMARYKEY主键约束【示例1】定义表的字段时直接指定主键。--定义表的字段时直接指定主键CREATETABLEclasses1(cls_idCHAR(8)PRIMARYKEY,--班级编号，并设置为主键cls_nameVARCHAR(10)NOTNULL);5.3.1PRIMARYKEY主键约束【示例2】使用表定义中的PRIMARYKEY子句设置主键。--使用表定义中的PRIMARYKEY子句设置主键CREATETABLEclasses2(cls_idCHAR(8),cls_nameVARCHAR(10)NOTNULL,PRIMARYKEY(cls_id)--设置班级编号为主键);5.3.1PRIMARYKEY主键约束【示例3】使用表定义中的PRIMARYKEY子句设置复合主键。CREATETABLEscores1(stu_idCHAR(14),crs_idCHAR(6),sc_gradeDECIMAL(5,2),sc_rebuildDECIMAL(5,2),PRIMARYKEY(stu_id,crs_id)--将stu_id和crs_id的组合设置为复合主键);5.3.1PRIMARYKEY主键约束【示例4】修改现有表以添加主键。如果你已经有一个表，但还没有设置主键，你可以使用ALTERTABLE语句来添加主键。--创建未设置主键的表CREATETABLEclasses3(cls_idCHAR(8),cls_nameVARCHAR(10)NOTNULL);--修改现有表以添加主键ALTERTABLEclasses3ADDPRIMARYKEY(cls_id);5.3.1PRIMARYKEY主键约束【示例4】修改现有表中的主键为另一字段

如果表中已设置主键，但需要将当前主键设置为另一字段时使用ALTERTABLE语句来实现。实现过程包括两个步骤，首先删除现有的主键，然后再添加新的主键。但一般来说主键通常要求保持不可变性，不建议修改主键。特别是在已经部署并运行的表中。--删除已有的主键ALTERTABLEclasses3dropPRIMARYKEY;--新建主键,此处通过新建一个自增量字段ID来作为主键--AUTO_INCREMENT选项设定字段为自增量字段，自增量字段必须是PRIMARYKEY或UNIQUEKEYALTERTABLEclasses3ADDCOLUMNIDINTAUTO_INCREMENTPRIMARYKEY;或--如果ID字段未设置为自增量字段，则使用ADDPRIMARYKEY设置主键ALTERTABLEclasses3dropPRIMARYKEY;ALTERTABLEclasses3ADDCOLUMNIDINT;ALTERTABLEclasses3ADDPRIMARYKEY(ID);在设置主键时，要综合考虑，注意以下方面：

自然键vs代理键：若业务字段（如身份证号）唯一稳定，可直接作为主键；否则建议使用自增整数（代理键）提升性能。

复合主键：适用于需要多字段联合唯一标识的场景，但会增加索引复杂度。

性能影响：主键字段应尽量短且有序，避免使用长字符串或频繁更新的字段。UNIQUEKEY约束5.3.25.3.2UNIQUEKEY约束

唯一约束（UNIQUEKEY）与主键一样也能确保字段或字段组合的值唯一，但与主键不同的是唯一约束允许字段为NULL，且一个表可以没有唯一约束也可定义多个唯一约束。唯一约束与主键的设置方法类似，既可以在创建表时直接指定，也可以针对已有的表添加或修改。【示例1】定义表的字段时直接指定唯一约束。--定义表的字段时直接指定唯一约束CREATETABLEstudents1(--此处学生表已简化stu_idCHAR(14)PRIMARYKEY,--学号，并设置为主键stu_nameVARCHAR(10)NOTNULL,--姓名，并设置为非空约束stu_phoneCHAR(11)UNIQUEKEY,--联系电话，并设置为唯一约束stu_QQVARCHAR(20)UNIQUE--QQ号，并设置为唯一约束（简写为UNIQUE）);5.3.2UNIQUEKEY约束【示例2】使用表定义中的UNIQUEKEY子句设置唯一约束。--使用表定义中的UNIQUEKEY子句设置主键CREATETABLEstudents2(--此处学生表已简化stu_idCHAR(14)PRIMARYKEY,--学号，并设置为主键stu_nameVARCHAR(10)NOTNULL,--姓名，并设置为非空约束stu_phoneCHAR(11),--联系电话stu_QQVARCHAR(20),--QQ号UNIQUEKEY(stu_phone),--设置联系电话为唯一约束UNIQUEKEY(stu_QQ)--设置QQ号为唯一约束);5.3.2UNIQUEKEY约束【示例3】修改现有表以添加唯一约束。如果你已经有一个表，且还需要设置更多唯一约束，你可以使用ALTERTABLE语句来添加唯一约束。--创建未设置主键的表CREATETABLEstudents3(--此处学生表已简化stu_idCHAR(14)PRIMARYKEY,--学号，并设置为主键stu_nameVARCHAR(10)NOTNULL,--姓名，并设置为非空约束stu_phoneCHAR(11)UNIQUE,--联系电话stu_QQVARCHAR(20)--QQ号);--修改现有表以添加主键ALTERTABLEstudents3ADDUNIQUEKEY(stu_QQ);5.3.2UNIQUEKEY约束【示例4】删除唯一约束。

要从表中删除一个UNIQUE约束，你可以使用ALTERTABLE语句并指定DROPINDEX。这是因为在MySQL中，当你为单列添加UNIQUE约束时，实际上是在创建索引，其默认索引名为字段名。如果你尝试删除一个单列的UNIQUE约束，实际上是删除这个索引。要特别注意的是，对于复合唯一约键（即包含多列的唯一约束），你需要使用索引的名称来删除它，而不是列名。--创建有复合唯一键的表--此处假定不同学校可以采用相同的学号编排，一个学校注册的学号必须唯一CREATETABLEstudents4(stu_idCHAR(14)PRIMARYKEY,--学号，并设置为主键stu_nameVARCHAR(10)NOTNULL,--姓名，并设置为非空约束stu_phoneCHAR(11)UNIQUE,--联系电话stu_QQVARCHAR(20)UNIQUE,--QQ号school_idINTNOTNULL,--学校编号，外键关联到学校表--建立学校编号与学生学号的复合唯一键，索引名为unique_registrationUNIQUEKEYunique_registration(stu_id,school_id));--删除唯一键ALTERTABLEstudents4DROPINDEXstu_QQ;--删除建立在QQ号上的唯一键ALTERTABLEstudents4DROPINDEXunique_registration;--删除复合唯一键CHECK约束5.3.35.3.3CHECK约束

CHECK约束用于限制列中可以存储的值范围。通过在列上设置CHECK约束，可以确保数据满足特定的条件或规则。需要注意的是，MySQL直到MySQL8.0.16版本才开始引入对CHECK约束的完整支持。在支持CHECK约束的MySQL版本中，我们可以利用它来防止无效数据的插入，从而维护数据的准确性和一致性。而在早期版本中，我们可以通过触发器来实现类似于check约束的功能。

在MySQL中，可以在创建表的同时创建CHECK约束。如果CHECK约束只针对单一字段进行约束，可以在字段定义中用CHECK关键字来实现约束定义或者CONSTRAIN子句来实现约束创建。如果CHECK约束涉及多个字段则应该通过CONSTRAIN子句来实现约束创建。

当然，与其它约束类似的，也可以在已存在的表上使用ALTERTABLE命令来创建CHECK约束，或修改它。5.3.3CHECK约束【示例1】定义表的字段时直接创建CHECK约束。--定义表的字段时直接创建CHECK约束CREATETABLEscores2(stu_idCHAR(14),crs_idCHAR(6),sc_gradeDECIMAL(5,2)CHECK(sc_grade>=0ANDsc_grade<=100),sc_rebuildDECIMAL(5,2),PRIMARYKEY(stu_id,crs_id)--将stu_id和crs_id的组合设置为复合主键);5.3.3CHECK约束【示例2】使用表定义中的CHECK或CONSTRAINT子句创建CHECK约束--使用表定义中的UNIQUEKEY子句设置主键CREATETABLEscores3(stu_idCHAR(14),crs_idCHAR(6),sc_gradeDECIMAL(5,2),sc_rebuildDECIMAL(5,2),PRIMARYKEY(stu_id,crs_id),--将stu_id和crs_id的组合设置为复合主键--使用CHECK子句创建CHECK约束，约束名为系统默认名：表名_chk_编号CHECK(sc_rebuild<=60ANDsc_rebuild>=0),--补考成绩最高为60分--使用CONSTRAIN子句设置约束名来创建一个CHECK约束，CONSTRAINTchk_gradeCHECK(sc_grade>=0ANDsc_grade<=100));5.3.3CHECK约束【示例3】修改现有表以添加CHECK约束。如果你已经有一个表，且还需要设置更多唯一约束，你可以使用ALTERTABLE语句来添加唯一约束。--创建未设置主键的表CREATETABLEscores4(stu_idCHAR(14),crs_idCHAR(6),sc_gradeDECIMAL(5,2),sc_grade_dateDATE,sc_rebuildDECIMAL(5,2),sc_rebuild_dateDATE,PRIMARYKEY(stu_id,crs_id)--将stu_id和crs_id的组合设置为复合主键);--修改现有表以添加CHECK约束ALTERTABLEscores4ADDCHECK(sc_rebuild<=60ANDsc_rebuild>=0);ALTERTABLEscores4ADDCONSTRAINTchk_grade1CHECK(sc_grade>=0ANDsc_grade<=100);ALTERTABLEscores4ADDCONSTRAINTchk_dateCHECK(sc_rebuild_date>sc_grade_date);5.3.3CHECK约束【示例4】删除CHECK约束

在MySQL中，CHECK约束是定义为一个CONSTRAINT的。要从表中删除一个CHECK约束，你可以使用ALTERTABLE语句并指定DROPCHECK或DROPCONSTRAINT。如果在CHECK约束创建的时候未指定约束名或忘记了约束名，可以通过SHOWCREATETABLE命令查看表创建语句获取约束名及约束内容，再使用ALTERTABLE语句删除对应的CHECK约束。

另外，MySQL不支持直接修改CHECK约束。要修改已经存在的CHECK约束，需要先删除旧的约束，然后添加一个新的约束。--查看表创建语句，运行结果如图5.9SHOWCREATETABLEscores4;--删除CHECK约束scores4_chk_1（系统默认约束名，编号从1开始）ALTERTABLEscores4DROPCONSTRAINTscores4_chk_1;--删除CHECK约束chk_grade1ALTERTABLEscores4DROPCHECKchk_grade1;--删除CHECK约束chk_dateALTERTABLEscores4DROPCONSTRAINTchk_date;FOREIGNKEY参照完整性5.3.45.3.4FOREIGNKEY参照完整性

FOREIGNKEY约束用于维护两个表之间的关系。它指定了一个子表中的列作为外键，该列的值必须对应于另一个父表的主键或UNIQUEKEY的值。通过FOREIGNKEY约束，我们可以确保引用完整性，防止孤立记录的存在。当尝试在子表中插入或更新一个不存在于父表中的外键值时，数据库将拒绝该操作，从而维护了数据的一致性和关联性。FOREIGNKEY约束还有助于级联更新和删除操作，当父表中的主键值发生变化或父表记录被删除时，可以自动更新或删除子表中相应的外键记录。FOREIGNKEY子句的基本语法如下：FOREIGNKEY(field_name)REFERENCESparent_table(field_name)

field_name是外键字段名

parent_table

是父表名5.3.4FOREIGNKEY参照完整性【示例1】创建表时添加外键约束。创建表时可以在创建表的SQL语句中直接添加外键约束。CREATETABLEscores5(scores_idINTAUTO_INCREMENTPRIMARYKEY,stu_idCHAR(14)NOTNULL,crs_idCHAR(6)NOTNULL,sc_gradeDECIMAL(5,2),--创建学生表到成绩表的外键约束CONSTRAINTfk_stu_scoFOREIGNKEY(stu_id)REFERENCESstudents(stu_id),--创建课程表到成绩表的外键约束FOREIGNKEY(crs_id)REFERENCEScourses(crs_id))ENGINE=InnoDBDEFAULTCHARSET=gb2312;5.3.4FOREIGNKEY参照完整性【示例2】修改已存在的表添加外键约束。可以使用ALTERTABLE语句向已存在的表中添加外键约束。ALTERTABLEscores4ADDCONSTRAINTfk_stu4_courses4FOREIGNKEY(stu_id)REFERENCESstudents4(stu_id);5.3.4FOREIGNKEY参照完整性3.删除外键约束

在MySQL中，与CHECK约束相同，外键是定义为一个CONSTRAINT的。要删除一个已存在的外键约束，可以使用ALTERTABLE语句配合DROPFOREIGNKEY或DROPCONSTRAINT子句来实现。但不管使用哪一个子句格式均应该使用约束名来删除外键约束。同样，MySQL不支持直接修改外键本身，可以通过删除并重新创建外键的方式来修改它。可以通过SHOWCREATETABLE命令查看表创建语句获取约束名及约束内容。ALTERTABLEscores4DROP