数据完整性机制的研究与实现

王红，陈功平

（六安职业技术学院信息工程系，安徽六安237158）

数据完整性机制的研究与实现

王红，陈功平

（六安职业技术学院信息工程系，安徽六安237158）

为了保证数据库中数据的准确性、一致性，数据库设计者应合理设计数据表的数据完整性。SQLServer数据库管理系统作为微软的核心产品，可以通过主键、标识列、惟一键、检查约束、外键约束等手段实现实体完整性、域完整性和参照完整性，参照完整性是保证数据一致性的重要机制，SQLServer中的触发器也可以实现参照完整性，以保证数据表间的插入规则、更新规则和删除规则的约定。

数据完整性；参照完整性；SQLServer数据库管理系统；触发器

数据完整性可以有效地保证数据库中数据的一致性和正确性［1］。数据完整性设计是数据库设计中的重点，其设计的优劣会直接影响数据库的后期使用，数据完整性按照所影响的对象不同，可分为实体完整性、域完整性和参照完整性［2］。

（1）实体完整性：用于保证关系表中的行不重复，防止相同记录的存在，取自万物不相同之意。

（2）域完整性：用于保证关系表中列的取值在一定范围（领域）内，限定取值的有界性。

（3）参照完整性：用于保证数据表之间数据的一致性，参照完整性是数据设计中非常重要的一个环节。

1 数据完整性实施策略

数据完整性有很强的数学理论基础，各数据库生产厂商按照完整性的要求，使用不同的策略来实现完整性理论。微软公司作为软件行业的龙头，在数据库管理系统设计方面有独特的优势，其开发的ACESS、VFP、SQLServer等关系数据库管理系统应用广泛，其中SQLServer是实际应用最广泛、管理能力最强的数据库管理系统，获得了各行业的认可，在数据库界有着不可动摇的地位，SQLServer数据库管理系统在实现数据完整性设计方面的方法和手段较多。

1.1 实体完整性实施策略

实体完整性是为了防止行出现重复，设计人员为了实现实体完整性，通常会虚拟出一个属性，比如户籍表虚拟出“身份证号”，学生表虚拟出“学号”，职工表虚拟出“职工编号”等。SQLServer中实现实体完整性的方法有主键约束、惟一键约束和标识字段。

（1）主键约束。设计数据表时应为每张表都设置主键约束，SQLServer中每个关系表的主键只能有一个，主键可以包含多个字段，但主键的取值不能重复也不可为空。指定关系表中的字段为主键可以使用图形方式选择，也可使用PRIMARY KEY关键字表示。

（2）惟一键约束。关系表可以设置多个惟一键，设置惟一键字段的取值不能重复，可取空值，但空值只能有一个。SQLServer中惟一键用UNIQUE表示，通常惟一键作为主键的补充，比如学生表指定学号为主键可保证其实体完整性，若再要保证“身份证号”列也不重复，就只能使用惟一键来实现。

（3）标识字段。SQLServer中的标识字段能够实现自动编号功能，因此可以保证记录行的惟一性［3］。每个表的标识字段只能有一个，标识字段的数据类型必须为整型。标识字段列的值无需用户输入，计算机根据自动编号算法自动生成，通常用标识字段和主键共同使用来实现实体完整性及参照完整性。

SQLServer中定义标识字段的关键字为IDENTITY（起始编号，步长），比如IDENTITY（1，1）表示一个“1、2、3…”的序列，IDENTITY（-5，-2）表示一个“-5、-7、-9…”的序列。

1.2 域完整性实施策略

域完整性的实现策略主要有：为列选择合适的数据类型和宽度，定义默认值，SQLServer中还可以用检查约束进一步限定列的取值域。

定义默认值可以在创建数据表时为字段加入DEFAULT关键字，也可以在数据表创建完成后通过ALTER TABLE关键字为数据表列添加默认值约束，列有默认值后，当用户未输入内容时自动用默认值填充。

检查约束是实现域完整性的主要手段。比如要存储年龄信息，就应将数据类型定义为整型而非字符型，姓名信息应定义为字符型而非数值型，若要应限制年龄的值为非负数，就需要再次定义检查约束。检查约束的关键字为CHECK，如CHECK（年龄＞=0 and年龄＜=200），CHECK（性别=‘男’or性别=‘女’）。

1.3 参照完整性实施策略

参照完整性是数据库设计中最重要的环节，是保证数据一致性的重要手段，实现的主要手段为创建外键约束（FOREIGN KEY），在SQLServer数据库管理系统中还可以通过触发器实现。

（1）使用外键约束实现参照完整性。外键约束实现的是数据表间的关联，表间的数据关联有一对一（1∶1）、一对多（1∶n）和多对多（m∶n）三种类型，数据库可以直接创建1∶1和1∶n关联，而m∶n关联无法直接实现，可以将m∶n联系拆分成多个1∶1或1∶n联系来间接实现。

在认知上，两张表之间能够创建关联的条件是两张表要有相同意义的字段，在SQLServer中只要两张表具有数据类型相同、宽度相同并创建一定的约束后的列就可创建关联，实现外键约束。创建外键约束时要选择主键表和外键表，1∶1关联，主键表和外键表是相对的，可任选；1∶n关联，1端的表是主键表，n端的表是外键表。外键约束的实现条件如表1所示。

表1 SQL Server中实现外键约束的条件

数据表创建关联后可通过配置更新规则和删除规则来保证数据一致性，参照完整性规则及其可选配置值如表2所示。

使用外键约束创建数据关联实现参照完整性时，要求关联拓扑图中无回路，不允许出现外键级联［4］，而在实际应用时，数据表间的关联关系出现回路的概率高，可以借助触发器来完善。

表2 参照完整性规则

（2）使用触发器实现数据完整性。触发器是数据库对象中的一种，可以编写“当对数据库对象执行某项操作所引发的事件”代码，在SQLServer中，数据表中记录的INSERT、UPDATE、DELETE操作和数据库、服务器中的CREATE、ALTER、DROP操作都可以设置触发器，因此，根据触发器所基于的数据库对象的不同分为DML触发器和DDL触发器两类［5］。

DML触发器：数据操纵触发器，基于数据操纵语言（DataMaster Language，DML）中的INSERT、UPDATE、DELETE命令创建。

DDL触发器：数据定义触发器，基于数据定义语言（Data Define Language，DDL）中的CREATE、ALTER、DROP命令创建。

因此，DML触发器可以配合外键约束实现参照完整性，DML触发器中有Inserted和Deleted两张辅助表帮助用户设计触发器事件，这两张表只在DML触发器中可用，是一个只读的逻辑表而非实际表。

Inserted表，存放即将加入数据表的数据行，执行INSERT时，存放的是将要插入的数据行，执行UPDATE时，存放的是更新后的数据行。

Deleted表，存放即将被删除的数据行，执行UPDATE时，存放的是更新前的数据行，执行DELETE时，存放的是被删除的数据行。

因此，INSERT只用到Inserted表，DELETE只用到Deleted表，UPDATE将更新后的数据行存入Inserted表，更新前的数据行存入Deleted表。

2 应用

数据完整性是数据表得以长期正确存储的关键。本文以“留言版”系统中的数据表设计为例，介绍数据表、数据完整性设计的过程和方法。

2.1 数据表设计

“留言版”系统功能可以让登录用户选择不同的版块发布留言、回复留言，因此必须要有用户表、版块表、留言表、留言回复表。

（1）用户表（tb_users）：存储注册用户的信息，表结构如表3所示。

表3 tb_users数据表结构

（2）版块表（tb_board）：存储留言版块的信息，表结构如表4所示。

表4 tb_board数据表结构

（3）留言表（tb_message）：存储留言的信息，表结构如表5所示。

表5 tb_message数据表结构

（4）留言回复表（tb_reply）：存储回复每条留言的信息，表结构如表6所示。

表6 tb_reply数据表结构

上述数据表结构只有必须的数据信息，设计者可根据需要添加其他属性以保存信息，表结构设计时，字段名、数据类型和宽度的选择要慎重，因为对表结构的更新易导致数据丢失，从而影响页面开发。数据表设计成功后进入数据完整性设计环节。

2.2 数据完整性设计

2.2.1 实体完整性

由上述2.1节可以看出，设计数据表时已经为每张表配置主键来完成实体完整性，tb_users使用主键完成实体完整性，tb_board、tb_message和tb_reply使用标识字段和主键共同实现实体完整性，主键约束可以在创建表时配置，也可数据表创建成功后配置，创建tb_board表并设置主键、默认值的SQL命令如下：

CREATE TABLE tb_board（id INT IDENTITY（-32768，1）PRIMARY KEY,board_name VARCHAR (100)NOTNULL，description VARCHAR（1000），createtime DATETIME DEFAULTGETDATE（），username VARCHAR（16））

2.2.2 域完整性

设计数据表时为每个字段选定了合适的数据类型和宽度，这是保证域完整性的基础，比如tb_users表中的pwd字段的数据类型为VARCHAR、宽度为16，可保证pwd列的取值最多为16个字符，但无法保证所录入值的最小字符数，为了密码安全性，通常要求密码的最小长度为6位，要实现该域完整性，SQLServer中可通过添加检查约束来实现，SQL命令如下：

ALTER TABLE tb_usersADDCHECK（LEN（pwd）＞=6）

2.2.3 参照完整性

根据上述1.3节的描述，“留言版”系统各数据表间的参照完整性设置如表7所示。

表7 “留言版”系统外键约束简明信息

（1）使用外键约束实现参照完整性。当A表的主键字段在B表中出现，则B表中的字段可称为A表主键的外键，主键和外键之间可以创建外键约束实现，创建外键约束可以使用外键表的设计器创建“关系”实现，也可以通过SQL命令实现，或通过创建关系图实现。

1）使用SQL命令创建外键约束。可以在创建表时新建外键约束，也可以在数据表创建成功后通过ALTER TABLE命令来创建外键约束，SQLServer中外键约束只能在外键表中设置和查看。

ALTER TABLE tb_board ADD FOREIGN KEY(username)REFERENCES tb_users(username)ON UPDATECASCADEONDELETESETNULL

其中ONUPDATECASCADE表示级联更新，ONDELETESETNULL表示删除规则设置为NULL。

2）使用数据库关系图创建外键约束。在数据库的“数据库关系图”中新建关系图，添加数据库中的所有表，使用鼠标从主键拖动到外键创建并设置外键约束的更新规则和删除规则。

“留言版”系统的数据库关系图如图1所示。

（2）使用触发器实现参照完整性。

1）插入规则。创建外键约束后可以自动保证插入规则，插入规则是针对外键表的约束，保证外键表的关联字段值来自于主键表［6］，触发器实现参照完整性中的插入规则是基于表的INSERT和UPDATE操作，以tb_users和tb_board表为例，创建触发器实现插入规则的命令如下：

CREATE TRIGGER tr_tb_board_updateON tb_board FOR INSERT,UPDATE

AS

DECLARE@username VARCHAR（16）

SELECT@username=username FROM inserted

IF@username ISNOT NULL OR@username!=”）AND EXISTS(SELECT*FROM inserted WHERE inserted.username NOT IN（SELECTusername FROM tb_users））

ROLLBACK TRANSACTION

功能说明：当向表tb_board执行INSERT或UPDATE时，若username列的值不为空值或空串时，且在tb_users表中不存在，则回滚事务。

2）更新规则。更新规则是针对主键表的约束，当主键表的关联字段UPDATE后，外键表关联字段所采取的措施称为更新规则，以tb_users和tb_board表为例，创建触发器实现更新规则的命令如下：

CREATETRIGGER tr_tb_users_updateON tb_users FORUPDATE

AS

IFUPDATE（username）

UPDATE tb_board SET username=（SELECT username FROM inserted）WHERE username=（SELECT username FROM deleted）

功能说明：当更新tb_users表的username字段值时，将tb_board表对应的username字段的值一并更新。

若将上述命令AS后的语句更换为如下语句，则表示默认的更新规则“不执行任何操作”。

IF（EXISTS（SELECT*FROM insertedWHERE username IN（SELECTusername FROM tb_board）））

ROLLBACK TRANSACTION

3）删除规则。删除规则也是针对主键表的约束，当主键表执行DELETE时，外键表关联字段所采取的措施称为删除规则，以tb_users和tb_board表为例，创建触发器实现删除规则的命令如下：

CREATETRIGGER tr_tb_users_deleteON tb_users FORDELETE

AS

UPDATE tb_board SETusername=“WHERE username=（SELECTusername FROM deleted）

功能说明：当删除tb_users表的数据行时，将tb_board表对应的username值设置为空串。

3 结语

为了实现数据安全性、一致性，数据库管理系统的生产厂商也在努力升级［7］，作为数据库设计者，应该使用各种方法来实现数据完整性，保证数据又快又好地运行下去。数据完整性不仅要在数据库中进行配置，在数据录入和修改页面也应该通过脚本代码等各种途径实现。

本文以SQLServer数据库管理系统为模板，研究和实现了数据完整性，尤其是使用触发器来定义插入规则、更新规则和删除规则，这也给了设计人员一个重新设计完整性规则的入口，在数据库设计方面有着广泛的应用前景和意义。

［1］刘永楠,邹兆年,李建中,等.数据完整性的评估方法［J］.计算机研究与发展,2013,50（S1）∶230-238.

［2］于洋洋,虞慧群,范贵生.一种云存储数据完整性验证方法［J］.华东理工大学学报∶自然科学版,2013,39（2）∶211-216.

［3］曹夕,许力,陈兰香.云存储系统中数据完整性验证协议［J］.计算机应用,2012,32（1）∶8-12.

［4］康效龙,张玉清,田玉敏.触发器在实现数据库安全方面的新应用［J］.计算机工程,2005,31（19）∶157-159.

［5］CHANDRASP.Avoid Referential Integrity ErrorsWhen Deleting Records from Databases［J］.SQLServerMagazine,2009,11（6）∶15-16.

［6］刘树,徐硕,黄其泉,等.一种基于触发器的数据同步系统的研究及关键技术实现［J］.计算机应用与软件,2012,29（12）∶189-191.

［7］NAMHEUNS,YUNHO L,DOHYUNK,etal.A study ofuser data integrity duringacquisition of Android devices［J］.Digital Investigation,2013,10（2）∶3-11.

【责任编辑：王桂珍foshanwgzh@163.com】

Research and implementation of data integrity

WANGHong，CHENGong-ping

（Departmentof Information Engineering,Luan Vocation Technology College,Luan 237158,China）

∶In order toensure theaccuracy and consistencyof the data in the database,the database designermust set the data integrity for data tables.SQL Server databasemanagement system as the core productofMicrosoft, through theprimary key,identity column,unique key,check constraints,foreign key constraintsand othermeans to achieve the entity integrity,domain integrity and referential integrity.Referential integrity is an important means to ensure the consistency of data,SQL Server triggers can also implement referential integrity,to ensure the data insertion rules,theupdate ruleand the deletion rule.

data integrity;referential integrity;SQLServer databasemanagementsystem;trigger

TP309.2

A

1008-0171（2015）01-0081-07

2014-06-16

安徽省高等学校教学质量与教学改革工程项目（2012jyxm667，2013jyxm667）

王红（1983-），女，安徽霍邱人，六安职业技术学院讲师。