SQL Server 2008数据库完整性的应用探索

SQL Server 2008数据库完整性的应用探索

发表时间：2019-07-29T11:42:00.673Z 来源：《基层建设》2019年第14期作者：宋龙坤王杰陈梅[导读] 摘要：随计算机技术和网络应用的普及，数据库技术在据计算机领域作用日益突出，SQL标准使用一系列的概念来描述完整性，包括关系模型的实体完整性、参照完整性、用户定义完整性等。山东协和学院山东济南 250107摘要：随计算机技术和网络应用的普及，数据库技术在据计算机领域作用日益突出，SQL标准使用一系列的概念来描述完整性，包括关系模型的实体完整性、参照完整性、用户定义完整性等。本文基于SPJ数据库系统的开发，阐述了数据库完整性的理论和实践，并对数据库完整性问题进行探讨。

关键词：SQL Server；数据完整性；SPJ数据库系统；

1 引言数据库完整性是指数据库中数据的正确性和相容性，而合理的数据完整性既能减少数据冗余，又能在测试中尽早发现错误，提高系统效率。而SQL Server数据库系统是一种关系数据库系统，扩张性非常强，性能也很高，在实现数据库的完整性方面有充分体现。关系数据库的实体完整性在CREATE TABLE 中通过PRIMARY KEY定义实现，参照完整性在CREATE TABLE 中利用FOREIGN KEY短语确定哪些列为外码，REFERENCES短语指明外码参照哪些表的主码。而用户定义的完整性是根据应用要求，来定义属性的约束条件。完整性约束条件包括NOT NULL、CHECK等语句。断言，则利用数据定义语言中的CREATE ASSERTION 语句，通过声明性断言指定更具一般性的约束。触发器是对表进行插入，更新，删除时会自动执行的特殊存储过程。采取一系列方案，保证了数据库的完整性。

2 SPJ数据库管理系统简介设有一个SPJ数据库，包括S，P，J，SPJ四个关系模式： S（SNO，SNAME，STATUS，CITY）； P（PNO，PNAME，COLOR，WEIGHT）； J（JNO，JNANE，CITY）； SPJ（SNO，PNO，JNO，QTY）。其中：供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成；零件表P由零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT）组成；工程项目表J 由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）组成；供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量组成（QTY）组成，表示某供应商供应某种零件给某工程项目的数量为QTY。

3 数据库完整性的实现方法 3.1实体完整性关系数据库的实体完整性在CREATE TABLE 中用PRIMARY KEY定义。以上述P表中的Pno设为主键为例：（1）在列级定义的主键 CREATE TABLE P （ PNO CHAR（9） PRIMARY KEY， /*在列级定义主键*/ PNAME CHAR（20） NOT NULL， COLOR CHAR（9）， WEIGHT CHAR（9））；（2）在表级定义的主键 CREATE TABLE P （ PNO CHAR（9）， PNAME CHAR（20） NOT NULL， COLOR CHAR（9）， WEIGHT CHAR（9）， PRIMARY KEY（pno） /*在表级定义主码*/ ）；

3.2参照完整性参照完整性在CREATE TABLE 中用FOREIGN KEY短语定义哪些列为外码，REFERE短语指明这些外码参照哪些表的主码。以定义SPJ 中的参照完整性为例： CREATE TABLE SPJ （SNO CHAR（4） NOT NULL， PNO CHAR（4） NOT NULL， JNO CHAR（4） NOT NULL， QTY INT， primary key（Sno，Pno，JNO） foreign key sno references s（sno）； foreign key pno references p（pno）； foreign key jno references j（jno））

3.3用户定义的完整性

用户定义的完整性是在CREATE TABLE 中定义属性的同时，根据应用要求定义属性上的约束条件，即属性值限制，包括：列值非空（NOT NULL）；列值唯一（UNIQUE）；检查列值是否满足一个条件表达式（CHECK短语）。

（1）不允许为空。CREATE TABLE SPJ中的SNO、PNO、JNO均是。

（2）列值唯一。以简化建立P表，要求Pname列取值唯一，Pno列为主码为例：

CREATE TABLE P，

（PNO NUMERIC（2），

PNAME CHAR（9）UNIQUE NOT NULL，/* Pname唯一，且不能取空值*/

PRIMARY KEY（Pno）

）；

其中，NUMERIC表示数字；UNIQUE表示唯一的。

（3）用CHAR短语指定列值应该满足的条件。以P表的Weight 必须大于0为例：

CERATE TABLE P

（PNO CHAR（9） PRIMARY KEY，/*在列级定义主码*/

PNAME CHAR（8）NOT NULL， /*Pname属性不允许取空值*/

COLOR CHAR（20），

WEIGHT CHAR（2）CHECK（Weight >0）

）；

3.4完整性约束

（1）命名子句：CONSTRAINT <完整性约束条件名><完整性约束条件>。<完整性约束条件>包括NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK短语等。

例如：CONSTRAINT C1 CHECK（Pno BETWEEN 12 AND 40）用于检查零件重量是否位于12—40之间，CONSTRAINT C2 NOT NULL用于控制零件名为非空，CONSTRAINT PKey PRIMARY KEY（Pno）即为主键约束。

（2）表中的完整性限制是可以修改的，可通过使用ALTER TABLE 语句实现。以修改P中的约束条件，要求零件重量改在0—50之间可先删除原来的约束条件，再增加新的约束限制。

ALTER TABLE P DROP CONSTRAINT C1；

ALTER TABLE P ADD CONSTRAINT C1 CHECK（Weight BETWEEN 0 AND 50）；

3.5域中的完整性限制

<1>以建立一个颜色域，且限制其中的命名为例：

代码：CREATE DOMAIN Colorr CHAR（2）

CONSTRAINT GO CHECK（V ALUE IN（‘红’，‘绿’））；

<2>以删除域Colorr 的限制条件GD为例：

代码：ALTER DOMAIN Colorr

DROP CONSTRAINT GD；

<3>以在域Colorr上增加性别限制条件GDD为例：

代码：ALTER DOMAIN Colorr

ADD CONSTRAINT GDD CHECK（V ALUE IN（‘1’，’0’））；

3.6触发器

SQL使用CERATE TRIGGER 命令触发器，一般格式：

CREATE TRIGGER<触发器名>

{BEFORE|AFTER}<触发事件>ON<表名>

REFERENCING NEW|OLD ROW AS <变量>

FOR EACH {ROW|STATEMENT}

[WHEN <触发条件>]<触发动作体>

（1）一个触发器

当对表SPJ的QTY属性进行修改时，若供应数量增加了10%，则将此次操作记录到另一个表PJ-U（Pno，Jno，Oldqty，Newqty）中，其中，Oldqty是修改前的供应数量，Newqty是修改后的供应数量。

CREATE TRIGGER PJ_T

AFTER UPDATE OF QTY ON SPJ

REFERENCING

OLDROW AS OLDQTY，

NEWROW AS NEWQTY，

FOR EACH ROW /*行级触发器，即每执行一次QTY的更新，下面的规则就行一次*/

WHEN（NEW TUPLE.QTY>=1.1* OLDTUPLE.QTY）

/*触发条件，只有该条件为真时才执行*/

INSERT INTO SC_U（PNO，JNO，OLDQTY，NEWQTY）

V ALUES（OLDQTY.PNO，OLDQTY.JNO，OLDQTY.QTY，NEWQTY.QTY）

其中，PJ_T是触发器的名字，UPDATE OF QTY ON PJ是触发事件，AFTER是触发的时机，表示对当时SPJ的QTY属性修改完后再触发下面的规则。REFERENCING指出引用的变量，如果触发时间是UPDATE操作并且有FOR EACH ROW子句，则可以引用的变量有OLDROW和NEWROW分别修改之前的原则和修改后的元组。若没有FOR EACH ROW子句，则可以引用的变量有OLDTABLE（指表原来