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

1、第第7章章 数据完整性数据完整性 2023年2月12日星期日1第第7 7章章 数据完整性数据完整性 q 数据完整性的概念数据完整性的概念 q 约束管理约束管理 q 默认管理默认管理 q 规则管理规则管理 q 完整性技术比较研究完整性技术比较研究 q 习题习题第第7章章 数据完整性数据完整性 2023年2月12日星期日2一、数据完整性的概念一、数据完整性的概念 所谓完整性，就是指存储在数据库中数据的一致性和正确性。数据完整性是衡量数据库中数据质量好坏的一种标志，是确保数据库中数据一致、正确以及符合企业规则的一种思想，是使无序的数据条理化，确保正确的数据被存放在正确的位置的一种手段，实施数据库完整

2、性的目的是确保数据的质量。所以，满足完整性要求的数据具有以下3个特点：数据的值正确无误。数据的存在必须确保同一表格数据之间的和谐关系。数据的存在必须能确保维护不同表格数据之间的和谐关系。在Microsoft SQL Server系统中，从维护数据完整性的意义上来看，数据完整性分为四类：实体完整性、域完整性、参考完整性、用户定义的完整性。第第7章章 数据完整性数据完整性 2023年2月12日星期日31.实体完整性实体完整性实体完整性，也可以称为行完整性，规定表的每一行在表中是惟一的实体。实体就是数据库所要表示的一个实际的物体或事件。实体完整性要求每个实体都保持惟一性，因此，要求表中的所有行有一个

3、惟一的标识符，这种标识符一般称为主键值。实体完整性要求数据库表中的每一条记录都是惟一的，即表中没有重复的记录。因此，这就要求所有数据记录中至少必须有一个字段的内容是不能重复的。第第7章章 数据完整性数据完整性 2023年2月12日星期日42域完整性（值域完整性）域完整性（值域完整性）域完整性，也可以称为列完整性，是指给定列的输入有效性，即数据库表中的列必须满足某种特定的数据类型或约束。指定一个数据集对某一个列是否有效和确定是否允许空值。域完整性通常是通过有效性检查来实现的，并且还可以通过限制数据类型、格式或者可能的取值范围来实现。因此，强制域有效性的方法有：限制类型（通过数据类型）、格式（通过

4、 CHECK 约束和规则）或可能值的范围（通过 FOREIGN KEY 约束、CHECK 约束、DEFAULT 定义、NOT NULL 定义和规则）。第第7章章 数据完整性数据完整性 2023年2月12日星期日53.参照完整性（引用完整性）参照完整性（引用完整性）参照完整性是在插入或删除数据时，维护表格间数据一致性的手段。参照完整性确保在不同表之间的关键性数据保持一致（涉及两个或两个以上表的数据的一致性维护），防止了数据丢失或无意义的数据在数据库中扩散。当添加、删除或修改数据库表中的记录时，可以借助引用完整性来保证相关联的表之间的数据一致性。在SQL Server中，参照完整性一般建立在主键与

5、外键之间的关系，或者外键与唯一索引之间关系的基础上，它确保了有主关键字的表中对应其它表的外部关键字的行存在。第第7章章 数据完整性数据完整性 2023年2月12日星期日6第第7章章 数据完整性数据完整性 2023年2月12日星期日74.用户定义的完整性用户定义的完整性这是由用户定义的完整性。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。SQL Server 提供了定义和检验这类完整性的机制，以便用统一的系统方法来处理它们，而不是用应用程序来承担这一功能。用户定义完整性可以定义不属于其他任何完整性分类的特定业务规则，其它的完整性类型都支持

6、用户定义的完整性。用户定义的完整性主要是体现实际运用的业务规则，可以通过前面三种完整性的实施而得到维护。例如：在titles表中，规定商业类用书的title_id的前两个字符必须是BU等。第第7章章 数据完整性数据完整性 2023年2月12日星期日8实现数据完整性实现数据完整性在Microsoft SQL Server系统中，可以使用两种方式实现数据完整性，即声明数据完整性和过程数据完整性。声明数据完整性（说明性引用数据完整性）声明数据完整性（说明性引用数据完整性）就是通过在对象定义中定义的数据标准来实现数据完整性，在插入、修改和删除数据时由系统本身自动强制来实现的。声明数据完整性的方式包括使

7、用各种约束、缺省和规则。过程数据完整性（引用的行动完整性）过程数据完整性（引用的行动完整性）是通过在脚本语言中定义的数据完整性标准来实现的。在执行这些脚本的过程中，由脚本中定义的强制完整性的实现。过程数据完整性的方式包括使用视图、触发器和存储过程等，其中视图和存储过程不能自动执行。第第7章章 数据完整性数据完整性 2023年2月12日星期日9实现数据完整性的途径实现数据完整性的途径数据完整性类型实施途径数据完整性类型实施途径实体完整体性主键约束 PRIMARY KEY域完整性默认值约束 Default惟一性约束 Unique Key检查约束 Check索引 Unique Index外键约束 F

8、oreign Key标识列 Identity Column数据类型 Data Type参照完整性外键约束 Foreign Key规则 Rule检查约束 Check用户定义完整性Rule、Triggers、Procedure触发器 TriggersCreate Table中的全部列级和表级约束系统存储过程 Stored Procedure第第7章章 数据完整性数据完整性 2023年2月12日星期日10二、约束管理二、约束管理 约束是通过限制列中、行中和表之间数据输入值的范围来保证数据完整性的非常有效的方法。约束可以分为两种：列级约束：是行定义的一部分，只能够应用在一列上。表级约束：其定义独立于列

9、的定义，可以应用在一个表中的多个列上。约束的类型 定义约束 默认管理 检查约束管理 主键约束管理 惟一性约束管理 外键约束管理 约束类型约束类型综合例题第第7章章 数据完整性数据完整性 2023年2月12日星期日11约束的类型约束的类型完整性类型完整性类型约约 束束 类类 型型描描 述述域完整性域完整性DEFAULT(缺省)在使用INSERT语句插入数据时，如果某个列的值没有明确提供，则将定义的缺省值插入到该列中CHECK(检查)指定某一个列中的可保存值的范围实体完整性实体完整性PRIMARY KEY(主键)每一行的惟一标识符，确保用户不能输入冗余值和确保创建索引，提高性能，不允许空值UNIQ

10、UE(惟一性)防止出现冗余值，并且确保创建索引，提高性能。允许空值参考完整性参考完整性FOREIGN KEY(外键)定义一列或几列，其值与本表或者另外一个表的主键值匹配约束是保证数据完整性的有效方法。约束可以确保把有效的数据输入到列中和维护表和表之间的特定关系。每一种数据完整性类型，例如实体完整性、域完整性、参考完整性，都由不同的约束类型来保障。下表描述了不同类型的约束和完整性之间的关系。第第7章章 数据完整性数据完整性 2023年2月12日星期日12定义约束定义约束定义约束从无到有的创建约束，这种操作可以使用CREATE TABLE语句或ALTER TABLE语句完成。使用CREATE TA

11、BLE语句表示在创建表的时候定义约束，使用ALTER TABLE语句表示在已有的表中添加约束。如果表中已经有数据，那么也可以增加约束。第第7章章 数据完整性数据完整性 2023年2月12日星期日13CREATE TABLE语句语句CREATE TABLE table_name(column_name data_type CONSTRAINT constraint_name PRIMARY KEY CLUSTERED|NONCLUSTERED|UNIQUE CLUSTERED|NONCLUSTERED|FOREIGN KEY REFERENCES ref_table(ref_colunm)|DE

12、FAULT constant_expression|CHECK logical_expression 使用ALTER TABLE语句定义约束的语法与使用CREATE TABLE语句定义约束的语法类似。第第7章章 数据完整性数据完整性 2023年2月12日星期日14【示例】定义一个主键约束CREATE TABLE students(student_id int NOT NULL CONSTRAINT PK_student_id PRIMARY KEY,student_name varchar(10)NOT NULL,student_gender char(1)NOT NULL,student_b

13、irthday datetime NOT NULL)第第7章章 数据完整性数据完整性 2023年2月12日星期日15当定义约束或修改约束的定义时，应该考虑下列因素：l不必删除表，就可以直接创建、修改和删除约束的定义。l应该在应用程序中增加错误检查机制，测试数据是否与约束相冲突。l当在表上增加索引时，SQL SERVER系统检查表中的数据是否与约束冲突。当创建约束时，应该指定约束的名称。否则，系统将要提供复杂的、系统自动生成的名称。对于一个数据库来说，约束名称必须是惟一的。一般来说，约束的名称格式应该是：约束类型简称约束类型简称_表名表名_列号列号_代号代号第第7章章 数据完整性数据完整性 20

14、23年2月12日星期日16约束信息的查看约束信息的查看查看约束信息的方法为：（1）执行系统存储过程，如：sp_help 或 sp_helpconstraint。（2）查询信息模式视图，如：check_constraints、referential_constraints或table_constraints。（3）查看一些系统表，例如约束的定义存储在下列系统表中：sysconstraints、sysreferences和syscomments。第第7章章 数据完整性数据完整性 2023年2月12日星期日17（1）使用sp_helpconstraint可得到数据库中某一个表中的全部约束信息。【例】

15、显示pubs数据库中authors表中的全部约束信息USE pubsEXEC sp_helpconstraint authors第第7章章 数据完整性数据完整性 2023年2月12日星期日18（2）syscomments系统表包含了每一个视图、规则、缺省、触发器、检查约束、缺省约束和存储过程等信息。在该系统表中的文本列上记录了这些对象的原始定义语句。使用该系统表，可以查看有关约束的定义信息。【例】显示pubs数据库中syscomments系统表的内容USE pubsSELECT id,text,texttype,languageFROM syscomments第第7章章 数据完整性数据完整性

16、2023年2月12日星期日19（3）sysreferences系统表记录了与外键约束有关的信息。【例】显示pubs数据库中sysreferences系统表的内容USE pubsSELECT*FROM sysreferences第第7章章 数据完整性数据完整性 2023年2月12日星期日20默认（默认（DEFAULT）约束管理）约束管理当使用INSERT语句插入数据时，如果某一个列没有指定数据，那么默认约束就在该列中输入一个值。因此，默认约束保证了域完整性。DEFAULT约束用于指定一个字段的默认值。它的作用是：当向表中插入数据时，如果用户没有给某一字段输入数据，则系统自动将默认值作为该字段的数

17、据内容。向表中添加数据时，如果没有输入字段值，则此字段的值可能是下面几种情况：此字段定义了默认值，则此字段的内容为默认值；此字段未定义默认值，而且允许为NULL值，则NULL值将成为该字段的内容；此字段未定义默认值，也不允许为NULL值，保存时将会出现错误信息，而且添加数据操作失败。第第7章章 数据完整性数据完整性 2023年2月12日星期日21使用使用CREATE TABLE语句创建语句创建DEFAULT约束约束在CREATE TABLE语句中添加DEFAULT子句，DEFAULT子句格式如下：CONSTRAINT constraint_name DEFAULT constant_expre

18、ssion 其中：constraint_name 为约束名；constant_expression为常量表达式。【示例】将入学日期字段的默认值设置为2003-9-1CREATE TABLE stud_new(学号 char(8)NOT NULL,姓名 char(8)NOT NULL,入学日期 datetime CONSTRAINT dt DEFAULT 2003-9-1)第第7章章 数据完整性数据完整性 2023年2月12日星期日22使用企业管理器创建使用企业管理器创建DEFAULT约束约束第第7章章 数据完整性数据完整性 2023年2月12日星期日23当使用默认约束时，需要考虑下列一些因素：

19、l默认约束只能应用于INSERT语句。l每一个列只能定义一个默认约束。l默认约束不能放在有IDENTITY属性的列上或者数据类型为timestamp的列上。l默认约束允许指定一些由系统函数提供的值。这些系统函数包括USER、CURRENT_USER、SESSION_USER、SYSTEM_USER、CURRENT_TIMESTAMP等。第第7章章 数据完整性数据完整性 2023年2月12日星期日24检查（检查（CHECK）约束管理）约束管理检查约束用来限制用户输入某一个列的数据，即在该列中只能输入指定范围的数据。检查约束非常类似于WHERE子句，其共同之处就是指定可接受数据的条件。检查约束与外

20、键约束的相同之处，在于都是通过检查数据的值的合理性来实现数据完整性的维护。但是，外键约束是从另一张表上获得合理的数据，而检查约束则是通过对一个逻辑表达式的结果进行判断来对数据进行检查。检查约束可以用来限制字段上可以接受的数据值。检查约束使用逻辑表达式来限制字段上可以接受的数据值。检查约束通过检查输入表中字段的的数据值来维护域完整性。第第7章章 数据完整性数据完整性 2023年2月12日星期日25使用检查约束的方式有：在创建表格时，定义检查约束，检查约束是表格定义的一部分。在已经建立的表上添加检查约束。修改或删除表上已经定义的检查约束。第第7章章 数据完整性数据完整性 2023年2月12日星期日

21、26使用使用CREATE TABLE语句创建检查约束语句创建检查约束使用CREATE TABLE语句创建表时，可以通过在该语句中添加一个CHECK子句创建检查约束。该子句的语法格式如下：CONSTRAINT check_name CHECK(check_criterial)其中，check_name为约束名，check_criterial为约束条件。【示例】将“学号”和“课程编号”两个字段的组合设置为主键，再将“成绩”字段的取值限制在0到100之间USE lxCREATE TABLE 学生成绩(学号 char(8)NOT NULL,课程编号 char(3)NOT NULL,成绩 real NO

22、T NULL CONSTRAINT CK_CJ CHECK (成绩成绩=0 AND 成绩成绩=0 AND vale=$1000 and range=18 AND employee_age=35)GOsp_bindrule rule_employee_age,employee.age第第7章章 数据完整性数据完整性 2023年2月12日星期日81 2、将规则绑定到用户定义的数据类型【示例】假设存在名为 rule_ssn 的规则和名为 ssn 的用户定义数据类型，此示例将 rule_ssn 绑定到 ssn。在 CREATE TABLE语句中，类型 ssn 的列继承 rule_ssn 规则。类型 s

23、sn 的现有列也继承 rule_ssn 规则，除非为 futureonly_flag 指定了 futureonly 或者在 ssn 上直接绑定了规则。绑定到列的规则始终优先于绑定到数据类型的规则。USE master EXEC sp_bindrule rule_ssn,ssn GO第第7章章 数据完整性数据完整性 2023年2月12日星期日82 3、使用 futureonly_flag【示例】将 rule_ssn 规则绑定到用户定义数据类型 ssn。因为已指定 futureonly，所以不影响类型 ssn 的现有列。USE master EXEC sp_bindrule rule_ssn,ss

24、n,futureonly GO 4、使用分隔标识符【示例】显示在 object_name 中分隔标识符的使用 USE master CREATE TABLE t.2(c1 int)EXEC sp_bindrule rule1,t.2.c1 GO第第7章章 数据完整性数据完整性 2023年2月12日星期日83解除规则绑定解除规则绑定使用sp_unbindrule在当前数据库中为列或用户定义数据类型解除规则绑定。语法格式为：sp_unbindrule objname=object_name ,futureonly=futureonly_flag其中：object_name为要解除规则绑定的列或用户

25、自定义数据类型的名称。futureonly=futureonly_flag为一个可选参数，仅在解除一个捆绑在用户自定义数据类型上的规则时使用。futureonly_flag默认值是NULL，如果设置为future_only，则在解除绑定前，使用这个数据类型的表列将继续受到规则的约束。其目的是为了防止具有指定用户定义数据类型的列丢失掉捆绑在其上的规则。第第7章章 数据完整性数据完整性 2023年2月12日星期日84【示例】为表 employees 的 startdate 列解除规则绑定。exec sp_unbindrule employees.startdate【示例】为用户定义数据类型 ssn

26、 解除规则绑定。这将为该数据类型的现有列和将来的列解除规则绑定。exec sp_unbindrule ssn【示例】为用户定义数据类型ssn解除规则绑定，现有的 ssn 列不受影响。exec sp_unbindrule ssn,futureonly第第7章章 数据完整性数据完整性 2023年2月12日星期日85删除规则删除规则如果规则未绑定到任何列或用户定义的数据类型，那么该规则将被立即删除。如果规则被绑定于表列或用户定义的数据类型，那么在删除该规则之前，必须从所有的表列或用户定义的数据类型上解除该规则对象。当要删除的规则仍捆绑在列或用户自定义数据类型上时，执行DROP RULE语句会显示错误

27、信息，同时DROP RULE操作将被撤销。使用DROP RULE从当前数据库中删除一个或多个用户定义的规则。语法格式如下：DROP RULE 规则名规则名 ,.n【示例】删除规则USE company DROP RULE rule_employee_ageGO第第7章章 数据完整性数据完整性 2023年2月12日星期日86规则的优先级规则的优先级用于规则绑定的优先级规则如下：绑定于列的规则优先于绑定于用户定义的数据类型的规则。如果一个表列和定义该列的用户定义的数据类型都绑定了规则，那么绑定于表列的规则有效。如果在表列上绑定了一个新规则，那么该规则代替绑定于定义该列的用户定义的数据类型的规则。如

28、果把规则绑定到某一列或者用户定义的数据类型上，则新规则取代原来的规则。用户只能使用绑定到表列或用户定义的数据类型之上的规则。如果把规则绑定到用户定义的数据类型上，那么它不会取代绑定于使用这种类型定义的列上。第第7章章 数据完整性数据完整性 2023年2月12日星期日87规则对已经输入到表中的数据无效。因此，表中已有的数据可以不满足规则指定的要求。如果希望一个规则限制已经输入到表中的数据，那么就要在数据输入到表中之前就把定义好的规则绑定到用户定义的数据类型或表列上。规则的优先级新规则绑定到绑定于用户定义的数据类型的旧规则绑定于列的旧规则用户定义的数据类型替换旧规则没有变化表列替换旧规则替换旧规则

29、第第7章章 数据完整性数据完整性 2023年2月12日星期日88A.使用企业管理器创建创建规则B.使用企业管理器查看查看规则C.使用企业管理器绑定绑定规则D.使用企业管理器删除删除规则使用企业管理器管理规则使用企业管理器管理规则第第7章章 数据完整性数据完整性 2023年2月12日星期日89五、完整性技术比较研究五、完整性技术比较研究 之后高高触发器之前中低缺省和规则之前低中约束事务之前或者之后开 支功能性数据完整性方法数据完整性方法的性能比较数据完整性方法的性能比较第第7章章 数据完整性数据完整性 2023年2月12日星期日90小 结本章介绍了数据完整性技术，内容包括数据完整性的概念、约束管

30、理、默认管理、规则管理以及使用标识列。完整性类型技术实现功能描述域完整性DEFAULT（默认值）输入数据时如果某个列没有明确提供值，则将该默认值插入到列中DEFAULT(默认技术)）CHECK（检查）指定某列可接受值的范围或模式RULE（规则技术）实体完整性PRIMARY KEY（主键）行的唯一标识符，不允许空值UNIQUE（唯一键）防止出现冗余值，允许空值IDENTITY（标识列）确保值的唯一性，不允许空值，不允许用户更新参照完整性FOREIGN KEY（外键）保证列与参照列的一致性表 完整性技术第第7章章 数据完整性数据完整性 2023年2月12日星期日911什么是数据完整性？在SQL S

31、erver系统中包括几种数据完整性类型？2给出下列约束定义：主键约束、外键约束、惟一性约束、检查约束、默认约束3主键约束和唯一性约束的异同点是什么？4什么是默认值？为表中数据提供默认值有几种方法？在企业管理器中如何创建？创建、修改、删除默认值分别使用什么语句？5什么是规则？在企业管理器中如何创建？定义好的规则使用什么方法对列或用户自定义数据类型起作用？创建、修改、删除规则分别使用什么 T-SQL 语句？6在企业管理器中操作综合练习题。习习 题题第第7章章 数据完整性数据完整性 2023年2月12日星期日927利用企业管理器创建销售管理数据库的表结构，每个表至少输入10条记录，要求记录要符合现实意义。各表结构如下：第第7章章 数据完整性数据完整性 2023年2月12日星期日93