您所在的位置:IT专家堂 > 数据库 > 使用检查约束验证SQL Server中的数据

使用检查约束验证SQL Server中的数据

2009-04-15 13:21 黄永兵 译 51CTO 字号:T | T
一键收藏,随时查看,分享好友!

构建应用程序最关键的目标之一是确保所有进入数据库的数据都要符合你的业务规则,数据校验是应用程序的关键部分,确保你的数据满足业务分析师制定的需求。本文将给大家介绍如何使用检查约束验证SQL Server中的数据。

AD:

【51CTO独家特稿】有许多校验数据和业务规则是否匹配的方法,在应用程序代码中可以强制实施数据校验,也可以由数据库引擎执行数据校验。根据校验的方法不同,规则需求将确定你的应用程序该如何正确地以及该在哪里校验数据,本文将向你介绍如何使用数据库“检查约束”校验SQLServer中的数据。

什么是检查约束?

检查约束是一个识别SQLServer表中每行可接受的列值的规则,检查约束帮助实施域的完整性,域完整性定义了数据库表中列的有效值,检查

约束可以验证单列的域完整性,也可以验证多列的域完整性,在单个列上可以有多个检查约束,如果插入或更新的数据违反了检查约束,数据

库引擎将暂时停止INSERT和UPDATE操作。

检查约束由逻辑表达式构成,逻辑表达式可能是单个表达式,如“Salary<200000.00”,也可能是多个表达式,如“RentalDate>GETDATE

()andRentalDate

中的数据,检查约束是基于列的,因此,即便表中某列的检查约束没有通过,也不会影响到表中其它列的INSERT和UPDATE操作,检查约束可以在列级创建,也可以在表级创建。

在CREATETABLE语句中创建检查约束

创建检查约束的一个方法就是在创建表的时候创建,下面是一个简单的CREATETABLE脚本,它包含了创建一个检查约束的代码:

CREATETABLEdbo.Payroll

(

IDintPRIMARYKEY,

PositionIDINT,

SalaryTypenvarchar(10),

Salarydecimal(9,2)

CHECK(Salary<150000.00)

);

这里的CHECK子句关联了Salary列,这是一个列级的约束,如果你创建了一个列级约束,你只能在检查约束的逻辑表达式中使用列名,这里的检查约束列就只允许Salary列的值小于150000。创建这个表时也会创建CHECK约束,约束名由系统自动生成,如果你想在CREATETABLE操作时命名你的检查约束,代码就可以变成下面这样:

CREATETABLEdbo.Payroll

(

IDintPRIMARYKEY,

PositionIDINT,

SalaryTypenvarchar(10),

Salarydecimal(9,2)

CONSTRAINTCK_Payroll_SalaryCHECK(Salary<150000.00)

);

这里我将检查约束命名为CK_Payroll_Salary了。

上面的例子都仅在单个列上创建了检查约束,而且也只有一个条件,其实检查约束表达式可以包括多个条件,下面就是一个包含多个条件的检查约束:

CREATETABLEdbo.Payroll

(

IDintPRIMARYKEY,

PositionIDINT,

SalaryTypenvarchar(10),

Salarydecimal(9,2)

CONSTRAINTCK_Payroll_Salary

CHECK(Salary>10.00andSalary<150000.00)

);

如果要让SQLServer拒绝一条记录,那在检查约束逻辑表达式的最终输出中需要计算为FALSE,因此,在这个例子中,检查约束会验证Salary大于10且小于150000,这两个条件中任意一个检查结果返回FLASE,都会直接拒绝Payroll表中对行的INSERT或UPDATE请求,提示也会显示一条错误消息。

如果你想创建一个表级检查约束,你可以使用下面的代码:

CREATETABLEdbo.Payroll

(

IDintPRIMARYKEY,

PositionIDINT,

Salarydecimal(9,2),

SalaryTypenvarchar(10),

CHECK(Salary>10.00andSalary<150000.00)

);

在这里我创建了单个表级约束,检查Salary列,但可以使用表中的任意列,因为这是一个表级检查,注意CHECK子句将会引起SQLServer生成一个检查约束名,因为我没有手动为其命名。

在现有表上创建检查约束

有时,在你设计和创建好表后,你可能想要在表上放一个检查约束,你可以使用ALTERTABLE语句来实现,下面是一个例子:

ALTERTABLEdbo.Payroll

WITHNOCHECKADDCONSTRAINTCK_Payroll_SalaryType

CHECK(SalaryTypein('Hourly','Monthly','Annual'));

我在这里的创建检查约束将会检查Payroll表中SalaryType列的值为“Hourly”,“Monthly”或“Annual”的所有记录,我还给这个检查约束起了一个名字,叫做“CK_Payroll_SalaryType”。

你也可以在一个ALTERTABLE语句中给表添加多个检查约束,下面就是这样一个例子:

ALTERTABLEdbo.Payroll

WITHNOCHECKADDCONSTRAINTCK_Payroll_SalaryType

CHECK(SalaryTypein('Hourly','Monthly','Annual')),

CONSTRAINTCK_Payroll_Salary

CHECK(Salary>10.00andSalary<150000.00);

在这里我在一条ADDCONSTRAINT子句中为SalaryType和Salary这两列同时增加了检查约束。

创建多列约束

不用在每个列上都创建约束,相反,可以在多个列上同时创建一个约束来检查这些列的值,例如,如果我想创建单个约束来检查Salary和SalaryType,那我可能使用如下的代码:

ALTERTABLEdbo.PayrollWITHNOCHECK

ADDCONSTRAINTCK_Payroll_Salary_N_SalaryType

CHECK(SalaryTypein('Hourly','Monthly','Annual')

andSalary>10.00andSalary<150000.00);

这个约束和前面的两个约束完成的事情是一样的,但你要记住,这样做有一个不好的后果,那就是最终在理解究竟是SalaryType列,还是Salary列,或者这两列违反了你的检查约束时可能比较困难。

这样做之后还有一个让人烦恼的是不止使用一列来判断某个特定列的值是否有效,例如,假设我想要确保在输入HourlySalaryType时,我想要Salary小于100,或者在输入MonthlySalaryType时,Salary小于10000,当输入AnnualSalaryType时,Salary合计是准确的。为了实现这个约束条件,我使用下面的ADDCONSTRAINT子句:

ALTERTABLEdbo.PayrollWITHNOCHECK

ADDCONSTRAINTCK_Payroll_SalaryType_Based_On_Salary

CHECK((SalaryType='Hourly'andSalary<100.00)or

(SalaryType='Monthly'andSalary<10000.00)or

(SalaryType='Annual'));

在这里我将多列条件集中在一起了,又用or条件将它们进行分离,这样我的检查约束就可以验证每个不同的SalaryType的Salary值了。

理解遇到空值时会发生什么

回顾一下我在本文的第一小节“什么是检查约束”中讲到的“当检查约束的条件表达式返回一个FALSE值时,记录不能INSERT和UPDATE”,因为如此,空值可能让进入数据库的数据并不符合你的要求。例如,假设在payroll表上只有一个CK_Paryroll_SalaryType检查约束,只需要刷新检查约束所在内存即可:

ALTERTABLEdbo.Payroll

WITHNOCHECKADDCONSTRAINTCK_Payroll_SalaryType

CHECK(SalaryTypein('Hourly','Monthly','Annual'));

现在,如果你执行下面的INSERT语句:

INSERTINTOdbo.Payrollvalues(1,1,'Hourly',25.00);

INSERTINTOdbo.Payrollvalues(2,2,NULL,25.00);

INSERTINTOdbo.Payrollvalues(3,3,'Horly',25.00);

你认为将会发生什么?只有第一条INSERT语句会起作用吗?第二条和第三条INSERT将会怎么样?它们都会违反CK_Payroll_SalaryType吗?结果是只有第三条INSERT语句会失败,它之所以失败是因为SalaryType被打乱了,不再仅仅是“Hourly”,“Monthly”或“Annual”了,那为什么第二个INSERT语句没有返回FALSE呢?很明显,NULL(空值)也不是有效的SalaryType,第二条INSERT语句能够工作的原因是它运行时CK_Payroll_SalaryType约束没有返回FALSE值,所以数据库引擎就插入了第二条记录。

为什么会这样呢?因为NULL(空值)在比较操作中时会返回UNKNOWN,因为UNKNOWN并不等价于FLASE,故没有违反检查约束。因此,你在写检查约束时要当心,你可能想要排除掉包含NULL(空值)的值。上面的例子如果想要排除掉NULL(空值),那代码要做如下的改动:

ALTERTABLEdbo.Payroll

WITHNOCHECKADDCONSTRAINTCK_Payroll_SalaryType

CHECK((SalaryTypein('Hourly','Monthly','Annual'))

andSalaryTypeisnotNULL);

另一个选择是将SalaryType列设置为一个NOTNULL字段,这样之后就不用再创建一个检查约束了,但你会获得一个不能向表中插入NULL值的错误消息。

通过检查约束进行数据验证

使用检查约束后,可以确保你的数据库只包括通过了检查的数据,这样允许你让数据库引擎控制你的数据有效性,这样做之后,你的应用程序就不用再进行数据验证了,否则程序代码中到处都穿插有数据校验的脚本,通过这种方法使数据验证工作更轻松,更简洁。

【编辑推荐】

  1. SQL Server 2005常见问题浅析
  2. SQL Server数据库内容替换方法
  3. SQL Server数据库优化经验总结

【责任编辑:彭凡TEL:(010)68476606】




分享到:

栏目热门

更多>>

  • 头条 使用检查约束验证SQL Server中的数据
  • 构建应用程序最关键的目标之一是确保所有进入数据库的数据都要符合你的业务规则,数据校验是应用程序的关键部分,确保你的数据满足业务分析师制定的需求。本文将给大家介绍如何使用检查约束验证SQL Server中的数据。
  1. 对话职业经理人阿朱:程序员转型期的职业选择
  2. 从检测到防护:全面打造网站安全

热点职位

更多>>

热点专题

更多>>

读书

软件设计师考试考前冲刺预测卷及考点解析
本书依据最新版《软件设计师考试大纲》的考核要求,深入研究了历年软件设计师考试试题的命题风格和试题结构,对考查的知识点进行

51CTO旗下网站

领先的IT技术网站 51CTO 中国首个CIO网站 CIOage 中国首家数字医疗网站 HC3i 51CTO学院 区块链第一聚合媒体 zhijiapro