数据库完整性及SQL.ppt

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

1、第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制8.1 8.1 完整性约束条件及完整性控制完整性约束条件及完整性控制 8.2 8.2 SQL ServerSQL Server的数据完整性及其实现的数据完整性及其实现8.3 8.3 案例案例6 6：活期储蓄管理系统：活期储蓄管理系统 数据库的完整性控制数据库的完整性控制12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制 数据库的完整性是指数据的正确性、有效性和相容性，防止错误数据进入数据库，保证数据库中数据的质

2、量。正确性是指数据的合法性；有效性是指数据是否属于所定义的有效范围；相容性是指描述同一现实的数据应该相同。数据库是否具备完整性涉及到数据库系统中的数据是否正确、可信和一致，保持数据库的完整性是非常重要的。12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制8.1 8.1 完整性约束条件及完整性控制完整性约束条件及完整性控制 为了保证数据库的完整性，DBMS必须提供一种功能来保证数据库中的数据是正确的，避免由于不符合语义的错误数据的输入和输出。检查数据库中数据是否满足规定的条件称为“完整性检查”。数据库中数据应满足的条件

3、称为“完整性约束条件”，有时也称为完整性规则。v 完整性控制完整性控制 DBMS中执行完整性检查的子系统称为“完整性子系统”。它的主要功能有：定义完整性功能，即提供定义完整性约束条件的机制。检查完整性功能，即检查用户发出的操作请求，看其是否违背了完整性约束条件。控制完整性功能，即监视数据操作的整个过程，如果发现有违背了完整性约束条件的情况，则采取一定的动作来保证数据的完整性。12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制v 完整性规则完整性规则 完整性子系统是根据“完整性规则集”工作的。完整性规则集是由DBA或应

4、用程序员事先向完整性子系统提出的有关数据约束的一组规则。每个完整性规则有三个部分组成：(1)什么时候使用规则进行检查（称为规则的“触发条件”）；(2)要检查什么样的错误（称为“约束条件”）；(3)若检查出错误，应该怎样处理（称为“ELSE”子句）。12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制v RDBMSRDBMS中的完整性规则中的完整性规则 在RDBMS中，完整性规则分为三类：域完整性规则 域完整性规则是使基本表的列输入有效。域完整性主要由用户定义的完整性组成。控制域完整性有效的方法有：限制数据类型、格式、可

5、能的取值范围、修改列值时必须满足的条件等。实体完整性规则 实体完整性规则用来约束现实世界中的实体是可区分的，即它们具有惟一性标识。这一规则在关系模型中的体现是基本表所有主属性都不能取空值（NULL）。参照完整性规则 参照完整性规则用来约束具有参照关系的两个表中，主码和外码的数据要保持一致。12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制8.2 8.2 SQL ServerSQL Server的数据完整性及其实现的数据完整性及其实现 SQL Server具有较健全的数据完整性控制机制，它使用约束、默认、规则和触发器4

6、种方法定义和实施数据库完整性功能。8.2.1 8.2.1 SQL ServerSQL Server中的几种约束中的几种约束v SQL ServerSQL Server的数据完整性种类的数据完整性种类 SQL Server 2000中的数据完整性包括域完整性、实体完整性和参照完整性3种。域完整性 域完整性为列级和元组级完整性。它为列或列组指定一个有效的数据集，并确定该列是否允许为空值（NULL）。实体完整性 实体完整性为表级完整性，它要求表中所有的元组都应该有一个惟一标识，即主关键字。参照完整性 参照完整性是表级完整性，它维护从表中的外码与主表中主码的相容关系。12/22/2022第第8 8章章

7、 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制vSQL ServerSQL Server数据完整性方式数据完整性方式 SQL Server使用声明数据完整性和过程数据完整性两种方式实现数据完整性。声明数据完整性 声明数据完整性通过在对象定义中定义、系统本身自动强制来实现。声明数据完整性包括各种约束、默认和规则。过程数据完整性 过程数据完整性通过使用脚本语言(Transact-SQL)定义，系统在执行这些语言时强制实现数据完整性。过程数据完整性包括触发器和存储过程等。vSQL ServerSQL Server实现数据完整性的具体方法实现数据完整性的

8、具体方法 SQL Server实现数据完整性的主要方法有4种：约束、默认、规则和触发器。约束 约束通过限制列中的数据、行中的数据和表之间数据来保证数据完整性。12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制表8-1 SQL Server 2000约束的5种类型和其完整性功能完整性类型完整性类型约束类型约束类型完整性功能描述完整性功能描述域完整性DEFAULT(默认)插入数据时，如果没有明确提供列值，则用默认值作为该列的值CHECK(检查)指定某个列或列组可以接受值的范围，或指定数据应满足的条件实体完整性PRIMAR

9、Y KEY(主码)指定主码，确保主码不重复，不允许主码为空值UNIQUE(惟一值)指出数据应具有惟一值，防止出现冗余参照完整性FOREIGN KEY(外码)定义外码、被参照表和其主码12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制使用CREATE语句创建约束的语法形式如下：CREATE TABLE(，n ，n)其中，的格式和内容为：CONSTRAINT PRIMARY KEYCLUSTERED|NONCLUSTERED|UNIQUECLUSTERED|NONCLUSTERED|FOREIGN KEYREFERENC

10、ES()|DEFAULT|CHECK 12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制的格式和内容为：CONSTRAINT PRIMARY KEYCLUSTERED|NONCLUSTERED()|UNIQUECLUSTERED|NONCLUSTERED()|FOREIGN KEY()REFERENCES(被参照表)()|CHECK()12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制 触发器 触发器是一种功能强、开销高的数据完整性方法。触

11、发器具有INSERT、UPDATE和DELETE三种类型。一个表可以具有多个触发器。触发器的用途是维护行级数据的完整性。与CHECK约束相比，触发器能强制实现更加复杂的数据完整性，能执行操作或级联操作，能实现多行数据间的完整性约束，能按定义动态的、实时的维护相关的数据。默认和规则 默认(DEFAULT)和规则(RULE)都是数据库对象。当它们被创建后，可以绑定到一列或几列上，并可以反复使用。当使用INSERT语句向表中插人数据时，如果有绑定DEFAULT的列，系统就会将DEFAUTLT指定的数据插入；如果有绑定RULE的列，则所插入的数据必须符合RULE的要求。12/22/2022第第8 8章

12、章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制8.2.2 8.2.2 默认默认 默认是一种数据库对象，可以绑定到一列或多列上，也可以绑定到用户自定义的数据类型上，其作用类似于DEFAULT约束，能为INSERT语句中没有指定数据的列提供事先定义的默认值。默认值可以是常量、内置函数或数学表达式。默认对象在功能上与默认约束是一样的，但在使用上有所区别。默认约束在CREATE TABLE或ALTER TABLE语句中定义后，被嵌入到定义的表的结构中。也就是说，在删除表的时候默认约束也将随之被删除。而默认对象需要用CREATE DEFAULT语句进行定

13、义，作为一种单独存储的数据库对象，它是独立于表的，删除表并不能删除默认对象，需要使用DROP DEFAULT语句删除默认对象。12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制v创建默认创建默认 使用T-SQL语句CREATE DEFAULT语句可以创建默认对象。语法如下：CREATE DEFAULT default AS constant_expression 其中，default为默认对象的名称；constant_expression为常量表达式。常量表达式中可以包括常量、内置函数或数学表达式，但不能包括任何列名

14、或其它数据库对象。【例8-1】在数据库MyDb中创建默认对象borrow_time，其值为当前系统日期。CREATE DEFAULT borrow_time AS getdate()12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制v绑定默认绑定默认 默认对象创建后，并不能直接使用，必须绑定到指定表的某一 列 或 者 用 户 定 义 的 数 据 类 型 上。执 行 系 统 存 储 过 程sp_bindefault可以将默认绑定到列或者用户定义的数据类型上。系统存储过程sp_bindefault的语法为：sp_bind

15、efault default,object_name 【例8-2】将【例8-1】中创建的默认对象borrow_time绑定到数据库MyDB中borrowinf表的借期列上，使用如下代码：EXEC sp_bindefault borrow_time,borrowinf.借期 注意：若该表创建时已有默认约束，应先删除，再绑定。12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制v 查看默认查看默认 在企业管理器中，展开【数据库】的【默认】目录，可以看到数据库中所有的默认对象，如图8-1所示。图8-1查看数据库MyDb中的默

16、认对象 12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制 使用系统存储过程sp_help可以查看默认对象的名称、拥有者等基本信息。使用系统存储过程sp_helptext可以查看默认对象的定义。v 解除默认和删除默认解除默认和删除默认 使用DROP DEFAULT语句可以删除当前数据库中的默认对象。但在删除之前，应该先使用系统存储过程sp_unbindefault来解除该默认对象在列或用户自定义数据类型上的绑定。调用系统存储过程sp_unbindefault，解除默认的语法为：sp_unbindefault obje

17、ct_name,futurnonly_flag 解除默认对象的绑定后，默认对象并没有消失，仍然在数据库中。12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制 【例8-3】解除绑定在borrowinf表的借期列上的默认对象。执行如下代码：USE MyDb EXEC sp_unbindefault borrowinf.借期 删除默认对象语句DROP DEFAULT的语法是：DROP DEFAULT object_name 【例8-4】删除绑定在表borrowinf的“借期”列上的默认对象。可使用如下代码：EXEC sp_

18、unbindefault borrowinf.借期 DROP DEFAULT borrow_time12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制8.2.3 8.2.3 规则规则 规则也是一种数据库对象，与默认的使用方法类似，规则可以绑定到表的一列或多列上，也可以绑定到用户定义的数据类型上。它的作用与CHECK约束的部分功能相同，为INSERT和UPDATE语句限制输入数据的取值范围。规则与CHECK约束的不同之处在于：CHECK约束是在使用CREATE TABLE语句建表时指定的，而规则是作为独立于表的数据库对

19、象，通过与指定表或数据类型绑定来实现完整性约束。在一列上只能使用一个规则，但可以使用多个CHECK约束。规则可以应用于多个列，还可以应用于用户自定义的数据类型，而CHECK约束只能应用于它定义的列。12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制v创建规则创建规则 使用T-SQL语句CREATE RULE可以创建规则，语法如下：CREATE RULE rule AS condition_expression 其中，rule：为规则的名称；condition_expression：为一个条件表达式，用来指定满 足规则

20、的条件。【例8-5】在MyDb数据库上定义规则，使用了该规则的列被限制为必须大于0。CREATE RULE range_rule AS value0 12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制v绑定和解除规则绑定和解除规则 规则创建后，需要将其绑定到表的列上或用户自定义数据类型上。当向绑定了规则的列或列的数据类型为绑定规则的用户自定义数据类型的列插入或更新数据时，新的数据必须符合规则。使用系统存储过程sp_bindrule可以将规则绑定到列或用户自定义数据类型上。语法如下：sp_bindrule rule,o

21、bject_name,futureonly_flag 其中，rule：为规则的名称。object_name：为规则要绑定到的列名或用户自定义的数据类型名。futureonly_flag：是一个可选项，仅在要绑定到用户自定义的数据类型时使用。如果futureonly_flag为futureonly，则以前创建的使用该数据类型的列不受绑定规则限制。12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制 【例8-6】将【例8-5】中的range_rule规则绑定到MyDb数据库的books表的定价列上。EXEC sp_bind

22、rule rangge_rule,books.定价 注意在此数据库中，若表books的定价列上已经有CHECK约束时，应该先删除它，再执行此例的代码。图8-2 绑定规则到MyDb数据库表上 12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制 使用系统存储过程sp_unbindrule可以将绑定到列或用户自定义数据类型上的规则解除。语法如下：sp_unbindrule object_name,futureonly_flag 其中，rule：为规则的名称。object_name：为规则已绑定到的列或用户自定义数据类型的名

23、称。futureonly_flag：是一个可选项，仅在要解除一个绑定到用户自定义的数据类型上的规则时使用，其目的是防止具有指定用户定义数据类型的列丢失绑定在其上的规则。12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制v 查看规则查看规则 在企业管理器中，展开数据库的规则目录，可以看到数据库中所有的规则，如图8-3所示。图8-3查看规则 12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制 使用系统存储过程sp_help可以查看规则的拥有者、

24、创建时间等信息。图8-4显示了查看规则的情况。图8-4 用sp_help查看规则 12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制 使用系统存储过程sp_helptext可以查看规则的定义。图8-5显示规则的定义。图8-5显示规则的定义 12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制v删除规则删除规则 使用DROP RULE语句可以删除当前数据库中的一个或多个规则。在删除规则时，应先将规则从它所绑定的列或用户定义数据类型上解除。【例8

25、-7】删除规则range_rule。代码和执行情况，如图8-6所示。图8-612/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制8.3 8.3 案例案例6 6：活期储蓄管理系统数据库的完整性控制：活期储蓄管理系统数据库的完整性控制v 实体完整性实体完整性 实体完整性一般在创建表时，通过定义主码完成。若有遗漏应及时修改，使各关系满足实体完整性。v 参照完整性参照完整性定义触发器 表“储户基本信息”和“储户动态信息”互为参照关系，若将“储户基本信息”表作为被参照关系，“储户动态信息”表为参照关系。考 虑 到 实 际 情 况

26、，在“储 户 基 本 信 息”表 上 定 义“Update”、“Delete”触发器。12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制Transact-SQL程序如下：CREATE TRIGGER dele ON dbo.储户基本信息 FOR DELETE,UPDATEASDECLARE zh char(15)SELECT zh=d.账号 FROM deleted dIF exists(SELECT *FROM 储户动态信息 WHERE 账号=zh)BEGIN PRINT 储户动态信息表中包含储户+zh+的信息，不

27、能进行删除操作!ROLLBACKEND12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制用关系图建立参照完整性 存取款表的被参照关系有两个：储蓄所表、储户基本信息表。储户基本信息表和储户动态信息表互为参照表。用SQL Server 2000提供的关系图建立参照完整性的步骤如下：1)打开【活期储蓄】数据库，右击【关系图】，在弹出的菜单中选择【新建数据库关系图】。2)在出现的向导界面中单击【下一步】，则出现如图8-7所示的对话框。3)从可用表】中，将所需的表添加到【要添加到关系图中的表】中，单击【下一步】按钮，在向导对话

28、中单击【完成】按钮，则选择的表出现在关系图中，如图8-8所示。4)拖动储户基本信息表的“账号”到储户动态信息表的“账号”，在弹出的创建关系对话框中确定参照关系，单击【确定】按钮，完成（如图8-9所示）。用相同的方法建立其他外码约束关系。右击任一关系，在弹出的菜单中选择显示关系标签，这形成如图8-10所示的关系图。关闭窗口，给关系图命名，完成。12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制图8-7 选择关系图中所需的表对话框 12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL

29、 Server的完整性控制的完整性控制图8-8 选中的表在关系图中 12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制图8-9 建立约束 12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制图8-10 活期储蓄的关系图 12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制v用户定义的完整性用户定义的完整性定义约束 使储户动态信息表中的属性“状态”、“信誉”和存取款表的

30、属性“标志”的域为0,1 Tansact-SQL命令如下：ALTER TABLE 储户动态信息 ADD CHECK(状态=0 or 状态=1)ALTER TABLE 储户动态信息 ADD CHECK(信誉=0 or 信誉=1)ALTER TABLE 存取款 ADD CHECK(标志=0 or 标志=1)12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制 上述问题可以通过创建规则、绑定规则来实现。Tansact-SQL命令如下：-创建规则range_rull CREATE RULE range_rull AS rag=0 or rag=1 Go -绑定规则 EXEC sp_bindrule range_rull,储户动态信息.状态 EXEC sp_bindrule range_rull,储户动态信息.信誉 EXEC sp_bindrule range_rull,存取款.标志12/22/2022第第8 8章章 数据库完整性及数据库完整性及SQL ServerSQL Server的完整性控制的完整性控制存取款的域为大于零的实数 ALTER TABLE 存取款 ADD CHECK(金额(元)0)12/22/2022