数据库完整性实用教案

1、学习(xux)要点 数据库完整性概述 SQL2005完整性实现 触发器 概述 创建、修改、查看、删除 使用(shyng) 说明 AFTER触发器 INSTEADOF触发器第1页/共60页第一页，共61页。数据库完整性概述(i sh) 数据完整性 （Data Integrity） 定义 指数据的准确性和一致(yzh)性。 目的 防止数据库中存在不符合语义规定的数据和防止因错误 信息的输入输出造成无效操作或错误信息 分类 实体完整性：用于保证数据库中数据表的每一个特定实体的记 录都是唯一的。 域完整性：用于保证指定列的数据具有正确的数据类型、格 式和有效的数据范围。 参照完整性：确保同一键值在所有

2、相关联表之间都一致(yzh)。 用户定义完整性 ：由用户自定义的不属于其他任何完 整性分类的特定业务规则。第2页/共60页第二页，共61页。数据库完整性概述(i sh)数据完整性 （Data Integrity）为维护数据库的完整性，DBMS必须(bx)：提供定义完整性约束条件的机制提供完整性检查的方法违约处理学习(xux)要点完整性类型完整性类型实实 现现 技技 术术实体完整性实体完整性UNIQUE约束、约束、PRIMARY KEY约束、约束、IDENTITY属性属性域完整性域完整性CHECK约束、约束、 FOREIGN KEY约束、约束、 DEFAULT定义、定义、NOT NULL定义、定

3、义、默认默认对象、对象、规则规则对象对象参照完整性参照完整性FOREIGN KEY约束、约束、CHECK约束、约束、触发器触发器用户定义完整性用户定义完整性CREATE TABLE中的所有列级和表级约束、中的所有列级和表级约束、存储存储过程过程、触发器触发器第3页/共60页第三页，共61页。完整性实现(shxin) SQL Server 2005完整性实现(shxin) DEFAULT约束 CHECK约束 PRIMARY KEY约束 UNIQUE约束 FOREIGN KEY约束 NOT NULL约束 标识列 默认值 规则 学习(xux)要点第4页/共60页第四页，共61页。约束(yush) D

4、EFAULT约束 定义 在用户未输入某些列的数据时，数据库系统为用户提供的默认值。 （默认值必须与所约束列的数据类型相一致） 是可以(ky)避免不允许为空值的数据错误 可以(ky)加快用户的输入速度 创建 SSMS方式 T-SQL方式约束(yush)列表第5页/共60页第五页，共61页。 使用(shyng)SSMS工具DEFAULT约束(yush)DEFAULT约束(yush)第6页/共60页第六页，共61页。CREATE TABEL BookInfo( b_ID VARCHAR(16), b_Detail VARCHAR(100) DEFAULT 暂无描述暂无描述(mio sh) 使用(sh

5、yng)T-SQL定义（一）DEFAULT约束(yush)CREATE TABEL 数据表名数据表名( 列名列名 数据类型数据类型 CONSTRAIN 约束名约束名 DEFAULT 默认默认值值 ，.)列级约束 示 例NEXT第7页/共60页第七页，共61页。CREATE TABEL BookInfo( b_Detail VARCHAR(100)， CONSTRAINT de_Detail DEFAULT 暂无描述暂无描述(mio sh) FOR b_Detail ) 使用(shyng)T-SQL定义（二）DEFAULT约束(yush)NEXTCREATE TABEL 数据表名数据表名( 列名

6、列名1 数据类型，数据类型， CONSTRAIN 约束名约束名 DEFAULT 默认值默认值 FOR (列名列名 ,n) ,.)表级约束示 例第8页/共60页第八页，共61页。 使用(shyng)T-SQL定义(三）DEFAULT约束(yush)ALTER TABEL 数据表名数据表名( ADD CONSTRAIN 约束约束(yush)名名 DEFAULT 默认值默认值 FOR (列名列名 ,n) ，.)修改表：添加约束ALTER TABEL BookInfo( ADD CONSTRAINT de_Detail DEFAULT 暂无描述 FOR b_Detail )示 例返回第9页/共60页第

7、九页，共61页。约束(yush) CHECK约束 定义 限制用户输入数据的取值范围、格式等。 一个列可以定义多个CHECK 约束。 当往表中插入记录或修改相应列的值时，系统检查约束条件是否满足(mnz)，若不满足(mnz)则拒绝执行数据操作。 创建 SSMS方式 T-SQL方式约束(yush)列表第10页/共60页第十页，共61页。 使用(shyng)SSMS工具CHEECK约束(yush)CHECK约束(yush)第11页/共60页第十一页，共61页。CREATE TABEL 数据表名数据表名( 列名列名 数据类型数据类型 CONSTRAIN 约束约束(yush)名名 CHECK (逻辑表达

8、式逻辑表达式) ，.)CREATE TABEL BookInfo( b_Quantity SMALLINT CHECK (b_Quantity=1 AND b_Quantity =100), ) 使用(shyng)T-SQL定义（一）CHECK 约束(yush)列级约束 示 例NEXT第12页/共60页第十二页，共61页。 使用(shyng)T-SQL定义（二、三）CHECK 约束(yush)返回(fnhu)CREATE TABEL 数据表名数据表名( 列名列名1 数据类型，数据类型， CONSTRAIN 约束名约束名 CHECK (逻辑表达式逻辑表达式) ,.)表级约束 ALTER TABE

9、L 数据表名数据表名( ADD CONSTRAIN 约束名约束名 CHECK (逻辑表达式逻辑表达式) ，.)修改表：添加约束第13页/共60页第十三页，共61页。约束(yush) PRIMARY KEY约束 定义 若是单列作为主键，可以在列级定义或表级定义 若是列集合作为主键，必须在表级定义 一个表只能有一个PRIMARY KEY约束，受约束的列或列集合的值不能为空和重复，必须能唯一标识表中的行。 定义了PRIMARY KEY 约束的列的列宽不能更改。 创建(chungjin) SSMS方式 T-SQL方式约束(yush)列表第14页/共60页第十四页，共61页。 使用(shyng)SSMS

10、工具返回(fnhu)PRIMARY约束(yush)第15页/共60页第十五页，共61页。 使用(shyng)T-SQL定义（一）NEXTPRIMARY约束(yush)CREATE TABEL 数据表名数据表名( 列名列名 数据类型数据类型 CONSTRAIN 约束约束(yush)名名 PRIMARY KEY ，.)CREATE TABEL BookInfo( b_ID VARCHAR(16) PRIMARY KEY， )列级约束 示 例默认的约束名为：PK_PK_BookInfo BookInfo 第16页/共60页第十六页，共61页。 使用(shyng)T-SQL定义（二）PRIMARY约束

11、(yush)CREATE TABEL 数据表名数据表名( 列名列名1 数据类型，数据类型， CONSTRAIN 约束约束(yush)名名 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_ PK_ BorrowReturn BorrowReturn NEXT第17页/共60页第十七页，共61页。 使用(shyng)T-SQL定义（三）返回(fnhu

12、)PRIMARY约束(yush)ALTER TABEL 数据表名数据表名( ADD CONSTRAIN 约束名约束名 PRIMARY KEY( 列名列名, ) ，.)ALTER TABLE BorrowReturn ADD CONSTRAINT PK_ BorrowReturn PRIMARY KEY(s_ID,r_ID)示 例修改表：添加约束第18页/共60页第十八页，共61页。约束(yush) UNIQUE约束 定义 也称唯一值约束，确保在非主键列中不输入重复值。 一个表可以定义多个(du )UNIQUE 约束。 在允许空值的列上保证唯一性时，应使用UNIQUE约束。 当往表中插入记录或修

13、改相应列的值时，系统检查是否有重复值，若有则拒绝执行数据操作。 创建 SSMS方式 T-SQL方式约束(yush)列表第19页/共60页第十九页，共61页。 使用(shyng)SSMS工具返回(fnhu)UNIQUE约束(yush)第20页/共60页第二十页，共61页。 使用(shyng)T-SQL定义（一）NEXTUNIQUE约束(yush)CREATE TABEL 数据表名数据表名( 列名列名 数据类型数据类型 CONSTRAIN 约束约束(yush)名名 UNIQUE ，.)CREATE TABEL ReaderType( rt_ Name VARCHAR(10) UNIQUE , )列

14、级约束 示 例第21页/共60页第二十一页，共61页。 使用(shyng)T-SQL定义（二、三）UNIQUE约束(yush)返回(fnhu)CREATE TABEL 数据表名数据表名( 列名列名1 数据类型，数据类型， CONSTRAIN 约束名约束名 UNIQUE (列名列名1,n) ,.)表级约束 ALTER TABEL 数据表名数据表名( ADD CONSTRAIN 约束名约束名 UNIQUE (列名列名1,n) ，.)修改表：添加约束第22页/共60页第二十二页，共61页。约束(yush) FOREIGN KEY约束 定义 也称为外键约束，用于强制实现参照完整性，建立两个表之间的关系

15、。 可以(ky)规定表中的某列参照同一个表或另外一个表中已有的PRIMARY KEY 约束或UNIQUE 约束的列。 一个表可以(ky)有多个FOREIGN KEY 约束。 创建 SSMS方式 T-SQL方式约束(yush)列表第23页/共60页第二十三页，共61页。 使用(shyng)SSMS工具 Step1NEXTFOREIGN KEY约束(yush)第24页/共60页第二十四页，共61页。 使用(shyng)SSMS工具 Step2NEXTFOREIGN KEY约束(yush)第25页/共60页第二十五页，共61页。 使用(shyng)SSMS工具 Step3NEXTFOREIGN KE

16、Y约束(yush)第26页/共60页第二十六页，共61页。 使用(shyng)SSMS工具 Step4返回(fnhu)FOREIGN KEY约束(yush)第27页/共60页第二十七页，共61页。 使用(shyng)T-SQL定义（一）示例(shl)CREATE TABEL 数据表名数据表名( 列名列名 数据类型数据类型 CONSTRAIN 约束约束(yush)名名 FOREIGN KEY REFERENCES 参照主键表参照主键表 (参照列参照列) ON DELETE CASCADE | ON UPDATE CASCADE ，. )列级约束 FOREIGN KEY约束 NO ACITON N

17、O ACITON：默认选项。将引发错误，撤销对父表的删除（更新）操作 CASCADECASCADE：当在主表删除（更新）一行，则自动将子表中的对应行删除（更新） SET NULLSET NULL：删除（更新）父表的一行，子表相应行的外键值被置为NULLNULL SET DEFAULTSET DEFAULT：删除（更新）父表的一行，子表相应行的外键值被置为 外键的默认值第28页/共60页第二十八页，共61页。 使用(shyng)T-SQL定义（一）FOREIGN KEY约束(yush)CREATE TABEL BookInfo( b_ID VARCHAR(16) PRIMARY KEY, b_N

18、ame VARCHAR(50) NOT NULL, bt_ID CHAR(10) REFERENCES BookType(bt_ID), )示 例NEXT第29页/共60页第二十九页，共61页。 使用(shyng)T-SQL定义（二）示例(shl)CREATE TABEL 数据表名数据表名( 列名列名1 数据类型，数据类型， CONSTRAIN 约束约束(yush)名名 FOREIGN KEY(列名列名) REFERENCES 参照主键表参照主键表 (参照列参照列) ON DELETE CASCADE | ON UPDATE CASCADE ,. )表级约束 FOREIGN KEY约束第30页

19、/共60页第三十页，共61页。 使用(shyng)T-SQL定义（二）NEXTFOREIGN KEY约束(yush)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) ) 示 例第31页/共60页第三十一页，共61页

20、。 使用(shyng)T-SQL定义（三）UNIQUE约束(yush)返回(fnhu)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 ,)示 例

21、第32页/共60页第三十二页，共61页。约束(yush) NOT NULL约束 实质 表中列可以定义为允许或不允许空值。 默认情况下，创建表列允许空值。一个列中出现NULL 值，意味着用户还没有(mi yu)为该列输入值。 若设置为NOT NULL，用户没有(mi yu)输入该列值，则引发错误。 示例 SSMS方式 T-SQL方式约束(yush)列表第33页/共60页第三十三页，共61页。 使用(shyng)SSMS工具返回(fnhu)NOT NULL约束(yush)第34页/共60页第三十四页，共61页。 使用(shyng)T-SQL定义NOT NULL 约束(yush)CREATE TAB

22、EL 数据表名数据表名( 列名列名 数据类型数据类型 CONSTRAIN 约束约束(yush)名名 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, )示 例返回第35页/共60页第三十五页，共61页。约束(yus

23、h) 标识列（IDENTITY） 概述 当用户向表中插入新的数据行时，系统自动为该行的IDENTITY 列赋值，并保证其值在表中的唯一性。 常与PRIMARY KEY 约束一起使用。 每个表中只能有一个IDENTITY 列，其列值不能由用户更新，也不允许绑定默认值或建立DEFAULT约束 标识列的有效数据类型可以是任何整数数据类型分类(fn li)的数据类型(bit 数据类型除外)，也可以是decimal 数据类型，但不允许出现小数。 创建 SSMS方式 T-SQL方式约束(yush)列表第36页/共60页第三十六页，共61页。 使用(shyng)SSMS工具返回(fnhu)标识(biozh)

24、列（IDENTITY）第37页/共60页第三十七页，共61页。 使用(shyng)T-SQL定义返回(fnhu)CREATE TABEL 数据表名数据表名( 列名列名 数据类型数据类型 IDENTITY (种子种子(zhng zi), 增量增量) ， ，.)CREATE TABEL BorrowReturn( br_ID INT IDENTITY( 1 ,1 ) PRIMARY KEY，)语法 示 例标识列（IDENTITY）第38页/共60页第三十八页，共61页。DEFAULT（默认值） 概 述 “默认值”是一种数据库对象，可以被绑定到列。如果(rgu)绑定的列或者数据类型没有明确提供值，则

25、将默认指定的数据插入。 “默认值”的执行与DEFAULT 约束功能相同，但是DEFAULT 约束定义和表存储在一起，当除去表时，将自动除去它。 当在多个列中，特别是不同表中的列中多次使用同一默认值时，适合采用“默认”技术。管理(gunl)第39页/共60页第三十九页，共61页。 管理(gunl)“默认值” 创建 绑定 解绑示例(shl)CREATE DEFAULT 默认默认(mrn)名称名称 AS 常数表达式常数表达式 sp_bindefault 默认名称默认名称, 数据表名数据表名.列名列名sp_unbindefault 数据表名数据表名.列名列名DEFAULT（默认值） 第40页/共60页

26、第四十页，共61页。 示例(shl)： 创建 绑定 解绑返回(fnhu)sp_bindefault d_Ssex , Student.Ssexsp_unbindefault Student.SsexUSE StudyGOCREATE DEFAULT d_Ssex AS 女女DEFAULT（默认值） 第41页/共60页第四十一页，共61页。RULE（规则(guz)） 概 述 “规则”是一种数据库对象，可以绑定到列，定义之后可以反复使用。 执行“规则”功能与CHECK约束(yush)相同。 每个列只能有一个绑定的规则。但列可以同时具有规则和多个CHECK 约束(yush)。约束(yush)列表第4

27、2页/共60页第四十二页，共61页。RULE（规则(guz)） 管理(gunl)“规则” 创建 绑定 解绑示例(shl)CREATE RULE 规则规则名称名称 AS 条件条件表达式表达式 sp_bindrule 规则名规则名, 数据表名数据表名.列名列名sp_unbindrule 数据表名数据表名.列名列名第43页/共60页第四十三页，共61页。RULE（规则(guz)） 示例(shl)： 创建 绑定 解绑返回(fnhu)sp_bindrule r_Ssex , Student.Ssexsp_unbindrule Student.SsexUSE StudyGOCREATE RULE r_Ss

28、ex AS sex=女女 OR sex=男男第44页/共60页第四十四页，共61页。触发器 概述 触发器 触发器是特殊类型的存储过程，主要由操作事件(INSERT、UPDATE、DELETE) 触发而被自动执行。 与表相关联：必须定义在表上。 不能直接调用，也不能传递或接受参数 触发器可以实现比约束更复杂的数据完整性，经常用于 加强( jiqing)数据的完整性约束和业务规则。 触发器本身是一个特殊的事务单位。第45页/共60页第四十五页，共61页。触发器 概述 触发器的类型 按触发时刻分 AFTER触发器 执行触发操作和处理完约束后激发，只能定义于表上，同一个表上可定义多个。 INSTEAD

29、触发器 由触发器的程序代替(dit)触发语句的执行，在处理约束之前激发，可在表或视图上创建，同一个表或视图只可定义一个。 按触发语句分 INSERT触发器 UPDATE触发器、 DELETE触发器 第46页/共60页第四十六页，共61页。触发器 Inserted表和deleted表 触发器触发时，系统自动在内存创建deleted表或inserted表。它们是只读的，不允许修改；触发器执行完成后，自动删除。 inserted 表 临时保存了插入或更新后的记录行 可以从inserted表中检查插入的数据是否(sh fu)满足业务需求，如果不满足，则向用户报告错误消息，并回滚插入操作。 delete

30、d 表 临时保存了删除或更新前的记录行 可以从deleted表中检查被删除的数据是否(sh fu)满足业务需求，如果不满足，则向用户报告错误消息，并回滚插入操作。第47页/共60页第四十七页，共61页。触发器触发器使用的限制CREATE TRIGGER 必须是批处理中的第一条语句(yj)。 在同一个表中约束的执行优先于触发器，若在操作中触发器与约束发生冲突，触发器将不执行。 不能在临时表上建立触发器，但是在触发器定义中可以引用临时表。 触发器中不允许以下 T-SQL 语句(yj)：ALTER DATABASEALTER DATABASECREATE DATABASECREATE DATABAS

31、EDISK INITDISK INITDISK RESIZEDISK RESIZEDROP DATABASEDROP DATABASELOAD DATABASELOAD DATABASELOAD LOGLOAD LOGRECONFIGURERECONFIGURERESTORE DATABASERESTORE DATABASERESTORE LOGRESTORE LOG第48页/共60页第四十八页，共61页。触发器Inserted表和deleted表 inserted表和deleted表存放(cnfng)的信息修改操作inserted表deleted表增加增加(INSERT)(INSERT)记录

32、记录存放新增的记录存放新增的记录-删除删除(DELETE)(DELETE)记录记录-存放被删除的记录存放被删除的记录修改修改(UPDATE)(UPDATE)记录记录存放更新后的记录存放更新后的记录存放更新前的记录存放更新前的记录第49页/共60页第四十九页，共61页。触发器 创建(chungjin)（SSMS）第50页/共60页第五十页，共61页。触发器 创建(chungjin)（SSMS）创建触发器的模板(mbn)，只需修改相应的参数就可创建触发器第51页/共60页第五十一页，共61页。触发器 创建（TSQL） 基本( jbn)语句格式CREATE TRIGGER CREATE TRIGGE

33、R 触发器名ON ON 表名或视图(sht)(sht)名 FOR | AFTER | INSTEAD OF FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE INSERT , UPDATE , DELETEAS AS IF UPDATE(IF UPDATE(列名1) AND|OR UPDATE(1) AND|OR UPDATE(列名2)2) .n .n SQL SQL语句第52页/共60页第五十二页，共61页。触发器 创建（TSQL） 基本( jbn)语句格式CREATE TRIGGER CREATE TRIGGER 触发器名ON ON 表名或

34、视图(sht)(sht)名 FOR | AFTER | INSTEAD OF FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE INSERT , UPDATE , DELETEAS AS IF UPDATE(IF UPDATE(列名1) AND|OR UPDATE(1) AND|OR UPDATE(列名2)2) .n .n SQL SQL语句第53页/共60页第五十三页，共61页。触发器 创建（TSQL） 参数(cnsh)说明u AFTER: AFTER:执行指定操作语句和处理完约束执行指定操作语句和处理完约束(yush)(yush)后激发触发

35、器。后激发触发器。u FOR: FOR:如果仅指定如果仅指定 FOR FOR 关键字，则关键字，则 AFTER AFTER 是默认设置。是默认设置。u INSTEAD OF: INSTEAD OF:指定语句激发触发器并被代替执行。指定语句激发触发器并被代替执行。u DELETE DELETE、INSERTINSERT、UPDATEUPDATE：指定激活触发器的数据修改语句，必须：指定激活触发器的数据修改语句，必须至少指定一个选项，若指定的选项多于一个，需用逗号分隔这些选项。至少指定一个选项，若指定的选项多于一个，需用逗号分隔这些选项。u AS AS：指定触发器要执行的操作。：指定触发器要执行的

36、操作。u IF UPDATE( IF UPDATE(列名列名1) AND|OR UPDATE(1) AND|OR UPDATE(列名列名2):2):指定何列数据修改指定何列数据修改时激发触发器。不能用于时激发触发器。不能用于DELETEDELETE语句。语句。第54页/共60页第五十四页，共61页。触发器 创建(chungjin)（TSQL） 示例1CREATE TRIGGER tr_addbookCREATE TRIGGER tr_addbookON BookInfoON BookInfoFOR INSERT,UPDATEFOR INSERT,UPDATEASASBEGINBEGIN DEC

37、LARE 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,inserted SELECT p_no=Publisher.p_ID FROM Publisher,inserted WHERE Publisher.p_ID=inserted.p_ID WHERE Publisher.p_ID=inserted.p_ID SELECT bt_no=BookType.bt_ID FROM BookType,inserted SELECT bt_no=BookType.bt_ID FROM BookType,inserted WHERE BookType.bt_ID=inserted.bt_ID WHERE BookType.bt_ID=inserted.bt_ID IF p_no is NULL OR bt_no is NULL IF p_no is NULL OR bt_no is NULL BEGIN BEGIN ROLLBACK TRANSACTION ROLLBACK TRANSACTION RAISERROR( RAISERROR(不存在