SQL应用与开发:(八)数据完整性

2024-08-26 20:32

本文主要是介绍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语句的我们,这一章也尤为重要,无规则不成方圆。

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





这篇关于SQL应用与开发:(八)数据完整性的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1109600

相关文章

基于Python开发一个有趣的工作时长计算器

《基于Python开发一个有趣的工作时长计算器》随着远程办公和弹性工作制的兴起,个人及团队对于工作时长的准确统计需求日益增长,本文将使用Python和PyQt5打造一个工作时长计算器,感兴趣的小伙伴可... 目录概述功能介绍界面展示php软件使用步骤说明代码详解1.窗口初始化与布局2.工作时长计算核心逻辑3

MySQL 表的内外连接案例详解

《MySQL表的内外连接案例详解》本文给大家介绍MySQL表的内外连接,结合实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录表的内外连接(重点)内连接外连接表的内外连接(重点)内连接内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选,我

如何确定哪些软件是Mac系统自带的? Mac系统内置应用查看技巧

《如何确定哪些软件是Mac系统自带的?Mac系统内置应用查看技巧》如何确定哪些软件是Mac系统自带的?mac系统中有很多自带的应用,想要看看哪些是系统自带,该怎么查看呢?下面我们就来看看Mac系统内... 在MAC电脑上,可以使用以下方法来确定哪些软件是系统自带的:1.应用程序文件夹打开应用程序文件夹

MySQL的ALTER TABLE命令的使用解读

《MySQL的ALTERTABLE命令的使用解读》:本文主要介绍MySQL的ALTERTABLE命令的使用,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1、查看所建表的编China编程码格式2、修改表的编码格式3、修改列队数据类型4、添加列5、修改列的位置5.1、把列

Mybatis嵌套子查询动态SQL编写实践

《Mybatis嵌套子查询动态SQL编写实践》:本文主要介绍Mybatis嵌套子查询动态SQL编写方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录前言一、实体类1、主类2、子类二、Mapper三、XML四、详解总结前言MyBATis的xml文件编写动态SQL

解决mysql插入数据锁等待超时报错:Lock wait timeout exceeded;try restarting transaction

《解决mysql插入数据锁等待超时报错:Lockwaittimeoutexceeded;tryrestartingtransaction》:本文主要介绍解决mysql插入数据锁等待超时报... 目录报错信息解决办法1、数据库中执行如下sql2、再到 INNODB_TRX 事务表中查看总结报错信息Lock

MySQL启动报错:InnoDB表空间丢失问题及解决方法

《MySQL启动报错:InnoDB表空间丢失问题及解决方法》在启动MySQL时,遇到了InnoDB:Tablespace5975wasnotfound,该错误表明MySQL在启动过程中无法找到指定的s... 目录mysql 启动报错:InnoDB 表空间丢失问题及解决方法错误分析解决方案1. 启用 inno

MySQL 安装配置超完整教程

《MySQL安装配置超完整教程》MySQL是一款广泛使用的开源关系型数据库管理系统(RDBMS),由瑞典MySQLAB公司开发,目前属于Oracle公司旗下产品,:本文主要介绍MySQL安装配置... 目录一、mysql 简介二、下载 MySQL三、安装 MySQL四、配置环境变量五、配置 MySQL5.1

MySQL 添加索引5种方式示例详解(实用sql代码)

《MySQL添加索引5种方式示例详解(实用sql代码)》在MySQL数据库中添加索引可以帮助提高查询性能,尤其是在数据量大的表中,下面给大家分享MySQL添加索引5种方式示例详解(实用sql代码),... 在mysql数据库中添加索引可以帮助提高查询性能,尤其是在数据量大的表中。索引可以在创建表时定义,也可

Mybatis Plus JSqlParser解析sql语句及JSqlParser安装步骤

《MybatisPlusJSqlParser解析sql语句及JSqlParser安装步骤》JSqlParser是一个用于解析SQL语句的Java库,它可以将SQL语句解析为一个Java对象树,允许... 目录【一】jsqlParser 是什么【二】JSqlParser 的安装步骤【三】使用场景【1】sql语