第5 章 数据完整性及实现

数据库技术及应用 SQL Server,Database Technology and Application,,东北师范大学,第5 章 数据完整性及实现,本章重点：数据完整性数据完整性实现默认值,,东北师范大学,5.1 数据完整性,5.1.1 数据完整性概述5.1.2 数据完整性的分类,,东北师范大学,5.1.1 数据完整性概述,数据完整性（Data Integrity）是指存储在数据库中的数据的一致性、准确性和可靠度。换言之，实施数据完整性的目的就是为了确保数据库中数据的质量。 数据完整性的设计是评估数据库设计好坏的一个重要指标。在SQL Server系统中，在设计数据库和设计表时，设计者就要确认每列对应哪些数据是正确的，使用什么方法可以不会有错误的数据存到列中。数据完整性为我们提供了这样的保障，其类型有3种。,,东北师范大学,5.1.1 数据完整性概述,完整性控制机制应具有三个功能：（1）定义功能，即提供定义完整性约束条件的机制；（2）检查功能，即检查用户发出的操作请求是否违背了约束条件；（3）如果发现用户操作请求使数据违背了完整性约束条件，则采取一定的动作来保证数据的完整性。完整性控制机制的工作原理基本上分为两类，一种是定义完整性时就立刻进行检查，如实体完整性的定义。另外一种是定义完整性之后进行检查的，例如参照完整性的定义。,,东北师范大学,5.1.2 数据完整性的分类,1. 实体完整性（Entity Integrity）要求表中所有行有一个标识符（主键），其值不能空值（NULL），且能唯一地标识对应的行。 2.参照完整性（Referential Integrity）是指添加、修改或删除数据行时，两个表的主键和外键的数据的一致性。 3.用户自定义完整性（User-Defined Integrity）是用户自行定义的，不属于其它完整性的所有规则。其中用户自定义完整性主要包括域完整性（Domain Integrity）和其他自定义完整性。,5.1.2 数据完整性的分类,,东北师范大学,5.2 数据完整性实现,5.2.1实体完整性的实现5.2.2参照完整性的实现5.2.3 用户自定义完整性（域完整性）的实现,,东北师范大学,5.2.1 实体完整性的实现,1.利用企业管理器创建PRIMARY KEY约束 操作步骤如下： （1）启动SQL Server企业管理器。 （2）展开“SQL Server组”，展开“数据库”，选择使用的数据库，再展开“表”。 （3）选择要创建约束的表，单击鼠标右键，打开快捷菜单。 （4）选择“设计表”菜单命令，进入“设计表”窗口。 （5）选择要创建约束的列（学号），单击鼠标右键，打开快捷菜单。,,东北师范大学,5.2.1 实体完整性的实现,（6）选择“索引/键”菜单命令，进入“属性”窗口。 （7）选择“索引/键”选项卡，首先，单击“新建”按钮，然后，命名索引名，选择索引列，及索引顺序，选择索引文件组，最后，单击“关闭”按钮，返回“设计表”窗口。,,东北师范大学,5.2.1 实体完整性的实现,2.利用企业管理器创建UNIQUE约束 操作步骤如下： （1）启动SQL Server企业管理器。 （2）展开“SQL Server组”，展开“数据库”，选择使用的数据库，再展开“表”。 （3）选择要创建约束的表（，单击鼠标右键，打开快捷菜单。 （4）选择“设计表”菜单命令，进入“设计表”窗口。 （5）选择要删除约束的列，单击鼠标右键，打开快捷菜单。,,东北师范大学,5.2.1 实体完整性的实现,（6）选择“索引/键”菜单命令，进入“属性”窗口。（7）选择“索引/键”选项卡，首先，单击“新建”按钮，然后，命名索引名，选择索引列及索引顺序，选择索引文件组，选择创建UNIQUE前的复选框，最后，单击“关闭”按钮，返回“设计表”窗口。,,东北师范大学,5.2.1 实体完整性的实现,3.利用企业管理器删除PRIMARY KEY约束、删除UNIQUE约束操作步骤如下： （1）启动SQL Server企业管理器。 （2）展开“SQL Server组”，展开“数据库”，选择使用的数据库，再展开“表”。 （3）选择要创建约束的表，单击鼠标右键，打开快捷菜单。 （4）选择“设计表”菜单命令，进入“设计表”窗口。 （5）选择要删除约束的列，单击鼠标右键，打开快捷菜单。,,东北师范大学,5.2.1 实体完整性的实现,（6）选择“索引/键”菜单命令，进入“属性”窗口。（7）首先，选择“索引/键”选项卡，然后，单击“删除”按钮，最后在单击“关闭”按钮，返回“设计表”窗口。,,东北师范大学,5.2.1 实体完整性的实现,5.利用SQL语句创建、删除PRIMARY KEY和UNIQUE约束SQL命令格式：ALTER TABLE DROP CONSTRAINT Check_name 命令功能：修改表结构，并删除数据完整性。6.利用SQL语句修改PRIMARY KEY约束SQL命令格式：ALTER TABLE Table_name ADD CONSTRAINT constraint_name PRIMARY KEY CLUSTERED | NONCLUSTERED ( column ,.n )命令功能：修改表结构，并可增加数据完整性。,,东北师范大学,5.2.1 实体完整性的实现例子,1.原理解析1）实体完整性定义实体完整性规定表的每一行在表中是唯一的实体，SQL语法中表示的Primary Key和Identity约束就是实体完整性的体现。实体完整性规则：每个关系中的主键的任何属性不能取空值。注意：空值为NULL，不是0，也不是空格，而是一个“不知道”或者“不确定”的数据值。,,东北师范大学,5.2.1 实体完整性的实现例子,2）实施完整性检查的时机实施完整性检查的时机分为立即检查和延时检查，只有选择正确的检查时机才能保证语义的正确性，即保证数据的完整性。例如：“转帐”事务的完整性控制条件是：转出账户A和转入账户B的余额之和保持不变。转帐动作是：A减金额，B加金额如果在更新A动作发生后立即启动检查就没有意义，应该延迟到B更新结束后才检查完整性条件。实体完整性规则检查的时机是立即检查，而参照完整性和触发器一般都是延迟检查。,,东北师范大学,5.2.1 实体完整性的实现例子,3）事务处理事务是一组数据库操作的集合，这些操作要么一起成功，要么一起失败。操作的提交和回退是一同生效的。事务处理的概念对维护数据的完整性和一致性是十分重要的。,,东北师范大学,5.2.1 实体完整性的实现例子（实验内容）,1）在数据库School中建立表Stu_Info,进行主键约束，在没有违反实体完整性的前提下插入并更新一条记录2）演示违反实体完整性的插入操作3）演示违反实体完整性的更新操作4）演示事务的处理，包括事务的建立，处理以及出错是的事务回滚。5）通过建立Scholarship表，插入数据，演示当与现有的数据环境不等时，无法建立实体完整性以及参照完整性。6)删除数据库School中Stu_Info的实体完整性。,,东北师范大学,5.2.1 实体完整性的实现例子（实验内容）,1）在数据库School中建立表Stu_Info,进行主键约束，在没有违反实体完整性的前提下插入并更新一条记录例子见讲课的例子创建实体完整性的例子.txt思考 为什么sno置为没有违反非空的约束空值是，就是什么字符都没有，空值可以和空值相等，也就是可以比较 null是指什么都没有，与空值类型不同，null不可以比较，即nullnull，要判断null值只能用is,,东北师范大学,5.2.1 实体完整性的实现例子（实验内容）,2）演示违反实体完整性的插入操作讲课的例子演示违反实体完整性的插入操作例子.txt,分析：违反了主键的唯一性属性，将破坏实体完整性，系统终止操作。,,东北师范大学,5.2.1 实体完整性的实现例子（实验内容）,3）演示违反实体完整性的更新操作讲课的例子演示违反实体完整性的更新操作例子.txt服务器: 消息 515，级别 16，状态 2，行 1无法将 NULL 值插入列 sno，表 School.dbo.Stu_info；该列不允许空值。UPDATE 失败。语句已终止。分析：违反了主键的非空属性，将破坏实体完整性，系统中止操作。,,东北师范大学,5.2.1 实体完整性的实现例子（实验内容）,4）演示事务的处理，包括事务的建立，处理以及出错是的事务回滚。讲课的例子演示事务的处理的例子.txt注意：当 SET XACT_ABORT 为 ON 时，如果执行 Transact-SQL 语句产生运行时错误，则整个事务将终止并回滚。当 SET XACT_ABORT 为 OFF 时，有时只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。如果错误很严重，那么即使 SET XACT_ABORT 为 OFF，也可能回滚整个事务。OFF 是默认设置。编译错误（如语法错误）不受 SET XACT_ABORT 的影响。对于大多数 OLE DB 提供程序（包括 SQL Server），必须将隐式或显示事务中的数据修改语句中的 XACT_ABORT 设置为 ON。唯一不需要该选项的情况是在提供程序支持嵌套事务时。,,东北师范大学,5.2.1 实体完整性的实现例子（实验内容）,5）通过建立Scholarship表，插入数据，演示当与现有的数据环境不等时，无法建立实体完整性以及参照完整性。讲课的例子通过建立Scholarship表的例子.txt服务器: 消息 8111，级别 16，状态 1，行 1无法在表 Scholarship 中可为空的列上定义 PRIMARY KEY 约束。服务器: 消息 1750，级别 16，状态 1，行 1未能创建约束。请参阅前面的错误信息。分析：当前数据环境不满足M_ID成为主键。,,东北师范大学,5.2.1 实体完整性的实现例子（实验内容）,6)删除数据库School中Stu_Info的实体完整性。讲课的例子删除数据库School中Stu_Info的实体完整性.txt服务器: 消息 3725，级别 16，状态 1，行 1约束 PK_Stu_info 正由表 SC 的外键约束 FK_SC_sno_1CF15040 引用。服务器: 消息 3727，级别 16，状态 1，行 1未能除去约束。请参阅前面的错误信息。,,东北师范大学,5.2.1 实体完整性的实现例子（实验内容）,修改数据库School中Stu_info的实体完整性。讲课的例子修改数据库School中Stu_Info的实体完整性.txt,,东北师范大学,5.2.2 参照完整性的实现,1.利用企业管理器定义表间的参照完整性 操作步骤如下： （1）启动SQL Server企业管理器。 （2）在“控制台根目录”窗口，展开“SQL Server组”，展开“数据库”，选择使用的数据库，再展开“表”。 （3）在“控制台根目录”窗口，选择“表”，单击鼠标右键，打开快捷菜单。 （4）在快捷菜单中，选择“新建表”菜单命令，进入“设计表”窗口。 （5）在“设计表”窗口，设计表的结构。,,东北师范大学,5.2.2 参照完整性的实现,（6）重复（3）-（5）的操作，在“设计表”窗口，设计表的结构。 （7）在“控制台根目录”窗口，选择“关系图”，单击鼠标右键，打开快捷菜单。 （8）在快捷菜单中，选择“新建数据库关系图”菜单命令，进入“新关系图”窗口，并打开“创建数据库关系的向导”。 （9）在“创建数据库关系的向导”各窗口，依次添加要建立关系的表，返回“新关系图”窗口。 （10）在“新关系图”窗口，首先，选择外键列，将其拖到主键列处，进入“创建关系”窗口。 （11）在“创建关系”窗口，首先，确定“关系名”，然后，选择其它参数，最后，单击“确定”按钮，返回“新关系图”窗口，结束创建参照完整性的操作。,,东北师范大学,5.2.2 参照完整性的实现,2.利用SQL语句创建参照完整性SQL命令格式：CREATE TABLE （ 类型 (长度) 缺省值列级约束 ， 数据类型缺省值列级约束. ，UNIQUE（列名，列名.） ，PRIMARY KEY（列名，列名） ，FOREIGN KEY （列名，列名） REFERENCES 表名（列名，列名） ，CKECK (条件) )命令功能：创建表结构，并创建数据完整性。,,东北师范大学,5.2.2 参照完整性的实现,3.利用SQL语句修改参照完整性SQL命令格式：ALTER TABLE ADDCONSTRAINT Constraint_name FOREIGN KEY( Column ,.n )命令功能：修改数据完整性。,,东北师范大学,5.2.2 参照完整性的实现例子,1.原理分析1）参照完整性参照完整性是指两个表的主关键字和外关键字对应一致。它确保了有主关键字的表中对应其他表的外关键字的行存在，即保证了表之间的数据一致性，防止了数据丢失或无意义的数据在数据库中的扩散。参照完整性是建立在外关键字和主关键字之间或外关键字和唯一关键字之间的关系上的。2）Sql Server中的完整性体现在Sql Server中参照完整性作用表现在如下几个方面：禁止在从表中插入包含主表中不存在的关键字的数据行。禁止会导致从表中的相应值孤立的主表中的外关键字值改变。禁止删除在从表中的有对应记录的主表记录。,,东北师范大学,5.2.2 参照完整性的实现例子,3）Sql语句中删除和插入基本关系元组-表里的一行是一个元组 （1）在被参照关系删除元组的三种控制方式:级联删除：将参照关系（从表）中与被参照关系（主表）中要删除元组主键值相同的元组一起删除。受限删除：只有参照关系中没有元组与被参照关系中要删除元组主键值相同才能执行删除操作，否则拒绝。置空值删除：删除被参照关系中的元组，同时将参照关系中相应元组的外键值置为空。（2）在参照关系中插入元组受限插入：只有被参照关系中元组与参照关系中要插入元组外键值相同时，才执行插入操作，否则拒绝。递归插入：插入元组外键值在被参照关系中没有元组相同，则首先向被参照关系插入元组，其主键值等于参照关系元组的外键值，然后再向参照关系插入元组。,,东北师范大学,5.2.2 参照完整性的实现例子,4）DBMS对参照完整性进行检查的四种情况（1）在四种情况下DBMS要进行检查，分别是对参照表进行插入和修改以及对被参照表进行删除和修改。（2）SQL Server四种情况违反参照完整性的处理方法表1所示。,,东北师范大学,5.2.2 参照完整性的实现例子,,东北师范大学,5.2.2 参照完整性的实现例子（实验内容）,（1）为演示参照完整性，建立表Course，令cno为其主键，并在Stu_info中插入数据。（2）建立表SC，令sno和cno分别为参照Stu_info表以及Course表的外键，设定为级联删除，并令（sno，cno）为其主键。在不违反参照完整性的前提下，插入数据。（3）演示违反参照完整性的插入数据。（4）在Stu_info中删除数据，演示级联删除。（5）在Course中删除数据，演示级联删除。（6）在Course中删除数据，演示不能级联删除。（7）删除SC表的参照完整性。,,东北师范大学,5.2.2 参照完整性的实现例子（实验步骤）,（1）为演示参照完整性，建立表Course，令cno为其主键，并在Stu_info中插入数据。讲课的例子数据参照完整性的例子为演示参照完整性，建立表Course.txt,,东北师范大学,5.2.2 参照完整性的实现例子（实验步骤）,(2)建立表SC，令sno和cno分别为参照Stu_info表以及Course表的外键，设定为级联删除，并令（sno，cno）为其主键。在不违反参照完整性的前提下，插入数据。讲课的例子数据参照完整性的例子建立表SC，令sno和cno分别为参照.txt,,东北师范大学,5.2.2 参照完整性的实现例子（实验步骤）,（3）演示违反参照完整性的插入数据。讲课的例子数据参照完整性的例子演示违反参照完整性的插入数据。.txt服务器: 消息 547，级别 16，状态 1，行 1INSERT 语句与 COLUMN FOREIGN KEY 约束 FK_SC_sno_1CF15040 冲突。该冲突发生于数据库 School，表 Stu_info, column sno。语句已终止。,,东北师范大学,5.2.2 参照完整性的实现例子（实验步骤）,（4）在Stu_info中删除数据，演示级联删除。讲课的例子数据参照完整性的例子在Stu_info中删除数据，演示级联删除.txt分析：由于On delete cascade的连带删除作用，当表Stu_info中删除某一个学号，SC中对应这个学号为外键的所有记录都要被删除。,,东北师范大学,5.2.2 参照完整性的实现例子（实验步骤）,（5）在Course中删除数据，演示级联删除。讲课的例子数据参照完整性的例子在Course中删除数据，演示级联删除.txt,,东北师范大学,5.2.2 参照完整性的实现例子（实验步骤）,（6）在stu_info中删除数据，演示不能级联删除。讲课的例子数据参照完整性的例子在SCNew中删除数据，演示不能级联删除。.txt服务器: 消息 547，级别 16，状态 1，行 1DELETE 语句与 COLUMN REFERENCE 约束 FK_SCNew_sno_276EDEB3 冲突。该冲突发生于数据库 School，表 SCNew, column sno。语句已终止。,5.2.2 参照完整性的实现例子（实验步骤）,,东北师范大学,5.2.2 参照完整性的实现例子（实验步骤）,（7）删除SC表的参照完整性。,,东北师范大学,5.2.3 用户自定义完整性域完整性的实现,1.利用企业管理器创建、删除CHECK 约束操作步骤如下：（1）启动SQL Server企业管理器。（2）展开“SQL Server组”，展开“数据库”，选择使用的数据库，再展开“表”。（3）选择创建约束的表，单击鼠标右键，打开快捷菜单。（4）选择“设计表”菜单命令。（5）单击鼠标右键，打开快捷菜单。（6）选择“CHECK 约束”菜单命令，进入“属性”窗口。（7）选择“CHECK约束”选项卡，命名约束名，输入约束表达式。,,东北师范大学,5.2.3 用户自定义完整性域完整性的实现,2.利用CREATE命令创建CHECK约束SQL命令格式： CREATE . (Column_name Datatype NOT NULL | NULL DEFAULT Constraint_Expression Check_name CHECK ( Logical_Expression ) )命令功能：创建一个表，并定义数据完整性。,,东北师范大学,5.2.3 用户自定义完整性域完整性的实现,3.利用SQL命令修改CHECK约束SQL命令格式： ALTER TABLE ADD CONSTRAINT Check_name CHECK (Logical_expression) 命令功能：修改表结构，并可以定义数据完整性。4.利用SQL语句删除CHECK约束SQL命令格式： ALTER TABLE DROP CONSTRAINT Check_name 命令功能： 修改表结构，并可以删除数据完整性。,,东北师范大学,5.2.3 用户自定义完整性例子,1.原理分析1）用户自定义完整性不同的关系数据库系统根据其应用环境的不同，往往需要一些特殊的约束条件。用户自定义完整性即是针对某一个特定关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。（1）域完整性（Domain Integrity）域完整性是指数据库表中列必须满足某些特定的数据类型或者约束。域约束是最常见的用户自定义完整性约束，当有新数据插入到数据库中，系统可以按照定义进行关系属性取值是否属于域完整性正确的检测。其中约束又包括取值范围精度等规定。表中Check Foreign key约束和Default Not Null定义都属于域完整性的范畴。,,东北师范大学,5.2.3 用户自定义完整性例子,现在的DBMS中，一般都有域完整性的检查功能。Sql Server提供了定义和检查这类完整性的机制，以便用统一的系统方法来处理它们。而不是用应用程序来承担这一功能。其他完整性类型都支持用户定义的完整性。一个属性能否取空值一般由语义决定，也是域约束的内容之一。,,东北师范大学,5.2.3 用户自定义完整性例子,2）SQL的约束机制（1）静态约束：对静态对象的约束是反映数据库状态合理性的约束，如实体完整性。（2）动态约束：对动态对象的约束是反映数据库状态变迁的约束，如触发器。SQL中用于属性约束方面有Not Null、Check等子句；而用于全局约束方面的有Create Assertion、Create Rule等语句。全局约束：以上的约束都是对某一个元组或者元组的某一个属性进行约束，还有一种约束属于全局约束，那就是断言-ASSERTION。 全局约束是指一些比较复杂的完整性约束，这些约束涉及到多个属性间的联系或多个不同关系间的联系。 断言的语法格式： CREATE ASSERTION assertion_name CHECK ( ) ；,,东北师范大学,5.2.3 用户自定义完整性例子,现在举例说明断言的创建 ，下面是一个学生教学数据库的关系模式 学生 （学号，姓名，年龄，性别） S ( SNO , SN , AGE, SEX ) 课程 （课程号，课程名， 任课老师，学分） C ( CNO , CN , T, CREDIT ) 选课关系（学号，课程号，成绩） SC ( SNO, CNO, G )例题1， 要求不允许男同学选修张永老师教授的课程。讲课的例子数据自定义完整性的例子CREATE ASSERTION说明断言的创建.txt,,东北师范大学,5.2.3 用户自定义完整性例子,看来上面的SQL语句有点复杂，实际上仔细阅读，可以看出他的意思为“ 不存在这样的学生，他既是男同学，而且还选修了张永老师所教的课程“。当建立了这样一个断言ss1后，数据库系统会把这个断言的定义存放在数据字典中，每当学生选课时，即插入或更新数据库表的时候，系统都要检查一下所作的插入或修改操作是否满足ss1断言的约束条件，如果满足条件，则允许更新，否则，则拒绝插入或更新，并给出出错信息。,,东北师范大学,5.2.3 用户自定义完整性例子,例题2 ， 学生必须在选修了数学课以后，才能选修其他的课程。 CREATE ASSERTION ass2 CHECK (NOT EXISTS ( SELECT * FROM SC WHERE SNO NOT IN ( SELECT SNOFROM C, SCWHERE C.CNO = SC.CNO AND CN = 数学) ） );断言的创建因为使用了多表操作，因此，不好理解。仔细观察，这个断言的意思是“不存在这样的一个元组，SC表中的学号不在选修了数学课的学号中“。,,东北师范大学,5.2.3 用户自定义完整性例子,对上面的几种约束进行比较。 1） 对于属性的约束，只对一个属性进行检查，每当插入或修改时激活测试条件，不一定能够保持数据的一致性。 2） 对于元组的约束，只对一个元组加以约束，每当插入或修改是激活测试条件，不一定能保证数据的一致性。 3）全局约束（断言），对多个关系聚合操作，每当任何变动都可以激活测试条件，断言虽不能完全保证数据的一致性，但他能够保证数据操作不出错。,,东北师范大学,5.2.3 用户自定义完整性例子,3)一般的规则组成规则一般是由规则标识（可默认）以及规则语义组成。规则语义由约束作用的数据对象、约束语义（如断言Assertion）、触发条件以及违反规则的响应动作构成。例如：规则1：公司职工的低薪是3000元。受约束的对象：“职工”关系的Salary属性。约束语义：Title 职工时，Salary=3000。触发条件：更新职工元组违反响应：拒绝执行更新操作。,,东北师范大学,5.2.3 用户自定义完整性例子,4）规则的一般分类如表所示：,,东北师范大学,5.2.3 用户自定义完整性例子,例如：静态规则约束：“日期”的格式为YY-MM-DD;“成绩”的值域为0-100；“学号”不能取空值；“职称”为教授时，工资值大于3000。动态规则约束：更新时“工资”的新值应该超过旧值；事务的一致性，转帐前后余额的和保持不变。,,东北师范大学,5.2.3 用户自定义完整性例子,5）Check约束（1）Check约束是对列或者列的组合取值限制，它采用SQL语句中Where子句相同的表达方式来表达，分为表一级和列一级的约束。（2）空值的问题空值意味着检查约束的值是未知的，所以空值的出现不会违反检查约束的条件。（3）潜在的语义问题大多数的DBMS不会检查约束和默认值定义的语义，所以要注意语义冲突。例如：讲课的例子数据自定义完整性的例子要注意语义冲突.txt,,东北师范大学,5.2.3 用户自定义完整性例子,Check与Check之间的语义冲突Check(empno10 and empno9)还可能存在的语义冲突定义了置空删除，但表中检查约束要求此列不能为空；定义该列不能为空，检查约束要求此列为空。