在SQL Server中使用检查约束来验证数据
什么是检查约束?
检查约束是一个规则，它确认一个SQL Server表中某条记录中的数据可接受的字段值。检查约束帮助执行域完整性。域完整性定义了一个数据库表中字段的有效值。检查约束可以验证一个单独字段或一些字段的域完整性。你对一个单独的字段可以有多个检查完整性。如果被插入或更新的数据违反了一个检查约束，那么数据库引擎将不允许这个插入或更新的操作发生。
检查约束包括一个逻辑表达式，用以确认什么是有效的表达式。逻辑表达式可能是一个单独的表达式比如“Salary < 200000.00”，或多个表达式，比如“RentalDate > GETDATE() and RentalDate < DATEADD(YY,1,GETDATE())”。如果一个逻辑表达式的一个检查约束返回了FALSE值，那么这个检查约束将限制这个表中数据插入或更新。对于逻辑表达式返回的是FALSE以外的值的所有记录将通过这个检查约束并允许记录被更新或插入。为了这个记录能够被插入或更新，与给定INSERT或UPDATE语句相关的所有数据都不能进行检查约束失败(返回一个FALSE值)。检查约束可以在字段级别或表级别被创建。
在一个CREATE TABLE语句上创建检查约束
创建检查约束的一个方法是在表创建时进行。这是一个简单的CREATE TABLE脚本，它创建了一个单独的检查约束：
| CREATE TABLE dbo.Payroll
( PositionID INT, SalaryType nvarchar(10), Salary decimal(9,2) CHECK (Salary < 150000.00) ); |
这里我有一个CHECK 子句，它与Salary字段关联。这是一个字段级别的约束。如果你创建一个字段级别的约束，那么你在你的检查约束的逻辑表达式中只能使用这个字段名称。这个检查约束只允许Salary字段低于$150,000.00。当我的表创建之后，这个CHECK约束也将被创建，并被赋予一个系统生成的约束名称。如果你想在一个CREATE TABLE操作期间命名你的检查约束，那么你可以运行下面的代码：
| CREATE TABLE dbo.Payroll
( ID int PRIMARY KEY, PositionID INT, SalaryType nvarchar(10), Salary decimal(9,2) CONSTRAINT CK_Payroll_Salary CHECK (Salary < 150000.00) ); |
上面的每个例子都创建了一个单独的条件字段检查约束。一个检查约束表达式可以有多个条件。下面是一个例子，它显示了一个有多个条件的检查约束：
| CREATE TABLE dbo.Payroll
( PositionID INT, SalaryType nvarchar(10), Salary decimal(9,2) CONSTRAINT CK_Payroll_Salary CHECK (Salary > 10.00 and Salary < 150000.00) ); |
记住，为了让SQL Server 拒绝一条记录，这个检查约束的逻辑表达式的最终结果需要是FALSE。因此，在这个例子中，这个检查约束验证了一个Salary大于$10.00并小于$150,000.00。当这个检查约束中的这些条件中的任何一个为FALSE，那么在Payroll表中将不会插入或更新一条记录，并会显示一个错误信息。
如果你想创建一个表级别的检查约束，那么你可以运行下面的代码：
| CREATE TABLE dbo.Payroll
( ID int PRIMARY KEY, PositionID INT, Salary decimal(9,2), SalaryType nvarchar(10), CHECK (Salary > 10.00 and Salary < 150000.00) ); |
这里我创建了一个单独的表约束，它检查Salary字段，但是它不是关联到字段，而是关联到这个表。在这个检查约束中我可以使用我的表中的任何字段，只要我想，因为它是一个表检查约束，但是在我的例子中，我只使用了Salary字段。注意，这个CHECK子句将使得SQL Server生成一个检查约束名称，因为我没有给这个约束名称。
在一个现有的表上创建一个检查约束
有时，在你设计和创建了一个表后，你想对一个表添加一个检查约束。这可以通过使用ALTER TABLE 语句来完成。下面是这么做的例子：
| ALTER TABLE dbo.Payroll
WITH NOCHECK ADD CONSTRAINT CK_Payroll_SalaryType CHECK (SalaryType in (''''Hourly'''',''''Monthly'''',''''Annual'''')); |
在这里我创建了一个检查约束，它将检查我的Payroll表中的所有记录在SalaryType字段中只有“Hourly”、“ Monthly”或“Annual”值。我还用一个名称命名了我的检查约束，在这个例子中是“CK_Payroll_SalaryType”。
你可以使用一个单独的ALTER TABLE语句来一次添加多个检查约束到你的表中。下面是这么做的例子：
| ALTER TABLE dbo.Payroll
WITH NOCHECK ADD CONSTRAINT CK_Payroll_SalaryType CHECK (SalaryType in (''''Hourly'''',''''Monthly'''',''''Annual'''')), CONSTRAINT CK_Payroll_Salary CHECK (Salary > 10.00 and Salary < 150000.00); |
在这里我已经使用一个单独的ADD CONSTRAINT子句添加了SalaryType和Salary约束。
创建多个字段约束
你没有必要创建只能检查一个单独字段的值的约束。你可以创建一次检查多个字段中的值的约束。例如，如果我想创建一个检查上面所创建的Salary和SalaryType约束的单独约束，那么可以使用下面的代码：
| ALTER TABLE dbo.Payroll WITH NOCHECK
ADD CONSTRAINT CK_Payroll_Salary_N_SalaryType CHECK (SalaryType in (''''Hourly'''',''''Monthly'''',''''Annual'''') and Salary > 10.00 and Salary < 150000.00); |
这个单独约束所做的事情和上面两个约束一样。记住，当你这么做时，要了解是SalaryType 、Salary 还是两个字段都违反了你的检查约束就更很困难了。.
前一个例子的另一个方法是在不只一个的字段中使用这个值，从而确定某一指定字段值是否是有效的。例如，假设我想确保当我输入一个“Hourly” SalaryType时，我希望Salary小于$100.00，或输入“Monthly” SalaryType时，Salary不超过$10,000，而当输入一个“Annual” SalaryType时任何Salary数值都可以。要实现这个约束，我使用下面的ADD CONSTRAINT子句：
| ALTER TABLE dbo.Payroll WITH NOCHECK
ADD CONSTRAINT CK_Payroll_SalaryType_Based_On_Salary CHECK ((SalaryType = ''''Hourly'''' and Salary < 100.00) or (SalaryType = ''''Monthly'''' and Salary < 10000.00) or (SalaryType = ''''Annual'''')); |
这里，我将多个字段条件一起使用并使用一个“or”条件来分隔它们，所以我的检查约束可以验证每个不同的SalaryType的Salary数量。
了解当值为Null时会发生什么
回忆下在本篇文章的“什么是检查约束”章节中所说的关于记录是怎样只在检查约束得出结果FALSE时才认为检查约束没有通过。因此，字段中的NULL值可能允许你输入数据到你的数据库中，而这并不满足你的需求。
假设我在我的payroll表上只有CK_Paryroll_SalaryType 检查约束。这里需要回忆的就是这个检查约束：
| ALTER TABLE dbo.Payroll
WITH NOCHECK ADD CONSTRAINT CK_Payroll_SalaryType CHECK (SalaryType in (''''Hourly'''',''''Monthly'''',''''Annual'''')); |
现在你运行下面的INSERT语句：
INSERT INTO dbo.Payroll values (1, 1, ''''Hourly'''',25.00);
INSERT INTO dbo.Payroll values (2, 2, NULL, 25.00);
INSERT INTO dbo.Payroll values (3, 3, ''''Horly'''',25.00);
会发生什么?会只有第一个INSERT语句起作用吗?第二个和第三个INSERT语句会怎样?它们都违反CK_Payroll_SalaryType吗?结果是只有第三个INSERT语句失败了。它失败是由于SalaryType输入错误，它不是“Hourly”、“ Monthly”或“Annual”。为什么第二个INSERT没有得出false呢?显然，“NULL”不是SalaryTypes的有效值。第二个INSERT语句起作用的原因是在第二个INSERT语句运行时，CK_Payroll_SalaryType约束不是FALSE。因此，数据库引擎插入了这条记录。那么为什么会出现这种情况呢?这是因为NULL值用在比较操作中时，它被当作UNKNOWN。因为UNKNOWN不是FALSE，所以没有违反检查约束。因此，当你编写你的检查约束时，你需要对需要拒绝包含NULL值的地方很谨慎。另一个编写上面的约束从而使得拒绝SalaryType值为NULL的方法是如下编写你的约束：
| ALTER TABLE dbo.Payroll
WITH NOCHECK ADD CONSTRAINT CK_Payroll_SalaryType CHECK ((SalaryType in (''''Hourly'''',''''Monthly'''',''''Annual'''')) and SalaryType is not NULL); |
另一个选择是使SalaryType成为一个非NULL字段。如果你这么做就不会违反检查约束，但是反过来你会得到一个错误信息显示你不能插入一个NULL值到你的表中。
通过检查约束进行数据验证
通过使用检查约束，你可以确保你的数据库只包含通过了约束的数据。这使得你可以让数据库引擎控制你的数据验证。这么做将使得你的应用程序不需要在每个你希望插入一条记录或更新一条记录到一个表中的地方都写数据验证规则的代码。检查约束是执行数据验证的一个简洁方法。
