SQL应用与开发：（八）数据完整性

一.定义

数据完整性是指存储在数据库中的所有数据值均正确的状态下。如果数据库中存储有不正确的数据值，则称该数据库已丧失数据完整性。

SQL数据库不只是存储数据，它必须保证所存储的数据是正确的。如果数据不准确或者不一致，那么该数据的完整性就会受到破坏，从而给数据库本身的可靠性带来问题。为了保证数据库的完整性，SQL提供了大量的完整性约束。完整性约束是应用于基表的规则基表用于约束可以放在这些表中的值。约束也可以应用于单个列、单个表或多个表。

二.学习内容

1.了解约束

约束是指关系数据库中的对象，用于存放关于插入到一个表某一列的数据的规则。约束是用来保证关系数据库中数据的准确性和一致性。在关系数据库中，通过在表上应用约束来处理数据库完整性。

SQL中的约束可分为3中类型：

与表相关的约束  它是在表定义的一种约束。该约束可以被定义为列定义的一个部分，或者定义为表定义中的一个元素。在表级别定义的约束可以应用于一个或多个列。

断言 在断言定义中定义的一种约束。断言可以与一个或多个表进行关联。

域约束 在域定义中定义的一种约束。域约束与在特定域中定义的任何列有关。

在这三种约束中，最通用的约束是与表相关的约束，该约束包含的约束选项数量最多。与表相关的约束可以分成两个子类别，即表约束和列约束。这两个子类别中的约束都是在表定义中定义的。列定义包含了列约束，表元素也包含了表约束。列约束和表约束都支持大量不同类型的约束，但是断言约合苏和域约束只限于一种类型的约束。

下面这幅图概括描述了可以创建的约束类型：

2.使用NOT NULL约束

空可以被看作是一个标识符(标识符是一个表示有关列的某种事实的字符、数字或位)。在空值情况下，如果没有为列提供值，那么标识符就被设置，表示值是未知的。每个列都有一个表示该列是否接受空值的nullability属性。在默认情况下，所有列都接受空值。但是，通过使用一个标明列将不接受空值的NOT NULL约束，可以重写。

最初创建时，必须为表中的每一列指定一个数据类型。通过某一列的数据类型来确定能够输入到表中列中的数据。当一列被创建时，可以将其定义为NOT NULL约束。插入一行数据到表中时，如果没有给一列输入值，那么该列便是NULL。如果指定一列为NOT NULL，则意味着不允许在该列中输入空值。

其创建语法：

<column_name> { <data_type> | <domain>} NOT NULL

举例来说，创建一个含有NOT NULL约束列的表，我们创建一个名为“业务员信息”的表，该表包含列“业务员编号”、“业务员姓名”、“家庭住址”和“电话”。创建表成功后，在向该表中添加数据时，你、不允许列“业务员编号”和“业务员姓名”为空。

CREATE TABLE 业务员信息

(业务员编号 INTEGER NOT NULL,

业务员姓名 VARCHAR(50) NOT NULL,

家庭住址 VARCHAR(50),

电话 VARCHAR(12)

)

3.唯一约束

在SQL 完整性约束类型中，列约束和表约束都支持唯一约束。而在SQL中具有两种类型的唯一的约束，即UNIQUE约束和PRIMARY KEY约束。

3.1.1UNIQUE约束

UNIQUE约束可以使用在列集内强制执行值得唯一性。对于UNIQUE约束中的列，表中不允许有两行包含相同的非空值。主键也强制执行唯一性，但主键不允许为空值，而且每个表中主键也只能有一个，但是在UNIQUE约束中却可以拥有多个。

若违背UNIQUE约束，系统会弹出违反约束的信息。

3.1.2PRIMARY KEY约束

PRIMARY KEY约束和UNIQUE约束一样，也是一种SQL唯一约束。这两种约束只允许指定列中的唯一值，二者都可以应用于一列或多列，并且也都定义为列约束和表约束。

PRIMARY KEY约束标识列或列集，这些列或列集的值唯一标识表中的行。一个PRIMARY KEY约束可以：

1.作为表定义的一部分在创建时创建；2.添加到尚没有PRIMARY KEY约束的表中；3.如果已有PRIMARY KEY约束，则可以对其进行修改和删除。

但是，由于主键在表中所起的作用，PRIMARY KEY约束有两个限制：

1.利用PRIMARY KEY约束定义的列不能包含空值，即使没有在该列上指定NOT NULL，该列仍然必须包含一个数据值；2.对应每一列只能定义一个PRIMARY KEY约束。

4.使用FROEIGN KEY约束

FROEIGN KEY(外键)约束主要用来维护两个表之间的一致性关系。外键的建立主要是通过加将一个表中的主键所在的列包含到另一个表中，这些列就是另一个表的外键。所以，外键是关系数据库中国增强表之间参照完整性的主要机制，定义为外键的列用来参照另一个表中定义为主键的列。也就是说，外键是依赖于另一个表中某列的一列数据，它确保了两个表中都存在相应的数据。

当创建FROEIGN KEY约束时，必须遵循以下原则：

被引用列必须是被引用表中的候选键(通常为主键)；

FROEIGN KEY约束可以被创建为表约束或列约束。如果把外键创建为表约束，那么可以包括一列或多列；

引用表中的外键包括的列数必须和被引用的列数相同，并且每个引用列使用的数据类型必须与对应的被引用列相。但是引用列名称不必与被引用列的名称相同；

如果在定义FROEIGN KEY约束时没有指定被引用列，那么在被引用表的主键中定义的列默认为被引用列。

举例来说，在表中指定FROEIGN KEY约束对两个表进行关联：

创建两个表，其中名为“操作人员信息”的表包含列“操作人员编号”、“操作人员姓名”和“联系电话”，并将列“操作人员编号”指定为PRIMARY KEY约束；另一名为“入库信息”的表包含列“入库编号”、“入库日期”和“操作人员编号”，将列“入库编号”指定为PRIMARY KEY约束，并通过列“操作人员编号”与“操作人员信息”表进行关联，即指定列“操作人员编号”为ROREIGN KEY约束。

首先创建“操作人员信息”表，其语句如下：

CREATE TABLE 操作人员信息

(操作人员编号 INTEGER PRIMARY KEY,

操作人员姓名 VARCHAR(50),

联系电话 VARCHAR(12)

)

然后根据上述要求创建“入库信息”表，其语句如下：

CREATE TABLE 入库信息

(入库编号 INTEGER PRIMARY KEY,

入库日期 SMALLDATETIME，

操作人员编号 INTEGER,

CONSTRAINT FK_操作人员编号

FOREIGN KEY(操作人员编号)

REFERENCES 操作人员信息(操作人员编号)

)

5.CHECK约束

 在完整性约束图中我们可以看出，CHECK约束可以被定义为表约束、列约束、域约束，或者被定义在断言中。CHECK约束允许指定可以包括在列中的值。例如，可以定义值得范围，列举值的列表，或者一些其他准确限制列中许可值的条件。

5.1在表约束和列约束中定义CHECK约束

创建列约束的语法规则：<column_name> {<data_type> | <domain>} CHECK {<search_condition>}

创建表约束的语法规则：[CONSTRAINT <column_name>] CHECK {<search_condition>}

其中，<search_condition>与SELECT语句中的查询条件相似，而这里是在创建表的过程中就为列指定了取值范围。

举例来说，创建一个“销售人员信息”表，该表中包含“编号”、“姓名”、“参加工作时间”和“联系电话”，并且要求在向表中添加数据时，列“参加工作时间”应介于2015年1月1日到2015年5月1日之间。

下面就两个约束分别创建，以作比较：

将CHECK约束作为列约束在创建表的语句中定义：

CREATE TABLE销售人员信息

(编号 INTEGER NOT NULL,

姓名 VARCHAR(50)，

参加工作时间 SMALLDATETIME NOT NULL,

CONSTRAINT CK_ 参加工作时间

CHECK

(参加工作时间 BETWEEN ‘2015-01-01‘ AND ‘2015-05-01‘)

)

将CHECK约束作为表约束在创建表的语句中定义：

CREATE TABLE销售人员信息

(编号 INTEGER NOT NULL,

姓名 VARCHAR(50)，

参加工作时间 SMALLDATETIME NOT NULL

CHECK

(参加工作时间 BETWEEN ‘2015-01-01‘ AND ‘2015-05-01‘)

)

5.2定义断言

断言仅仅是一种可以应用于多个表的CHECK约束，因此必须在表定义之外独立地创建断言。创建断言时使用下列语法：

CREATE ASSERTION <constraint_name> CHECK <search_condition>

创建断言和穿件表CHECK约束非常相似，但在CHECK关键字后必须提供必要的限定条件，并且断言的条件必须一直为真。

举例来说，重新创建表“销售信息”，包含的列仍未“商品编号”、“商品名称”、“销售价格”和“销售日期”，则其创建语句如下：

CREATE TABLE 销售信息

(商品编号 INTEGER PRIMARY KEY,

商品名称 VARCHAR(50) UNIQUE,

销售价格 MONEY NOT NULL,

销售日期 SMALLDATETIME NOT NULL

)

上述语句中并没有指定CHECK约束。如果对该表中的列“销售价格”中的数据进行总和，并且希望其总和大于10000，则可以创建一个断言把“销售价格”中的数据加起来，并验证总和是否大于10000。

CREATE ASSERTION AS_销售价格

CHECK 

((SELECT SUM(销售价格) FROM 销售信息) > 10000)

在该语句中使用了一个子查询(SELECT SUM(销售价格) FROM 销售信息)，并且将这个子查询的结果与10000进行比较。如果在“销售价格”列中添加的数据的总和小雨10000，那么系统将弹出一个出错消息。

5.3创建域和域约束

还有一种约束CHECK约束是插入域定义中的域约束。除了不用连接域约束和特定的列约束或表以外，域约束的定义与其他约合苏的定义相似。而当需要表示某个定义列的中的值时，域约束使用关键字VALUE。创建域的语法如下所示：

CREATE DOMAIN <domain_name> [AS] <data_type> 

[DEFAULT <default_value>]                                                            

[CONSTRAINT <constrain_name >] CHECK (<search_condition>)

举例来说，在5.1的例子中的“销售信息”表创建之后，引用列“销售日期”创建一个机遇SMALLDATETIME数据类型的域，并且要求所有的值都必须在2015年1月1日到2015年5月1日之间。

CREATE DOMAIN 销售日期 AS SMALLDATETIME

CONSTRAINT CK_销售日期

CHECK

(VALUE BETWEEN ‘2015-01-01‘ AND ‘2015-05-01‘)

在上述的语句中添加VALUE关键字，它表示由“销售信息”域定义的列中的值。因此，要插入的值必须在“2015-01-01”和“2015-05-01”。

6.使用规则

规则限制了可以存在表中或用户定义数据类型的值。，它可以使用多种方式来完成对数据值的检验，可以使用函数返回验证信息，也可以使用关键字BETWEEN、LIKE和IN完成度输入数据的检查。

规则是数据库对象之一，它的作用与CHECK约束的部分功能相同，在向表的某列插入或更新数据时，用它来限制输入的新值的取值范围。规则与CHECK约束不同之处在于：

CHECK约束是用CREATE TABLE语句在建表时指定的，而规则需要作为单独的数据库对象来实现；

在一列上只能使用一个规则，但可以使用多个CHECK约束；

规则可以应用于多个示例，还可以应用于用户自定义的数据类型，而CHECK约合苏只能应用于它定义的列。

规则是实现域完整性的方法之一，它用来验证一个数据库中的数据是否处于一个指定的值域范围内。当数据库中的数据被插入或更新时，需要检查这个新值是否遵循规则，如果违反了规则，那么这一操作将会失败。

6.1创建规则

创建规则使用CREATE RULE语句，其格式如下：

CREATE RULE <rule_name>

AS 

<condition_expression>

其中，<rule_name>表示创建的规则名称，<condition_expression>表示定义规则的条件。规则可以是WHERE自己中任何有效地表达式，并且可以包括算术运算符、关系运算符以及IN、LIKE、BETWEEN等关键字。

举例来说，在数据库“销售管理系统”中创建一个名为“城市_rule”的规则，限定输入的值必须是“北京市”、“上海市”、“广州市”、“南京市”、“深圳市”之一。

CREATE RULE 城市_rule

AS

@供应商所在城市 IN (‘北京市‘，‘上海市‘，‘广州市‘，‘南京市‘，‘深圳市‘)

6.2绑定规则

要使用规则，必须首先将其和列或者用户定义数据定义类型绑定。可以使用sp_bindrule存储过程对规则进行绑定。sp_bindrule存储过程的语法格式如下：

sp_bindrule [ @rulename=] <rule_name>,

[ @objname= ] <object_name>

[ ,@futureonly= ] <futureonly_flag>

其中，[ objname= ] <object_name>表示绑定了规则的表和列或用户定义的数据类型，[ @futureonly= ] <futureonly_flag>只有将规则绑定到用户定义的数据类型时才使用。

举例来说，将上一例子中创建的规则“城市_rule”绑定到“供应商信息”表的“供应商所在城市”列上。

sp_bindrule 城市_rule，‘供应商信息.供应商所在城市‘

6.3删除规则

对于不再需要的规则，可以使用DROP RULE语句删除。要删除规则首先要解除对改队则的绑定，解除规则的绑定可以使用sp_unbindrule存储过程，其语法格式如下：

sp_unbindrule [ @objectname= ] <object_name>

[ ,@futureonly= ] <futureonly_flag>]

举例来说，要删除规则“城市_rule”，那么首先要将该绑定从“供应商信息”表的列“供应商所在城市”上解除，可以使用下述语句解除绑定。

sp_unbindrule ‘供应商信息.供应商所在城市‘

在解除绑定之后，就可以使用下述DROP RULE语句将该规则删除。

DROP RULE 城市_rule

也可以在企业管理器中将规则删除，但是在删除规则之前必须确定该规则不存在任何的绑定，即要删除规则，必须先解除规则上的绑定。

三.学习小结

关于数据库的完整性的整理，到这里也算是完结了，而相对于其它章节的整理中，本章节尤为简单，个人的理解。都是一些简单的SQL语句的练习，在先前的学习中，对于早已熟悉SQL语句的我们，这一章也尤为重要，无规则不成方圆。

对于自己的整理，也由于在数据库中练习，相得益彰。多去实践，才能提升对这规则的更好理解。