教学课件：第7章-数据库完整性.ppt

第七章,数据库完整性,学习要点,数据库完整性概述SQL2005完整性实现触发器概述创建、修改、查看、删除使用说明AFTER触发器INSTEADOF触发器,数据库完整性概述,数据完整性（Data Integrity）定义指数据的准确性和一致性。目的 防止数据库中存在不符合语义规定的数据和防止因错误 信息的输入输出造成无效操作或错误信息分类实体完整性：用于保证数据库中数据表的每一个特定实体的记 录都是唯一的。域完整性：用于保证指定列的数据具有正确的数据类型、格 式和有效的数据范围。参照完整性：确保同一键值在所有相关联表之间都一致。用户定义完整性：由用户自定义的不属于其他任何完 整性分类的特定业务规则。,数据库完整性概述,数据完整性（Data Integrity）为维护数据库的完整性，DBMS必须：提供定义完整性约束条件的机制提供完整性检查的方法违约处理,学习要点,完整性实现,SQL Server 2005完整性实现DEFAULT约束 CHECK约束 PRIMARY KEY约束 UNIQUE约束 FOREIGN KEY约束 NOT NULL约束 标识列默认值规则,学习要点,约束,DEFAULT约束 定义在用户未输入某些列的数据时，数据库系统为用户提供的默认值。（默认值必须与所约束列的数据类型相一致）是可以避免不允许为空值的数据错误 可以加快用户的输入速度 创建SSMS方式T-SQL方式,约束列表,使用SSMS工具,DEFAULT约束,DEFAULT约束,CREATE TABEL BookInfo(b_ID VARCHAR(16),b_Detail VARCHAR(100)DEFAULT 暂无描述),使用T-SQL定义（一）,DEFAULT约束,CREATE TABEL 数据表名(列名 数据类型 CONSTRAIN 约束名 DEFAULT 默认值，.),列级约束,示 例,NEXT,CREATE TABEL BookInfo(b_Detail VARCHAR(100)，CONSTRAINT de_Detail DEFAULT 暂无描述 FOR b_Detail),使用T-SQL定义（二）,DEFAULT约束,NEXT,CREATE TABEL 数据表名(列名1 数据类型，CONSTRAIN 约束名 DEFAULT 默认值 FOR(列名,n),.),表级约束,示 例,使用T-SQL定义(三）,DEFAULT约束,ALTER TABEL 数据表名(ADD CONSTRAIN 约束名 DEFAULT 默认值 FOR(列名,n)，.),修改表：添加约束,ALTER TABEL BookInfo(ADD CONSTRAINT de_Detail DEFAULT 暂无描述 FOR b_Detail),示 例,返回,约束,CHECK约束定义限制用户输入数据的取值范围、格式等。一个列可以定义多个CHECK 约束。当往表中插入记录或修改相应列的值时，系统检查约束条件是否满足，若不满足则拒绝执行数据操作。创建SSMS方式T-SQL方式,约束列表,使用SSMS工具,CHEECK约束,CHECK约束,CREATE TABEL 数据表名(列名 数据类型 CONSTRAIN 约束名 CHECK(逻辑表达式)，.),CREATE TABEL BookInfo(b_Quantity SMALLINT CHECK(b_Quantity=1 AND b_Quantity=100),),使用T-SQL定义（一）,CHECK 约束,列级约束,示 例,NEXT,使用T-SQL定义（二、三）,CHECK 约束,返回,CREATE TABEL 数据表名(列名1 数据类型，CONSTRAIN 约束名 CHECK(逻辑表达式),.),表级约束,ALTER TABEL 数据表名(ADD CONSTRAIN 约束名 CHECK(逻辑表达式)，.),修改表：添加约束,约束,PRIMARY KEY约束定义若是单列作为主键，可以在列级定义或表级定义若是列集合作为主键，必须在表级定义一个表只能有一个PRIMARY KEY约束，受约束的列或列集合的值不能为空和重复，必须能唯一标识表中的行。定义了PRIMARY KEY 约束的列的列宽不能更改。创建SSMS方式T-SQL方式,约束列表,使用SSMS工具,返回,PRIMARY约束,使用T-SQL定义（一）,NEXT,PRIMARY约束,CREATE TABEL 数据表名(列名 数据类型 CONSTRAIN 约束名 PRIMARY KEY，.),CREATE TABEL BookInfo(b_ID VARCHAR(16)PRIMARY KEY，),列级约束,示 例,默认的约束名为：PK_BookInfo,使用T-SQL定义（二）,PRIMARY约束,CREATE TABEL 数据表名(列名1 数据类型，CONSTRAIN 约束名 PRIMARY KEY(列名1,n),.),CREATE TABEL BorrowReturn(s_ID CHAR(8)NOT NULL,r_ID CHAR(8)NOT NULL,PRIMARY KEY(s_ID,r_ID),表级约束,示 例,组合键必须在表级定义默认的约束名为：PK_ BorrowReturn,NEXT,使用T-SQL定义（三）,返回,PRIMARY约束,ALTER TABEL 数据表名(ADD CONSTRAIN 约束名 PRIMARY KEY(列名,)，.),ALTER TABLE BorrowReturn ADD CONSTRAINT PK_ BorrowReturn PRIMARY KEY(s_ID,r_ID),示 例,修改表：添加约束,约束,UNIQUE约束定义也称唯一值约束，确保在非主键列中不输入重复值。一个表可以定义多个UNIQUE 约束。在允许空值的列上保证唯一性时，应使用UNIQUE约束。当往表中插入记录或修改相应列的值时，系统检查是否有重复值，若有则拒绝执行数据操作。创建SSMS方式T-SQL方式,约束列表,使用SSMS工具,返回,UNIQUE约束,使用T-SQL定义（一）,NEXT,UNIQUE约束,CREATE TABEL 数据表名(列名 数据类型 CONSTRAIN 约束名 UNIQUE，.),CREATE TABEL ReaderType(rt_ Name VARCHAR(10)UNIQUE,),列级约束,示 例,使用T-SQL定义（二、三）,UNIQUE约束,返回,CREATE TABEL 数据表名(列名1 数据类型，CONSTRAIN 约束名 UNIQUE(列名1,n),.),表级约束,ALTER TABEL 数据表名(ADD CONSTRAIN 约束名 UNIQUE(列名1,n)，.),修改表：添加约束,约束,FOREIGN KEY约束定义也称为外键约束，用于强制实现参照完整性，建立两个表之间的关系。可以规定表中的某列参照同一个表或另外一个表中已有的PRIMARY KEY 约束或UNIQUE 约束的列。一个表可以有多个FOREIGN KEY 约束。创建SSMS方式T-SQL方式,约束列表,使用SSMS工具Step1,NEXT,FOREIGN KEY约束,使用SSMS工具Step2,NEXT,FOREIGN KEY约束,使用SSMS工具Step3,NEXT,FOREIGN KEY约束,使用SSMS工具Step4,返回,FOREIGN KEY约束,使用T-SQL定义（一）,示例,CREATE TABEL 数据表名(列名 数据类型 CONSTRAIN 约束名 FOREIGN KEY REFERENCES 参照主键表(参照列)ON DELETE CASCADE|ON UPDATE CASCADE，.),列级约束,FOREIGN KEY约束,NO ACITON：默认选项。将引发错误，撤销对父表的删除（更新）操作 CASCADE：当在主表删除（更新）一行，则自动将子表中的对应行删除（更新）SET NULL：删除（更新）父表的一行，子表相应行的外键值被置为NULL SET DEFAULT：删除（更新）父表的一行，子表相应行的外键值被置为 外键的默认值,使用T-SQL定义（一）,FOREIGN KEY约束,CREATE TABEL BookInfo(b_ID VARCHAR(16)PRIMARY KEY,b_Name VARCHAR(50)NOT NULL,bt_ID CHAR(10)REFERENCES BookType(bt_ID),),示 例,NEXT,使用T-SQL定义（二）,示例,CREATE TABEL 数据表名(列名1 数据类型，CONSTRAIN 约束名 FOREIGN KEY(列名)REFERENCES 参照主键表(参照列)ON DELETE CASCADE|ON UPDATE CASCADE,.),表级约束,FOREIGN KEY约束,使用T-SQL定义（二）,NEXT,FOREIGN KEY约束,CREATE TABLE BorrowReturn(br_ID CHAR(6)PRIMARY KEY,s_ID CHAR(8)REFERENCES NOT NULL,r_ID CHAR(8)REFERENCES NOT NULL,FOREIGN KEY(s_ID)REFERENCES BookStore(s_ID)，FOREIGN KEY(r_ID)REFERENCES ReaderInfo(r_ID),示 例,使用T-SQL定义（三）,UNIQUE约束,返回,ALTER TABEL 数据表名(ADD CONSTRAIN 约束名 FOREIGN KEY(列名)REFERENCES 参照主键表(参照列)ON DELETE CASCADE|ON UPDATE CASCADE，.),修改表：添加约束,ALTER TABEL BookInfo(ADD FOREIGN KEY(bt_ID)REFERENCES BookType(bt_ID)ON DELETE CASCADE ON UPDATE CASCADE,),示 例,约束,NOT NULL约束实质表中列可以定义为允许或不允许空值。默认情况下，创建表列允许空值。一个列中出现NULL 值，意味着用户还没有为该列输入值。若设置为NOT NULL，用户没有输入该列值，则引发错误。示例SSMS方式T-SQL方式,约束列表,使用SSMS工具,返回,NOT NULL约束,使用T-SQL定义,NOT NULL 约束,CREATE TABEL 数据表名(列名 数据类型 CONSTRAIN 约束名 NULL|NOT NULL，),表级约束,CREATE TABEL BookInfo(b_ID varchar(16)PRIMARY KEY,b_Name varchar(50)UNIQUE NOT NULL,bt_ID char(10)REFERENCES BookType(bt_ID),b_Author varchar(20)NOT NULL,b_Translator varchar(20)NULL,),示 例,返回,约束,标识列（IDENTITY）概述当用户向表中插入新的数据行时，系统自动为该行的IDENTITY 列赋值，并保证其值在表中的唯一性。常与PRIMARY KEY 约束一起使用。每个表中只能有一个IDENTITY 列，其列值不能由用户更新，也不允许绑定默认值或建立DEFAULT约束标识列的有效数据类型可以是任何整数数据类型分类的数据类型(bit 数据类型除外)，也可以是decimal 数据类型，但不允许出现小数。创建SSMS方式T-SQL方式,约束列表,使用SSMS工具,返回,标识列（IDENTITY）,使用T-SQL定义,返回,CREATE TABEL 数据表名(列名 数据类型 IDENTITY(种子,增量)，.),CREATE TABEL BorrowReturn(br_ID INT IDENTITY(1,1)PRIMARY KEY，),语法,示 例,标识列（IDENTITY）,DEFAULT（默认值）,概 述“默认值”是一种数据库对象，可以被绑定到列。如果绑定的列或者数据类型没有明确提供值，则将默认指定的数据插入。“默认值”的执行与DEFAULT 约束功能相同，但是DEFAULT 约束定义和表存储在一起，当除去表时，将自动除去它。当在多个列中，特别是不同表中的列中多次使用同一默认值时，适合采用“默认”技术。,管理,管理“默认值”创建绑定解绑,示例,CREATE DEFAULT 默认名称 AS 常数表达式,sp_bindefault 默认名称,数据表名.列名,sp_unbindefault 数据表名.列名,DEFAULT（默认值）,示例：创建绑定解绑,返回,sp_bindefault d_Ssex,Student.Ssex,sp_unbindefault Student.Ssex,USE StudyGOCREATE DEFAULT d_Ssex AS 女,DEFAULT（默认值）,RULE（规则）,概 述“规则”是一种数据库对象，可以绑定到列，定义之后可以反复使用。执行“规则”功能与CHECK约束相同。每个列只能有一个绑定的规则。但列可以同时具有规则和多个CHECK 约束。,约束列表,RULE（规则）,管理“规则”创建绑定解绑,示例,CREATE RULE 规则名称 AS 条件表达式,sp_bindrule 规则名,数据表名.列名,sp_unbindrule 数据表名.列名,RULE（规则）,示例：创建绑定解绑,返回,sp_bindrule r_Ssex,Student.Ssex,sp_unbindrule Student.Ssex,USE StudyGOCREATE RULE r_Ssex AS sex=女 OR sex=男,触发器,概述 触发器 触发器是特殊类型的存储过程，主要由操作事件(INSERT、UPDATE、DELETE)触发而被自动执行。与表相关联：必须定义在表上。不能直接调用，也不能传递或接受参数 触发器可以实现比约束更复杂的数据完整性，经常用于 加强数据的完整性约束和业务规则。触发器本身是一个特殊的事务单位。,触发器,概述 触发器的类型按触发时刻分 AFTER触发器 执行触发操作和处理完约束后激发，只能定义于表上，同一个表上可定义多个。INSTEAD触发器 由触发器的程序代替触发语句的执行，在处理约束之前激发，可在表或视图上创建，同一个表或视图只可定义一个。按触发语句分 INSERT触发器 UPDATE触发器、DELETE触发器,触发器,Inserted表和deleted表 触发器触发时，系统自动在内存创建deleted表或inserted表。它们是只读的，不允许修改；触发器执行完成后，自动删除。inserted 表 临时保存了插入或更新后的记录行 可以从inserted表中检查插入的数据是否满足业务需求，如果不满足，则向用户报告错误消息，并回滚插入操作。deleted 表临时保存了删除或更新前的记录行 可以从deleted表中检查被删除的数据是否满足业务需求，如果不满足，则向用户报告错误消息，并回滚插入操作。,触发器,触发器使用的限制CREATE TRIGGER 必须是批处理中的第一条语句。在同一个表中约束的执行优先于触发器，若在操作中触发器与约束发生冲突，触发器将不执行。不能在临时表上建立触发器，但是在触发器定义中可以引用临时表。触发器中不允许以下 T-SQL 语句：,触发器,Inserted表和deleted表 inserted表和deleted表存放的信息,触发器,创建（SSMS）,触发器,创建（SSMS）,创建触发器的模板，只需修改相应的参数就可创建触发器,触发器,创建（TSQL）基本语句格式,CREATE TRIGGER 触发器名ON 表名或视图名 FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETEAS IF UPDATE(列名1)AND|OR UPDATE(列名2).n SQL语句,触发器,创建（TSQL）基本语句格式,CREATE TRIGGER 触发器名ON 表名或视图名 FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETEAS IF UPDATE(列名1)AND|OR UPDATE(列名2).n SQL语句,触发器,创建（TSQL）参数说明,AFTER:执行指定操作语句和处理完约束后激发触发器。FOR:如果仅指定 FOR 关键字，则 AFTER 是默认设置。INSTEAD OF:指定语句激发触发器并被代替执行。DELETE、INSERT、UPDATE：指定激活触发器的数据修改语句，必须至少指定一个选项，若指定的选项多于一个，需用逗号分隔这些选项。AS：指定触发器要执行的操作。IF UPDATE(列名1)AND|OR UPDATE(列名2):指定何列数据修改时激发触发器。不能用于DELETE语句。,触发器,创建（TSQL）示例1,CREATE TRIGGER tr_addbookON BookInfoFOR INSERT,UPDATEASBEGIN DECLARE p_no CHAR(4),DECLARE bt_no CHAR(10)SELECT p_no=Publisher.p_ID FROM Publisher,inserted WHERE Publisher.p_ID=inserted.p_ID SELECT bt_no=BookType.bt_ID FROM BookType,inserted WHERE BookType.bt_ID=inserted.bt_ID IF p_no is NULL OR bt_no is NULL BEGIN ROLLBACK TRANSACTION RAISERROR(不存在这样的出版社或图书类别！,16,10)ENDEND,触发器,创建（TSQL）示例2,CREATE TRIGGER tr_changeON BorrowReturnFOR INSERTASBEGIN UPDATE BookStore SET s_Status=借出 WHERE s_ID=(SELECT s_ID FROM inserted)END,触发器,禁用或启用（SSMS）,触发器,启用或启用触发器（T-SQL）基本语句格式示例,学习要点,ALTER TABLE 表名 ENABLE|DISABLE TRIGGER ALL|触发器名,n,ALTER TABLE BookInfo DISABLE TRIGGER tr_addbook,触发器,修改触发器（T-SQL）修改删除,学习要点,DROP TRIGGER 触发器名,ALTER TRIGGER 触发器名ON 表名或视图名 FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETEAS IF UPDATE(列名1)AND|OR UPDATE(列名2).n SQL语句,