《数据库完整性.pptx》由会员分享，可在线阅读，更多相关《数据库完整性.pptx（60页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、学习要点数据库完整性概述SQL2005完整性实现触发器概述创建、修改、查看、删除使用说明AFTER触发器INSTEADOF触发器第1页/共60页数据库完整性概述数据完整性（Data Integrity）定义指数据的准确性和一致性。目的 防止数据库中存在不符合语义规定的数据和防止因错误 信息的输入输出造成无效操作或错误信息分类实体完整性：用于保证数据库中数据表的每一个特定实体的记 录都是唯一的。域完整性：用于保证指定列的数据具有正确的数据类型、格 式和有效的数据范围。参照完整性：确保同一键值在所有相关联表之间都一致。用户定义完整性：由用户自定义的不属于其他任何完 整性分类的特定业务规则。第2页/
2、共60页数据库完整性概述数据完整性（Data Integrity）为维护数据库的完整性，DBMS必须：提供定义完整性约束条件的机制提供完整性检查的方法违约处理学习要点完整性类型完整性类型实实现现技技术术实体完整性实体完整性UNIQUE约束、约束、PRIMARYKEY约束、约束、IDENTITY属性属性域完整性域完整性CHECK约束、约束、FOREIGNKEY约束、约束、DEFAULT定义、定义、NOTNULL定义、定义、默认默认对象、对象、规则规则对象对象参照完整性参照完整性FOREIGNKEY约束、约束、CHECK约束、约束、触发器触发器用户定义完整性用户定义完整性CREATETABLE中的
3、所有列级和表级约束、中的所有列级和表级约束、存存储过程储过程、触发器触发器第3页/共60页完整性实现SQL Server 2005完整性实现DEFAULT约束 CHECK约束 PRIMARY KEY约束 UNIQUE约束 FOREIGN KEY约束 NOT NULL约束 标识列默认值规则 学习要点第4页/共60页约束DEFAULT约束 定义在用户未输入某些列的数据时，数据库系统为用户提供的默认值。（默认值必须与所约束列的数据类型相一致）是可以避免不允许为空值的数据错误 可以加快用户的输入速度 创建创建SSMS方式T-SQL方式约束列表第5页/共60页使用SSMS工具DEFAULT约束DEFAU
4、LT约束第6页/共60页CREATETABELBookInfo(b_IDVARCHAR(16),b_DetailVARCHAR(100)DEFAULT暂无描述暂无描述)使用T-SQL定义（一）DEFAULT约束CREATETABEL数据表名数据表名(列名列名数据类型数据类型CONSTRAIN约束名约束名DEFAULT默认值默认值，.)列级约束 示例NEXT第7页/共60页CREATETABELBookInfo(b_DetailVARCHAR(100)，CONSTRAINTde_DetailDEFAULT暂无描述FORb_Detail)使用T-SQL定义（二）DEFAULT约束NEXTCREAT
5、ETABEL数据表名数据表名(列名列名1数据类型，数据类型，CONSTRAIN约束名约束名DEFAULT默认值默认值FOR(列名列名,n),.)表级约束示例第8页/共60页使用T-SQL定义(三）DEFAULT约束ALTERTABEL数据表名数据表名(ADDCONSTRAIN约束名约束名DEFAULT默认值FOR(列名,n)，.)修改表：添加约束ALTERTABELBookInfo(ADDCONSTRAINTde_DetailDEFAULT暂无描述FORb_Detail)示例返回第9页/共60页约束CHECK约束定义限制用户输入数据的取值范围、格式等。一个列可以定义多个CHECK 约束。当往表
6、中插入记录或修改相应列的值时，系统检查约束条件是否满足，若不满足则拒绝执行数据操作。创建创建SSMS方式T-SQL方式约束列表第10页/共60页使用SSMS工具CHEECK约束CHECK约束第11页/共60页CREATETABEL数据表名数据表名(列名列名数据类型数据类型CONSTRAIN约束名约束名CHECK(逻辑表达式逻辑表达式)，.)CREATETABELBookInfo(b_QuantitySMALLINTCHECK(b_Quantity=1ANDb_Quantity=100),)使用T-SQL定义（一）CHECK 约束列级约束 示例NEXT第12页/共60页使用T-SQL定义（二、三
7、）CHECK 约束返回CREATETABEL数据表名数据表名(列名列名1数据类型，数据类型，CONSTRAIN约束名约束名CHECK(逻辑表达式逻辑表达式),.)表级约束 ALTERTABEL数据表名数据表名(ADDCONSTRAIN约束名约束名CHECK(逻辑表达式逻辑表达式)，.)修改表：添加约束第13页/共60页约束PRIMARY KEY约束定义若是单列作为主键，可以在列级定义或表级定义若是列集合作为主键，必须在表级定义一个表只能有一个PRIMARY KEY约束，受约束的列或列集合的值不能为空和重复，必须能唯一标识表中的行。定义了PRIMARY KEY 约束的列的列宽不能更改。创建创建S
8、SMS方式T-SQL方式约束列表第14页/共60页使用SSMS工具返回PRIMARY约束第15页/共60页使用T-SQL定义（一）NEXTPRIMARY约束CREATETABEL数据表名数据表名(列名列名数据类型数据类型CONSTRAIN约束名约束名PRIMARYKEY，.)CREATETABELBookInfo(b_IDVARCHAR(16)PRIMARYKEY，)列级约束 示例默认的约束名为：PK_PK_BookInfo BookInfo 第16页/共60页使用T-SQL定义（二）PRIMARY约束CREATETABEL数据表名数据表名(列名列名1数据类型，数据类型，CONSTRAIN约束
9、名约束名PRIMARYKEY(列名列名1,n),.)CREATETABELBorrowReturn(s_IDCHAR(8)NOTNULL,r_IDCHAR(8)NOTNULL,PRIMARYKEY(s_ID,r_ID)表级约束 示例组合键必须在表级定义默认的约束名为：PK_ PK_ BorrowReturn BorrowReturn NEXT第17页/共60页使用T-SQL定义（三）返回PRIMARY约束ALTERTABEL数据表名数据表名(ADDCONSTRAIN约束名约束名PRIMARYKEY(列名列名,)，.)ALTERTABLEBorrowReturnADDCONSTRAINTPK_B
10、orrowReturnPRIMARYKEY(s_ID,r_ID)示例修改表：添加约束第18页/共60页约束UNIQUE约束定义也称唯一值约束，确保在非主键列中不输入重复值。一个表可以定义多个UNIQUE 约束。在允许空值的列上保证唯一性时，应使用UNIQUE约束。当往表中插入记录或修改相应列的值时，系统检查是否有重复值，若有则拒绝执行数据操作。创建创建SSMS方式T-SQL方式约束列表第19页/共60页使用SSMS工具返回UNIQUE约束第20页/共60页使用T-SQL定义（一）NEXTUNIQUE约束CREATETABEL数据表名数据表名(列名列名数据类型数据类型CONSTRAIN约束名约束
11、名UNIQUE，.)CREATETABELReaderType(rt_NameVARCHAR(10)UNIQUE,)列级约束 示例第21页/共60页使用T-SQL定义（二、三）UNIQUE约束返回CREATETABEL数据表名数据表名(列名列名1数据类型，数据类型，CONSTRAIN约束名约束名UNIQUE(列名列名1,n),.)表级约束 ALTERTABEL数据表名数据表名(ADDCONSTRAIN约束名约束名UNIQUE(列名列名1,n)，.)修改表：添加约束第22页/共60页约束FOREIGN KEY约束定义也称为外键约束，用于强制实现参照完整性，建立两个表之间的关系。可以规定表中的某列
12、参照同一个表或另外一个表中已有的PRIMARY KEY 约束或UNIQUE 约束的列。一个表可以有多个FOREIGN KEY 约束。创建SSMS方式T-SQL方式约束列表第23页/共60页使用SSMS工具Step1NEXTFOREIGN KEY约束第24页/共60页使用SSMS工具Step2NEXTFOREIGN KEY约束第25页/共60页使用SSMS工具Step3NEXTFOREIGN KEY约束第26页/共60页使用SSMS工具Step4返回FOREIGN KEY约束第27页/共60页使用T-SQL定义（一）示例CREATETABEL数据表名数据表名(列名列名数据类型数据类型CONSTR
13、AIN约束名约束名FOREIGNKEYREFERENCES参照主键表参照主键表(参照列参照列)ONDELETECASCADE|ONUPDATECASCADE，.)列级约束 FOREIGN KEY约束 NO ACITON NO ACITON：默认选项。将引发错误，撤销对父表的删除（更新）操作 CASCADECASCADE：当在主表删除（更新）一行，则自动将子表中的对应行删除（更新）SET NULLSET NULL：删除（更新）父表的一行，子表相应行的外键值被置为NULLNULL SET DEFAULTSET DEFAULT：删除（更新）父表的一行，子表相应行的外键值被置为 外键的默认值第28页/
14、共60页使用T-SQL定义（一）FOREIGN KEY约束CREATETABELBookInfo(b_IDVARCHAR(16)PRIMARYKEY,b_NameVARCHAR(50)NOTNULL,bt_IDCHAR(10)REFERENCESBookType(bt_ID),)示例NEXT第29页/共60页使用T-SQL定义（二）示例CREATETABEL数据表名数据表名(列名列名1数据类型，数据类型，CONSTRAIN约束名约束名FOREIGNKEY(列名列名)REFERENCES参照主键表参照主键表(参照列参照列)ONDELETECASCADE|ONUPDATECASCADE,.)表级约
15、束 FOREIGN KEY约束第30页/共60页使用T-SQL定义（二）NEXTFOREIGN KEY约束CREATETABLEBorrowReturn(br_IDCHAR(6)PRIMARYKEY,s_IDCHAR(8)REFERENCESNOTNULL,r_IDCHAR(8)REFERENCESNOTNULL,FOREIGNKEY(s_ID)REFERENCESBookStore(s_ID)，FOREIGNKEY(r_ID)REFERENCESReaderInfo(r_ID)示例第31页/共60页使用T-SQL定义（三）UNIQUE约束返回ALTERTABEL数据表名数据表名(ADDCON
16、STRAIN约束名约束名FOREIGNKEY(列名列名)REFERENCES参照主键表参照主键表(参照列参照列)ONDELETECASCADE|ONUPDATECASCADE，.)修改表：添加约束ALTERTABELBookInfo(ADDFOREIGNKEY(bt_ID)REFERENCESBookType(bt_ID)ONDELETECASCADEONUPDATECASCADE,)示例第32页/共60页约束NOT NULL约束实质表中列可以定义为允许或不允许空值。默认情况下，创建表列允许空值。一个列中出现NULL 值，意味着用户还没有为该列输入值。若设置为NOT NULL，用户没有输入该列
17、值，则引发错误。示例示例SSMS方式T-SQL方式约束列表第33页/共60页使用SSMS工具返回NOT NULL约束第34页/共60页使用T-SQL定义NOT NULL 约束CREATETABEL数据表名数据表名(列名列名数据类型数据类型CONSTRAIN约束名约束名NULL|NOTNULL，)表级约束 CREATETABELBookInfo(b_IDvarchar(16)PRIMARYKEY,b_Namevarchar(50)UNIQUENOTNULL,bt_IDchar(10)REFERENCESBookType(bt_ID),b_Authorvarchar(20)NOTNULL,b_Tr
18、anslatorvarchar(20)NULL,)示例返回第35页/共60页约束标识列（IDENTITY）概述当用户向表中插入新的数据行时，系统自动为该行的IDENTITY 列赋值，并保证其值在表中的唯一性。常与PRIMARY KEY 约束一起使用。每个表中只能有一个IDENTITY 列，其列值不能由用户更新，也不允许绑定默认值或建立DEFAULT约束标识列的有效数据类型可以是任何整数数据类型分类的数据类型(bit 数据类型除外)，也可以是decimal 数据类型，但不允许出现小数。创建创建SSMS方式T-SQL方式约束列表第36页/共60页使用SSMS工具返回标识列（IDENTITY）第37
19、页/共60页使用T-SQL定义返回CREATETABEL数据表名数据表名(列名列名数据类型数据类型IDENTITY(种子种子,增量增量)，.)CREATETABELBorrowReturn(br_IDINTIDENTITY(1,1)PRIMARYKEY，)语法 示例标识列（IDENTITY）第38页/共60页DEFAULT（默认值）概 述“默认值”是一种数据库对象，可以被绑定到列。如果绑定的列或者数据类型没有明确提供值，则将默认指定的数据插入。“默认值”的执行与DEFAULT 约束功能相同，但是DEFAULT 约束定义和表存储在一起，当除去表时，将自动除去它。当在多个列中，特别是不同表中的列中
20、多次使用同一默认值时，适合采用“默认”技术。管理第39页/共60页管理“默认值”创建绑定解绑示例CREATEDEFAULT默认默认名称名称AS常数常数表达式表达式sp_bindefault默认名称默认名称,数据表名数据表名.列名列名sp_unbindefault数据表名数据表名.列名列名DEFAULT（默认值）第40页/共60页示例：创建绑定解绑返回sp_bindefaultd_Ssex,Student.Ssexsp_unbindefaultStudent.SsexUSEStudyGOCREATEDEFAULTd_SsexAS女女DEFAULT（默认值）第41页/共60页RULE（规则）概 述
21、“规则”是一种数据库对象，可以绑定到列，定义之后可以反复使用。执行“规则”功能与CHECK约束相同。每个列只能有一个绑定的规则。但列可以同时具有规则和多个CHECK 约束。约束列表第42页/共60页RULE（规则）管理“规则”创建绑定解绑示例CREATERULE规则规则名称名称AS条件条件表达式表达式sp_bindrule规则名规则名,数据表名数据表名.列名列名sp_unbindrule数据表名数据表名.列名列名第43页/共60页RULE（规则）示例：创建绑定解绑返回sp_bindruler_Ssex,Student.Ssexsp_unbindruleStudent.SsexUSEStudyG
22、OCREATERULEr_SsexASsex=女女ORsex=男男第44页/共60页触发器概述 触发器 触发器是特殊类型的存储过程，主要由操作事件(INSERT、UPDATE、DELETE)触发而被自动执行。与表相关联：必须定义在表上。不能直接调用，也不能传递或接受参数 触发器可以实现比约束更复杂的数据完整性，经常用于 加强数据的完整性约束和业务规则。触发器本身是一个特殊的事务单位。第45页/共60页触发器概述 触发器的类型按触发时刻分 AFTER触发器 执行触发操作和处理完约束后激发，只能定义于表上，同一个表上可定义多个。INSTEAD触发器 由触发器的程序代替触发语句的执行，在处理约束之前
23、激发，可在表或视图上创建，同一个表或视图只可定义一个。按触发语句分 INSERT触发器 UPDATE触发器、DELETE触发器 第46页/共60页触发器Inserted表和deleted表 触发器触发时，系统自动在内存创建触发器触发时，系统自动在内存创建deleteddeleted表或表或insertedinserted表。它们是只读的，不允许修改；触发器执行完表。它们是只读的，不允许修改；触发器执行完成后，自动删除。成后，自动删除。inserted 表 临时保存了插入或更新后的记录行 可以从inserted表中检查插入的数据是否满足业务需求，如果不满足，则向用户报告错误消息，并回滚插入操作。
24、deleted 表临时保存了删除或更新前的记录行 可以从deleted表中检查被删除的数据是否满足业务需求，如果不满足，则向用户报告错误消息，并回滚插入操作。第47页/共60页触发器触发器使用的限制CREATE TRIGGER 必须是批处理中的第一条语句。在同一个表中约束的执行优先于触发器，若在操作中触发器与约束发生冲突，触发器将不执行。不能在临时表上建立触发器，但是在触发器定义中可以引用临时表。触发器中不允许以下 T-SQL 语句：ALTER DATABASEALTER DATABASECREATE DATABASECREATE DATABASEDISK INITDISK INITDISK
25、RESIZEDISK RESIZEDROP DATABASEDROP DATABASELOAD DATABASELOAD DATABASELOAD LOGLOAD LOGRECONFIGURERECONFIGURERESTORE DATABASERESTORE DATABASERESTORE LOGRESTORE LOG第48页/共60页触发器Inserted表和deleted表 inserted表和deleted表存放的信息修改操作inserted表deleted表增加增加(INSERT)(INSERT)记录记录存放新增的记录存放新增的记录-删除删除(DELETE)(DELETE)记录记录-
26、存放被删除的记录存放被删除的记录修改修改(UPDATE)(UPDATE)记录记录存放更新后的记录存放更新后的记录存放更新前的记录存放更新前的记录第49页/共60页触发器创建（SSMS）第50页/共60页触发器创建（SSMS）创建触发器的模板，只需修改相应的参数就可创建触发器第51页/共60页触发器创建（TSQL）基本语句格式CREATE TRIGGERCREATE TRIGGER 触发器名ONON 表名或视图名 FORFOR|AFTERAFTER|INSTEAD OFINSTEAD OF INSERTINSERT,UPDATEUPDATE,DELETEDELETEAS AS IF UPDATE
27、IF UPDATE(列名1)1)ANDAND|OROR UPDATEUPDATE(列名2)2).n .n SQL SQL语句第52页/共60页触发器创建（TSQL）基本语句格式CREATE TRIGGERCREATE 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语句第53页/共60页触发器创
28、建（TSQL）参数说明u AFTER AFTER:执行指定操作语句和处理完约束后激发触发器。执行指定操作语句和处理完约束后激发触发器。u FORFOR:如果仅指定如果仅指定 FOR FOR 关键字，则关键字，则 AFTER AFTER 是默认设置。是默认设置。u INSTEAD OFINSTEAD OF:指定语句激发触发器并被代替执行。指定语句激发触发器并被代替执行。u DELETEDELETE、INSERTINSERT、UPDATEUPDATE：指定激活触发器的数据修改语：指定激活触发器的数据修改语句，必须至少指定一个选项，若指定的选项多于一个，需用句，必须至少指定一个选项，若指定的选项多于
29、一个，需用逗号分隔这些选项。逗号分隔这些选项。u ASAS：指定触发器要执行的操作。：指定触发器要执行的操作。u IF UPDATEIF UPDATE(列名列名1)1)ANDAND|OROR UPDATEUPDATE(列名列名2)2):指定何列指定何列数据修改时激发触发器。不能用于数据修改时激发触发器。不能用于DELETEDELETE语句。语句。第54页/共60页触发器创建（TSQL）示例1CREATE TRIGGERCREATE TRIGGER tr_addbook tr_addbookONON BookInfo BookInfoFORFOR INSERT,UPDATE INSERT,UPD
30、ATEASASBEGINBEGIN 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 Publisher,insertedinserted WHERE Publisher.p_ID=inserted.p_ID WHERE Publisher.p_ID=inserted.p_ID SELECT bt_no=BookType.bt_I
31、DSELECT 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 NULLIF p_no is NULL OR bt_no is NULL BEGIN BEGIN ROLLBACK TRANSACTIONROLLBACK TRANSACTION RAISERROR(RAISERROR(不存在这样的出版社或图书类别！,1
32、6,10),16,10)END ENDENDEND第55页/共60页触发器创建（TSQL）示例2CREATE TRIGGERCREATE TRIGGER tr_change tr_changeONON BorrowReturn BorrowReturnFORFOR INSERT INSERTASASBEGINBEGIN UPDATE BookStore UPDATE BookStore SET s_Status=SET s_Status=借出 WHERE s_ID=(SELECT s_ID FROM WHERE s_ID=(SELECT s_ID FROM insertedinserted)E
33、NDEND第56页/共60页触发器禁用或启用（SSMS）第57页/共60页触发器启用或启用触发器（T-SQL）基本语句格式示例学习要点ALTER TABLE ALTER TABLE 表名 ENABLEENABLE|DISABLEDISABLE TRIGGER TRIGGER ALL|ALL|触发器名,n,n ALTER TABLE ALTER TABLE BookInfoBookInfo DISABLEDISABLE TRIGGER TRIGGER tr_addbooktr_addbook第58页/共60页触发器修改触发器（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语句第59页/共60页感谢您的欣赏第60页/共60页