192.数据完整性管理

第11章  数据的完整性管理

•    11.1 关于数据完整性 

•    11.2 实体完整性的实现

•    11.3 参照完整性的实现

•    11.4 用户定义完整性的实现

11.1.1数据完整性的概念

Ø 数据完整性包含两方面的含义：数据的正确性和数据的相容性，它们共同保证数据在语义上的合理性和有效性。

Ø  学生信息表中的性别只能是“男”或者是“女”，而不能是其他数据，这就是数据的正确性；学生姓名对应的学生必须是已经存在的，而不是虚构的姓名，这就是数据的相容性。

Ø 数据的完整性和安全性是两个不同的概念，它们存在着本质的区别。

•         完整性是为了防止数据库中出现语义上不正确的数据，保证每一个数据都能得到有效的解释。例如，如果学生的成绩超过100分（成绩采用百分制），那么这个分数是没有意义的，这种数据不具备完整性。

•         安全性则是为了防止数据库遭到恶意破坏和非法操作，从而引起不正确的数据更新或数据丢失。

Ø  如果一个用户未经授权而能够私自进入数据库，更改学生的成绩（即使更改的结果仍然在100分之内），那么这种数据是不安全的。

Ø 数据完整性的保证一般是由DBMS提供的相应机制来实现。这些机制包括完整性约束条件、完整性检查方法以及违约处理等。

11.1.2 数据完整性的分类

Ø 数据完整性大致可分为四种类型：实体完整性、参照完整性、域完整性、用户定义完整性等。

1. 实体完整性

ü 实体完整性又称行完整性，是指任何一个实体都存在区别于其他实体的特征，而且这些特征值都不能为空（NULL）。实体的这些特征是由关系中的某个或某些字段来刻画的，即实体完整性要求任意两行在这些字段上的取值不能完全相等且字段值不能为空。也就是说，如果某两行在这些字段上的取值分别相等或者为空，则相应的关系不满足实体完整性约束。

ü 保证实体完整性，或者说保证关系中不存在“相同”的两行主要是通过主键（Primary Key）、唯一码（Unique Key）、唯一索引（Unique Index）、CKECK约束和标识字段（Identity Column）等机制来实现。

2. 参照完整性

Ø 参照完整性又称引用完整性，它是指主关系表（被参照表，常称主表）中的数据与从关系表（参照表，常称从表）中数据的一致性，具体见2.1.3节的介绍。

Ø 参照完整性主要是通过定义表间的主键（主码）和外键（外码）关联来实现。另外，存储过程、触发器等也通常用来实现数据的参照完整性。

3. 域完整性

Ø 域即是字段（列）。域完整性即字段的完整性，它是字段值在语义上的合理性和有效性。例如，学生成绩不能超过100分，姓名字段值不能为空（NULL）等都是域完整性的例子。

Ø 域完整性主要是通过检查（Check）、规则（Rule）、数据类型（Data Type）、外键（Foreign Key）、默认值（Default）、触发器（Trigger）等机制来保证。

4. 用户定义完整性

Ø 实体完整性和参照完整性是关系模型最基本的要求。除此外，在面向具体的应用时，用户还可以根据实际需要定义一些特殊的约束条件。这种针对具体应用的、由用户定义的特殊约束条件就是用户定义完整性。

Ø 用户定义完整性通常是由规则、触发器、表约束等机制来实现的。

Ø 实际上，域完整性中有部分也是由用户来定义的，如将成绩字段的取值范围定义在0到100之间等，但不是全部由用户定义，如有的是由数据类型等自动约定的。所以，用户定义完整性和域完整性是相交关系，但不是隶属关系。

Ø 现在的DBMS产品一般都提供定义和检查这些数据完整性的机制。因此，在应用开发时用户应在DBMS中定义数据的完整性，然后由DBMS自动检查并给出相应提示信息，而不应由应用程序来保证数据的完整性。

第11章  数据的完整性管理

•    11.1 关于数据完整性 

•    11.2 实体完整性的实现

•    11.3 参照完整性的实现

•    11.4 用户定义完整性的实现

11.2.1 实体完整性的定义

Ø 实体完整性在CREATE TABLE或ALTER TABLE语句中可以通过主键约束、唯一约束或Identity字段来实施。其中，主键约束是最常用的实体完整性实施方法。当主键由一个字段构成，主键约束既可以定义为列级约束，也可以定义为表级约束；如果主键由多个字段构成，则主键约束必须定义为表级约束。

1. 主键约束

【例11.1】将表student中的字段s_no定义为主键，从而使该表满足实体完整性。

CREATE TABLE student(

     s_no char(8)     PRIMARY KEY,       -- 定义主键

     s_name char(8),

     s_sex char(2) ,

     s_birthday smalldatetime,

     s_speciality varchar(50),

     s_avgrade numeric(3,1),

     s_dept varchar(50)

);

Ø  上述定义语句中，使用关键字PRIMARY KEY将字段s_no定义为主键，属于列级约束。当涉及由两个或两个以上字段构成主键时，必须定义为表级约束。

     【例子】下列定义的表SC中，主键是由字段s_no和字段c_name构成，属于表级约束。

CREATE TABLE SC(

    s_no      char(8),

    c_name      varchar(20),

    c_grade numeric(3,1),

   PRIMARY KEY(s_no, c_name)  -- 将(s_no, c_name)设为主键

);

2. 唯一约束

Ø 在SQL Server中，唯一约束可以通过创建唯一索引来实现的，也可以在待设置字段后面说明关键字Unique的方法来完成。

  【例11.2】  先创建表student，然后为字段s_no定义唯一索引，从而同样可以保证表中不会出现重复的两行，因此也满足实体完整性。

CREATE TABLE student(      -- 定义表student

    s_no char(8),

    s_name char(8),

    s_sex char(2) ,

    s_birthday smalldatetime,

    s_speciality varchar(50),

    s_avgrade numeric(3,1),

    s_dept varchar(50)

);

 CREATE UNIQUE INDEX unique_index ON student(s_no);  -- 定义唯一索引

Ø  从实体完整性的角度看，上述这段代码等价于下列语句：

CREATE TABLE student(  

    s_no char(8)           Unique,

    s_name char(8),

    s_sex char(2),

    s_birthday smalldatetime,

    s_speciality varchar(50),

    s_avgrade numeric(3,1),

    s_dept varchar(50)

);

3. Identity字段

Ø 在创建表时可以使用关键字Identity来定义一种特殊的字段，称为Identity字段。该字段的值一般不需用户去操作。当用户对表进行插入或删除时，它将按照定义时设定的初值和增量值自动调整。实际上其作用相当于主键的作用，它可以保证任何记录都不可能在该字段上取值相等。

Ø Identity字段除了用于实现实体完整性以外，没有其他的实际意义。

Ø IDENTITY的语法如下：

IDENTITY [ ( seed , increment ) ]

       其中，参数seed用于设定装载到表中的第一个行所使用的值，参数increment表示增量值。如果这两个参数都不指定，则取默认值(1,1)。

【例11.3】创建带Identity字段的表student，使表中第一行的Identity字段值为0，增量值为10。

CREATE TABLE student(

     id_num int IDENTITY(0,10), -- 定义Identity字段

     s_no char(8),

     s_name char(8),

     s_sex char(2) ,

    s_birthday smalldatetime,

    s_speciality varchar(50),

    s_avgrade numeric(3,1),

    s_dept varchar(50)

);

Ø  不需对Identity字段插入数据值，该字段值是按照既定的设置自动增加的。

    【例子】插入下面3条记录后，表student中的数据如图11.1所示。

INSERT student VALUES('20170201','刘洋','女','1997-02-03','计算机应用技术',98.5,'计算机系');

INSERT student VALUES('20170202','王晓珂','女','1997-09-20','计算机软件与理论',88.1,'计算机系');

INSERT student VALUES('20170203','王伟志','男','1996-12-12','智能科学与技术',89.8,'智能技术系');

11.2.2 实体完整性的检查

Ø 在定义主键和唯一约束以后，每当用户向表中插入数据或在表中更新数据时，只要涉及到约束作用的字段则必将检查插入或更新后的数据是否满足约束条件。对于主键约束，所检查的内容包括：

•       主键值是否唯一：如果唯一则操作成功，否则拒绝插入或更新数据（保证唯一性）。

•       主键值是否为空（NULL）：主键中只要有一个字段的值为空，则拒绝输入数据或修改数据。

Ø 对于唯一性约束，只检查上述内容的第一项，即检查是否唯一即可。对于由Identity字段定义的约束，它能够自动保证该字段值的唯一性和非空性，从而实现实体的完整性。

第11章  数据的完整性管理

•    11.1 关于数据完整性 

•    11.2 实体完整性的实现

•    11.3 参照完整性的实现

•    11.4 用户定义完整性的实现

11.3.1 参照完整性的定义

Ø 参照完整性是通过定义外键与主键之间或外键与唯一约束字段之间的对应关系来实现的，由这种关系形成的约束称为外键约束。

Ø 在SQL语言中，外键约束通常是由嵌套在CREATE TABLE语句或ALTER TABLE语句中的短语FOREIGN KEY…REFERENCES…来定义。它涉及到两个表：一个是主表（被参照表），由关键字REFERENCES指定；另一个是从表（参照表），是使用短语FOREIGN KEY…REFERENCES…的表。

Ø 需要注意的是，在创建主表和从表的时候，须先定义主表，然后定义从表。顺序不能反了。

【例11.4】创建表SC对表student的外键约束。

Ø 首先创建表student，并将字段s_no定义为主键，然后创建表SC并定义表SC对表student的外键约束SC_FR。实现代码如下：

CREATE TABLE student(                                                        -- 【主表，要先创建】

     s_no char(8)          PRIMARY KEY, -- 定义主键

     s_name char(8),

     s_sex char(2),

     s_birthday smalldatetime,

     s_speciality varchar(50),

     s_avgrade numeric(3,1),

     s_dept varchar(50) 

);

CREATE TABLE SC(                                     -- 【从表，要后创建】

     s_no        char(8),                                    -- 外键

    c_name     varchar(20),

    c_grade     numeric(3,1),

    PRIMARY KEY(s_no, c_name),                                                        -- 将(s_no, c_name)设为主键

    FOREIGN KEY (s_no) REFERENCES student(s_no)                   -- 定义s_no为SC的外键

);

Ø  在表student和表SC中，利用短语FOREIGN KEY…REFERENCES将表student中的字段s_no和表SC中的字段s_no关联起来，建立起这两个表之间的主外键关联，形成一种参照完整性约束。其中，s_no为表student的主键，为表SC的外键。

Ø  在上述参照完整性约束的定义中，主表student中与从表SC关联的字段一般要定义为主键，如果不定义为主键，至少也要满足唯一约束，否则将出错。

  【例子】  下面的定义也是合法的：

CREATE TABLE student(        -- 【主表】

     s_no char(8),

     s_name char(8),

     s_sex char(2),

     s_birthday smalldatetime,

    s_speciality varchar(50),

    s_avgrade numeric(3,1),

    s_dept varchar(50) 

);

CREATE UNIQUE INDEX unique_index ON student(s_no);     -- 定义唯一索引

CREATE TABLE SC(                  -- 【从表】

    s_no   char(8),

    c_name     varchar(20),

    c_grade     numeric(3,1),

   PRIMARY KEY(s_no, c_name),  -- 将(s_no, c_name)设为主键

   FOREIGN KEY (s_no) REFERENCES student(s_no)  -- 定义外键

);

如果没有定义唯一索引unique_index，则上述代码将产生运行错误。

11.3.2 参照完整性的检查

Ø 参照完整性在两个表之间定义了一种对应关系，这种关系一般是基于一个或多个字段来定义的，这些字段通常称为关联字段。

Ø 这种关系要求：对于从表中的每一条记录，在主表中必须包含在关联字段上取值相等的记录；但对于主表中的每一条记录，并不要求在从表中存在与之关联的记录。简单地说，任何时候主表必须“包含”从表中的记录。

Ø 表11.1到表11.2之间的连线表示了表SC到表student的对应关系。其中，表student作为主表，表SC作为从表，关联字段是“学号”。该字段是表student的主键，是表SC的外键。对于从表SC中的每一条记录，在主表student中都有一条记录在关联字段“学号”上取值相等。

【例子】表SC中学号为“20060201”的三条记录对应着表student中的第一条记录等。这样，通过建立表SC的外键与表student中的主键之间的联系就实现了这两个表之间的参照完整性。

Ø  参照完整性约束一旦建立，则任何破坏这种约束的DML操作（包括插入、更新和删除操作）都被拒绝执行，从而起到保护数据完整性之目的。为此，SQL Server对数据库操作是否保持参照完整性提供了检查机制和相应的违约处理。

Ø  对于参照完整性来说，数据库操作应该遵循以下几条原则：

ü 从表不能引用主表中不存在的键值。这是理解外键约束概念的关键，由此不难理解以下几点。

ü 当向从表中插入记录后，必须保证主表中已经存在与此记录相关联的记录。

ü 当修改主表或从表中的数据时，不允许出现存在从表中的记录在主表中没有相关联的记录的情况。

ü 如果要从主表中删除记录，则必须先删除从表中与此相关联的记录（如果存在的话），然后才能删除主表中的记录；对于表的删除操作，必须先删除从表，然后才能删除主表。

ü 一旦某种数据库操作违反了上述的某一条准则，则SQL Server采取默认的处理措施——拒绝执行。

第11章  数据的完整性管理

•    11.1 关于数据完整性 

•    11.2 实体完整性的实现

•    11.3 参照完整性的实现

•    11.4 用户定义完整性的实现

11.4.1 域完整性的实现

        域完整性是通过对指定的字段定义相应的约束来实现的。这种约束属于列级约束，常用的约束主要包括以非空（NOT NULL）约束、唯一（Unique）约束、检查（Check）约束和默认值（Default）约束等。

1. 非空约束

Ø 当对指定的字段创建非空约束后，该字段的输入值不允许为空（NULL），否则操作被拒绝。创建的方法是在字段后加上关键字NOT NULL而形成的。如果不显式说明NOT NULL，则表示没有对其创建非空约束，允许其取空值。

【例11.5】创建数据表student，使其包含的字段中字段s_no和s_name不能取空值，其他字段允许取空值。

       该表的定义语句如下：

CREATE TABLE student(

     s_no          char(8)      NOT NULL,     -- 创建非空约束

    s_name       char(8)      NOT NULL,   -- 创建非空约束

    s_sex           char(2),

    s_birthday                       smalldatetime,

    s_speciality                             varchar(50),

    s_avgrade        numeric(3,1),

    s_dept         varchar(50)

);

      其中，由于对字段s_birthday、s_speciality、s_avgrade和s_dep没有显式说明关键字NOT NULL，所以这些字段的值允许为空。

Ø  非空约束在SSMS中创建的方法是，在表结构设计窗口中使待设置字段后面的复选框处于非选中状态，也就是说，使复选卡不被打勾。

  

【例子】  图11.2所示创建的表与例11.5创建的表是等效的。

2. 唯一约束

Ø 在11.2.1节中作为实体完整性的实现方法介绍了唯一约束，实际上唯一约束也是可以看作域完整性的实现方法。其创建方法可参见该节相关内容，此不赘言。

3. 检查约束

Ø 检查约束的应用比较灵活，使用频率高，范围广，是一种非常有用的约束。

Ø 定义检查约束的方法很多，常用的方法包括使用SQL语句定义、在SSMS中定义、使用规则定义等。

（1）使用SQL语句

        在SQL语言中使用如下的子句来定义检查约束：

CHECK ( logical_expression )

        其中，logical_expression为返回TRUE或FALSE的逻辑表达式，它通常由一个字段或多个字段名构成的表达式，只有结果满足该表达式的操作才能被接受，否则拒绝执行。

       该子句一般嵌入CREATE TABLE或ALTER TABLE语句中。

【例11.6】创建数据表student，要求：性别（s_sex）字段取值为“男”或“女”，出生日期（s_birthday）只能为1980年1月1日到2010年1月1日之间的日期值，平均成绩（s_avgrade）在0到100之间。

       依据题意，分别构造三个CHECK子句，然后将之放在相应字段的后面，创建表student的SQL语句如下：

CREATE TABLE student(

   s_no  char(8)          PRIMARY KEY,

   s_name char(8),

   s_sex     char(2)          CHECK(s_sex = '男' OR s_sex = '女’),

   s_birthday smalldatetime CHECK(s_birthday>='1980-1-1' AND                                   s_birthday<='2010-1-1’),

  s_speciality   varchar(50),

  s_avgrade     numeric(3,1) CHECK(s_avgrade >= 0 AND s_avgrade <= 100),

  s_dept     varchar(50)

);

（2）使用SSMS

Ø 使用SSMS定义检查约束时，其操作过程基本上是表结构的设计过程。不同的是，对每一个需要定义检查约束的字段，右击它（右击该字段所在的行）并在弹出的菜单中选择“CHECK约束…”命令，这时将弹出“CHECK约束”对话框。在此对话框中单击【添加】按钮，然后在右边列表框中的“表达式”一栏处设置逻辑表达式logical_expression。设置完毕后单击【关闭】按钮即可。

Ø 图11.3列举了对性别字段定义检查约束时在“CHECK约束”对话框中所作的设置。

（3）使用规则

Ø 使用规则定义检查约束的基本思路是，先创建数据表以及创建满足要求的规则，然后将规则绑定到相应的字段上。

Ø 对于例11.6，可以用下列的语句来创建符合要求的数据表：

       -- 创建数据表

CREATE TABLE student(

      s_no            char(8),     

      s_name       char(8),

      s_sex           char(2),

      s_birthday smalldatetime,

      s_speciality       varchar(50),

      s_avgrade   numeric(3,1),

      s_dept         varchar(50)

);

            -- 创建规则

CREATE RULE sex_range AS @s_sex = '男' OR @s_sex = '女'

CREATE RULE birthday_range AS @s_birthday>='1970-1-1' AND @s_birthday<='2000-1-1'

CREATE RULE avgrade_range AS @s_avgrade >= 0 AND @s_avgrade <= 100

            -- 绑定规则

sp_bindrule 'sex_range', 'student.s_sex';

sp_bindrule 'birthday_range', 'student.s_birthday';

sp_bindrule 'avgrade_range', 'student.s_avgrade’;

        注意，对于规则的创建和绑定语句，在执行时必须置于批处理语句中的第一条。由于上面有多条创建语句和绑定语句“连”在一起，因此只能逐条执行，而不能一次性执行所有的语句。

4. 默认值约束

Ø 默认值约束也是常用的一种约束。当对一个字段定义了默认值约束以后，如果在插入记录时该字段没有输入值，则自动被填上定义的默认值。

Ø 与检查约束类似，默认值约束也有三种定义方法。

（1）使用SQL语句

•       在SQL语句中，默认值约束是用关键字DEFAULT来定义。

  【例11.7】创建数据表student，使得字段s_speciality和字段s_dept初始值分别为“计算机软件与理论”和“计算机科学系”。

      该表可用如下的SQL语句来创建：

CREATE TABLE student(

     s_no            char(8),

     s_name char(8),

     s_sex           char(2),

     s_birthday smalldatetime,

     s_speciality      varchar(50)   DEFAULT  '计算机软件与理论’,

     s_avgrade   numeric(3,1),

     s_dept   varchar(50)         DEFAULT  '计算机科学系'

);

（3）使用规则

Ø 与检查约束的定义一样，在使用规则定义默认值约束时，先创建表和规则，然后将规则绑定到相应的字段上。

Ø 对于例11.7，当使用规则定义默认值时可用下列语句来实现：

-- 创建数据表

CREATE TABLE student(

   s_no                   char(8),     

   s_name             char(8),

   s_sex                 char(2),

   s_birthday              smalldatetime,

   s_speciality             varchar(50),

   s_avgrade               numeric(3,1),

   s_dept             varchar(50)

);

11.4.2 表级约束完整性的实现

Ø 表级约束是基于一个或多个字段的约束。显然，列级约束也可以定义为表级约束，但如果涉及到两个或两个以上字段的约束则必须定义为表级约束（而不能定义为列级约束）。

  【例11.8】  创建数据表SC(s_no, c_name, c_grade)，其中将(s_no, c_name)定义为主键，平均成绩（s_avgrade）在0到100之间，课程（c_name）字段只能取值为“英语”、“数据库原理”或“算法设计与分析”。

     依据题意，可构造如下的建表语句：

CREATE TABLE SC(

   s_no char(8),

   c_name   varchar(20),

   c_grade   numeric(3,1),

   PRIMARY KEY(s_no, c_name),     -- 将(s_no, c_name)设为主键

   CHECK(c_grade >= 0 AND c_grade <= 100 AND c_name IN('英语','数据库原理','算法设计与分析'))

);

Ø  表级约束CHECK(c_grade >= 0 AND c_grade <= 100 AND c_name IN('英语','数据库原理','算法设计与分析'))也可以转化为列级约束：

CHECK(c_grade >= 0 AND c_grade <= 100)

CHECK(c_name IN('英语','数据库原理','算法设计与分析'))

Ø  上述语句可以转化为下列语句：

CREATE TABLE SC(

  s_no           char(8),

  c_name      varchar(20)           CHECK(c_name IN('英语','数据库原理','算法设计与分析')),

  c_grade numeric(3,1)        CHECK(c_grade >= 0 AND c_grade <= 100),

  PRIMARY KEY(s_no, c_name)

);

       但由于主键约束PRIMARY KEY(s_no, c_name)涉及到两个字段，所以不能转化为列级约束来定义。