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

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值的错误消息。

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

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