论文部分内容阅读
摘 要:数据完整性是数据库安全中一个重要的部分,对保护数据正确性、一致性和有效性有重要作用,是在进行数据库设计时要考虑的一个重要问题,就SQL Server中数据完整性的类型和实现作了阐述,并结合一个应用实例进行了示范。
关键词:数据完整性;域完整性;实体完整性;引用完整性;用户定义完整性
中图分类号:TP311.13文献标识码:A文章编号:16723198(2009)22025902
数据完整性是关系数据库模型中数据安全的重要组成部分,能够避免破坏数据项之间的结构,维持数据的正确性、一致性和有效性,保持同一数据的不同副本协调一致,从而可以极大地提高数据的可用性。数据完整性的控制机制一般包括三个方面内容:数据语义约束条件的定义;约束条件的检查;违背约束条件时系统的异常处理。
1 数据完整性类型
(1)域完整性。对表字段取值进行约束,提供了一个给定域的有效入口,包括数据类型、取值范围、格式、精度等的规定,例如设定学号必须全是数字等,因此保证了一个数据库不可能包含任何无意义的或者不合理的值。
(2)实体完整性。以表记录为单位进行约束,规定一个表中的每一行必须是唯一的。开发者需要指定一个表中的一列或一组列作为它的主键,表中的每行必须含有一个唯一的主键。主键首先不能为空值(NULL),其次不能与表中已有行的主键值相同。
(3)引用完整性。定义一个关系数据库中不同的列和不同的表之间的关系,一列或一组列中的值引用相关的一列或一组列中的值,必须要与相关的一列或一组列中的值相匹配。从属的一列或一组列称作外键,被引用的列或一组列称作父键。父键必须是一个主键或唯一值键,若父键和外键属于同一个表,则称作自引用完整性。
(4)用户定义完整性。以上三种数据完整性约束能够实现数据库中大部分数据完整性,但总有一些约束条件是开发者不能应用以上数据完整性约束来实行的。如入学时间不能晚于毕业时间;又如当某一学生记录从学生简历中被删除时,需要同时在历史表中登记被删除的学生记录。实现诸如此类的数据完整性保护,需要开发者自己通过创建存储过程和触发器等来实现。我们把这类完整性约束称为用户定义完整性。
2 数据完整性的实现
(1)声明型数据完整性:作为数据库说明的一部分在数据库定义中实现,通过 CREATE TABLE和ALTER TABLE语句中使用表约束或列约束的方式来限制表中的值。这种方法实现数据完整性简单,而且不容易出错,可以直接将数据完整性的要求定义在表和列上。这种方法可以实现数据的实体完整性与参照完整性,部分实现域完整性与用户定义完整性。
(2)过程型数据完整性:主要指通过缺省、规则和触发器,或视图和存储过程实现。这一类完整性实现相对来说较复杂,但是具有更大的灵活性。主要用于实现域完整性与用户完整性。
2.1 域完整性的实现
(1)缺省值(default constraint)约束:它与列值相关联,而不是与数据类型相关联,可以为任何数据类型(除timestamp和具有identity属性的列外)定义缺省值约束。
(2)检查(check)约束:它限制输入到一列或多列中的可能值,从而保证SQL Server数据库中数据的域完整性,在check约束中可以包含搜索条件,但是不能包含子查询,一个表中可以包含多个check约束,对于列也可以定义多个check约束。
(3)缺省(default):缺省是一种数据库对象,它与缺省值约束的作用类似,为INSERT 语句中未指定数据的列设置缺省值,缺省对象只适用受INSERT语句影响的行。定义缺省的格式为:
CREATE DEFAULT [owner] default_name AS constant_expression
其中default_name是新建的缺省的名字,constant_expression是一个常量表达式,在这个表达式中不包含有任何列名或其它数据库对象名,但可使用不涉及数据库的SQL Server内部函数。
缺省创建后,使用系统存储过程sp_binddefault与表中的列捆绑,也可以与用户定义的数据类型捆绑,其语法如下:
Sp_binddefault default_name, object_name
其中object_name是要指定与该缺省相绑定的列名或用户定义的类型名,如果指定的是表中的列,其格式为table.column,否则被认为是用户定义的数据类型名,如果名字中含有空格或标点符号或是保留字,则必须用引号将它放入引号中。
(4)规则(rule):规则是数据库对象之一,指定向某列(或用与该规则绑定的用户定义数据类型的所有列)插入或更新数据时,限制输入新值的取值范围。一个规则可以是:值的清单或值的集合、值的范围、必须满足的单值条件、用LIKE子句定义的编辑掩码等。当数据库中数据值被更新或插入时,就要检查新值是否遵循规则,如果不符合规则就拒绝执行更新或插入操作。规则在实现功能上等同于CHECK约束。
(5)触发器(trigger)是特定类型的存储过程,作为数据修改(增、删、改)语句的一部分而在相应操作执行时自动执行,是维护数据库完整性最强大的工具,能在任何情况下去维护数据的完整性,应用程序不能绕过触发器(除非是批量复制和TRUNCATE TABLE)不予执行,这也正是触发器和一般存储过程的一大区别。
2.2 实体完整性的实现
(1)列的Identity属性说明为Identity的列由系统自动为其赋值,并保证其值在该表中的惟一性,每一个表中只允许有一个列定义为Identity的。定义时Identity后可跟seed和increment参数,前一参数表示该列的起始值,后一参数表示该列值每次的增量。
(2)主键(primary key)约束指定表中一列或数列作为数据表的主键,下面是对customers表中的customer id建立主键值约束。
CREATE TABLE customers (Customer_id INT Primary Key, Customer_name NVARCHAR (20), Customer_address NVARCHAR (20))
在这个customers表中customer_id的取值必须不相同且不能取空值。
(3)惟一性(unique)约束和primary key约束相类似,定义数据列值的惟一性约束,有一行可取NULL值,并且惟一性约束在一个表中可以使用多次,而主键值只可以在一个表中使用一次。在缺省的情况下,惟一约束产生非聚簇索引,而主键约束产生聚簇索引。
2.3 参照完整性的实现
通过在同一个数据库的两个表中进行主键约束和外键约束来实现,参照的列和被参照的列的名字必须相同。在被参照表中,主键值被其它表所参照时,该行不能被删除,也不允许改变。
在参照表中,不允许参照不存在的主键值。下面例子在定义了orders表的customer_id列參照customers中的customer_id列。
CREATE TABLE orders(Order_id INT Primary Key,Customer_id INT RETERENCES customers(customer_id),Order_date DATETIME)
2.4 用户定义完整性的实现
(1)存储过程(procedure)是一个存储在数据库内的可执行的数据库对象,它可以接受参数、返回状态值和参数值,并且可以嵌套调用。本来存储过程是提供安全性考虑的,但是通过安全性提供了另一级的完整性-如果表的修改都是由存储过程来完成,便可以确保这些数据表的完整性和一致性,而不用担心手工操作可能产生的不一致的问题。
(2)视图(view):是查看存储在表中数据的一种逻辑方法,它是通过一个被存储的SELECT语句来实现的。如果需要对多个表设置一些完整性约束,可以用带WITH CHECK OPTION从句的视图定义来实现。实现功能和触发器类似。
另外,对于用户定义完整性也可以通过default、rule、trigger等方式来实现,操作方式与前所述相同。
3 应用实例
(1)CREATE TABLE 员工。
( 编号 CHAR(4) PRIMARY KEY,
姓名CHAR(8) NOT NULL,
性别CHAR(2) CHECK ( 性别 IN (‘男’,‘女’)) DEFAULT ‘男’,
生日 DATETIME,
职务 CHAR(8),
基本工资 MONEY CHECK (基本工资>=600),
部门号 SMALLINT REFERENCES 部门(部门号) )
本例为在建立数据表的同时就实现了几种完整性约束:
①本表定义时对各字段设置数据类型使各字段只能接收储存相应数据类型的数据;对性别和基本工资设置CHECK约束,性别只能取“男”或“女”基本工资只能接收不小于600的MONEY型数据;另外对性别还设置了默认值,则性别默认为“男”,从而部分实现了域完整性约束;
②对编号设置PRIMARY KEY约束使编号成为主键,其取值不能重复也不能为空值;对姓名设置NOT NULL约束,这样姓名不能为空值,部分实现了其实体完整性约束;
③通过“部门号 SMALLINT REFERENCES 部门(部门号)”将员工表和部门表连接起来,员工表中的外键“部门号”参照部门表中的主键“部门号”,从而部分实现了参照完整性。
(2)CREATE TABLE 商品。
( 商品号CHAR(6) PRIMARY KEY,
商品名CHAR(24) NOT NULL,
型号CHAR(16),
单位CHAR(2) DEFAULT ‘台’,
单价MONEY NOT NULL,
存量INT,
厂家CHAR(24) )
本例中通過“单位 CHAR(2) DEFAULT ‘台’”对单位设置了缺省值。
(3)视图的例子。
CREATE VIEW NYG AS SELECT 编号,姓名,性别,基本工资,部门号FROM 员工WHERE 性别=‘男’ WITH CHECK OPTION
通过以上定义创建一个男员工视图后,向此视图中添加数据时只能添加性别为“男”的数据,拒绝添加性别项为“女”的数据。
(4)存储过程的创建与调用。
IF EXISTS(SELECT name FROM sysobjects WHERE name=‘员工查找’ AND type=‘p’)
DROP PROCEDURE员工查找
GO
CREATE PROCEDURE员工查找
@ygname char(10)
AS
SELECT 姓名,基本工资,部门.部门号 FROM 员工,部门 WHERE 员工.部门号=部门.部门号 AND 员工.姓名=@ygname
GO
定义了这个存储过程以后,若程序中某处需要查找员工,即可调用此过程,用法为:
EXEC员工查找‘王明’ 或 EXEC员工查找 @ygname=‘王明’。
参考文献
[1]肖慎勇.SQL Server数据库管理与开发[M].北京:清华大学出版社,2006.
[2] (美) Peter Rob, Carlos Coronel著,陈立军译.数据库系统设计、实现与管理[M].北京:电子工业出版社,2004.
[3]潘林森,姚渝春.SQL Server数据库系统中的数据完整性控制方法[J].重庆师范大学学报(自然科学版),2004,(6).
关键词:数据完整性;域完整性;实体完整性;引用完整性;用户定义完整性
中图分类号:TP311.13文献标识码:A文章编号:16723198(2009)22025902
数据完整性是关系数据库模型中数据安全的重要组成部分,能够避免破坏数据项之间的结构,维持数据的正确性、一致性和有效性,保持同一数据的不同副本协调一致,从而可以极大地提高数据的可用性。数据完整性的控制机制一般包括三个方面内容:数据语义约束条件的定义;约束条件的检查;违背约束条件时系统的异常处理。
1 数据完整性类型
(1)域完整性。对表字段取值进行约束,提供了一个给定域的有效入口,包括数据类型、取值范围、格式、精度等的规定,例如设定学号必须全是数字等,因此保证了一个数据库不可能包含任何无意义的或者不合理的值。
(2)实体完整性。以表记录为单位进行约束,规定一个表中的每一行必须是唯一的。开发者需要指定一个表中的一列或一组列作为它的主键,表中的每行必须含有一个唯一的主键。主键首先不能为空值(NULL),其次不能与表中已有行的主键值相同。
(3)引用完整性。定义一个关系数据库中不同的列和不同的表之间的关系,一列或一组列中的值引用相关的一列或一组列中的值,必须要与相关的一列或一组列中的值相匹配。从属的一列或一组列称作外键,被引用的列或一组列称作父键。父键必须是一个主键或唯一值键,若父键和外键属于同一个表,则称作自引用完整性。
(4)用户定义完整性。以上三种数据完整性约束能够实现数据库中大部分数据完整性,但总有一些约束条件是开发者不能应用以上数据完整性约束来实行的。如入学时间不能晚于毕业时间;又如当某一学生记录从学生简历中被删除时,需要同时在历史表中登记被删除的学生记录。实现诸如此类的数据完整性保护,需要开发者自己通过创建存储过程和触发器等来实现。我们把这类完整性约束称为用户定义完整性。
2 数据完整性的实现
(1)声明型数据完整性:作为数据库说明的一部分在数据库定义中实现,通过 CREATE TABLE和ALTER TABLE语句中使用表约束或列约束的方式来限制表中的值。这种方法实现数据完整性简单,而且不容易出错,可以直接将数据完整性的要求定义在表和列上。这种方法可以实现数据的实体完整性与参照完整性,部分实现域完整性与用户定义完整性。
(2)过程型数据完整性:主要指通过缺省、规则和触发器,或视图和存储过程实现。这一类完整性实现相对来说较复杂,但是具有更大的灵活性。主要用于实现域完整性与用户完整性。
2.1 域完整性的实现
(1)缺省值(default constraint)约束:它与列值相关联,而不是与数据类型相关联,可以为任何数据类型(除timestamp和具有identity属性的列外)定义缺省值约束。
(2)检查(check)约束:它限制输入到一列或多列中的可能值,从而保证SQL Server数据库中数据的域完整性,在check约束中可以包含搜索条件,但是不能包含子查询,一个表中可以包含多个check约束,对于列也可以定义多个check约束。
(3)缺省(default):缺省是一种数据库对象,它与缺省值约束的作用类似,为INSERT 语句中未指定数据的列设置缺省值,缺省对象只适用受INSERT语句影响的行。定义缺省的格式为:
CREATE DEFAULT [owner] default_name AS constant_expression
其中default_name是新建的缺省的名字,constant_expression是一个常量表达式,在这个表达式中不包含有任何列名或其它数据库对象名,但可使用不涉及数据库的SQL Server内部函数。
缺省创建后,使用系统存储过程sp_binddefault与表中的列捆绑,也可以与用户定义的数据类型捆绑,其语法如下:
Sp_binddefault default_name, object_name
其中object_name是要指定与该缺省相绑定的列名或用户定义的类型名,如果指定的是表中的列,其格式为table.column,否则被认为是用户定义的数据类型名,如果名字中含有空格或标点符号或是保留字,则必须用引号将它放入引号中。
(4)规则(rule):规则是数据库对象之一,指定向某列(或用与该规则绑定的用户定义数据类型的所有列)插入或更新数据时,限制输入新值的取值范围。一个规则可以是:值的清单或值的集合、值的范围、必须满足的单值条件、用LIKE子句定义的编辑掩码等。当数据库中数据值被更新或插入时,就要检查新值是否遵循规则,如果不符合规则就拒绝执行更新或插入操作。规则在实现功能上等同于CHECK约束。
(5)触发器(trigger)是特定类型的存储过程,作为数据修改(增、删、改)语句的一部分而在相应操作执行时自动执行,是维护数据库完整性最强大的工具,能在任何情况下去维护数据的完整性,应用程序不能绕过触发器(除非是批量复制和TRUNCATE TABLE)不予执行,这也正是触发器和一般存储过程的一大区别。
2.2 实体完整性的实现
(1)列的Identity属性说明为Identity的列由系统自动为其赋值,并保证其值在该表中的惟一性,每一个表中只允许有一个列定义为Identity的。定义时Identity后可跟seed和increment参数,前一参数表示该列的起始值,后一参数表示该列值每次的增量。
(2)主键(primary key)约束指定表中一列或数列作为数据表的主键,下面是对customers表中的customer id建立主键值约束。
CREATE TABLE customers (Customer_id INT Primary Key, Customer_name NVARCHAR (20), Customer_address NVARCHAR (20))
在这个customers表中customer_id的取值必须不相同且不能取空值。
(3)惟一性(unique)约束和primary key约束相类似,定义数据列值的惟一性约束,有一行可取NULL值,并且惟一性约束在一个表中可以使用多次,而主键值只可以在一个表中使用一次。在缺省的情况下,惟一约束产生非聚簇索引,而主键约束产生聚簇索引。
2.3 参照完整性的实现
通过在同一个数据库的两个表中进行主键约束和外键约束来实现,参照的列和被参照的列的名字必须相同。在被参照表中,主键值被其它表所参照时,该行不能被删除,也不允许改变。
在参照表中,不允许参照不存在的主键值。下面例子在定义了orders表的customer_id列參照customers中的customer_id列。
CREATE TABLE orders(Order_id INT Primary Key,Customer_id INT RETERENCES customers(customer_id),Order_date DATETIME)
2.4 用户定义完整性的实现
(1)存储过程(procedure)是一个存储在数据库内的可执行的数据库对象,它可以接受参数、返回状态值和参数值,并且可以嵌套调用。本来存储过程是提供安全性考虑的,但是通过安全性提供了另一级的完整性-如果表的修改都是由存储过程来完成,便可以确保这些数据表的完整性和一致性,而不用担心手工操作可能产生的不一致的问题。
(2)视图(view):是查看存储在表中数据的一种逻辑方法,它是通过一个被存储的SELECT语句来实现的。如果需要对多个表设置一些完整性约束,可以用带WITH CHECK OPTION从句的视图定义来实现。实现功能和触发器类似。
另外,对于用户定义完整性也可以通过default、rule、trigger等方式来实现,操作方式与前所述相同。
3 应用实例
(1)CREATE TABLE 员工。
( 编号 CHAR(4) PRIMARY KEY,
姓名CHAR(8) NOT NULL,
性别CHAR(2) CHECK ( 性别 IN (‘男’,‘女’)) DEFAULT ‘男’,
生日 DATETIME,
职务 CHAR(8),
基本工资 MONEY CHECK (基本工资>=600),
部门号 SMALLINT REFERENCES 部门(部门号) )
本例为在建立数据表的同时就实现了几种完整性约束:
①本表定义时对各字段设置数据类型使各字段只能接收储存相应数据类型的数据;对性别和基本工资设置CHECK约束,性别只能取“男”或“女”基本工资只能接收不小于600的MONEY型数据;另外对性别还设置了默认值,则性别默认为“男”,从而部分实现了域完整性约束;
②对编号设置PRIMARY KEY约束使编号成为主键,其取值不能重复也不能为空值;对姓名设置NOT NULL约束,这样姓名不能为空值,部分实现了其实体完整性约束;
③通过“部门号 SMALLINT REFERENCES 部门(部门号)”将员工表和部门表连接起来,员工表中的外键“部门号”参照部门表中的主键“部门号”,从而部分实现了参照完整性。
(2)CREATE TABLE 商品。
( 商品号CHAR(6) PRIMARY KEY,
商品名CHAR(24) NOT NULL,
型号CHAR(16),
单位CHAR(2) DEFAULT ‘台’,
单价MONEY NOT NULL,
存量INT,
厂家CHAR(24) )
本例中通過“单位 CHAR(2) DEFAULT ‘台’”对单位设置了缺省值。
(3)视图的例子。
CREATE VIEW NYG AS SELECT 编号,姓名,性别,基本工资,部门号FROM 员工WHERE 性别=‘男’ WITH CHECK OPTION
通过以上定义创建一个男员工视图后,向此视图中添加数据时只能添加性别为“男”的数据,拒绝添加性别项为“女”的数据。
(4)存储过程的创建与调用。
IF EXISTS(SELECT name FROM sysobjects WHERE name=‘员工查找’ AND type=‘p’)
DROP PROCEDURE员工查找
GO
CREATE PROCEDURE员工查找
@ygname char(10)
AS
SELECT 姓名,基本工资,部门.部门号 FROM 员工,部门 WHERE 员工.部门号=部门.部门号 AND 员工.姓名=@ygname
GO
定义了这个存储过程以后,若程序中某处需要查找员工,即可调用此过程,用法为:
EXEC员工查找‘王明’ 或 EXEC员工查找 @ygname=‘王明’。
参考文献
[1]肖慎勇.SQL Server数据库管理与开发[M].北京:清华大学出版社,2006.
[2] (美) Peter Rob, Carlos Coronel著,陈立军译.数据库系统设计、实现与管理[M].北京:电子工业出版社,2004.
[3]潘林森,姚渝春.SQL Server数据库系统中的数据完整性控制方法[J].重庆师范大学学报(自然科学版),2004,(6).