SQL Server中约束的介绍

约束定义

对于数据库来说,基本表的完整性约束分为列级约束条件和表级约束条件:

列级约束条件

     
 列级约束条件是对某一个特定列的约束,包含在列定义中,可以直接跟在该列的其他定义之后,用空格分隔,不用指定列名。

表级约束条件

     
 表级约束条件与列定义相互独立,不包括在列定义中,通常用于对两个或两个以上的列一起进行约束。

一、数据库完整性概述

约束介绍

在数据库管理系统中,保证数据库中的数据完整性是非常重要的。所谓数据完整性,就是指存储在数据库中数据的一致性和正确性。约束定义关于列中允许值的规则,是强制完整性的标准机制。使用约束优先于使用触发器、规则和默认值。查询优化器也使用约束定义生成高性能的查询执行计划。

数据完整性分类

在SQL
Server中,根据数据完整新措施所作用的数据库对象和范围不同,可以将数据完整性分为以下几种:

实体完整性

实体完整性简单的说,就是将表中的每一行看作一个实体。实体完整性要求表的标示符列或主键的完整性。可以通过建立唯一索引、PRIMARY
KEY约束、UNIQUE约束,以及列的IDENTITY属性来实施实体完整性。

域完整性

域完整性是指给定列的输入有效性。要求表中指定列的数据具有正确的数据类型、格式和有效的数据范围。强制域有效性的方法有:限制类型(通过数据类型)、格式(通过
CHECK 约束和规则)或可能值的范围。域完整性通过 FOREIGN KEY 约束、CHECK
约束、DEFAULT 定义、NOT NULL 定义和规则来实现。

引用完整性

引用完整性又称参照完整性。引用完整性维持被参照表和参照表之间的数据一致性,它通过主键(PRIMARY
KEY)约束和外键(FOREIGN
KEY)约束来实现。引用完整性确保键值在所有表中一致。这样的一致性要求不能引用不存在的值,如果键值更改了,那么在整个数据库中,对该键值的所有引用要进行一致的更改。在被参照表中,当其主键值被其它表所参照时,该行不能被删除也不允许改变。在参照表中,不允许参照不存在的主键值。

摘要

 

约束分类

SQLServer中有五种约束类型,分别是 PRIMARY
KEY约束、FOREIGN
KEY约束、UNIQUE约束、DEFAULT约束、和CHECK约束。查看或者创建约束都要使用到
Microsoft SQL Server Managment Studio。

对于SQL
Server中的约束,想必大家并不是很陌生。但是约束中真正的内涵是什么,并不是很多人都很清楚的。本文以详细的文字来介绍了什么是约束,以及如何在数据库编程中应用和使用这些约束,来达到更好的编程效果。(本文部分内容参考了SQL
Server联机手册)

1.数据库的完整性:

PRIMARY KEY约束

在表中常有一列或多列的组合,其值能唯一标识表中的每一行,这样的一列或多列成为表的主键(PrimaryKey)。

一个表只能有一个主键,而且主键约束中的列不能为空值。

只有主键列才能被作为其他表的外键所创建。

内容

 

FOREIGN KEY约束 

外键约束是用来加强两个表(主表和从表)的一列或多列数据之间的连接的。

创建外键约束的顺序是先定义主表的主键,再对从表定义外键约束。

只有主表的主键才能被从表用来作为外键使用,被约束的从表中的列可以不是主键,主表限制了从表更新和插入的操作。

  数据完整性分类
  实体完整性
  域完整性
  引用完整性
  用户定义完整性
  PRIMARY KEY约束
  DEFAULT约束
  CHECK约束
  UNIQUE约束
  FOREIGN KEY约束

①数据库的完整性是指数据的正确性和相容性

UNIQUE约束

唯一约束确保表中的一列数据没有相同的值。

与主键约束类似,唯一约束也强制唯一性,但唯一约束用于非主键的一列或者多列的组合,且一个表可以定义多个唯一约束。

正文

②数据库完整性是防止不合语义或不正确的数据进入数据库

DEFAULT约束

若在表中定义了默认值约束,用户在插入新的数据行时,如果该行没有指定数据,那么系统将默认值赋给该列,如果我们不设置默认值,系统默认为NULL。

在数据库管理系统中,保证数据库中的数据完整性是非常重要的。所谓数据完整性,就是指存储在数据库中数据的一致性和正确性。约束定义关于列中允许值的规则,是强制完整性的标准机制。使用约束优先于使用触发器、规则和默认值。查询优化器也使用约束定义生成高性能的查询执行计划。

③完整性体现了是否真实地反映现实世界

CHECK约束

CHECK 约束用于限制列中的值的范围。

Check约束通过逻辑表达式来判断数据的有效性,用来限制输入一列或多列的值的范围,在列中更新数据时,所要输入的内容必须满足Check约束的条件,否则将无法正确输入。

如果对单个列定义 CHECK
约束,那么该列只允许特定的值。

如果对一个表定义 CHECK
约束,那么此约束会在特定的列中对值进行限制。

SQL
Server联机丛书中,将数据完整性解释如下:“存储在数据库中的所有数据值均正确的状态。如果数据库中存储有不正确的数据值,则该数据库称为已丧失数据完整性。”强制数据完整性可确保数据库中的数据质量。

 

约束优缺点

优点:

1、保持数据库完整性。

2、保证列中数据的唯一性。

3、插入、更新、删除时严格的范围校验机制。

4、快速。

5、可以引用其他列。

6、在命令执行前发生。

7、遵循ANSI标准。

缺点:

1、插入、更新、删除时需要校验规则比较麻烦。

2、必须对每个表重新定义。

3、不能引用其他表。

4、不能绑定到数据类型。

 

例如,如果输入了 employee_id 值为 123
的职员,那么该数据库不应允许其他职员使用同一 ID 值。如果计划将
employee_rating 列的值范围设定为从 1 到 5,则数据库不应接受
6。如果表有一 dept_id
列,该列存储职员的部门编号,则数据库应只允许接受公司中的有效部门编号。

例: 

数据完整性分类

学生的年龄必须是整数,取值范围为14-29;

在SQL
Server中,根据数据完整新措施所作用的数据库对象和范围不同,可以将数据完整性分为以下几种。

学生的性别只能是男或女;

  实体完整性
  域完整性
  引用完整性
  用户定义完整性

学生的学号一定是唯一的;

SQL
Server联机丛书中指明:“对表进行计划有两个重要步骤:标识列的有效值和确定如何强制列中的数据完整性。”

学生所在的系必须是学校开设的系;

实体完整性

 

实体完整性简单的说,就是将表中的每一行看作一个实体。实体完整性要求表的标示符列或主键的完整性。可以通过建立唯一索引、PRIMARY
KEY约束、UNIQUE约束,以及列的IDENTITY属性来实施实体完整性。

 

域完整性

2.DBMS维护数据库完整性的机制:

域完整性是指给定列的输入有效性。要求表中指定列的数据具有正确的数据类型、格式和有效的数据范围。强制域有效性的方法有:限制类型(通过数据类型)、格式(通过
CHECK 约束和规则)或可能值的范围。域完整性通过 FOREIGN KEY 约束、CHECK
约束、DEFAULT 定义、NOT NULL 定义和规则来实现。

 

引用完整性

①提供定义完整性约束条件的机制

引用完整性又称参照完整性。引用完整性维持被参照表和参照表之间的数据一致性,他通过主键(PRIMARY
KEY)约束和外键(FOREIGN
KEY)约束来实现。引用完整性确保键值在所有表中一致。这样的一致性要求不能引用不存在的值,如果键值更改了,那么在整个数据库中,对该键值的所有引用要进行一致的更改。在被参照表中,当其主键值被其他表所参照时,该行不能被删除也不允许改变。在参照表中,不允许参照不存在的主键值。

  
DBMS应提供定义数据库完整性约束条件,并把它们存入数据库中。

强制引用完整性时,SQL Server 禁止用户进行下列操作:

②提供完整性检查的方法

  当主表中没有关联的记录时,将记录添加到相关表中。
  更改主表中的值并导致相关表中的记录孤立。
  从主表中删除记录,但仍存在与该记录匹配的相关记录。

  
检查数据是否满足完整性约束条件的机制称为完整性检查。一般在INSERT、UPDATE、DELETE语句执行后开始检查。

例如,对于 pubs 数据库中的 sales 和 titles 表,引用完整性基于 sales
表中的外键 (title_id) 与 titles 表中的主键 (title_id) 之间的关系。

 

图片 1

3.违约处理

用户定义完整性

DBMS若发现用户的操作违背了完整性约束条件,就采取一定的动作以保证数据的完整性,如拒绝执行该操作,或级联执行其他操作。

用户定义完整性使您得以定义不属于其它任何完整性分类的特定业务规则。所有的完整性类型都支持用户定义完整性。

 

建立和使用约束的目的是保证数据的完整性,约束是SQL
Server强制实行的应用规则,他能够限制用户存放到表中数据的格式和可能值。约束作为数据库定义的一部分在CREATE
TABLE语句中声明,所以又称做声明完整性约束。约束独立于表结构,可以在不改变表结构情况下,通过ALTER
TABLE语句来添加或者删除。在删除一个表时,该表所带的所有约束定义也被随之删除。

图片 2

PRIMARY KEY约束

 

在数据库的每个表中,经常有通过一列或者多个列,唯一的标识表中的每一行。就好像我们平时使用的身份证,能够唯一的标识每个人一样。这样的一列或者多个列,被称为主键,通过主键,可以强制表的实体完整性。

 

每一个表中只有一个PRIMARY
KEY约束,更简单的说,他是通过建立唯一索引保证指定列的实体完整性。在使用PRIMARY
KEY约束时,该列的空值属性必须定义为NOT
NULL,也就是说拥有主键的那一列,不能为空。

二、缺省(默认值)和规则

由于PRIMARY
KEY约束确保唯一数据,所以经常用来定义标识列。标识列就是表中已经指派了标识属性的列。标识属性生成唯一数字。

 

有些刚刚开始接触SQL
Server编程的技术爱好者通常会有这样的疑问,为什么非要在一个表里面建立主键?其实答案是很明显的,建立主键不仅可以保证表内数据的完整性,而且在为表建立主键的同时,Microsoft
SQL Server能够通过为主键创建唯一索引强制数据的唯一性。

缺省和规则来源于由Sybase开发的S默认值QL
Server,在老版本的SQL Server或者升级版本中都有缺省和规则的使用。

当在查询中使用主键时,也就是利用主键所在的列作为关键字进行查询,该所因还可以用来对数据进行快速访问。如果在PRIMARY
KEY约束中未指定索引类型时,默认情况下所建立的索引为簇索引。该索引只能通过删除PRIMARY
KEY约束或其相关表的方法来删除,而不能使用DROP INDEX语句删除。当PRIMARY
KEY约束由另一张表的FOREIGN KEY约束引用时,不能删除PRIMARY
KEY约束;要删除它,必须先删除FOREIGN KEY约束。

缺省是为列提供数据的一种方式,如果用户进行INSERT操作时不为列输入数据,则使用缺省值。

通常建立一列约束时,我们称之为列级PRIMARY
KEY约束,应用于多列时,称之为表级PRIMARY KEY约束。

规则是当用户进行INSERT或uPDATE操作时,对输入列中的数据设定的取值范围,是实现域完整性的方式之一。

列级PRIMARY KEY约束的定义格式为:

 

[CONSTRAINT constraint_name]
 PRIMARY KEY [CLUSTERED | NONCLUSTERED]
 [WITH [FILLFACTOR = fillfactor]]
 [ON {filegroup | DEFAULT}]

缺省与规则有以下特点:

表级PRIMARY KEY约束定义风格为:

(1)缺省与规则是数据库对象,它们是独立于表和列而建立的。

[CONSTRAINT constraint_name]
 PRIMARY KEY [CLUSTERED | NONCLUSTERED]
  {(column[,…n])}
 [WITH [FILLFACTOR = fillfactor]]
 [ON {filegroup | DEFAULT}]

(2)缺省与规则建立后与列或数据类型产生关联,列和数据类型就具有了缺省与规则的属性。

在上面的PRIMARY KEY约束定义中,WITH子句设置为PRIMARY
KEY约束所建立索引的页面填充度,ON子句指出存储索引的数据库文件组名称。将索引文件和表数据文件分寸到数据库中位于不同硬盘驱动器的数据文件中,有利于减轻单个硬盘的负载。

(3)缺省与规则定义后,可以重复使用,可以绑定到多个列或数据类型上。

DEFAULT约束

(4)缺省与规则不随表同时调入内存,当用到时才被调入内存,这可能会使程序执行出现延时。

使用DEFAULT约束,如果用户在插入新行是没有显示为列提供数据,系统会将默认支赋给该列。例如,在一个表的payterms列中,可以让数据库服务器在用户没有输入时填上”???”或者”fill
in
later”。默认值约束所提供的默认值约束所提供的默认值可以为常量、函数、系统零进函数、空值(NULL)等等。零进函数包括CURRENT_TIMESTAMP、SYSTEM_USER、CURRENT_USER、USER和SESSION_USER等。默认值约束的定义格式为:

 

[CONSREAINT constraint_name]
 DEFAULT constant_expression

缺省和规则对象通常只在它所创建的数据库中有效,不是ANSI标准,一般不提倡使用。

其中,constraint_name参数指出所建立的默认值约束名称。Constant_expression表达式为列提供默认值。在使用默认约束是,还应该注意以下两点:

应尽可能使用约束,任何可以使用缺省与规则的地方都有可以使用约束。

1. 每列只能有一个默认约束。
2. 约束表达式不能参照表中的其他列和其他表、视图或存储过程。

 

CHECK约束

1.
缺省

CHECK约束的主要作用是限制输入到一列或多列中的可能值,从而保证SQL
Server数据库中数据的域完整性。例如,可以在建立用户使用库时,强制用户的密码在10位以上。每个标允许建立多个CHECK约束。在CHECK约束中可以包含搜索条件,但不能包含子查询。

在SQL
Server中,有两种使用默认值的方法:

同样,我们可以为表中的每个列建立约束,每个列可以拥有多个CHECK约束,但是如果使用CREATE
TABLE语句,只能为每个列建立一个CHECK约束。如果CHECK约束被应用于多列时,他必须被定义为表级CHECK约束。

 

在表达式中,可以输入搜索条件,条件中可以包括AND或者OR一类的连接词。列级CHECK约束只能参照被约束列,而表级CHECK约束则只能参照表中列,它不能参照其他表中列。

①在创建表时,指定默认值。

例如,我们使用下面的语句在TB_CHECK_CONSTRAINT表中新加入一列ZIP_CODE及其相应的CHECK约束:

  用SQL
Server Management
Studio创建表时在设计表时指定默认值,可以在输入字段名称后,设定该字段的默认值。

ALTER Table TB_CHECK_CONSTRAINT
ADD
 ZIP_CODE char(6) null
 CONSTRAINT CH_ZIP_CODE check
  (ZIP_CODE like ‘[0-9] [0-9] [0-9] [0-9] [0-9] [0-9]’)

  或使用CREATE
TABLE语句中的DEFAULT子句指定默认值。

同样,我们可以使用CHECK或NOCHECK来打开或者关闭某个约束。例如,下面的语句将关闭上面建立的CH_ZIP_CODE约束:

 

ALTER Table TB_CHECK_CONSTRAINT
 NOCHECK CONSTRAINT CH_ZIP_CODE

②使用CREATE
DEFAULT语句创建默认对象后,使用存储过程sp_bindefault将该默认对象绑定到列上。

如果希望使用编辑器来建立约束关系,需要在数据库关系图中,右击包含约束的表,然后从快捷菜单中选择“约束”命令。或者可以将包含约束的表打开表设计器,在表设计器中右击,然后选择“约束”命令。

 

图片 3

默认值对象是单独存储的,删除表的时候,DEFAULT约束会自动删除,但是默认值对象不会被删除。

UNIQUE约束

创建默认值对象后,需要将其绑定到某列或者用户自定义的数据类型上。

该约束应用于表中的非主键列,UNIQUE约束保证一列或者多列的试题完整性,确保这些猎不会输入重复的值。例如,表中UserName列为主键,但是其中还包括身份证号码列,由于所有身份证号码不可能出现重复,所以可以在此列上建立UNIQUE约束,确保不会输入重复的身份证号码。

 

它与PRIMARY
KEY约束的不同之处在于,UNIQUE约束可以建立在多个列之上,而PRIMARY
KEY约束在一个表中只能有一个。

主要操作:

建立UNIQUE约束,可以使用如下办法:

 

1.

①创建默认值对象

在数据库关系图中右击将包含约束的表,然后从快捷菜单中选择”属性”命令。

②绑定默认值对象

 

③解除默认值对象的绑定

-或-

④查看默认值对象

为将包含约束的表打开表设计器,在表设计器中右击,然后从快捷菜单中选择”属性”命令。

⑤删除默认值对象

2.

 

选择”索引/键”选项卡。

 

3.

①创建默认对象

选择”新建”命令。系统分配的名称出现在”索引名”框中。

可以使用CREATE
DEFAULT语句创建默认对象。其语法格式如下:

4.

CREATE
DEFAULT default  AS constant_expression 

在”列名”下展开列的列表,选择要将约束附加到的列。若要将约束附加到多个列,在后续行中选择其它的列。

例如: create default d_grade as 1 

5.

 

选择”创建 UNIQUE”复选框。

②绑定默认对象

6.

默认对象创建后不能使用,必须首先将其绑定到某列或者用户自定义的数据类型上。其使用语法格式如下:

选择”约束”选项。

sp_bindefault
[@defname = ] ‘default’, [@objname
= ] ‘object_name’   [,
[@futureonly = ] ‘futureonly_flag’]

Copyright @ 2015-2019 ca88 版权所有
网站地图xml地图