第6章 数据完整性约束

第6章数据完整性约束学习目标：了解数据完整性约束的基本概念，掌握实体完整性定义和更新的方法，掌握参照完整性定义和更新的方法，掌握用户完整性定义和更新的方法。6.1数据完整性约束的概念数据的完整性约束分为三类。①实体完整性约束。②参照完整性约束。③用户定义完整性约束。6.2实体完整性约束6.2.1主键约束1．主键列必须遵守的规则①每个表只能定义一个主键。②主键的值，也称为键值，必须能够唯一标识表中的一条记录，且不能为NULL。③复合主键不能包含不必要的多余列。④一个列名在复合主键的列表中只能出现一次。2．定义主键约束的方式（1）列级主键约束列名数据类型[其他约束]PRIMARYKEY6.2实体完整性约束【例6-1】在数据库student_db中创建课程表course，要求在列级上定义主键CourseID，表的结构和说明见表6-1。6.2实体完整性约束USEstudent_db;DROPTABLEIFEXISTScourse;CREATETABLEcourse(CourseIDCHAR(6)PRIMARYKEY,CourseNameVARCHAR(20)NOTNULL,CreditSMALLINTNOTNULL,CourseHourSMALLINTNOTNULL,PreCourseIDCHAR(6),TermTINYINT);6.2实体完整性约束6.2实体完整性约束（2）表级主键约束[CONSTRAINT<约束名>]PRIMARYKEY(列名,…)【例6-2】创建成绩表score，表的结构和说明见表6-2，要求以表级方式定义主键约束，将学号、课程编号（StudentID,CourseID）的列组合设置为score表的主键。6.2实体完整性约束USEstudent_db;DROPTABLEIFEXISTSscore;CREATETABLEscore(StudentIDCHAR(10),CourseIDCHAR(6),ScoreDECIMAL(4,1),TermTINYINT,PRIMARYKEY(StudentID,CourseID));6.2实体完整性约束6.2.2唯一键约束（1）列级唯一键约束列名数据类型[其他约束]UNIQUE[KEY]（2）表级唯一键约束[CONSTRAINT<约束名>]UNIQUE[KEY](列名,…)6.2实体完整性约束【例6-3】重新创建班级表class。USEstudent_db;DROPTABLEIFEXISTSclass;CREATETABLEclass(ClassIDCHAR(8)PRIMARYKEY,ClassNameVARCHAR(20)NOTNULLUNIQUEKEY,MonitorVARCHAR(10)NOTNULLUNIQUEKEY,DepartmentNameVARCHAR(20)NOTNULL,GradeCHAR(4)NOTNULL,ClassNumSMALLINT);6.2实体完整性约束CREATETABLEclass(ClassIDCHAR(8)NOTNULL,ClassNameVARCHAR(20)NOTNULL,MonitorVARCHAR(10)NOTNULL,DepartmentNameVARCHAR(20)NOTNULL,GradeCHAR(4)NOTNULL,ClassNumSMALLINT,PRIMARYKEY(ClassID),UNIQUEKEY(ClassName),UNIQUEKEY(Monitor));6.2实体完整性约束定义主键约束时，系统会自动生成主键索引，而定义唯一键约束时，系统自动生成唯一索引。6.2实体完整性约束6.2.3完整性约束的命名[CONSTRAINT<约束名>]{PRIMARYKEY(主键列的列表)|UNIQUEKEY(唯一键列的列表)|FOREIGNKEY(外键列的列表)REFERENCES被参照关系的表名(主键列的列表)|CHECK(约束条件表达式)};6.2实体完整性约束【例6-4】重新创建学生表student，要求以表级完整性约束的命名方式定义主键StudentID，并指定主键约束名称为PK_student。USEstudent_db;DROPTABLEIFEXISTSstudent;CREATETABLEstudent(StudentIDCHAR(10),StudentNameVARCHAR(20)NOTNULL,SexCHAR(2)NOTNULL,BirthdayDATE,SourceVARCHAR(10),ClassIDCHAR(8)NOTNULL,CONSTRAINTPK_studentPRIMARYKEY(StudentID));6.2实体完整性约束6.3参照完整性约束6.3.1相关概念外键用来在两个表的记录之间建立联系。其中，被引用的表称为主表，引用外键的表称为从表。6.3.2外键约束1．列级外键约束列名数据类型[其他约束]REFERENCES主表名(主表中主键列的列表)[ONDELETE{CASCADE|RESTRICT|SETNULL|NOACTION|SETDEFAULT}][ONUPDATE{CASCADE|RESTRICT|SETNULL|NOACTION|SETDEFAULT}]2．表级外键约束[CONSTRAINT<约束名>]FOREIGNKEY[index_name](index_col_name1,...)REFERENCES主表名(index_col_name2,...)[ONDELETE{RESTRICT|CASCADE|SETNULL|NOACTION|SETDEFAULT}][ONUPDATE{RESTRICT|CASCADE|SETNULL|NOACTION|SETDEFAULT}]6.3参照完整性约束3．参照动作和动作策略①RESTRICT。②CASCADE。③SETNULL。④NOACTION。⑤SETDEFAULT：6.3参照完整性约束【例6-5】重新创建学生表student，要求以列级完整性约束方式定义外键，采用默认的RESTRICT参照动作策略。DROPTABLEIFEXISTSstudent;CREATETABLEstudent(StudentIDCHAR(10)PRIMARYKEY,StudentNameVARCHAR(20)NOTNULL,SexCHAR(2)NOTNULL,BirthdayDATE,SourceVARCHAR(10),ClassIDCHAR(8)NOTNULLREFERENCESclass(ClassID)ONUPDATERESTRICTONDELETERESTRICT);6.3参照完整性约束【例6-6】重新创建成绩表score，指定主键为StudentID和CourseID，指定外键分别为student(StudentID)、course(CourseID)。DROPTABLEIFEXISTSscore;CREATETABLEscore(StudentIDCHAR(10)NOTNULL,CourseIDCHAR(6)NOTNULL,ScoreDECIMAL(4,1),TermTINYINT,PRIMARYKEY(StudentID,CourseID),CONSTRAINTFK_score_studentFOREIGNKEY(StudentID)REFERENCESstudent(StudentID)ONUPDATERESTRICTONDELETERESTRICT,CONSTRAINTFK_score_courseFOREIGNKEY(CourseID)REFERENCEScourse(CourseID)ONUPDATERESTRICTONDELETERESTRICT);6.3参照完整性约束【例6-7】重新创建学生表student，要求在定义外键时，同时定义相应的参照动作。ALTERTABLEscoreDROPFOREIGNKEYFK_score_student;DROPTABLEIFEXISTSstudent;CREATETABLEstudent(StudentIDCHAR(10),StudentNameVARCHAR(20)NOTNULL,SexCHAR(2)NOTNULL,BirthdayDATE,SourceVARCHAR(10),ClassIDCHAR(8)NOTNULL,CONSTRAINTPK_studentPRIMARYKEY(StudentID),CONSTRAINTFK_studentFOREIGNKEY(ClassID)REFERENCESclass(ClassID)ONUPDATERESTRICTONDELETECASCADE);6.3参照完整性约束SHOWCREATETABLEstudent;6.3参照完整性约束6.4用户定义完整性约束6.4.1非空约束非空约束（NOTNULLCONSTRAINT）是指列的值不能为空。使用关键字NULL和NOTNULL给列自定义约束，NULL就是没有值或值空缺。列名数据类型NOTNULL[其他约束]6.4.2默认值约束列名数据类型[其他约束]DEFAULT默认值6.4用户定义完整性约束【例6-8】重新创建student表，将Sex列的约束修改为NOTNULL，只能取值“男”或“女”、默认值为“男”。DROPTABLEIFEXISTSstudent;CREATETABLEstudent(StudentIDCHAR(10),StudentNameVARCHAR(20)NOTNULL,SexENUM('男','女')NOTNULLDEFAULT'男',BirthdayDATE,SourceVARCHAR(10),ClassIDCHAR(8)NOTNULL,CONSTRAINTPK_studentPRIMARYKEY(StudentID),CONSTRAINTFK_studentFOREIGNKEY(ClassID)REFERENCESclass(ClassID)ONUPDATERESTRICTONDELETECASCADE);6.4用户定义完整性约束6.4.3检查约束1．列级检查约束列名数据类型[其他约束]CHECK(expr)2．表级检查约束CONSTRAINT<检查约束名>CHECK(expr)6.4用户定义完整性约束【例6-9】重新创建student表，生日必须在1990-1-1以后；性别为NOTNULL，只能取值“男”或“女”，默认值为“男”。CREATETABLEstudent(StudentIDCHAR(10),StudentNameVARCHAR(20)NOTNULL,SexCHAR(2)NOTNULLDEFAULT'男',BirthdayDATECHECK(Birthday>'1990-1-1'),#列级只能引用本列BirthdaySourceVARCHAR(10),ClassIDCHAR(8)NOTNULL,CONSTRAINTPK_studentPRIMARYKEY(StudentID),CONSTRAINTFK_studentFOREIGNKEY(ClassID)REFERENCESclass(ClassID)ONUPDATERESTRICTONDELETECASCADE,CONSTRAINTCK_studentCHECK(SexIN('男','女')));6.4用户定义完整性约束6.4用户定义完整性约束6.4用户定义完整性约束6.4.4自增约束列名数据类型[其他约束]AUTO_INCREMENT[=n]【例6-10】创建临时表temp1，设置ID列为INT型，要求其为主键，自动增加；Name列为CHAR(10)。CREATETABLEtemp1(IDINTNOTNULLAUTO_INCREMENT,PRIMARYKEY(ID),NameCHAR(10));6.5更新完整性约束6.5.1删除约束1．删除主键约束ALTERTABLEtb_nameDROPPRIMARYKEY;【例6-11】删除student表的主键约束。ALTERTABLEstudentDROPPRIMARYKEY;2．删除唯一键约束ALTERTABLEtb_nameDROPINDEX{约束名|唯一键约束名};【例6-12】删除class表的Monitor列上定义的唯一键约束。ALTERTABLEclassDROPINDEXMonitor;6.5更新完整性约束3．删除非空约束ALTERTABLEtb_nameMODIFY列名数据类型NULL;【例6-13】删除class表的DepartmentName列上定义的非空约束。ALTERTABLEclassMODIFYDepartmentNameCHAR(20)NULL;6.5更新完整性约束4．删除检查约束ALTERTABLEtb_nameDROPCHECK约束名;【例6-14】删除student表中定义的两个检查约束。ALTERTABLEstudentDROPCHECKCK_student;#命名的检查约束名ALTERTABLEstudentDROPCHECKstudent_chk_1;#系统指定的检查约束名6.5更新完整性约束5．删除自增约束ALTERTABLEtb_nameMODIFY列名INT;【例6-15】删除临时表temp1的ID列上定义的自增约束。ALTERTABLEtemp1MODIFYIDINT;6.5更新完整性约束6．删除默认值约束ALTERTABLEtb_nameALTER列名DROPDEFAULT;【例6-16】删除student表中定义的Sex列的默认值。SQL语句如下：ALTERTABLEstudentALTERSexDROPDEFAULT;6.5更新完整性约束7．删除外键约束ALTERTABLEtb_nameDROPFOREIGNKEYforeign_key_name;【例6-17】删除student表中命名的外键约束FK_student。ALTERTABLEstudentDROPFOREIGNKEYFK_student;SHOWCREATETABLEstudent;6.5更新完整性约束6.5.2添加约束1．添加主键约束ALTERTABLEtb_nameADD[CONSTRAINT<约束名>]PRIMARYKEY(主键列名);【例6-18】修改成绩表score，要求在表级上定义主键（StudentNo_CourseNo），并指定主键约束名称为PK_StudentNo_CourseNo。ALTERTABLEscoreADDCONSTRAINTPK_StudentNo_CourseNoPRIMARYKEY(StudentID,CourseID);6.5更新完整性约束ALTERTABLEscoreDROPPRIMARYKEY,ADDCONSTRAINTPK_StudentNo_CourseNoPRIMARYKEY(StudentID,CourseID);SHOWCREATETABLEscore;【例6-19】为student表添加主键约束StudentID。ALTERTABLEstudentADDPRIMARYKEY(StudentID);6.5更新完整性约束2．添加唯一键约束ALTERTABLEtb_nameADD[CONSTRAINT<约束名>]UNIQUEKEY(列名);【例6-20】在class表中，为ClassName列添加唯一键约束。ALTERTABLEclassMODIFYClassNameVARCHAR(20)NOTNULLUNIQUEKEY;ALTERTABLEclassADDCONSTRAINTUQ_MonitorUNIQUEKEY(Monitor);ALTERTABLEclassADDUNIQUEKEY(ClassName);6.5更新完整性约束3．添加非空约束ALTERTABLEtb_nameMODIFY列名数据类型NOTNULL;【例6-21】将student表的StudentName列修改为非空约束。ALTERTABLEstudentMODIFYStudentNameCHAR(20)NOTNULL;6.5更新完整性约束4．添加检查约束ALTERTABLEtb_nameADDCONSTRAINT<检查约束名>CHECK(expr)【例6-22】为score表的Score列添加检查约束，要求成绩的范围为0～100分。ALTERTABLEscoreADDCONSTRAINTCK_ScoreCHECK(Score>=0ANDScore<=100);6.5更新完整性约束5．添加自增约束ALTERTABLEtb_nameMODIFY列名INTAUTO_INCREMENT;【例6-23】在temp1表中，为ID列添加自增约束。SQL语句如下：ALTERTABLEtemp1MODIFYIDINTAUTO_I