1. 第6章 实施数据完整性 教学内容： • 用约束来实施数据完整性 • 用规则来实施数据完整性 • 用默认值来实施数据完整性

2. 6.1 数据完整性的概念 • 数据完整性是指数据的精确性和可靠性。 • 数据完整性是为了防止数据库中存在不符合语义的数据，为了维护数据的完整性，数据库管理系统必须要提供一种机制来检查数据库中的数据，看其是否满足语义规定的条件。 • 这些加在数据库数据之上的语义检查条件就称为数据完整性约束条件，这些完整性条件作为表定义的一部分存储在数据库中。 • DBMS中检查数据是否满足完整性条件的机制就称为完整性检查。

3. 6.1.1实现数据完整性的方法 • 四种数据完整性：实体完整性、域完整性、引用完整性和用户自定义的完整性 • 在服务器端 • 定义表时声明数据完整性 。 • 在服务器端触发器来实现 。 • 在客户端 • 在应用程序中编写代码来保证。在客户端实现数据完整性的好处是在将数据发送到服务器端之前，可以先进行判断，然后，只将正确的数据发送给数据库服务器。 • 缺点：数据完整性要求发生变化时，都必须要修改应用程序，加重了维护应用程序的负担。 • 这里只介绍在服务器端实现数据完整性的方法。

4. 6.1.2完整性约束条件的作用对象 • 字段（列）级约束 • 数据类型 、数据格式 、取值范围 、空值的约束 。 • 行（元组）级约束 • 各个字段之间的联系的约束 。订货数量小于等于库存数量 。 • 表（关系）级约束 • 表约束是指若干行之间、表之间的联系的约束。如玩具ID的取值不能重复也不能取空值

5. 6.2 约 束 • 主关键字约束（Primary Key Constraint） • 外关键字约束（Foreign Key Constraint） • 唯一性约束（Unique Constraint） • 检查约束（Check Constraint） • 缺省约束（Default Constraint） 约束提供了自动保持数据库完整性的一种方法

6. 6.2.1 主关键字约束 • 主关键字约束中（简称主键约束）指定表的一列或几列的组合的值在表中具有唯一性， • 每个表中只能有一列被指定为主关键字，且IMAGE 和TEXT 类型的字段都不能被指定为主关键字，也不允许指定主关键字列有NULL 属性。 • 主键约束确保实体完整性。 • 可以在创建表的时候定义主键约束，也可以在以后改变表的时候添加。 • 当定义主键 约束时，需要指定约束名。如果未指定，SQL Server会自动为该约束分配一个名字。 • 如果将主键约束定义在一个已经包含数据的列上，那么，该列中已经存在的数据将被检查。如果发现了任何重复的值，那么，主键约束将被拒绝。

7. 6.2.1 主关键字约束 语法如下： CONSTRAINT constraint_name PRIMARY KEY [CLUSTERED | NONCLUSTERED] （column_name1[, column_name2,…,column_name16]） 例6-1：创建订单（Orders）表，订单编号（cOrderNo）为主键。 CREATE TABLE Orders ( cOrderNo CHAR(6) CONSTRAINT pkOrderNo PRIMARY KEY CLUSTERED, … ) 也可以： ALTER TABLE Orders ADD CONSTRAINT pkOrderNo PRIMARY KEY CLUSTERED (cOrderNo)

8. 例6-2： 创建订单细节（OrderDetail）表，订单编号（cOrderNo）和玩具ID（cToyId）为组合主键。 • CREATE TABLE OrderDetail • ( • cOrderNo CHAR(6) NOT NULL, • … • CONSTRAINT pkOrderDetail PRIMARY KEY (cOrderNo, cToyId) • )

9. 6.2.2 外关键字约束 • 外关键字约束（简称外键约束）定义了表之间的关系。当一个表中的数据依赖于另一个表中的数据时，你可以使用外键约束避免两个表之间的不一致性。 • 当一个表中的一个列或多个列的组合和其它表中的主关键字定义相同时，就可以将这些列或列的组合定义为外关键字，并设定它适合哪个表中哪些列相关联。 • 级联更新 • 插入检查 • 外键约束实施了引用完整性。 • 与主关键字相同，不能使用一个定义为 TEXT 或IMAGE 数据类型的列创建外关键字。外关键字最多由16个列组成。

10. 外关键字约束语法如下： CONSTRAINT constraint_name FOREIGN KEY （column_name1[, column_name2,…,column_name16]） REFERENCES ref_table [ （ref_column1[,ref_column2,…, ref_column16] ）] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] ] [ NOT FOR REPLICATION ]

11. 例6-3： 创建订单细节（OrderDetail）表，订单编号（cOrderNo）和玩具ID（cToyId）为组合主键。同时它们又是外键，和订单表的订单编号、玩具表的玩具ID相关联。 CREATE TABLE OrderDetail ( cOrderNo CHAR(6) REFERENCES Orders (cOrderNo), /*省略部分关键字，行级约束*/ cToyId CHAR(6) NOT NULL, … CONSTRAINT pkOrderDetail PRIMARY KEY (cOrderNo, cToyId) , FOREIGN KEY (cToyId) REFERENCES Toys (cToyId) /*表级约束*/ ) 也可以用ALTER TABLE命令修改表，如下： ALTER TABLE OrderDetail ADD CONSTRAINT fkOrderNo FOREIGN KEY (cOrderNo) REFERENCES Orders (cOrderNo) ALTER TABLE OrderDetail ADD CONSTRAINT fkToyId FOREIGN KEY (cToyId ) REFERENCES Toys(cToyId )

12. 6.2.3 唯一性约束 • 唯一性约束指定一个或多个列的组合的值具有唯一性，以防止在列中输入重复的值。唯一性约束指定的列可以有NULL 属性。由于主关键字值是具有唯一性的，因此主关键字列不能再设定唯一性约束。唯一性约束最多由16个列组成。 • 创建UNIQUE约束有关的规则有： • 它可以创建在列级，也可以创建在表级。 • 它不允许一个表中有两行取相同的非空值。 • 一个表中可以有多个UNIQUE约束。 • 即使指定了WITH NOCHECK 选项，也不能阻止根据约束对现有数据进行的检查。

13. 语法如下： CONSTRAINT constraint_name UNIQUE [CLUSTERED | NONCLUSTERED] （column_name1[, column_name2,…,column_name16]） 例6-4：创建国家（Country）表，指定国家不能重复，ID号为主键。 CREATE TABLE Country ( cCountryId CHAR(3) PRIMARY KEY, /*行级约束，没有指定约束名*/ cCountry CHAR(25) NOT NULL UNIQUE /*行级约束，没有指定约束名*/ ) ALTER TABLE Country ADD CONSTRIANT unqCountry UNIQUE (cCountry) /*表级约束*/

14. 6.2.4 检查约束 • 检查约束通过限制插入列中的值来实施域完整性。可以在一列上定义多个检查约束。它们按照定义的次序被实施。当约束被定义成表级时，单一的检查约束可以被应用到多列。 语法如下： CONSTRAINT constraint_name CHECK [NOT FOR REPLICATION] (logical_expression)

15. 1．IN关键字 使用IN 关键字可以确保：键入的值被限制在一个常数表达式列表中。 例如：下列命令在表Shopper的列cCity上创建了CHECK约束chkCity， CREATE TABLE Shopper ( … cCity CHAR(15) NOT NULL CONSTRAINT chkCity CHECK(cCity IN ('Boston', 'Chicago',' Dallas','New York', 'Paris','Washington')) … ) 也可以 ALTER TABLE Shopper ADD CONSTRAINT chkCity CHECK(cCity IN ('Boston', 'Chicago',' Dallas','New York', 'Paris','Washington'))

16. 2．LIKE关键字 使用LIKE关键字可以通过通配符来确保输入某一列的值符合一定的模式。 例如： CHECK (cShopperId LIKE “[0-9][0-9][0-9][0-9] [0-9][0-9]”) 上述CHECK约束指定[0-9][0-9]只能包含六位数值。

17. 3．BETWEEN 关键字 可以通过BETWEEN关键字来指明常数表达式的范围。该范围中包括上限值和下限值。 例如： CHECK (siToyQoh BETWEEN 0 AND 100) 上述CHECK约束指定了属性siToyQoh的值只能在0和100之间。

18. 6.2.5 缺省约束 • 缺省约束可用于为某列指定一个常数值，这样用户就不需要为该列插入值。只能在一列上创建一个缺省约束，且该列不能是IDENTITY列。 • 缺省约束通过定义列的默认值或使用数据库的默认值对象绑定表的列，来指定列的默认值。SQL Server 推荐使用缺省约束，而不使用定义默认值的方式来指定列的默认值。 语法如下： CONSTRAINT constraint_name DEFAULT constant_expression [FOR column_name]

19. 例6-6 • 在Shopper的cCity属性上创建DEFAULT约束。如果没有指定城市，则属性cCity将缺省地包含“Chicago”。 CREATE TABLE Shopper ( … cCity char(15) DEFAULT “Chicago”, … ) • 如果表已经创建，但没有指定缺省，则可以用ALTER TABLE命令来指定缺省。如下： ALTER TABLE Shopper ADD CONSTRAINT defCity DEFAULT “Chicago” FOR cCity

20. 6.2.6 在企业管理器中创建约束

21. 6.2.7 系统对约束的检查 1．主键约束 • 对于主键约束，每当用户执行插入数据时，系统检查新插入的数据的主键值是否与已存在的主键值重复，或者新插入的主键值是否为空。当用户执行修改有主键约束的列时，系统检查修改后的主键值是否与表中的主键值重复，或者修改后的主键值是否有空值。 • 只有当新插人数据或者修改后的主键值满足不重复、不空时，系统才进行插入和修改操作，否则出错。

22. 6.2.7 系统对约束的检查 2．唯一值约束 • 对唯一值约束的检查同主键很类似．只是在检查有唯一值约束的列时，系统只需检查新插入数据或者更改后的有唯一值约束的列的值是否与表中已有数据有重复，而不检查是否有空值。只要新插人数据或者更改后的值满足不重复这个条件，即可进行操作。 • 注意：对于有唯一值约束的列，可以有空值，但整个列只允许有一个空值。系统会将后续的空值看成与第一个空值重复的值，因此全拒绝操作。

23. 6.2.7 系统对约束的检查 • 3．外键约束 • 当在子表中插人数据时，检查新插入数据的外键值是否在主表的主键值范围内，若在主键值范围，则插入，否则失败。 • 当在子表中修改外键列的值时，检查修改后的外键值是否在主表的主键值范围 内，若在主键值范围，则进行修改，否则失败。 • 当在主表中删除数据时，检查被删除数据的主键值是否在于表中有对它的引用，若无对它的引用，则删除之；若有，则看是否允许级联删除，若允许级联删除则将子表中外键值等于被删除数据的主键值的记录一起删掉，若不允许级联删除则删除失败。 • 当更改主表中的主键列的值时，检查被更改的主键值是否在子表中有对它的引用，若无对它的引用，则更改之，若有，剐看是否允许级联更改，若允许级联更改，则将于表中外键值等于被更改主键值的记录的外键一起进行更改；若不允许级联更改，则更改失败。

24. 6.2.7 系统对约束的检查 4．检查约束 • 对检查约束同唯一值约束类似。当用户插入数据或修改有列取值约束的数据时，系统检查新插入的值或更改后的值是否符合列检查（取值范围）约束，若符合则执行插入或修改操作，否则拒绝操作。 5． 缺省约束 • 对于缺省约束，是当用户对数据进行插入操作并且没有为某个列提供值时，系统检查省略值的列是否有默认值约束，若有别插入默认值，若无，则系统检查此列是否允许为空，若允许，则插入空值，否则出错。

25. 6.3 规则 • 规则（Rule）就是数据库中对存储在表的列或用户自定义数据类型中的值的规定和限制。 • 规则是单独存储的独立的数据库对象。 • 规则与其作用的表或用户自定义数据类型是相互独立的。 • 规则和约束可以同时使用，表的列可以有一个规则及多个检查约束。

26. 6.3.1 创建规则 语法如下： CREATE RULE rule_name AS condition_expression • 其中condition_expression 子句是规则的定义。condition_expression 子句可以是能用于WHERE 条件子句中的任何表达式，它可以包含算术运算符、关系运算符和谓词（如IN、LIKE、 BETWEEN 等）。 注意：condition_expression子句中的表达式必须以字符“@”开头。

27. 规则的例子

28. 2．用企业管理器创建规则 • 在企业管理器中选择数据库对象“规则”，单击右键从快捷菜单中选择“新建规则”选项。

29. 3．使用规则的限制 • 在一个时刻，只有一条规则可以绑定到某列或某个用户自定义数据类型上。 • 如果一条规则被绑定到某用户自定义数据类型上，它并不会替代绑定到该数据类型的列上的规则。 • 如果一条新的规则绑定到某列或某数据类型上，而该列或类型上已经绑定了一条规则，那么，新规则将替代旧规则。 • 规则不会应用到已经插入表的数据。表中现有的值不一定要符合规则指定的标准。 • 不能为系统定义的数据类型定义规则。

30. 6.3.2 查看规则 • sp_helptext [@objname =] 'name' • 例如：exec sp_helptext birthday_defa 查看birthday_defa规则 。 • 用企业管理器查看规则

31. 6.3.3规则的绑定与松绑 • 创建规则后，规则仅仅只是一个存在于数据库中的对象，并未发生作用。 • 将规则与数据库表或用户自定义对象联系起来，才能达到创建规则的目的。 • 解除规则与对象的绑定称为“松绑”。

32. 1．用存储过程Sp_bindrule 绑定规则 • 语法如下： sp_bindrule [@rulename =] 'rule', [@objname =] 'object_name' [, 'futureonly'] • 各参数说明如下： • [@rulename =] 'rule'：指定规则名称。 • [@objname =] 'object_name'：指定规则绑定的对象。 • 'futureonly'：此选项仅在绑定规则到用户自定义数据类型上时才可以使用。当指定此选项时，仅以后使用此用户自定义数据类型的列会应用新规则，而当前已经使用此数据类型的列则不受影响。

33. 绑定规则示例 • 例6-7：绑定规则rulMaxPrice到用户自定义数据类型MaxPrice上。 exec sp_bindrule rulMaxPrice, MaxPrice • 例6-8：绑定规则rulMaxPrice到用户自定义数据类型MaxPrice上，带'futureonly'选项。 exec sp_bindrule rulMaxPrice, MaxPrice, 'futureonly' • 注意：规则对已经输入表中的数据不起作用。

34. 2．用Sp_unbindrule 解除规则的绑定 • 语法如下： sp_unbindrule [@objname =] 'object_name'[,'futureonly'] • 例6-9：解除规则rulMaxPrice与用户自定义数据类型MaxPrice的绑定，带'futureonly'选项。 exec sp_unbindrule birthday, 'futureonly'

35. 在企业管理器中绑定/解除规则

36. 6.3.4 删除规则 • 语法如下： DROP RULE {rule_name} [,...n] • 注意：在删除一个规则前，必须先将与其绑定的对象解除绑定。 例6-10：删除多个规则 DROP RULE mytest1_rule,mytest2_rule

37. 6.4 默认值 • 默认值（Default，也称缺省值）是往用户输入记录时没有指定具体数据的列中自动插入的数据。 • 默认值对象与ALTER TABLE 或CREATE TABLE 命令操作表时用DEFAULT 选项指定的默认值功能相似，但默认值对象可以用于多个列或用户自定义数据类型，它的管理与应用同规则有许多相似之处。 • 表的一列或一个用户自定义数据类型也只能与一个默认值相绑定。

38. 6.4.1 创建默认值 1．用CREATE DEFAULT 命令创建默认值 CREATE DEFAULT 命令用于在当前数据库中创建默认值对象。 其语法如下： CREATE DEFAULT default_name AS constant_expression • 其中constant_expression 子句是默认值的定义。constant_expression 子句可以是数学表达式或函数，也可以包含表的列名或其它数据库对象。 例6-11：创建生日默认值birthday_defa。 CREATE DEFAULT birthday_defa AS '1978-1-1'

39. 6.4.3默认值的绑定与松绑 • 1．用存储过程Sp_bindefault 绑定默认值 ，语法如下： sp_bindefault [@defname =] 'default', [@objname =] 'object_name' [, 'futureonly'] • 2．用存储过程Sp_unbindefault 解除默认值的绑定，其语法如下： Sp_unbindefault [@objname =] 'object_name' [,'futureonly']

40. 6.4.4 删除默认值 • 可以在企业管理器中选择默认值，单击右键，从快捷菜单中选择“删除” 选项删除默认值。 • 也可以使用DROP DEFAULT 命令删除当前数据库中的一个或多个默认值。 • 其语法如下： • DROP DEFAULT {default_name} [,...n] • 注意：在删除一个默认值前必须先将与其绑定的对象解除绑定。

41. 6.5 小结 • 要使数据库中的数据符合应用语义，必须要保证数据的完整性，包括数据的实体完整性、引用完整性、域完整性和用户自定义完整性。 • 约束 • 规则 • 默认值