实验五 数据库完整性技术

【实验目的】

1、掌握完整性的概念；

2、熟悉SQL SERVER 的完整性技术。

3、了解SQL SERVER 的违反完整性处理措施。

【实验性质】

综合性实验

【实验导读】

1、完整性概述

数据库完整性指数据的（逻辑而非物理）正确性和相容性。为了防止数据库中存在不合语义的数据，防止错误数据的输入和输出。数据库完整性技术包括完整性约束条件与完整性检查两部分。完整性约束条件指为维护数据库的完整性，DBMS提供加在数据库数据之上的语义约束条件，作为数据库模式的一部分存入数据库。完整性检查意味检查数据库是否满足完整性约束条件的机制。

完整性约束条件作用的对象可以是关系、元组、列三种。其中列约束主要是列的类型、取值范围、精度、排序等的约束条件。元组的约束是元组中各个字段间的联系的约束。关系的约束是若干元组间、关系集合上以及关系之间的联系的约束。完整性约束条件涉及这三类对象，其状态可以是静态的，也可以是动态的。

完整性约束条件一般分为实体完整性、参考完整性 自定义完整性。定义实体完整性约束条件要考虑修改关系中主码的问题；定义参考完整性约束条件要考虑外码能否接受空值问题、在被参照关系中删除元组的问题（级联删除或受限删除）、在参照关系中插入元组时的问题。

RDBMS一般提供定义完整性约束条件：作为表定义的一部分在创建表时创建（采用CREATE TABLE）；或者添加到尚没有完整性约束条件的表中（ALTER TABLE）。

2、MS SQL Server完整性控制技术。

详细内容参阅 “联机丛书”中“创建和管理数据库”－>“表” －>“设计表” －>“使用约束、默认值和空值” 、“创建和管理数据库”－>“表” －>“创建和修改表”相关主题以及“联机从书”的“Transact-SQL参考”相关语句。

在MS SQL Server中将完整性分为：

（1）实体完整性。实体完整性将行定义为特定表的唯一实体。实体完整性强制表的标识符列或主码的完整性（通过索引、UNIQUE 约束、PRIMARY KEY 约束或 IDENTITY 属性）。

（2）域完整性。域完整性是指给定列的输入有效性。强制域有效性的方法有：限制类型（通过数据类型）、格式（通过 CHECK 约束和规则）或可能值的范围（通过 FOREIGN KEY 约束、CHECK 约束、DEFAULT 定义、NOT NULL 定义和规则）。

（3）引用完整性。在输入或删除记录时，引用完整性保持表之间已定义的关系。在 MS SQL Server中，引用完整性基于外码与主码之间或外码与唯一码之间的关系（通过 FOREIGN KEY 和 CHECK 约束）。引用完整性确保键值在所有表中一致。这样的一致性要求不能引用不存在的值，如果键值更改了，那么在整个数据库中，对该键值的所有引用要进行一致的更改。

强制引用完整性时，MS SQL Server 禁止用户进行下列操作：

Ø 当主表中没有关联的记录时，将记录添加到相关表中。

Ø 更改主表中的值并导致相关表中的记录孤立。

Ø 从主表中删除记录，但仍存在与该记录匹配的相关记录。

（4）用户定义完整性。用户定义完整性使您得以定义不属于其它任何完整性分类的特定业务规则。所有的完整性类型都支持用户定义完整性（CREATE TABLE 中的所有列级和表级约束、存储过程和触发器）。

设计表时需要识别列的有效值并决定如何强制实现列中数据的完整性。MS SQL Server 提供多种强制列中数据完整性的机制：

Ø PRIMARY KEY 约束

Ø FOREIGN KEY 约束

Ø UNIQUE 约束

Ø CHECK 约束

Ø DEFAULT 定义

Ø 为空性

3、PRIMARY KEY 约束

表中经常有一个列或列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主码，通过它可强制表的实体完整性。当创建或更改表时可通过定义 PRIMARY KEY 约束来创建主码。

一个表只能有一个 PRIMARY KEY 约束，而且 PRIMARY KEY 约束中的列不能接受空值。由于 PRIMARY KEY 约束确保唯一数据，所以经常用来定义标识列。

当为表指定 PRIMARY KEY 约束时，MS SQL Server通过为主码列创建唯一索引强制数据的唯一性。当在查询中使用主码时，该索引还可用来对数据进行快速访问。

如果 PRIMARY KEY 约束定义在不止一列上，则一列中的值可以重复，但 PRIMARY KEY 约束定义中的所有列的组合的值必须唯一。

4、FOREIGN KEY 约束

外码是用于建立和加强两个表数据之间的链接的一列或多列。通过将保存表中主码值的一列或多列添加到另一个表中，可创建两个表之间的链接。这个列就成为第二个表的外码。当创建或更改表时可通过定义 FOREIGN KEY 约束来创建外码。FOREIGN KEY 约束并不仅仅只可以与另一表的 PRIMARY KEY 约束相链接，它还可以定义为引用另一表的 UNIQUE 约束。尽管 FOREIGN KEY 约束的主要目的是控制存储在外码表中的数据，但它还可以控制对主码表中数据的修改。

当用户试图删除或更新外码所指向的码时，级联引用完整性约束可以定义 MS SQL Server 所采取的操作。

5、UNIQUE 约束

可使用 UNIQUE 约束确保在非主码列中不输入重复值。尽管 UNIQUE 约束和 PRIMARY KEY约束都强制唯一性，但在强制下面的唯一性时应使用 UNIQUE 约束而不是 PRIMARY KEY 约束：

Ø 非主码的一列或列组合。一个表可以定义多个 UNIQUE 约束，而只能定义一个 PRIMARY KEY 约束。

Ø 允许空值的列。允许空值的列上可以定义 UNIQUE 约束，而不能定义 PRIMARY KEY 约束。

6、CHECK 约束

CHECK 约束通过限制输入到列中的值来强制域的完整性。这与 FOREIGN KEY 约束控制列中数值相似。区别在于它们如何判断哪些值有效：FOREIGN KEY 约束从另一个表中获得有效数值列表，CHECK 约束从逻辑表达式判断而非基于其它列的数据。

对单独一列可使用多个 CHECK 约束。按约束创建的顺序对其取值。通过在表一级上创建 CHECK 约束，可以将该约束应用到多列上。

7、DEFAULT 约束

元组中的每一列均必须有值，即使它是 NULL。可能会有这种情况，当向表中装载新行时可能不知道某一列的值，或该值尚不存在。如果该列允许空值，就可以将该行赋予空值。由于有时不希望有可为空的列，因此如果合适，更好的解决办法可能是为该列定义 DEFAULT 定义。

当将某行装载到为行定义了 DEFAULT 的表中时，若没有指定列值，则是隐性要求 MS SQL Server将默认值装载到该列中

8、允许空值

列为空性决定该列在表中是否允许空值。空值或 NULL 并不等于零 (0)、空白或零长度的字符串（如“”），NULL 意味着没有输入。NULL 的存在通常表明值未知或未定义。例如，pubs 数据库 titles 表中 price 列的空值并不表示该书没有价格，而是指其价格未知或尚未设定。总之，由于空值在查询和更新时会使事情变得更复杂，而且有其它列选项，如 PRIMARY KEY 约束等不能使用允许空值的列，所以应避免允许空值。

如果插入某行但没有为允许空值的列包括值，则 MS SQL Server提供 NULL 值（除非存在 DEFAULT 定义或 DEFAULT 对象）。

指定一列不允许空值而确保行中一列永远包含数据可以保持数据的完整性。如果不允许空值，用户在向表中写数据时必须在列中输入一个值，否则该行不被接收入数据库。

【实验内容】

use qixin
drop table 职工
drop table 部门

create table 部门
(
 部门号     char(4),
 名称     varchar(20) not null,
 经理名     varchar(8),
 地址     varchar(50),
 电话号     varchar(20),
 constraint PK_部门号 primary key(部门号),
 constraint U_名称 unique(名称)
)

create table 职工
(
 职工号     char(4) ,
 姓名     varchar(8) not null ,
 年龄     int ,
 职务     varchar(10) ,
 工资     money,
 部门号     char(4),
 constraint PK_职工号 primary key(职工号),
 constraint FK_部门号 foreign key(部门号) references 部门(部门号) on delete cascade,
 constraint CK_年龄 check(年龄<=60)
)

insert into 部门 values('0001','财务科','张三','湖北汽车工业学院','8238787')
/*(1)*/
--如果再次insert into 部门 values('0001','财务科','张三','湖北汽车工业学院','8238787')，
--则会违反了 PRIMARY KEY 约束 'PK__部门__571DF1D5'。不能在对象 '部门' 中插入重复键。
/*(2)*/
--如果执行insert into 部门 values('0002','财务科','张三','湖北汽车工业学院','8238787')
--则会违反了 UNIQUE KEY 约束 'UQ__部门__5812160E'。不能在对象 '部门' 中插入重复键。
insert into 部门 values('0002','教务科','李四','湖北汽车工业学院','8238787')
insert into 部门 values('0003','人事科','王二','湖北汽车工业学院','8238787')

insert into 职工 values('0001','张伟','30','科长',2000,'0001')
insert into 职工 values('0002','李红','25','副科长',1500,'0001')
insert into 职工 values('0003','王强','33','科长',2000,'0002')
insert into 职工 values('0004','赵东','34','副科长',1500,'0002')
insert into 职工 values('0005','陈三','29','科长',2000,'0003')
insert into 职工 values('0006','孙波','28','副科长',1500,'0003')
--如果执行insert into 职工 values('0007','陈红','70','副科长',1500,'0003')
--则会INSERT 语句与 COLUMN CHECK 约束 'CK__职工__年龄__656C112C' 冲突。
--该冲突发生于数据库 'qixin'，表 '职工', column '年龄'。

select * from 部门
select * from 职工

--当指定on delete cascade时为级联删除，删除部门表记录时，职工表中相关的记录也会同时删除
delete from 部门 where 部门号='0001'
delete from 部门 where 部门号='0002'
delete from 部门 where 部门号='0003'

--如果不指定on delete cascade时默认为受限删除,删除部门表记录时
--则会DELETE 语句与 COLUMN REFERENCE 约束 'FK__职工__部门号__6D0D32F4' 冲突。
--该冲突发生于数据库 'qixin'，表 '职工', column '部门号'。