07-第7章-数据库完整性.ppt

《07-第7章-数据库完整性.ppt》由会员分享，可在线阅读，更多相关《07-第7章-数据库完整性.ppt（60页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Copy by spring第七章第七章数据库完整性学习要点数据库完整性概述SQL2005完整性实现触发器概述创建、修改、查看、删除使用说明AFTER触发器INSTEADOF触发器数据库完整性概述数据完整性数据完整性（DataIntegrity）定义指数据的准确性和一致性。目的 防止数据库中存在不符合语义规定的数据和防止因错误 信息的输入输出造成无效操作或错误信息分类实体完整性实体完整性：用于保证数据库中数据表的每一个特定实体的记 录都是唯一的。域完整性域完整性：用于保证指定列的数据具有正确的数据类型、格 式和有效的数据范围。参照完整参照完整性：确保同一键值在所有相关联表之间都一致。用户定义完

2、整性用户定义完整性：由用户自定义的不属于其他任何完 整性分类的特定业务规则。数据库完整性概述数据完整性数据完整性（DataIntegrity）为维护数据库的完整性，DBMS必须：提供定义完整性约束条件的机制提供完整性检查的方法违约处理学习要点完整性类型完整性类型实实现现技技术术实体完整性实体完整性UNIQUE约束、约束、PRIMARYKEY约束、约束、IDENTITY属性属性域完整性域完整性CHECK约束、约束、FOREIGNKEY约束、约束、DEFAULT定义、定义、NOTNULL定义、定义、默认默认对象、对象、规则规则对象对象参照完整性参照完整性FOREIGNKEY约束、约束、CHECK约

3、束、约束、触发器触发器用户定义完整性用户定义完整性CREATETABLE中的所有列级和表级约束、中的所有列级和表级约束、存存储过程储过程、触发器触发器完整性实现SQLServer2005完整性实现完整性实现DEFAULT约束 CHECK约束 PRIMARY KEY约束 UNIQUE约束 FOREIGN KEY约束 NOT NULL约束 标识列默认值规则 学习要点约束DEFAULT约束约束定义在用户未输入某些列的数据时，数据库系统为用户提供的默认值。（默认值必须与所约束列的数据类型相一致）是可以避免不允许为空值的数据错误 可以加快用户的输入速度 创建创建SSMS方式T-SQL方式约束列表使用SS

4、MS工具DEFAULT约束DEFAULT约束CREATETABELBookInfo(b_IDVARCHAR(16),b_DetailVARCHAR(100)DEFAULT暂无描述暂无描述)使用使用T-SQL定义（一）定义（一）DEFAULT约束CREATETABEL数据表名数据表名(列名列名数据类型数据类型CONSTRAIN约束名约束名DEFAULT默认值默认值，.)列级约束列级约束 示示例例NEXTCREATETABELBookInfo(b_DetailVARCHAR(100)，CONSTRAINTde_DetailDEFAULT暂无描述暂无描述FORb_Detail)使用使用T-SQL定义

5、（二）定义（二）DEFAULT约束NEXTCREATETABEL数据表名数据表名(列名列名1数据类型，数据类型，CONSTRAIN约束名约束名DEFAULT默认值默认值FOR(列名列名,n),.)表级约束表级约束示示例例使用使用T-SQL定义定义(三）三）DEFAULT约束ALTERTABEL数据表名数据表名(ADDCONSTRAIN约束名约束名DEFAULT默认值默认值FOR(列名列名,n)，.)修改表：添加约束修改表：添加约束ALTERTABELBookInfo(ADDCONSTRAINTde_DetailDEFAULT暂无描述暂无描述FORb_Detail)示示例例返回约束CHECK约束

6、约束定义限制用户输入数据的取值范围、格式等。一个列可以定义多个CHECK 约束。当往表中插入记录或修改相应列的值时，系统检查约束条件是否满足，若不满足则拒绝执行数据操作。创建创建SSMS方式T-SQL方式约束列表使用SSMS工具CHEECK约束CHECK约束CREATETABEL数据表名数据表名(列名列名数据类型数据类型CONSTRAIN约束名约束名CHECK(逻辑表达式逻辑表达式)，.)CREATETABELBookInfo(b_QuantitySMALLINTCHECK(b_Quantity=1ANDb_Quantity=100),)使用使用T-SQL定义（一）定义（一）CHECK约束列级

7、约束列级约束 示示例例NEXT使用使用T-SQL定义（二、三）定义（二、三）CHECK约束返回CREATETABEL数据表名数据表名(列名列名1数据类型，数据类型，CONSTRAIN约束名约束名CHECK(逻辑表达式逻辑表达式),.)表级约束表级约束 ALTERTABEL数据表名数据表名(ADDCONSTRAIN约束名约束名CHECK(逻辑表达式逻辑表达式)，.)修改表：添加约束修改表：添加约束约束PRIMARYKEY约束约束定义若是单列作为主键，可以在列级定义或表级定义若是列集合作为主键，必须在表级定义一个表只能有一个PRIMARY KEY约束，受约束的列或列集合的值不能为空和重复，必须能唯

8、一标识表中的行。定义了PRIMARY KEY 约束的列的列宽不能更改。创建创建SSMS方式T-SQL方式约束列表使用使用SSMS工具工具返回PRIMARY约束使用使用T-SQL定义（一）定义（一）NEXTPRIMARY约束CREATETABEL数据表名数据表名(列名列名数据类型数据类型CONSTRAIN约束名约束名PRIMARYKEY，.)CREATETABELBookInfo(b_IDVARCHAR(16)PRIMARYKEY，)列级约束列级约束 示示例例默认的约束名为：默认的约束名为：PK_PK_BookInfo BookInfo 使用使用T-SQL定义（二）定义（二）PRIMARY约束C

9、REATETABEL数据表名数据表名(列名列名1数据类型，数据类型，CONSTRAIN约束名约束名PRIMARYKEY(列名列名1,n),.)CREATETABELBorrowReturn(s_IDCHAR(8)NOTNULL,r_IDCHAR(8)NOTNULL,PRIMARYKEY(s_ID,r_ID)表级约束表级约束 示示例例组合键必须在表级定义组合键必须在表级定义默认的约束名为：默认的约束名为：PK_ PK_ BorrowReturn BorrowReturn NEXT使用使用T-SQL定义（三）定义（三）返回PRIMARY约束ALTERTABEL数据表名数据表名(ADDCONSTRA

10、IN约束名约束名PRIMARYKEY(列名列名,)，.)ALTERTABLEBorrowReturnADDCONSTRAINTPK_BorrowReturnPRIMARYKEY(s_ID,r_ID)示示例例修改表：添加约束修改表：添加约束约束UNIQUE约束约束定义也称唯一值约束，确保在非主键列中不输入重复值。一个表可以定义多个UNIQUE 约束。在允许空值的列上保证唯一性时，应使用UNIQUE约束。当往表中插入记录或修改相应列的值时，系统检查是否有重复值，若有则拒绝执行数据操作。创建创建SSMS方式T-SQL方式约束列表使用使用SSMS工具工具返回UNIQUE约束使用使用T-SQL定义（一）

11、定义（一）NEXTUNIQUE约束CREATETABEL数据表名数据表名(列名列名数据类型数据类型CONSTRAIN约束名约束名UNIQUE，.)CREATETABELReaderType(rt_NameVARCHAR(10)UNIQUE,)列级约束列级约束 示示例例使用使用T-SQL定义（二、三）定义（二、三）UNIQUE约束返回CREATETABEL数据表名数据表名(列名列名1数据类型，数据类型，CONSTRAIN约束名约束名UNIQUE(列名列名1,n),.)表级约束表级约束 ALTERTABEL数据表名数据表名(ADDCONSTRAIN约束名约束名UNIQUE(列名列名1,n)，.)修

12、改表：添加约束修改表：添加约束约束FOREIGNKEY约束约束定义也称为外键约束，用于强制实现参照完整性，建立两个表之间的关系。可以规定表中的某列参照同一个表或另外一个表中已有的PRIMARY KEY 约束或UNIQUE 约束的列。一个表可以有多个FOREIGN KEY 约束。创建SSMS方式T-SQL方式约束列表使用使用SSMS工具工具Step1NEXTFOREIGN KEY约束使用使用SSMS工具工具Step2NEXTFOREIGN KEY约束使用使用SSMS工具工具Step3NEXTFOREIGN KEY约束使用使用SSMS工具工具Step4返回FOREIGN KEY约束使用使用T-SQ

13、L定义（一）定义（一）示例CREATETABEL数据表名数据表名(列名列名数据类型数据类型CONSTRAIN约束名约束名FOREIGNKEYREFERENCES参照主键表参照主键表(参照列参照列)ONDELETECASCADE|ONUPDATECASCADE，.)列级约束列级约束 FOREIGN KEY约束 NO ACITON NO ACITON：默认选项。将引发错误，撤销对父表的删除（更新）操作：默认选项。将引发错误，撤销对父表的删除（更新）操作 CASCADECASCADE：当在主表删除（更新）一行，则自动将子表中的对应行删除（更新）：当在主表删除（更新）一行，则自动将子表中的对应行删除（

14、更新）SET NULLSET NULL：删除（更新）父表的一行，子表相应行的外键值被置为：删除（更新）父表的一行，子表相应行的外键值被置为NULLNULL SET DEFAULTSET DEFAULT：删除（更新）父表的一行，子表相应行的外键值被置为删除（更新）父表的一行，子表相应行的外键值被置为 外键的默认值外键的默认值使用使用T-SQL定义（一）定义（一）FOREIGN KEY约束CREATETABELBookInfo(b_IDVARCHAR(16)PRIMARYKEY,b_NameVARCHAR(50)NOTNULL,bt_IDCHAR(10)REFERENCESBookType(bt_

15、ID),)示示例例NEXT使用使用T-SQL定义（二）定义（二）示例CREATETABEL数据表名数据表名(列名列名1数据类型，数据类型，CONSTRAIN约束名约束名FOREIGNKEY(列名列名)REFERENCES参照主键表参照主键表(参照列参照列)ONDELETECASCADE|ONUPDATECASCADE,.)表级约束表级约束 FOREIGN KEY约束使用使用T-SQL定义（二）定义（二）NEXTFOREIGN KEY约束CREATETABLEBorrowReturn(br_IDCHAR(6)PRIMARYKEY,s_IDCHAR(8)REFERENCESNOTNULL,r_ID

16、CHAR(8)REFERENCESNOTNULL,FOREIGNKEY(s_ID)REFERENCESBookStore(s_ID)，FOREIGNKEY(r_ID)REFERENCESReaderInfo(r_ID)示示例例使用使用T-SQL定义（三）定义（三）UNIQUE约束返回ALTERTABEL数据表名数据表名(ADDCONSTRAIN约束名约束名FOREIGNKEY(列名列名)REFERENCES参照主键表参照主键表(参照列参照列)ONDELETECASCADE|ONUPDATECASCADE，.)修改表：添加约束修改表：添加约束ALTERTABELBookInfo(ADDFOREI

17、GNKEY(bt_ID)REFERENCESBookType(bt_ID)ONDELETECASCADEONUPDATECASCADE,)示示例例约束NOTNULL约束约束实质表中列可以定义为允许或不允许空值。默认情况下，创建表列允许空值。一个列中出现NULL 值，意味着用户还没有为该列输入值。若设置为NOT NULL，用户没有输入该列值，则引发错误。示例示例SSMS方式T-SQL方式约束列表使用使用SSMS工具工具返回NOT NULL约束使用使用T-SQL定义定义NOTNULL约束CREATETABEL数据表名数据表名(列名列名数据类型数据类型CONSTRAIN约束名约束名NULL|NOTN

18、ULL，)表级约束表级约束 CREATETABELBookInfo(b_IDvarchar(16)PRIMARYKEY,b_Namevarchar(50)UNIQUENOTNULL,bt_IDchar(10)REFERENCESBookType(bt_ID),b_Authorvarchar(20)NOTNULL,b_Translatorvarchar(20)NULL,)示示例例返回约束标识列（标识列（IDENTITY）概述当用户向表中插入新的数据行时，系统自动为该行的IDENTITY 列赋值，并保证其值在表中的唯一性。常与PRIMARY KEY 约束一起使用。每个表中只能有一个IDENTITY

19、 列，其列值不能由用户更新，也不允许绑定默认值或建立DEFAULT约束标识列的有效数据类型可以是任何整数数据类型分类的数据类型(bit 数据类型除外)，也可以是decimal 数据类型，但不允许出现小数。创建创建SSMS方式T-SQL方式约束列表使用使用SSMS工具工具返回标识列（IDENTITY）使用使用T-SQL定义定义返回CREATETABEL数据表名数据表名(列名列名数据类型数据类型IDENTITY(种子种子,增量增量)，.)CREATETABELBorrowReturn(br_IDINTIDENTITY(1,1)PRIMARYKEY，)语法语法 示示例例标识列（IDENTITY）DE

20、FAULT（默认值）概概述述“默认值”是一种数据库对象，可以被绑定到列。如果绑定的列或者数据类型没有明确提供值，则将默认指定的数据插入。“默认值”的执行与DEFAULT 约束功能相同，但是DEFAULT 约束定义和表存储在一起，当除去表时，将自动除去它。当在多个列中，特别是不同表中的列中多次使用同一默认值时，适合采用“默认”技术。管理管理管理“默认值默认值”创建绑定解绑示例CREATEDEFAULT默认默认名称名称AS常数常数表达式表达式sp_bindefault默认名称默认名称,数据表名数据表名.列名列名sp_unbindefault数据表名数据表名.列名列名DEFAULT（默认值）示例：示

21、例：创建绑定解绑返回sp_bindefaultd_Ssex,Student.Ssexsp_unbindefaultStudent.SsexUSEStudyGOCREATEDEFAULTd_SsexAS女女DEFAULT（默认值）RULE（规则）概概述述“规则”是一种数据库对象，可以绑定到列，定义之后可以反复使用。执行“规则”功能与CHECK约束相同。每个列只能有一个绑定的规则。但列可以同时具有规则和多个CHECK 约束。约束列表RULE（规则）管理管理“规则规则”创建绑定解绑示例CREATERULE规则规则名称名称AS条件条件表达式表达式sp_bindrule规则名规则名,数据表名数据表名.列

22、名列名sp_unbindrule数据表名数据表名.列名列名RULE（规则）示例：示例：创建绑定解绑返回sp_bindruler_Ssex,Student.Ssexsp_unbindruleStudent.SsexUSEStudyGOCREATERULEr_SsexASsex=女女ORsex=男男触发器概述概述触发器 触发器是特殊类型的存储过程，主要由操作事件(INSERT、UPDATE、DELETE)触发而被自动执行自动执行。与表相关联：必须定义在表上。不能直接调用，也不能传递或接受参数 触发器可以实现比约束更复杂的数据完整性，经常用于 加强数据的完整性约束和业务规则。触发器本身是一个特殊的事

23、务单位。触发器概述概述触发器的类型按触发时刻分按触发时刻分 AFTER触发器 执行触发操作和处理完约束后激发，只能定义于表上，同一个表上可定义多个。INSTEAD触发器 由触发器的程序代替触发语句的执行，在处理约束之前激发，可在表或视图上创建，同一个表或视图只可定义一个。按触发语句分按触发语句分 INSERT触发器 UPDATE触发器、DELETE触发器 触发器Inserted表和表和deleted表表 触发器触发时，系统自动在内存创建触发器触发时，系统自动在内存创建deleteddeleted表或表或insertedinserted表。表。它们是只读的，不允许修改；触发器执行完成后，自动删除

24、。它们是只读的，不允许修改；触发器执行完成后，自动删除。inserted 表 临时保存了插入或更新后的记录行 可以从inserted表中检查插入的数据是否满足业务需求，如果不满足，则向用户报告错误消息，并回滚插入操作。deleted 表临时保存了删除或更新前的记录行 可以从deleted表中检查被删除的数据是否满足业务需求，如果不满足，则向用户报告错误消息，并回滚插入操作。触发器触发器使用的限制触发器使用的限制CREATE TRIGGER 必须是批处理中的第一条语句。在同一个表中约束的执行优先于触发器，若在操作中触发器与约束发生冲突，触发器将不执行。不能在临时表上建立触发器，但是在触发器定义中

25、可以引用临时表。触发器中不允许以下 T-SQL 语句：ALTER DATABASEALTER DATABASECREATE DATABASECREATE DATABASEDISK INITDISK INITDISK RESIZEDISK RESIZEDROP DATABASEDROP DATABASELOAD DATABASELOAD DATABASELOAD LOGLOAD LOGRECONFIGURERECONFIGURERESTORE DATABASERESTORE DATABASERESTORE LOGRESTORE LOG触发器Inserted表和表和deleted表表 insert

26、ed表和deleted表存放的信息修改操作inserted表deleted表增加增加(INSERT)(INSERT)记录记录存放新增的存放新增的记录记录-删删除除(DELETE)(DELETE)记录记录-存放被存放被删删除的除的记录记录修改修改(UPDATE)(UPDATE)记录记录存放更新后的存放更新后的记录记录存放更新前的存放更新前的记录记录触发器创建（创建（SSMS）触发器创建（创建（SSMS）创创建触建触发发器的模板，只需修改器的模板，只需修改相相应应的参数就可的参数就可创创建触建触发发器器触发器创建（创建（TSQL）基本语句格式CREATE TRIGGERCREATE TRIGGER

27、 触触发发器名器名ONON 表名或表名或视图视图名名 FORFOR|AFTERAFTER|INSTEAD OFINSTEAD OF INSERTINSERT,UPDATEUPDATE,DELETEDELETEAS AS IF UPDATEIF UPDATE(列名列名1)1)ANDAND|OROR UPDATEUPDATE(列名列名2)2).n .n SQL SQL语语句句触发器创建（创建（TSQL）基本语句格式CREATE TRIGGERCREATE TRIGGER 触触发发器名器名ONON 表名或表名或视图视图名名 FORFOR|AFTERAFTER|INSTEAD OFINSTEAD OF

28、 INSERTINSERT,UPDATEUPDATE,DELETEDELETEAS AS IF UPDATEIF UPDATE(列名列名1)1)ANDAND|OROR UPDATEUPDATE(列名列名2)2).n .n SQL SQL语语句句触发器创建（创建（TSQL）参数说明u AFTER AFTER:执行指定操作语句和处理完约束后激发触发器。执行指定操作语句和处理完约束后激发触发器。u FORFOR:如果仅指定如果仅指定 FOR FOR 关键字，则关键字，则 AFTER AFTER 是默认设置。是默认设置。u INSTEAD OFINSTEAD OF:指定语句激发触发器并被代替执行。指定

29、语句激发触发器并被代替执行。u DELETEDELETE、INSERTINSERT、UPDATEUPDATE：指定激活触发器的数据修改语：指定激活触发器的数据修改语句，必须至少指定一个选项，若指定的选项多于一个，需用句，必须至少指定一个选项，若指定的选项多于一个，需用逗号分隔这些选项。逗号分隔这些选项。u ASAS：指定触发器要执行的操作。：指定触发器要执行的操作。u IF UPDATEIF UPDATE(列名列名1)1)ANDAND|OROR UPDATEUPDATE(列名列名2)2):指定何列指定何列数据修改时激发触发器。不能用于数据修改时激发触发器。不能用于DELETEDELETE语句。

30、语句。触发器创建（创建（TSQL）示例1CREATE TRIGGERCREATE TRIGGER tr_addbook tr_addbookONON BookInfo BookInfoFORFOR INSERT,UPDATE INSERT,UPDATEASASBEGINBEGIN DECLARE p_no CHAR(4)DECLARE p_no CHAR(4),DECLARE bt_no CHAR(10)DECLARE bt_no CHAR(10)SELECT p_no=Publisher.p_ID FROM Publisher,SELECT p_no=Publisher.p_ID FROM

31、Publisher,insertedinserted WHERE Publisher.p_ID=inserted.p_ID WHERE Publisher.p_ID=inserted.p_ID SELECT bt_no=BookType.bt_IDSELECT bt_no=BookType.bt_ID FROM BookType,FROM BookType,insertedinserted WHERE BookType.bt_ID=inserted.bt_IDWHERE BookType.bt_ID=inserted.bt_ID IF p_no is NULL OR bt_no is NULL

32、IF p_no is NULL OR bt_no is NULL BEGIN BEGIN ROLLBACK TRANSACTIONROLLBACK TRANSACTION RAISERROR(RAISERROR(不存在不存在这样这样的出版社或的出版社或图书类别图书类别！,16,10),16,10)END ENDENDEND触发器创建（创建（TSQL）示例2CREATE TRIGGERCREATE TRIGGER tr_change tr_changeONON BorrowReturn BorrowReturnFORFOR INSERT INSERTASASBEGINBEGIN UPDATE B

33、ookStore UPDATE BookStore SET s_Status=SET s_Status=借出借出 WHERE s_ID=(SELECT s_ID FROM WHERE s_ID=(SELECT s_ID FROM insertedinserted)ENDEND触发器禁用或启用（禁用或启用（SSMS）触发器启用或启用触发器（启用或启用触发器（T-SQL）基本语句格式示例学习要点ALTER TABLE ALTER TABLE 表名表名 ENABLEENABLE|DISABLEDISABLE TRIGGER TRIGGER ALL|ALL|触触发发器名器名,n n ALTER TAB

34、LE ALTER TABLE BookInfoBookInfo DISABLEDISABLE TRIGGER TRIGGER tr_addbooktr_addbook触发器修改触发器（修改触发器（T-SQL）修改删除学习要点DROP TRIGGER 触发器名ALTER TRIGGERALTER TRIGGER 触触发发器名器名ONON 表名或表名或视图视图名名 FORFOR|AFTERAFTER|INSTEAD OFINSTEAD OF INSERTINSERT,UPDATEUPDATE,DELETEDELETEAS AS IF UPDATEIF UPDATE(列名列名1)1)ANDAND|OROR UPDATEUPDATE(列名列名2)2).n .n SQL SQL语语句句