数据完整性

什么是数据完整性

信息的质量是指信息的准确性、完整性、一致性等属性.数据完整性是保证数据质量的一个重要方法.那么什么是数据完整性?

数据完整性是指数据库中数据在逻辑上的一致性、正确性、有效性和相容性

下面我们从各个方面距举例说明数据完整性的重要性:

• 数据的逻辑一致性: 如果在表中插入两个工号一样而姓名不一样的工人信息,则无法保证工号的唯一性从而违反数据的一致性.
• 数据的逻辑正确性: 如果银行的数据库在插入存款金额时比实际存入数额少,显然顾客是不同意的,因此数据正确性非常重要.
• 数据的有效性: 如果工人离职,但工人数据库未及时更新,那么该人可凭借过期的工资卡领工资,显然也是不合理的.
• 数据相容性: 在工人表中的工人的信息应该和工资表中工人信息一致,否则就可能会出现问题.

具体地说,数据完整性体现在以下三个方面:

1. 域完整性: 又称列完整性,指定一个数据集对某个列是否有效和确定是否允许空值.
2. 实体完整性: 又称行完整性,要求表中的所有行有一个唯一的标识符,这种标识符一般称为主键.
3. 引用完整性: 保证主键和外键之间的关系总是得到维护.如果被参考表中的一行被一个外键所参考,那么这一行的数据便不能直接删除,用户也不能直接修改主键值,当然引用完整性也是有条件的,可以通过设置级联(CASCADE)改变这个完整性.

什么是约束

上面说了数据完整性的重要性,那么如何维护数据的完整性?在数据库原理中说过,约束是维护数据完整性的强有力手段.约束可以确保有效的数据插入表中,并维护表和表之间的特定关系.SQL server所提供的约束和数据完整性的关系如下表:

完整性类型	约束类型	描述
域完整性	DEFAULT	使用INSERT插入数据时,若未指定某个列的取值,则将默认值插入
域完整性	CHECK	指定某个列中的可保存值的范围
实体完整性	主键(PRIMARY KEY)	每一行的唯一标识符,不允许空值,确保自动创建唯一聚集索引
实体完整性	UNIQUE	防止冗余值,允许空值,确保自动创建唯一非聚集索引
引用完整性	外键(FOREIGN KEY)	定义一列或几列,其值与本表或另一个表的主键值相匹配

---

下面说明如何定义约束,以及约束的作用范围:

CREATE TABLE: 从无到有创建约束,在创建表时就创建约束.

ALTER TABLE: 在已有的表上创建约束,或对已有的约束进行修改.

在定义约束时,应该考虑如下情况:

• 不必删除表,就可以直接创建/修改/删除约束的定义.
• 应在应用程序中增加错误检查机制,测试数据是否与约束冲突.
• 在表上增加约束时,SQL server系统将检查表中的数据是否与约束冲突.

创建约束时,必须为约束指定唯一的名称.如不指定,SQL server系统将会自动指定一个复杂的、系统自动生成的约束名称.一般的,对于一个数据库来说,建议使用如下格式唯一地命名约束名称:

约束类型简称_表名_列名_代号

另外: 约束可以作用于一列,此时称为列级约束.也可以作用于几列,此时称为表级约束.可以使用以下目录视图来查看约束:

目录视图名称	描述
sys.key_constraints	查看有关主键和UNIQUE约束的信息
sys.check_constraints	查看有关CHECK约束的信息
sys.default_constraints	查看有关DEFAULT约束的信息

使用目录视图的实例如下:

USE AdventureWorks2017;
GO
SELECT * FROM sys.key_constraints;
SELECT * FROM sys.default_constraints;
SELECT * FROM sys.check_constraints;
GO

运行结果如下:

管理约束

本小节详细研究DEFAULT、CHECK、主键、外键、UNIQUE约束的特点、创建方式和修改等内容.

DEFAULT约束

定义DEFAULT约束的语法在CREATE TABLE和ALTER TABLE中不尽相同,这里给出了两种简单的语法:

--在CREATE TABLE语句的列属性中;
[CONSTRAINT constraint_name] DEFAULT constant_expression;
--在ALTER TABLE语句的ADD子句中;
[CONSTRAINT constraint_name] DEFAULT constant_expression FOR column_name [WITH VALUES];

在以上的例子中,CONSTRAINT constraint_name是可省略的,但还是建议指定约束名称.因为是设定默认值,因此constant_expression指的是当前列类型的常量表达式,在该列没有插入数据时,该列默认使用这个常量表达式的值.

请看下面的例子:

USE Temp;
GO
CREATE TABLE Person (
	name VARCHAR(32) NULL,
	IDNumber VARCHAR(32) CONSTRAINT dfc_Person_IDNumber_001 DEFAULT '0x3f3f3f3f',
	phoneNumber VARCHAR(20) NULL
)
INSERT INTO Person VALUES ('Ann',DEFAULT,NULL);
INSERT INTO Person VALUES ('Aaron',DEFAULT,NULL);
GO

SELECT * FROM Person;
GO

以上例子在创建表时为IDNumber列创建了一个DEFAULT约束,并使用该约束插入两条数据,运行结果如下:

同样,再以这个表为例,我们对其phoneNumber列增加一个DEFAULT约束,使用ALTER TABLE语句如下:

USE Temp;
GO

ALTER TABLE Person
	ADD CONSTRAINT dfc_Person_phoneNumber_002 DEFAULT '13467985200'
	FOR phoneNumber WITH VALUES
GO

INSERT INTO Person VALUES (NULL,DEFAULT,DEFAULT);
SELECT * FROM Person;
GO

运行结果如下:

上例中使用了WITH VALUES参数,作用是将新定义的常量值添加到表中已有的数据中(貌似对NULL无效)可以看到我们插入的新数据使用了新声明的默认值.

---

使用DEFAULT约束应注意以下几点:

• 定义的常量必须和列的数据类型和精度一致.
• DEFAULT约束只能用于INSERT语句插入数据时有效.
• 每一个列至多只能有一个DEFAULT约束.
• DEFAULT约束不可放在IDENTITY属性列/数据类型为TIMESTAMP的列上,因为这两个列都是用系统自动提供的值,不需要默认值.
• DEFAULT允许使用一些系统函数的返回值充当常量表达式的值,如SYSTEM_USER、GETDATE、CURRENT_USER等.

CHECK约束

CHECK约束用来限制用户输入某一个列的数据,即用户只能插入CHECK约束指定范围内的数据.CHECK约束类似外键约束,二者都是限制插入数据的范围,但是CHECK通过逻辑表达式限制,外键约束通过其它表限制.CHECK约束只有一种定义形式:

[CONSTRAINT constraint_name] CHECK (logical_expression);	--在此无需指定列名,对某个列的限制写在条件里;

下面我们只演示一种使用方法,即向已存在的表中插入CHECK约束:

USE Temp;
GO

ALTER TABLE Person ADD gender VARCHAR(6);
GO

ALTER TABLE Person ADD CONSTRAINT CK_Person_gender_003 CHECK (gender = 'female' OR gender = 'male');
GO

INSERT INTO Person VALUES ('Aveline',DEFAULT,DEFAULT,'female');
INSERT INTO Person VALUES ('Fox',DEFAULT,DEFAULT,'male');
INSERT INTO Person VALUES ('Fox',DEFAULT,DEFAULT,'xx');
GO

SELECT * FROM Person;
GO

上例中,为Person表新增了CHECK约束,限定值为female(女)或者male(男),并故意插入一行不符合CHECK约束的数据,运行结果如下:

错误信息:

查询结果:

可见不满足CHECK约束的数据无法插入表中.

---

关于CHECK约束,应注意的点:

• 一个列上可以有多个CHECK约束,CHECK约束不能包含子查询语句.
• 执行INSERT、UPDATE语句时将会验证该列的CHECK约束,防止发生错误,执行DELETE语句时不检查CHECK约束.
• CHECK约束可参考本表中的其它列,假设有总薪水和税后薪水,那么CHECK约束可定义为税后薪水 $\le$ 总薪水.
• 和DEFAULT约束一样,CHECK约束不能放在IDENTITY属性列/数据类型为TIMESTAMP的列上.

主键约束

主键约束是最重要、应用最多的一种约束,它强制实体完整性,一个表中至多有一个主键,且不允许为空值.

主键一般定义在一个列上,但也可以定义在多个列上,此时要求这些列的组合值不可重复(单列可重复).这两种定义的语法如下:

[CONSTRAINT constraint_name] PRIMARY KEY;	--当前列即作为主键;
[CONSTRAINT constraint_name] PRIMARY KEY (column_list);	--在此指定构成主键的列集合;

如下面的例子,我们先清空表(方便修改数据类型),再将其中一列设为主键,并插入数据:

USE Temp;
GO

DELETE FROM Person;	--先删除表中所有数据;
ALTER TABLE Person ALTER COLUMN IDNumber VARCHAR (20) NOT NULL;	--将IDNumber列设为不允许空值;
GO

ALTER TABLE Person ADD CONSTRAINT PK_Person_IDNumber_004 PRIMARY KEY (IDNumber);
GO

INSERT INTO Person (name,IDNumber,phoneNumber,gender)
	VALUES ('Tony','0x3f3f3f3f',NULL,'male');
INSERT INTO Person (name,IDNumber,phoneNumber,gender)
	VALUES ('Peter','0xffffffff',NULL,'male');

SELECT * FROM Person;
GO

运行结果如下:

或者我们在新建的表上为某一个列设定主键约束:

CREATE TABLE Person (
	name VARCHAR(32) NULL,
	IDNumber VARCHAR(32) PRIMARY KEY,
	phoneNumber VARCHAR(20) NULL
)

由于很好理解,结果图就不放了,说明一下主键约束的注意点:

• 每个表最多只能有一个主键约束.
• 主键列的值(或其多列的组合)是唯一的,且不允许空值.
• 主键约束在指定列上创建了一个唯一索引,默认是聚集索引,也可以是非聚集的,可以指定主键对应的索引为非聚集索引.
• 可以在定义主键时添加级联操作.

UNIQUE约束

UNIQUE约束指定表中一个或多个列不能有相同的两行数据存在.当表中存在主键又想要限定实体数据完整性时,应使用UNIQUE约束,UNIQUE约束的定义语法和主键约束非常类似,如下:

[CONSTRAINT constraint_name] UNIQUE;	--当前列即作为UNIQUE列;
[CONSTRAINT constraint_name] UNIQUE (column_list);	--在此指定构成UNIQUE列的列集合;

以一个例子说明:

USE Temp;
GO

DELETE FROM Person;	--先删除表中所有数据;

ALTER TABLE Person ADD CONSTRAINT UK_Person_phoneNumber_005 UNIQUE (phoneNumber);
GO

INSERT INTO Person (name,IDNumber,phoneNumber,gender)
	VALUES ('Aveline','0xxxxxxxxx',NULL,'female');
INSERT INTO Person (name,IDNumber,phoneNumber,gender)
	VALUES ('Sue','x00000000','0797-6611736','female');
INSERT INTO Person (name,IDNumber,phoneNumber,gender)
	VALUES ('Sue','x00000001',NULL,'female');

SELECT * FROM Person;
GO

上面的例子使用了UNIQUE约束,并插入三条数据,结果如下:

根据上面的例子,我们知道: UNIQUE约束允许空值,但空值也不可以出现重复!除此之外,UNIQUE约束还应注意以下几点:

• 一个表可有多个UNIQUE约束.
• UNIQUE可出现在多列中,这些列的组合值必须唯一,但是UNIQUE所在列并不是主键列.
• UNIQUE默认创建唯一性非聚集索引,但是也可以指定创建的索引为聚集索引.

外键约束

外键约束强制引用完整性.外键约束定义一个或多个列,这些列可以引用当前或其它表的主键列/UNIQUE约束列,通过外键约束可以实现表和表之间的依赖关系.外键约束有以下几种定于语法:

--在CREATE TABLE中定义只有一个列的外键约束;
[CONSTRAINT constraint_name] FOREIGN KEY REFERENCES referenced_tb_name (referenced_column);
ON DELETE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}
ON UPDATE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}
--在CREATE TABLE中定义有多个列的外键约束;
[CONSTRAINT constraint_name] FOREIGN KEY (column_list) REFERENCES referenced_tb_name (referenced_column_list);
ON DELETE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}
ON UPDATE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}
--在ALTER TABLE中定义只有一个列的外键约束;
[CONSTRAINT constraint_name] FOREIGN KEY (column) REFERENCES referenced_tb_name (referenced_column);
ON DELETE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}
ON UPDATE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}
--在ALTER TABLE中定义有多个列的外键约束;
[CONSTRAINT constraint_name] FOREIGN KEY (column_list) REFERENCES referenced_tb_name (referenced_column_list);
ON DELETE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}
ON UPDATE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}

在CREATE TABLE中直接在列定义后面加FOREIGN KEY关键字即可,ALTER TABLE需要指定外键约束作用的列集合.REFERENCES关键字用于指定外键所引用的表名称和列名称,其它参数的解释如下:

• NO ACTION: 不执行任何操作,当删除/更新主键表数据时,如果外键表有相应数据,则删除/更新失败,此参数是默认值.
• CASCADE: 级联操作,当删除/更新主键表数据时,如果外键表有相应数据,则同步进行删除/更新.
• SET NULL: 置空操作,当删除/更新主键表数据时,如果外键表有相应数据,则将外键表数据置为空值.
• SET DEFAULT: 缺省操作,当删除/更新主键表数据时,如果外键表有相应数据,则将外键表中的数据都设为默认值.

使用外键约束的例子如下:

USE Temp;
GO

CREATE TABLE Student (
	studentName VARCHAR(20) NOT NULL,
	studentID VARCHAR(20) PRIMARY KEY
)

CREATE TABLE Teacher (
	teacherName VARCHAR(20) NOT NULL,
	teacherID VARCHAR(20) PRIMARY KEY
)

CREATE TABLE Course (
	courseName VARCHAR(20) NOT NULL,
	courseID VARCHAR(20) PRIMARY KEY,
	teacherID VARCHAR(20) FOREIGN KEY REFERENCES Teacher (teacherID) ON UPDATE CASCADE ON DELETE CASCADE,
)

CREATE TABLE StudentCourse (
	credit DECIMAL(10,2) NOT NULL,
	courseID VARCHAR(20) FOREIGN KEY REFERENCES Course (courseID) ON UPDATE CASCADE ON DELETE CASCADE,
	studentID VARCHAR(20) FOREIGN KEY REFERENCES Student (studentID) ON UPDATE CASCADE ON DELETE CASCADE
)
GO

于是我们就建立了如下的数据库关系图(表之间的关系):

根据数据库原理: 要先在主键表中插入数据,才能在外键表中插入数据,且插入外键列的数据要么和主键相等,要么为NULL.

于是我们就来插入以下几条数据,表示这些表的关系:

USE Temp;
GO

--插入教师和学生信息;
INSERT INTO Teacher VALUES ('LiMing','000');
INSERT INTO Teacher VALUES ('LiuXinYu','001');
INSERT INTO Teacher VALUES ('FengJian','002');
INSERT INTO Student VALUES ('LiShuang','000');
INSERT INTO Student VALUES ('LiangDuoQuan','001');
INSERT INTO Student VALUES ('YuanJiaZheng','002');

--向课程表中插入课程和任课教师信息;
INSERT INTO Course VALUES (N'操作系统','000','000');
INSERT INTO Course VALUES (N'SQL server应用','001','001');
INSERT INTO Course VALUES (N'Linux操作系统','002','002');

--向学生选课表中插入学生和对应课程信息;
INSERT INTO StudentCourse VALUES (4,'002','000');
INSERT INTO StudentCourse VALUES (4,'002','001');
INSERT INTO StudentCourse VALUES (4,'002','002');
INSERT INTO StudentCourse VALUES (4,'000','000');
INSERT INTO StudentCourse VALUES (4,'000','001');
INSERT INTO StudentCourse VALUES (4,'000','002');
INSERT INTO StudentCourse VALUES (4,'001','002');

根据学生、课程、教师的ID对应关系,想必我们一定非常清楚这些数据的关系了,那么要如何查询呢?很简单,使用连接技术即可(或者分组技术,或直接根据主键外键对应关系查询),如下示例:

USE Temp;
GO

SELECT C.courseID,C.courseName,S.studentName,T.teacherName
FROM Student S,Course C,StudentCourse SC, Teacher T
WHERE C.courseID = SC.courseID AND S.studentID = SC.studentID AND T.teacherID = C.teacherID;
GO

运行结果如下:

此时我们更新一位教师的ID,再在上述查询中增加一列,以查询教师ID,看看会是什么结果:

USE Temp;
GO

UPDATE Teacher SET teacherID = '007' WHERE teacherName = 'LiuXinYu';
GO

SELECT C.courseID,C.courseName,S.studentName,T.teacherID,T.teacherName
FROM Student S,Course C,StudentCourse SC, Teacher T
WHERE C.courseID = SC.courseID AND S.studentID = SC.studentID AND T.teacherID = C.teacherID;
GO

运行结果如下:

注意图中红圈部分,我们的查询条件是T.teacherID = C.teacherID,可以看到这个值变成了'007'(初值是'001'),此案例说明不仅仅是Teacher表的teacherID值发生了变化,Course表的teacherID也同步发生了变化,这是我们在定义外键时就设置好的效果.同样的如果我们删除一个被引用的主键,引用它的外键也会一起跟着被删除,这里就不再演示了.

---

使用外键约束时,需要考虑以下问题:

• 外键约束提供了单列和多列引用完整性.在FOREIGN KEY和REFERENCES中的列的数量和数据类型应保持一一对应.
• 不像主键约束,外键约束不会自动创建索引,但是用户若是经常使用连接查询,为了加快速度应该手动在外键上创建索引.
• 当用户修改外键约束所在表的数据时,该用户必须拥有外键约束所参考表的SELECT或者REFERENCES权限.
• 定义外键约束时,若引用的是同一个表的数据,则只能用REFERENCES语句而不能用FOREIGN语句.

删除约束

和创建约束一样,删除约束也没有特定的关键字/语句,我们只能在ALTER TABLE中执行删除操作,具体语法如下:

ALTER TABLE tb_name
	DROP CONSTRAINT constraint_name;	--当然要首先知道约束的名称,才能删除约束;
GO

这里就不再举例子,有兴趣可以自己尝试删除一些约束.

禁止约束

在特殊情况下,需要禁止在已有的数据上/禁止在加载数据时应用约束,下面说一说这两种禁止约束的方式.

注意: 禁止某一个表上的约束并不影响参考该表的其它表上的约束.

禁止在已有数据上应用约束

一般地,在已有数据的表上定义约束时,SQL server系统会自动检查表中数据是否满足约束条件.但是,在某些特殊情况下,用户可以禁止这种检查,禁用在已有数据上应用约束,实际上就是说这些约束对表中已有的数据不起作用.这种禁止只能在向表中添加约束时才能指定.禁止在已有数据上应用约束时,应遵循如下规则:

• 只能禁止CHECK约束或者外键约束应用在已有数据上,对其它约束不可行.
• 对于要禁止的约束,应该在ALTER TABLE的约束定义语句最前面加上WITH NOCHECK选项.
• 接上一条,使用WITH NOCHECK增加的约束对其之后插入/更新的数据都有约束作用.
• 解释上一条,约束之后插入的数据不解释,更新包括对约束之后的数据的更新,更包括对该约束存在前数据的更新,即所有数据.

---

我们新建一个禁止在已有数据上应用的约束,如下:

USE Temp;
GO

ALTER TABLE StudentCourse 
WITH NOCHECK
	ADD CONSTRAINT CK_StudentCourse_credit_001 CHECK (credit < 4);
GO

UPDATE StudentCourse SET credit = 4.5 WHERE courseID = '000';
GO

可以看到出现了错误:

可见约束添加后,即时不检查它添加前的数据,这些数据在之后更新也是会受到约束影响的(除非一直不更新,否则就会受约束).

禁止在加载数据时应用约束

对于CHECK约束或者外键约束,还可以禁止在加载数据时应用约束,这是一种更为特殊的情况,也就是说,在向表中添加/更新数据时,可以不判断数据是否满足约束条件.为了避免应用约束的开支,以下情况应该禁止对加载的数据应用约束:

• 已经确保插入/更新的数据与约束要求相一致.
• 希望加载与约束要求相悖的数据,但是加载之后通过更新数据符合约束要求,再重新允许约束检查.

使用NOCHECK语句禁止约束应用到加载的数据上,语法格式如下:

--禁止约束应用到加载的数据上;
NOCHECK CONSTRAINT constraint_name;
NOCHECK CONSTRAINT ALL;
--对之前的约束解禁,使其可应用到加载的数据上;
CHECK CONSTRAINT constraint_name;
CHECK CONSTRAINT ALL;

注意,指定了ALL关键字表示禁用表上的所有约束,否则就需要指定具体的约束名来禁用特定约束.下面是一个例子:

USE Temp;
GO
ALTER TABLE StudentCourse
NOCHECK CONSTRAINT CK_StudentCourse_credit_001;	--禁止约束;
GO

UPDATE StudentCourse SET credit = 4.5 WHERE courseID = '000';	--插入不满足约束的数据;
SELECT C.courseName,SC.credit
FROM Course C,StudentCourse SC
WHERE C.courseID = SC.courseID;
GO

UPDATE StudentCourse SET credit = 3.5 WHERE courseID = '000';	--更新数据为满足约束;
ALTER TABLE StudentCourse
CHECK CONSTRAINT CK_StudentCourse_credit_001;	--启用约束;
SELECT C.courseName,SC.credit
FROM Course C,StudentCourse SC
WHERE C.courseID = SC.courseID;
GO

运行结果如下:

注意看插入的三条数据,以及更新后的三条数据,怎么样?是不是怀疑到底是不是禁用约束起的作用?很简单,我们可以从运行完之后的消息来分析究竟是什么的影响:

那为什么禁用约束会只有3条数据受影响呢?StudentCourse表中不是一共7行数据么?想想我们这个CHECK约束是什么约束吧–它对先于它存在于表中的数据不检查约束!那为什么要检查操作系统(courseID = '000')这3行的呢?这是因为在之前我们对操作系统(courseID = '000')有过一次数据更新!只不过失败了,但这也让操作系统(courseID = '000')的数据行都落入了该CHECK约束的"魔爪",因此当禁止该约束时,对这3行的约束就失效了,所以仅有3行受影响,恢复约束时也是同理.

上一篇: 索引和查询优化
下一篇: 视图