数据库完整性.

数据库原理与应用数据库系统概论第四版王珊萨师煊第5章数据库完整性数据库完整性机制触发器存储过程第5章数据库完整性数据库的完整性是指数据的正确性和相容性。完整性检查和控制的防范对象是不合语义的、不正确的数据。DBMS对完整性的维护机制：1、提供定义完整性约束条件的机制2、提供完整性检查的方法3、违约处理5.1实体完整性1、实体完整性定义PRIMARYKEY单属性构成的码有两种说明方法定义为列级约束条件定义为表级约束条件多个属性构成的码只有一种说明方法定义为表级约束条件5.1实体完整性（续）如：CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY,/*在列级定义主码*/SnameCHAR(20)NOTNULL,SsexCHAR(2),SageSMALLINT,SdeptCHAR(20));5.1实体完整性（续）如：CREATETABLEStudent(SnoCHAR(9),SnameCHAR(20)NOTNULL,SsexCHAR(2),SageSMALLINT,SdeptCHAR(20),PRIMARYKEY(Sno)/*在表级定义主码*/);5.1实体完整性（续）如：CREATETABLESC(SnoCHAR(9)NOTNULL,CnoCHAR(4)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno)/*只能在表级定义主码*/);5.1实体完整性（续）2、实体完整性检查和违约处理插入记录或对主码列进行更新操作时，按照实体完整性规则自动进行检查。包括：检查主码值是否唯一，如果不唯一则拒绝插入或修改。检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改。检查方法：全表扫描（耗时），索引5.2参照完整性可能破坏参照完整性的情况及违约处理被参照表(如Student)参照表(如SC)违约处理可能破坏参照完整性插入元组拒绝可能破坏参照完整性修改外码值拒绝删除元组可能破坏参照完整性拒绝/级连删除/设置为空值修改主码值可能破坏参照完整性拒绝/级连修改/设置为空值5.2参照完整性（续）1、参照完整性定义FOREIGNKEY(属性列)/*定义列为外码*/REFERENCES表名(主码)/*参照哪些表的主码*/2、参照完整性检查和处理拒绝(NOACTION)执行默认策略级联(CASCADE)操作设置为空值（SET-NULL）对于参照完整性，除了应该定义外码，还应定义外码列是否允许空值。5.2参照完整性（续）如：CREATETABLESC(SnoCHAR(9)NOTNULL,CnoCHAR(4)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno),FOREIGNKEY(Cno)REFERENCESCourse(Cno));5.2参照完整性（续）如：CREATETABLESC(SnoCHAR(9)NOTNULL,CnoCHAR(4)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno)ONDELETECASCADE/*删除Student记录时级联删除SC相应记录*/ONUPDATECASCADE,/*更新Student记录时级联更新SC相应记录*/FOREIGNKEY(Cno)REFERENCESCourse(Cno)ONDELETENOACTION/*删除Course记录使与SC不一致则拒绝删除*/ONUPDATECASCADE/*更新Course的Cno时级联更新SC相应记录*/);5.3用户自定义完整性1、不允许取空值NOTNULL2、列值唯一UNIQUE3、CHECK子句CHECK(约束条件表达式)4、违约处理方式拒绝5.3用户自定义完整性（续）如：CREATETABLESC(SnoCHAR(9)NOTNULL,/*不允许取空值*/CnoCHAR(4)NOTNULL,/*不允许取空值*/GradeSMALLINT,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno),FOREIGNKEY(Cno)REFERENCESCourse(Cno));5.3用户自定义完整性（续）如：CREATETABLEDEPT(DeptnoNUMERIC(2),DnameCHAR(9)UNIQUE,/*列值唯一*/LocationCHAR(10),PRIMARYKEY(Deptno));5.3用户自定义完整性（续）如：CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY,SnameCHAR(20)NOTNULL,SsexCHAR(2)CHECK(SsexIN’男’,’女,),/*Ssex属性只允许取’男’或’女’*/SageSMALLINT,SdeptCHAR(20));5.3用户自定义完整性（续）如：CREATETABLESC(SnoCHAR(9)NOTNULL,CnoCHAR(4)NOTNULL,GradeSMALLINTCHECK(Grade=0ANDGrade=100),/*Grade只能取0-100之间的整数*/PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno),FOREIGNKEY(Cno)REFERENCESCourse(Cno));5.3用户自定义完整性（续）如：CREATETABLEStudent(SnoCHAR(9),SnameCHAR(20)NOTNULL,SsexCHAR(2)SageSMALLINT,SdeptCHAR(20),PRIMARYKEY(Sno),CHECK(Ssex=‘女’ORSnameNOTLIKE‘Ms.%’)/*元组上的约束条件定义*/);5.4完整性约束命名子句1、语法格式CONSTRAINT完整性约束名完整性约束类型如：CREATETABLEStudent(SnoNUMERIC(6),SnameCHAR(20)CONSTRAINTC1NOTNULL,SsexCHAR(2)CONSTRAINTC2CHECK(SsexIN(‘男’,’女’)),SageSMALLINTCONSTRAINTC3CHECK(Sage30)),CONSTRAINTStudent_PKPRIMARYKEY(Sno),);5.4完整性约束命名子句2、修改表中的完整性约束ALTERTABLEStuentDROPCONSTRAINTC1;/*删除约束*/ALTERTABLEStuentADDCONSTRAINTC4CHECK(SnoBETWEEN900000AND999999);/*增加CHECK约束*/5.5触发器1、触发器(Trigger)定义触发器是用户定义在关系表上的一类由事件驱动的特殊存储过程。当发生表的UPDATE、DELETE、INSERT操作时，触发器被激活执行。5.5触发器（续）2、触发器的特点触发器是自动执行的，不能由用户显式调用。触发器可通过相关表实现级联更改。触发器可以防止恶意或错误的插入、删除和更新操作，并强制执行比CHECK约束更为复杂的其他限制。只要约束和默认值提供了所需的功能，就应使用约束和默认值。如果在触发器表上存在约束，它们在触发器执行前检查。如果违反约束，则触发器不执行。5.5触发器（续）3、触发器的分类按触发器被激活的时间分为：AFTER（FOR）:--for为老版本中的使用触发器在触发他们的语句完成后触发INSTEADOF:该触发器代替触发操作执行（比如执行插入操作时，实际执行的是相应触发器中的SQL语句）5.5触发器（续）3、触发器的分类按触发器的激活事件分为：INSERT触发器表或视图在执行插入记录操作时激活DELETE触发器表或视图在执行删除记录操作时激活UPDATE触发器表或视图在执行修改记录操作时激活5.5触发器（续）4、定义触发器CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}ASsql_statement[...n]加密触发器类型，AFTER表示执行操作后，INSTEADOF表示操作前。触发器激活后所作的操作，例如检查、回滚、插入、删除等触发事件5.5触发器（续）例1：向student表中插入信息后，显示student表中的所有信息，以确认插入的信息是否符合要求。CREATETRIGGERtrg_StudentInsertONStudentAFTERINSERTASbeginselect*fromStudentend验证：Insertintostudentvalues('99111','mary','女',19)5.4触发器（续）触发器使用的两个特殊表当触发器激发对INSERT、DELETE或UPDATE语句的响应时，两个特殊的表被创建。即插入表（inserted）和删除表（deleted），也称为幻表。inserted表和deleted表由系统自动创建和管理，结构与触发器所在的表结构相同。它们并不实际存在于数据库中，而是在触发的瞬间存在于缓存中。INSERT触发器的工作过程在定义了INSERT触发器的表上执行INSERT语句激活INSERT触发器INSERT语句插入的行被同时增加到触发器所在的表和inserted表中触发器动作被执行inserted表允许用户引用INSERT语句所插入的数据，这样触发器可以根据具体数据决定是否执行以及如何执行特定语句。5.4触发器（续）例2：在Northwind数据库中OrderDetails表中插入订单详情的时候，同步更新产品表Products中相应产品的库存量。INSERT触发器的工作过程（续）在定义了INSERT触发器的表上执行INSERT语句INSERT[OrderDetails]VALUES(10525,2,19.00,5,0.2)OrderDetailsOrderID105221052310524ProductID10417UnitPrice31.009.6530.00Quantity7924Discount0.20.150.0519.0020.210525插入的行同时加入到inserted表中inserted10525219.0050.2触发器动作被执行OrderDetailsOrderID105221052310524ProductID10417UnitPrice31.009.6530.00Quantity7924Discount0.20.150.0519.0020.210525触发器代码：USENorthwindCREATETRIGGEROrdDet_InsertONOrderDetailsAFTERINSERTASUPDATEPSETUnitsInStock=(P.UnitsInStock–I.Quantity)FROMProductsASPINNERJOINInsertedASIONP.ProductID=I.ProductIDUPDATEPSETUnitsInStock=(P.UnitsInStock–I.Quantity)FROMProductsASPINNERJOINInsertedASIONP.ProductID=I.ProductIDProductsProductIDUnitsInStock……123415106520215INSERT触发器的工作过程（续）DELETE触发器的工作过程在定义了DELETE触发器的表上执行DELETE语句激活DELETE触发器从数据库表中删除相应的行，并记录到deleted表中触发器动作被执行DELETE触发器不