数据库课件 (12)

1、第第1313章章 数据完整性控制数据完整性控制 数据库中的数据是从外部输入的。但数据库中的数据是从外部输入的。但多种因素会影响和干扰数据输入过程，进多种因素会影响和干扰数据输入过程，进而可能导致而可能导致无效或错误数据无效或错误数据进入数据库。进入数据库。为此，数据库管理系统通常提供相应的机为此，数据库管理系统通常提供相应的机制和措施，以防止制和措施，以防止不符合语义规定的数据不符合语义规定的数据进入数据库，进而实现进入数据库，进而实现数据完整性控制数据完整性控制。在在SQL Server 2005SQL Server 2005中，可以使用中，可以使用约束约束、默默认值认值、规则规则、存储过程

2、存储过程、触发器触发器等机制或等机制或数据库对象实现数据完整性控制。数据库对象实现数据完整性控制。 13.1 13.1 约束约束 SQL Server 2005 SQL Server 2005提供以下提供以下约束（约束（ConstraintConstraint）机制）机制对字对字段列中的数据完整性实施强制性控制，以段列中的数据完整性实施强制性控制，以限制字段列中数据的限制字段列中数据的取值范围取值范围。（1 1）PRIMARY KEYPRIMARY KEY约束约束（主键约束）（主键约束） 在关系数据库中，主键是若干字段列的组合，每个表都应在关系数据库中，主键是若干字段列的组合，每个表都应该有且

3、只有一个主键。因此，该有且只有一个主键。因此，一个表只能有一个主键约束一个表只能有一个主键约束。 在一个表中，任意两条记录在主键上的值都不相同。因此，在一个表中，任意两条记录在主键上的值都不相同。因此，主键及主键值具有唯一性主键及主键值具有唯一性。 此外，此外，主键值不能是空值主键值不能是空值。如果某条记录在一个字段列上。如果某条记录在一个字段列上的值是空值，则该字段列不能作为主键或主键的一部分。的值是空值，则该字段列不能作为主键或主键的一部分。 在一个表中，如果在一个表中，如果主键由单个字段列组成主键由单个字段列组成，则该主键约束，则该主键约束又称为又称为列级约束列级约束。如果。如果主键由多

4、个字段列组成主键由多个字段列组成，则该主键约束，则该主键约束又称为又称为表级约束表级约束。 主键约束作用在一个表的主键上。主键约束作用在一个表的主键上。 13.1 13.1 约束约束（2 2）NOT NULLNOT NULL约束约束（非空值约束）（非空值约束） 为表中某字段列设置非空值约束后，在该字段列中为表中某字段列设置非空值约束后，在该字段列中不能存不能存储空值储空值（NULLNULL）。因此，在表中添加记录或更新记录时，必须）。因此，在表中添加记录或更新记录时，必须确保在该字段列上赋予一个确定的值。确保在该字段列上赋予一个确定的值。 主键约束主键约束属于属于非空值约束非空值约束。（3 3

5、）UNIQUEUNIQUE约束约束（唯一性约束）（唯一性约束） 为表中某字段列设置唯一性约束后，除空值（为表中某字段列设置唯一性约束后，除空值（NULLNULL）外，）外，该字段列中该字段列中不能出现重复值不能出现重复值。在设置唯一性约束的字段列中。在设置唯一性约束的字段列中可可以存储空值以存储空值。 主键约束属于唯一性约束主键约束属于唯一性约束，但，但主键约束不允许出现空值主键约束不允许出现空值。所以，所以，主键约束的强度大于唯一性约束主键约束的强度大于唯一性约束。 此外，在表中某字段列上设置此外，在表中某字段列上设置唯一性约束唯一性约束时，系统会自动时，系统会自动将该字段列作为索引键创建将

6、该字段列作为索引键创建非聚集索引非聚集索引，并且是，并且是唯一索引唯一索引。 13.1 13.1 约束约束（4 4）CHECKCHECK约束约束（检查约束）（检查约束） 检查约束以检查约束以关系表达式关系表达式或或逻辑表达式逻辑表达式的形式为表的形式为表中某字段列设置一个取值范围，以限制外界在该字段中某字段列设置一个取值范围，以限制外界在该字段列的输入值。当在表中进行列的输入值。当在表中进行插入和更新记录插入和更新记录时，时，SQL SQL ServerServer将对进入具有检查约束的字段列中的值进行检将对进入具有检查约束的字段列中的值进行检测，只有满足检查约束中关系表达式或逻辑表达式的测，

7、只有满足检查约束中关系表达式或逻辑表达式的值才能写入该字段列，否则值才能写入该字段列，否则SQL ServerSQL Server会报错。会报错。 13.1 13.1 约束约束（5 5）FOREIGN KEYFOREIGN KEY约束（外键约束）约束（外键约束） 外键约束与外键约束与父表父表和和子表子表之间的一对多表间关系有着密切之间的一对多表间关系有着密切联系。联系。 由于父表主键与子表外键之间存在着对应关系，因此，由于父表主键与子表外键之间存在着对应关系，因此，在子表或父表中进行插入、更新或删除记录等数据操作时，在子表或父表中进行插入、更新或删除记录等数据操作时，必须确保父表主键值与子表外

8、键值对应关系的一致性。必须确保父表主键值与子表外键值对应关系的一致性。在子表中插入或更新记录时在子表中插入或更新记录时，记录在外键上的取值必须在，记录在外键上的取值必须在父表的主键中已经存在。父表的主键中已经存在。 在子表中设置外键在子表中设置外键，即设置外键约束，可以确保子表外，即设置外键约束，可以确保子表外键的取值在父表主键中已经存在。键的取值在父表主键中已经存在。 13.1 13.1 约束约束在父表中更新主键值在父表中更新主键值时，可能会破坏父表与子表之间时，可能会破坏父表与子表之间的记录对应关系。的记录对应关系。 为了在父表中更新主键值时保持父表与子表原有的为了在父表中更新主键值时保持

9、父表与子表原有的记录对应关系，记录对应关系，SQL ServerSQL Server在外键约束中提供了在外键约束中提供了级联更级联更新（新（Cascade UpdateCascade Update）操作）操作的机制的机制在父表中更新主在父表中更新主键值时，系统将自动更新子表中对应记录的外键值。键值时，系统将自动更新子表中对应记录的外键值。在父表中删除记录在父表中删除记录时，同样可能会破坏父表与子表之时，同样可能会破坏父表与子表之间的记录对应关系。间的记录对应关系。 为了在父表中删除记录时保持父表与子表原有的记为了在父表中删除记录时保持父表与子表原有的记录对应关系，录对应关系，SQL Serve

10、rSQL Server在外键约束中提供了在外键约束中提供了级联删除级联删除（Cascade DeleteCascade Delete）操作）操作的机制的机制在父表中删除记录在父表中删除记录时，与被删除记录的主键值对应的子表中的记录将被系时，与被删除记录的主键值对应的子表中的记录将被系统自动删除。统自动删除。13.2 13.2 默认值默认值默认值（默认值（Default）是是在表中插入记录时在表中插入记录时为为没有指定具体数据的字段列自动赋予的数据。没有指定具体数据的字段列自动赋予的数据。默认值可以是默认值可以是常量常量，也可以是，也可以是表达式表达式，还，还可以是可以是空值空值。通常，在通常，

11、在创建表创建表的时候为字段列的时候为字段列设置相应设置相应的约束和默认值的约束和默认值，也可以在，也可以在修改表结构修改表结构时设时设置约束和默认值。置约束和默认值。13.2 13.2 默认值默认值【例【例13-113-1】创建上述的】创建上述的“学生学生”、“课程课程”和和“选课选课”表，表，要求在要求在CREATE TABLECREATE TABLE命令中为字段列设置约束和默认值，并观命令中为字段列设置约束和默认值，并观察、验证约束和默认值在数据完整性控制中的作用。察、验证约束和默认值在数据完整性控制中的作用。（1 1）使用如下）使用如下CREATE TABLECREATE TABLE命令

12、命令创建创建“学生学生”表，同时为字段表，同时为字段列设置约束和默认值。列设置约束和默认值。CREATE TABLE CREATE TABLE 学生学生( ( 学号学号 CHAR(10) PRIMARY KEY,CHAR(10) PRIMARY KEY,-主键约束、非空值约束、唯一性约束主键约束、非空值约束、唯一性约束 姓名姓名 NCHAR(10) NOT NULL,NCHAR(10) NOT NULL,-非空值约束非空值约束 身份证号身份证号 CHAR(18) UNIQUE,CHAR(18) UNIQUE,-唯一性约束唯一性约束 性别性别 NCHAR(1) CHECK(NCHAR(1) CH

13、ECK(性别性别=男男OR OR 性别性别=女女) DEFAULT ) DEFAULT 男男,-,-检查约束和默检查约束和默认值认值 手机号手机号 CHAR(13),CHAR(13), 入学时间入学时间 SMALLDATETIME DEFAULT GETDATE() -SMALLDATETIME DEFAULT GETDATE() -默认值为调用内置函数的表达式默认值为调用内置函数的表达式) ) 13.2 13.2 默认值默认值13.2 13.2 默认值默认值（3 3）使用如下）使用如下ALTER TABLEALTER TABLE命令命令在在“学生学生”表中的表中的“手机号手机号”字段上设置检

14、查约束。字段上设置检查约束。ALTER TABLE ALTER TABLE 学生学生 WITH NOCHECK ADD CHECK (WITH NOCHECK ADD CHECK (手机号手机号 LIKE LIKE 10-90-90-90-90-90-90-90-90-90-90-10-90-90-90-90-90-90-90-90-90-90-90-9)90-9)（4 4）使用如下）使用如下CREATE TABLECREATE TABLE命令命令创建创建“课程课程”表，同时为字段表，同时为字段列设置约束。列设置约束。CREATE TABLE CREATE TABLE 课程课程( ( 课程号课

15、程号 char(4) PRIMARY KEY, -char(4) PRIMARY KEY, -主键约束、非空值约束、主键约束、非空值约束、唯一性约束唯一性约束 课程名称课程名称 nchar(10) NOT NULLnchar(10) NOT NULL -非空值约束非空值约束) )13.2 13.2 默认值默认值（6 6）使用如下）使用如下CREATE TABLECREATE TABLE命令命令创建创建“选课选课”表，同时为字段表，同时为字段列设置约束。列设置约束。CREATE TABLE CREATE TABLE 选课选课( ( 学号学号 char(10) char(10) FOREIGN K

16、EYFOREIGN KEY REFERENCES REFERENCES 学生学生( (学号学号) ON ) ON DELETEDELETE NO ACTIONNO ACTION, , -外键约束外键约束 课程号课程号 char(4) FOREIGN KEY REFERENCES char(4) FOREIGN KEY REFERENCES 课程课程( (课程号课程号) ON ) ON UPDATEUPDATE CASCADECASCADE, ,-外键约束外键约束 成绩成绩 intint CHECK ( CHECK (成绩成绩=0) AND (=0) AND (成绩成绩=100),=100),-

17、检查检查约束约束 PRIMARY KEY (PRIMARY KEY (学号学号, ,课程号课程号) ) -主键约束、非空值约束、主键约束、非空值约束、唯一性约束唯一性约束) )13.3 13.3 规则规则在在SQL ServerSQL Server数据库中，数据库中，规则（规则（RuleRule）是对是对字段列字段列或或用户定义数据类型用户定义数据类型的取值及其范围的规定和限制。的取值及其范围的规定和限制。规则规则与与检查约束检查约束在在功能功能上很上很相似相似，都能限制字段列，都能限制字段列的取值范围。但它们的的取值范围。但它们的用法不同用法不同，检查约束检查约束是在是在CREATE TAB

18、LECREATE TABLE或或ALTER TABLEALTER TABLE命令命令中定义和设置的，中定义和设置的，并嵌入和依赖于特定的表及其字段列。因此，删除表并嵌入和依赖于特定的表及其字段列。因此，删除表时检查约束也会随之被删除。而时检查约束也会随之被删除。而规则规则是用是用CREATE CREATE RULERULE命令创建的、命令创建的、独立于表之外的数据库对象独立于表之外的数据库对象。13.3.1 13.3.1 创建规则创建规则n创建规则，可以使用创建规则，可以使用CREATE RULECREATE RULE命令命令，其基本语法为：，其基本语法为： CREATE RULE rule_

19、name AS condition_expressionCREATE RULE rule_name AS condition_expression各选项及参数的含义和用法如下：各选项及参数的含义和用法如下：condition_expressioncondition_expression是定义规则的、包含一个变量的关系表达式是定义规则的、包含一个变量的关系表达式或逻辑表达式，且该变量必须以字符或逻辑表达式，且该变量必须以字符 开头。一般情况下，该变量的开头。一般情况下，该变量的名称与规则所关联的字段列或用户定义数据类型的名称相同。名称与规则所关联的字段列或用户定义数据类型的名称相同。CREATE

20、 RULE age_ruleCREATE RULE age_rule AS 18= AS 18=ageage AND AND age=70age=70CREATE RULE idno_rule AS idnoCREATE RULE idno_rule AS idno LIKE 0-90-90-90- LIKE 0-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-9%90-90-9%CREATE RULE postcode_ruleCREATE RULE postcode_rule

21、AS postcode LIKE 0-90-90- AS postcode LIKE 0-90-90-90-90-90-990-90-90-913.3.2 13.3.2 查看规则查看规则n查看规则及其代码，也可以使用系统存储查看规则及其代码，也可以使用系统存储过程过程sp_helptextsp_helptext，其基本语法为：，其基本语法为：sp_helptext rule_namesp_helptext rule_name13.3.3 13.3.3 绑定规则绑定规则u将规则绑定到表中的字段列或用户定义数据类型将规则绑定到表中的字段列或用户定义数据类型上，可以控制字段列的取值范围。绑定规则可以

22、上，可以控制字段列的取值范围。绑定规则可以使用系统存储过程使用系统存储过程sp_bindrulesp_bindrule，其基本语法为：，其基本语法为： sp_bindrule rule_name,object_namesp_bindrule rule_name,object_name 各选项及参数的含义和用法如下：各选项及参数的含义和用法如下：rule_namerule_name表示已经创建的规则的名称。表示已经创建的规则的名称。object_nameobject_name指定规则绑定的对象，可以是表中的指定规则绑定的对象，可以是表中的字段列或用户定义数据类型。如果是字段列或用户定义数据类型。

23、如果是表中的字段表中的字段列列，则，则object_nameobject_name需采用需采用table.columntable.column格式书写，格式书写，否则被绑定对象被当做用户定义数据类型。否则被绑定对象被当做用户定义数据类型。13.3.4 13.3.4 解除规则解除规则u将规则从绑定的字段列或用户定义数据类型上将规则从绑定的字段列或用户定义数据类型上解除，可以使用系统存储过程解除，可以使用系统存储过程sp_unbindrulesp_unbindrule，其基本语法为：其基本语法为： sp_unbindrule object_namesp_unbindrule object_name

24、 各选项及参数的含义和用法如下：各选项及参数的含义和用法如下：object_nameobject_name可以是字段列或用户定义数据类型可以是字段列或用户定义数据类型的名称。如果是的名称。如果是字段列字段列，则，则object_nameobject_name需采用需采用table.columntable.column格式书写，否则对象被当做用户定格式书写，否则对象被当做用户定义数据类型。义数据类型。13.3.5 13.3.5 删除规则删除规则l将解除绑定的规则删除，可以使用将解除绑定的规则删除，可以使用DROP DROP RULERULE命令，其基本语法为：命令，其基本语法为： DROP RU

25、LE rule_nameDROP RULE rule_name ,n ,n13.3.5 13.3.5 删除规则删除规则【例【例13-313-3】创建、绑定、解除和删除规则，并观察和验证规则的数据完整性控】创建、绑定、解除和删除规则，并观察和验证规则的数据完整性控制作用。程序代码如下：制作用。程序代码如下：-创建用户定义数据类型创建用户定义数据类型ageageEXEC sp_addtypeEXEC sp_addtype age, SMALLINT age, SMALLINTGOGO-向向“雇员雇员”表增加用户定义数据类型表增加用户定义数据类型ageage的的“年龄年龄”字段字段ALTER TAB

26、LE ALTER TABLE 雇员雇员 ADD ADD 年龄年龄 ageageGOGO-创建规则创建规则age_ruleage_rule和和idno_ruleidno_ruleCREATE RULE age_ruleCREATE RULE age_rule AS 18=age and age=70 AS 18=age and age=0) =0) AND (AND (成绩成绩=100)=100)，既可以控制成绩不为负数，又能够禁止成绩，既可以控制成绩不为负数，又能够禁止成绩超过超过100100分。分。 又如，在数据库又如，在数据库WestwindWestwind的的“订单订单”表中，订购日期、

27、发货表中，订购日期、发货日期和到货日期依次记录和反映了订单的日期和到货日期依次记录和反映了订单的3 3种处理状态。为此，可种处理状态。为此，可以使用如下以使用如下ALTER TABLEALTER TABLE命令设置命令设置CHECKCHECK约束：约束：ALTER TABLE ALTER TABLE 订单订单 WITH NOCHECK ADD CONSTRAINT CK_TimeWITH NOCHECK ADD CONSTRAINT CK_Time CHECK ( (CHECK ( (订购日期订购日期 发货日期发货日期) AND () AND (发货日期发货日期 到货日期到货日期) ) )13.4 13.4 数据完整性及其控制措施的分类数据完整性及其控制措施的分类