数据完整性

最新推荐文章于 2023-01-27 17:59:23 发布

tjjingpan

最新推荐文章于 2023-01-27 17:59:23 发布

阅读量1.1k

点赞数

分类专栏： mssql-server

本文链接：https://blog.csdn.net/tjjingpan/article/details/104281109

版权

mssql-server 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

数据完整性知识目标

● 了解数据完整性及其分类；

● 掌握约束的作用和各种约束的使用方法；

● 掌握默认值和规则的作用和使用方法；

● 理解标识列的作用和使用方法。

技能目标

● 会在数据表上创建和删除主键约束、外键约束、唯一约束、检查约束、默认约束；

● 会在数据表上创建默认值、规则；

● 会在数据表上创建标识列。知识学习数据的完整性是保证数据的正确性、一致性和相容性。为了保证数据库的完整性，数据库管理系统必须提供一种机制来检查数据库中的数据是否满足语义的要求，这种功能称为完整性检查。这些加在数据库数据上的语义约束条件称为数据库完整性约束条件。数据完整性分为四类：实体完整性、域完整性、参照完整性、用户定义的完整性。

1.实体完整性

实体完整性规定表的每一行在表中是唯一的实体。表中定义的PRIMARYKEY约束就是实体完整性的体现。

2.域完整性

域完整性是指数据库表中的属性列必须满足某种特定的数据类型或约束。其中约束又包括取值范围、精度等规定。表中的UNIQUE、CHECK、FOREIGN KEY约束和DEFAULT、NOT NULL定义都属于域完整性的范畴。

3.参照完整性

参照完整性是指两个表的主键和外键值的数据应对应一致。它确保了有主键的表中对应其他表的外键的数据行存在，即保证了表之间数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。参照完整性是建立在外键和主键之间的桥梁。在SQL Server2012中，参照完整性作用表现在如下几个方面：

①禁止在从表中插入包含主表中不存在的关键字的数据行。

②禁止会导致从表中的相应值孤立的主表中的外键值改变。

③禁止删除在从表中有对应记录的主表记录。

4.用户定义的完整性

不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性针对某个特定关系数据库的约束条件，反映某一具体应用所涉及的数据必须满足的语义要求。SQL Server 2012提供了定义和检验这类完整性的机制，以便用统一的系统方法来处理它们，而不是用应用程序来承担这一功能。其他的完整

创建约束

约束定义关于属性中允许值的规则，是一种强制性的规定，在SQL Server2012中提供的约束是通过定义列的取值规则来维护数据完整性的。约束可以在创建表的同时创建，也可以在已有的表上创建。

约束有5种类型：主键约束（PRIMARY KEY）、外键约束（FOREIGNKEY）、唯一约束（UNIQUE）、检查约束（CHECK）和默认（DEFAULT）约束。

在SQL Server 2012中，可以使用对象资源管理器、CREATE TABLE语句或ALTER TABLE语句创建约束。使用CREATE TABLE语句可以在创建表的同时一起创建约束，Transact-SQL语法格式如下：

创建表之后，使用ALTER TABLE语句创建约束的语法格式如下：

当约束不符合实际需求的时候，可以删除约束。可以使用对象资源管理或者ALTER TABLE语句删除约束。使用ALTER TABLE语句删除约束的Transact-SQL语法格式如下：

1.主键约束

主键约束的关键词为PRIMARY KEY， PRIMARY KEY约束用于定义基本表的主键，具有唯一标识作用，其值不能为NULL，也不能重复，以此来保证实体的完整性。

（1）创建主键约束

①使用对象资源管理器创建主键约束。

为Student数据库中的Student表创建主键约束。Student表的StuNo为主键，所以将StuNo定义为主键约束，确保在Student表中不会出现相同的学号和学号是空值的数据行，以此保证每个学生都是可以识别的。

将光标定位于StuNo行。
单击工具栏中的“设置主键”按钮[插图]，或右击StuNo行，在弹出的快捷菜单中选择“设置主键”命令，主键约束创建完成.
单击工具栏中的“保存”按钮[插图]，完成创建。

②使用Transact-SQL语句创建主键约束。使用Transact-SQL语句创建主键约束可以用CREATE TABLE命令在创建表的同时完成，也可以利用ALTERTABLE命令为已经存在的表创建主键约束。

CREATE TABLE Department1
(
	DepartNo nvarchar(2) CONSTRAINT PK_Department PRIMARY KEY,
	DepartName nvarchar(2) NOT NULL
)

等价形式如下：

CREATE TABLE Department2
(
	DepartNo nvarchar(2)  PRIMARY KEY,
	DepartName nvarchar(2) NOT NULL
)

使用ALTER TABLE语句为已存在的Course表和Class表添加主键约束。

ALTER TABLE Course
ADD CONSTRAINT PK_Course
PRIMARY KEY (CouNo)

ALTER TABLE Course
ADD CONSTRAINT PK_Course
PRIMARY KEY (CouNo)

（2）删除主键约束

①使用对象资源管理器删除主键约束。

ALTER TABLE Course
DROP CONSTRAINT PK_Course

2.外键约束

外键约束即FOREIGN KEY约束，外键约束指定某一个列或几列作为外键，以此来保证系统在外键上的取值是主表中的某一个主键值，或者取空值，进而保证两表间的参照完整性。外键是维护表与表之间对应唯一关系的一种方法。

（1）创建外键约束①使用对象资源管理器创建外键约束。

为Student数据库的StuCou表创建基于StuNo的外键约束，该约束名为“FK_StuCou_Student”，限制StuCou表的StuNo列的值必须是Student表的StuNo列已经存在的值，以此来保证实实在在存在的学生才能选课，不存在的学生无法选课，保证数据的参照完整性。

①使用对象资源管理器创建外键约束。

②使用Transact-SQL语句创建外键约束。使用Transact-SQL语句创建外键约束可以用CREATE TABLE命令在创建表的同时完成，也可以利用ALTERTABLE命令为已经存在的表创建外键约束。

为已经存在的表创建外键约束的语法格式如下：

参数说明如下：table_name：要添加外键约束且已经存在的表名。

constraint_name：要添加外键约束名。

columnname：外键列名。

ref_tablename：主键表名。

ref_columnname：主键表列名。

使用ALTER TABLE语句在Student表中为ClassNo创建外键约束，设置其名称为FK_Student_Class。

ALTER TABLE Student
ADD
CONSTRAINT FK_Student_Class
FOREIGN KEY (ClassNo)
REFERENCES Class (ClassNo)

（2）删除外键约束①使用对象资源管理器删除外键约束。

②使用Transact-SQL语句删除外键约束。

使用ALTER TABLE语句删除Student表中名称为FK_Student_Class的外键约束。

ALTER TABLE Student
DROP
CONSTRAINT FK_Student_Class

3.唯一约束

UNIQUE约束（唯一约束）指明基本表在某一列或多个列的组合上的取值必须唯一。但是使用UNIQUE约束的字段允许为NULL值。（1）创建唯一约束①使用对象资源管理器创建唯一约束。

（1）创建唯一约束①使用对象资源管理器创建唯一约束。

为Student数据库的Student表中的StuName列创建唯一约束，以此保证学生表中的学生姓名没有重复值。

②使用Transact-SQL语句创建唯一约束。使用Transact-SQL语句创建唯一约束可以用CREATE TABLE命令在创建表的同时完成，也可以利用ALTERTABLE命令为已经存在的表创建唯一约束。

为已经存在的表创建唯一约束的语法格式如下：

参数说明如下：

table1_name：要添加唯一约束且已经存在的表名。

constraint_name1：要添加唯一约束名。

colname1：唯一约束的列名。

使用ALTER TABLE语句在Department表的DepartName列上创建唯一约束，设置其名称为UN_DepartName。

在查询窗口中执行如下Transact-SQL语句：

ALTER TABLE Department
ADD
CONSTRAINT UN_DepartName
UNIQUE (DepartName)

注意：无论采用哪种方法创建唯一约束，前提是一定要确保唯一约束属性列的值不存在重复值，否则创建会失败。

（2）删除唯一约束①使用对象资源管理器删除唯一约束。

①使用对象资源管理器删除唯一约束。

②使用Transact-SQL语句删除唯一约束。

使用ALTER TABLE语句删除Department表中名称为UN_DepartName的唯一约束。

在查询窗口中执行如下Transact-SQL语句：

ALTER TABLE Department
DROP
CONSTRAINT UN_DepartName

4.检查约束

检查约束即CHECK约束，对可以放入列中的值进行限制，用来检查字段值所允许的范围，以此保证域的完整性。

（1）创建检查约束。

①使用对象资源管理器创建检查约束。

②使用Transact-SQL语句创建检查约束。可以利用ALTER TABLE命令为已经存在的表创建检查约束。

在查询窗口中执行如下Transact-SQL语句：

ALTER TABLE StuCou
ADD
CONSTRAINT CK_Score1 CHECK(Score >=0 AND Score <=100)

（2）删除检查约束

①使用对象资源管理器删除检查约束。

②使用Transact-SQL语句删除检查约束。可以利用ALTER TABLE命令为已经存在的表创建检查约束。

在查询窗口中执行如下Transact-SQL语句：

5.默认约束

在用户输入某些数据时，希望一些数据在没有特例的情况下被自动输入，例如学生的性别默认为“女”等情况，这个时候需要对数据创建默认约束。默认约束就是避免属性列值出现空值的一种办法，以此减少在客户端开发工具（如C#）中对SQL Server中空值进行特殊额外的处理。

（1）创建默认约束

①使用对象资源管理器创建默认约束。

使用对象资源管理器为Course表的Kind列创建默认约束，使Kind列的值默认为“理工”，即在不输入值的情况下的值为“理工”。

②使用Transact-SQL语句创建默认约束。使用Transact-SQL语句创建默认约束可以用CREATE TABLE命令在创建表的同时完成，也可以利用ALTERTABLE命令为已经存在的表创建默认约束。

语法格式如下：

参数说明如下：

table_name：基本表表名。

constraint_name：约束名。

constraint _expression：约束表达式。

column_name：默认约束的约束属性列列名。

使用Transact-SQL语句中的ALTER TABLE语句为StuCou表添加默认约束，使StuCou表中的State在未输入值时的默认值为“选课”。

ALTER TABLE StuCou
ADD
CONSTRAINT DF_State
DEFAULT ('选课') FOR State

（2）删除默认约束

①使用对象资源管理器删除默认约束。

②使用Transact-SQL语句删除默认约束。

在查询窗口中执行如下Transact-SQL语句：

ALTER TABLE StuCou
DROP
CONSTRAINT DF_State

创建默认值

默认是一种独立的数据对象，它与DEFAULT（默认）约束的作用相同，也是当向表中输入数据时，没有为列输入值的情况下，系统自动给该列赋予一个“默认值”。此处的默认对象与用CREATE TABLE或ALTER TABLE语句操作表时使用DEFAULT（默认）约束的功能相似，不同的是默认对象的定义独立于表，是一种数据库对象。在数据库中一次创建后，可以多次应用任意表的任意列，也可以应用于用户定义数据类型。默认值可以是常量、内置函数或数学表达式。

1.创建默认值

CREATE DEFAULT语句用于在数据库中创建默认对象，其语法格式如下：

CREATE DEFAULT 默认名 AS default_expression

其中，default_expression：指默认的定义，可以是数学表达式或函数，也可以是常量。

设置Student表的Pwd列的默认值为“00000000”，创建的默认值的名称为PwdDefault。

注意：执行完上述语句后，仅仅在Student数据库中创建了一个名为PwdDefault的默认对象，并未产生任何作用。

2.绑定默认值

创建默认值后，必须将其绑定到表的字段或用户自定义的数据类型上才能产生作用。绑定默认值可以使用sp_bindefault存储过程，也可以使用SQLServer对象资源管理器。

将名称为PwdDefault的默认值绑定到Student表的Pwd列上。

在查询窗口中，执行如下Transact-SQL语句：

注意：执行完上述语句后，将显示消息“已将默认值绑定到列”，如图3-38所示。“PwdDefault”默认值将作用于Student表的Pwd属性列上。

当在输入过程中，未指定Pwd列的值，系统将自动填充“00000000”值。打开Student表，进入编辑状态，添加一条记录，学号为“20150005”，姓名为“王勃”，班号为“20150101”，不输入Pwd列的值，系统自动填充“00000000”值，如图3-39所示。为了保证数据库中数据的一致性，将刚才插入的记录再进行删除。

3.解除默认值

解除默认值PwdDefault与Student表的Pwd列的绑定。

在查询窗口中执行如下Transact-SQL语句：

EXEC sp_unbindefault 'Student.Pwd'

注意：执行完后，解除了默认值PwdDefault与Student表的Pwd列的绑定。当在输入过程中，未指定Pwd列的值，将为空值。

4.删除默认值

当默认值不再符合实际需求时，可以将其删除。在删除前，必须将默认值解除。在查询分析器中使用DROP语句删除默认值。

在查询窗口中执行如下Transact-SQL语句：

DROP DEFAULT PwdDefault

注意：执行完后，名为PwdDefault的默认数据对象将在Student数据库中消失。

创建规则

规则（Rule）就是数据库对存储在表中的列或用户自定义数据类型中的值的规定和限制。规则与其作用的表或用户自定义数据类型是相互独立的，即表或用户自定义对象的删除、修改不会对与之相连的规则产生影响。规则与CHECK约束相似，不同之处在于在一个属性列上只能有一个规则，但可以有多个CHECK约束。相比之下，在ALTER TABLE或CREATE TABLE命令中使用的CHECK约束更加标准，但CHECK约束不能作用于用户自定义的数据类型，而规则不仅可以应用在多个列上还可以作用于用户自定义的数据类型。

创建规则

CREATE命令用于在数据库中创建默认对象，其语法格式如下：

CREATE RULE 规则名 AS condition_expression

其中，condition_expression：指规则的定义，任何数学表达式可包含算数运算符、关系运算符和谓词（如BETWEEN…AND、IN等）。

创建规则，使课程表Course的Credit值大于或等于1，小于或等于10。

在查询窗口中执行如下Transact-SQL语句：

CREATE RULE  Credit_rule
AS @Credit>=1 and @Credit <=10

绑定规则

创建规则后，规则仅仅是一个存在于数据库中的对象，并未发生作用。需要将规则与数据库表或用户自定义对象联系起来，才能达到创建规则的目的。所谓绑定就是指定规则作用于哪个表的哪一列或哪个用户自定义数据类型。

绑定规则Credit_rule到Course表的Credit字段。

在查询窗口中执行如下Transact-SQL语句：

EXEC sp_bindrule 'Credit_rule','Course.Credit'

松绑规则

解除规则与对象的绑定称为“松绑”。解除已绑定到Course表的Credit字段的规则Credit_rule。

在查询窗口中执行如下Transact-SQL语句：

EXEC sp_unbindrule 'Course.Credit'

删除规则

使用DROP RULE命令删除规则，语法结构如下：DROP RULE {rule_name} [，...n]

删除Credit_rule规则。

在查询窗口中执行如下Transact-SQL语句：

DROP RULE Credit_rule

注意：在删除一个规则前必须先将与其绑定的对象解除绑定。

创建标识列

在实际设计中，有时候希望计算机能自动生成标识列。如果初值（称为种子）为1，增量为1，则第一行数据的标识列值自动生成1。对于第二行数据标识列的值，系统自动生成为前一行的标识列值加上增量，即为2，不需要人工输入标识列的值。

标识列与主键约束、唯一约束一样，能唯一标识表中的每一行，可以用来保证表的完整性。

每个表只能定义一个标识列，语法格式如下：

列名数据类型IDENTITY（种子，增量）

注意：

①数据类型必须为数值型。可为下列数据类型之一：int、smallint。

②系统默认种子和增量的初值均为1。

③标识列不允许出现空值，也不能有默认约束和检查约束。

④对经常进行删除操作的表最好不要使用标识列，因为删除操作会使标识列的值出现不连续的情况。

在Student数据库中创建一个ClassRoom表，表中有两个属性列：ClassRoomID为标识列，初值为1，增量为1；ClassRoomName列为字符型，长度为30，不允许为空。

在查询窗口中执行如下Transact-SQL语句：

USE Student
GO
CREATE TABLE ClassRoom
(
	ClassRoomID int IDENTITY(1,1),
	ClassRomeName char(30) NOT NULL
)
GO

INSERT ClassRoom(ClassRomeName) VALUES('多媒体1教室')
INSERT ClassRoom(ClassRomeName) VALUES('多媒体2教室')
INSERT ClassRoom(ClassRomeName) VALUES('计算机1教室')
GO
SELECT * 
FROM ClassRoom
GO

执行结果如图所示，显示出3个数据行，其中ClassRoomID的值依次为1、2、3。

tjjingpan

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
数据完整性

数据完整性知识目标● 了解数据完整性及其分类；● 掌握约束的作用和各种约束的使用方法；● 掌握默认值和规则的作用和使用方法；● 理解标识列的作用和使用方法。技能目标● 会在数据表上创建和删除主键约束、外键约束、唯一约束、检查约束、默认约束；● 会在数据表上创建默认值、规则；● 会在数据表上创建标识列。知识学习数据的完整性是保证数据的正确性、一致性和相容性。为了保证...
复制链接

扫一扫

专栏目录