数据库完整性课件

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

2、一致。 用户定义完整性 ：由用户自定义的不属于其他任何完 整性分类的特定业务规则。,3,PPT学习交流,数据库完整性概述,数据完整性 （Data Integrity） 为维护数据库的完整性，DBMS必须： 提供定义完整性约束条件的机制 提供完整性检查的方法 违约处理,学习要点,4,PPT学习交流,完整性实现,SQL Server 2005完整性实现 DEFAULT约束 CHECK约束 PRIMARY KEY约束 UNIQUE约束 FOREIGN KEY约束 NOT NULL约束 标识列 默认值 规则,学习要点,5,PPT学习交流,约束,DEFAULT约束 定义 在用户未输入某些列的数据时，数据

3、库系统为用户提供的默认值。 （默认值必须与所约束列的数据类型相一致） 是可以避免不允许为空值的数据错误 可以加快用户的输入速度 创建 SSMS方式 T-SQL方式,约束列表,6,PPT学习交流,使用SSMS工具,DEFAULT约束,DEFAULT约束,7,PPT学习交流,CREATE TABEL BookInfo ( b_ID VARCHAR(16), b_Detail VARCHAR(100) DEFAULT 暂无描述 ),使用T-SQL定义（一）,DEFAULT约束,CREATE TABEL 数据表名 ( 列名 数据类型 CONSTRAIN 约束名 DEFAULT 默认值 ，. ),列级约

4、束,示 例,NEXT,8,PPT学习交流,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) ,. ),表级约束,示 例,9,PPT学习交流,使用T-SQL定义(三）,DEFAULT约束,ALTER TABEL 数据表名 ( ADD CONSTRAIN 约束名 DEF

5、AULT 默认值 FOR (列名 ,n) ，. ),修改表：添加约束,ALTER TABEL BookInfo ( ADD CONSTRAINT de_Detail DEFAULT 暂无描述 FOR b_Detail ),示 例,返回,10,PPT学习交流,约束,CHECK约束 定义 限制用户输入数据的取值范围、格式等。 一个列可以定义多个CHECK 约束。 当往表中插入记录或修改相应列的值时，系统检查约束条件是否满足，若不满足则拒绝执行数据操作。 创建 SSMS方式 T-SQL方式,约束列表,11,PPT学习交流,使用SSMS工具,CHEECK约束,CHECK约束,12,PPT学习交流,CR

6、EATE TABEL 数据表名 ( 列名 数据类型 CONSTRAIN 约束名 CHECK (逻辑表达式) ，. ),CREATE TABEL BookInfo ( b_Quantity SMALLINT CHECK (b_Quantity=1 AND b_Quantity =100), ),使用T-SQL定义（一）,CHECK 约束,列级约束,示 例,NEXT,13,PPT学习交流,使用T-SQL定义（二、三）,CHECK 约束,返回,CREATE TABEL 数据表名 ( 列名1 数据类型， CONSTRAIN 约束名 CHECK (逻辑表达式) ,. ),表级约束,ALTER TABEL

7、 数据表名 ( ADD CONSTRAIN 约束名 CHECK (逻辑表达式) ，. ),修改表：添加约束,14,PPT学习交流,约束,PRIMARY KEY约束 定义 若是单列作为主键，可以在列级定义或表级定义 若是列集合作为主键，必须在表级定义 一个表只能有一个PRIMARY KEY约束，受约束的列或列集合的值不能为空和重复，必须能唯一标识表中的行。 定义了PRIMARY KEY 约束的列的列宽不能更改。 创建 SSMS方式 T-SQL方式,约束列表,15,PPT学习交流,使用SSMS工具,返回,PRIMARY约束,16,PPT学习交流,使用T-SQL定义（一）,NEXT,PRIMARY约

8、束,CREATE TABEL 数据表名 ( 列名 数据类型 CONSTRAIN 约束名 PRIMARY KEY ，. ),CREATE TABEL BookInfo ( b_ID VARCHAR(16) PRIMARY KEY， ),列级约束,示 例,默认的约束名为：PK_BookInfo,17,PPT学习交流,使用T-SQL定义（二）,PRIMARY约束,CREATE TABEL 数据表名 ( 列名1 数据类型， CONSTRAIN 约束名 PRIMARY KEY (列名1,n) ,. ),CREATE TABEL BorrowReturn ( s_ID CHAR(8) NOT NULL,

9、r_ID CHAR(8) NOT NULL, PRIMARY KEY (s_ID,r_ID) ),表级约束,示 例,组合键必须在表级定义 默认的约束名为： PK_ BorrowReturn,NEXT,18,PPT学习交流,使用T-SQL定义（三）,返回,PRIMARY约束,ALTER TABEL 数据表名 ( ADD CONSTRAIN 约束名 PRIMARY KEY( 列名, ) ，. ),ALTER TABLE BorrowReturn ADD CONSTRAINT PK_ BorrowReturn PRIMARY KEY(s_ID,r_ID),示 例,修改表：添加约束,19,PPT学习交

10、流,约束,UNIQUE约束 定义 也称唯一值约束，确保在非主键列中不输入重复值。 一个表可以定义多个UNIQUE 约束。 在允许空值的列上保证唯一性时，应使用UNIQUE约束。 当往表中插入记录或修改相应列的值时，系统检查是否有重复值，若有则拒绝执行数据操作。 创建 SSMS方式 T-SQL方式,约束列表,20,PPT学习交流,使用SSMS工具,返回,UNIQUE约束,21,PPT学习交流,使用T-SQL定义（一）,NEXT,UNIQUE约束,CREATE TABEL 数据表名 ( 列名 数据类型 CONSTRAIN 约束名 UNIQUE ，. ),CREATE TABEL ReaderTyp

11、e ( rt_ Name VARCHAR(10) UNIQUE , ),列级约束,示 例,22,PPT学习交流,使用T-SQL定义（二、三）,UNIQUE约束,返回,CREATE TABEL 数据表名 ( 列名1 数据类型， CONSTRAIN 约束名 UNIQUE (列名1,n) ,. ),表级约束,ALTER TABEL 数据表名 ( ADD CONSTRAIN 约束名 UNIQUE (列名1,n) ，. ),修改表：添加约束,23,PPT学习交流,约束,FOREIGN KEY约束 定义 也称为外键约束，用于强制实现参照完整性，建立两个表之间的关系。 可以规定表中的某列参照同一个表或另外一

12、个表中已有的PRIMARY KEY 约束或UNIQUE 约束的列。 一个表可以有多个FOREIGN KEY 约束。 创建 SSMS方式 T-SQL方式,约束列表,24,PPT学习交流,使用SSMS工具 Step1,NEXT,FOREIGN KEY约束,25,PPT学习交流,使用SSMS工具 Step2,NEXT,FOREIGN KEY约束,26,PPT学习交流,使用SSMS工具 Step3,NEXT,FOREIGN KEY约束,27,PPT学习交流,使用SSMS工具 Step4,返回,FOREIGN KEY约束,28,PPT学习交流,使用T-SQL定义（一）,示例,CREATE TABEL 数

13、据表名 ( 列名 数据类型 CONSTRAIN 约束名 FOREIGN KEY REFERENCES 参照主键表 (参照列) ON DELETE CASCADE | ON UPDATE CASCADE ，. ),列级约束,FOREIGN KEY约束,NO ACITON：默认选项。将引发错误，撤销对父表的删除（更新）操作 CASCADE：当在主表删除（更新）一行，则自动将子表中的对应行删除（更新） SET NULL：删除（更新）父表的一行，子表相应行的外键值被置为NULL SET DEFAULT：删除（更新）父表的一行，子表相应行的外键值被置为 外键的默认值,29,PPT学习交流,使用T-SQL

14、定义（一）,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,30,PPT学习交流,使用T-SQL定义（二）,示例,CREATE TABEL 数据表名 ( 列名1 数据类型， CONSTRAIN 约束名 FOREIGN KEY(列名) REFERENCES 参照主键表 (参照列) ON DELETE CASCADE | ON UPDATE CAS

15、CADE ,. ),表级约束,FOREIGN KEY约束,31,PPT学习交流,使用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) ),示 例,32,

16、PPT学习交流,使用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 , ),示 例,33,PPT学习交流,约束,NOT N

17、ULL约束 实质 表中列可以定义为允许或不允许空值。 默认情况下，创建表列允许空值。一个列中出现NULL 值，意味着用户还没有为该列输入值。 若设置为NOT NULL，用户没有输入该列值，则引发错误。 示例 SSMS方式 T-SQL方式,约束列表,34,PPT学习交流,使用SSMS工具,返回,NOT NULL约束,35,PPT学习交流,使用T-SQL定义,NOT NULL 约束,CREATE TABEL 数据表名 ( 列名 数据类型 CONSTRAIN 约束名 NULL | NOT NULL ， ),表级约束,CREATE TABEL BookInfo ( b_ID varchar(16) P

18、RIMARY 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, ),示 例,返回,36,PPT学习交流,约束,标识列（IDENTITY） 概述 当用户向表中插入新的数据行时，系统自动为该行的IDENTITY 列赋值，并保证其值在表中的唯一性。 常与PRIMARY KEY 约束一起使用。 每个表中只能有一个IDENTITY 列，其列值不能由用户更新，也不允许

19、绑定默认值或建立DEFAULT约束 标识列的有效数据类型可以是任何整数数据类型分类的数据类型(bit 数据类型除外)，也可以是decimal 数据类型，但不允许出现小数。 创建 SSMS方式 T-SQL方式,约束列表,37,PPT学习交流,使用SSMS工具,返回,标识列（IDENTITY）,38,PPT学习交流,使用T-SQL定义,返回,CREATE TABEL 数据表名 ( 列名 数据类型 IDENTITY (种子, 增量) ， ，. ),CREATE TABEL BorrowReturn ( br_ID INT IDENTITY( 1 ,1 ) PRIMARY KEY， ),语法,示 例,

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

21、ult 数据表名.列名,DEFAULT（默认值）,41,PPT学习交流,示例： 创建 绑定 解绑,返回,sp_bindefault d_Ssex , Student.Ssex,sp_unbindefault Student.Ssex,USE Study GO CREATE DEFAULT d_Ssex AS 女,DEFAULT（默认值）,42,PPT学习交流,RULE（规则）,概 述 “规则”是一种数据库对象，可以绑定到列，定义之后可以反复使用。 执行“规则”功能与CHECK约束相同。 每个列只能有一个绑定的规则。但列可以同时具有规则和多个CHECK 约束。,约束列表,43,PPT学习交流,R

22、ULE（规则）,管理“规则” 创建 绑定 解绑,示例,CREATE RULE 规则名称 AS 条件表达式,sp_bindrule 规则名, 数据表名.列名,sp_unbindrule 数据表名.列名,44,PPT学习交流,RULE（规则）,示例： 创建 绑定 解绑,返回,sp_bindrule r_Ssex , Student.Ssex,sp_unbindrule Student.Ssex,USE Study GO CREATE RULE r_Ssex AS sex=女 OR sex=男,45,PPT学习交流,触发器,概述 触发器 触发器是特殊类型的存储过程，主要由操作事件(INSERT、UP

23、DATE、DELETE) 触发而被自动执行。 与表相关联：必须定义在表上。 不能直接调用，也不能传递或接受参数 触发器可以实现比约束更复杂的数据完整性，经常用于 加强数据的完整性约束和业务规则。 触发器本身是一个特殊的事务单位。,46,PPT学习交流,触发器,概述 触发器的类型 按触发时刻分 AFTER触发器 执行触发操作和处理完约束后激发，只能定义于表上，同一个表上可定义多个。 INSTEAD触发器 由触发器的程序代替触发语句的执行，在处理约束之前激发，可在表或视图上创建，同一个表或视图只可定义一个。 按触发语句分 INSERT触发器 UPDATE触发器、 DELETE触发器,47,PPT学

24、习交流,触发器,Inserted表和deleted表 触发器触发时，系统自动在内存创建deleted表或inserted表。它们是只读的，不允许修改；触发器执行完成后，自动删除。 inserted 表 临时保存了插入或更新后的记录行 可以从inserted表中检查插入的数据是否满足业务需求，如果不满足，则向用户报告错误消息，并回滚插入操作。 deleted 表 临时保存了删除或更新前的记录行 可以从deleted表中检查被删除的数据是否满足业务需求，如果不满足，则向用户报告错误消息，并回滚插入操作。,48,PPT学习交流,触发器,触发器使用的限制 CREATE TRIGGER 必须是批处理中的

25、第一条语句。 在同一个表中约束的执行优先于触发器，若在操作中触发器与约束发生冲突，触发器将不执行。 不能在临时表上建立触发器，但是在触发器定义中可以引用临时表。 触发器中不允许以下 T-SQL 语句：,49,PPT学习交流,触发器,Inserted表和deleted表 inserted表和deleted表存放的信息,50,PPT学习交流,触发器,创建（SSMS）,51,PPT学习交流,触发器,创建（SSMS）,创建触发器的模板，只需修改相应的参数就可创建触发器,52,PPT学习交流,触发器,创建（TSQL） 基本语句格式,CREATE TRIGGER 触发器名 ON 表名或视图名 FOR |

26、AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS IF UPDATE(列名1) AND|OR UPDATE(列名2) .n SQL语句,53,PPT学习交流,触发器,创建（TSQL） 基本语句格式,CREATE TRIGGER 触发器名 ON 表名或视图名 FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS IF UPDATE(列名1) AND|OR UPDATE(列名2) .n SQL语句,54,PPT学习交流,触发器,创建（TSQL） 参数说明,AFTER:执行指定操作语句和处理完约束后激发触

27、发器。 FOR:如果仅指定 FOR 关键字，则 AFTER 是默认设置。 INSTEAD OF:指定语句激发触发器并被代替执行。 DELETE、INSERT、UPDATE：指定激活触发器的数据修改语句，必须至少指定一个选项，若指定的选项多于一个，需用逗号分隔这些选项。 AS：指定触发器要执行的操作。 IF UPDATE(列名1) AND|OR UPDATE(列名2):指定何列数据修改时激发触发器。不能用于DELETE语句。,55,PPT学习交流,触发器,创建（TSQL） 示例1,CREATE TRIGGER tr_addbook ON BookInfo FOR INSERT,UPDATE AS BEGI