使用CHECK约束执行业务规则

【 tulaoshi.com - 编程语言 】

　　如果你不熟悉CHECK约束，那么习惯上它们通过限制插入到一个字段中的值来执行字段域完整性。我发现CHECK约束很不幸地被用于大多数的数据模型中。在大多数的案例中，我看到使用触发器来执行业务规则，而这本来是应该用一个CHECK约束来执行的。我个人使用CHECK而不是触发器是基于很多个原因的。首先，我不需要浪费时间来写专门的代码。另外，CHECK约束是在数据库修改通过数据库引擎完成之前执行的，然而触发器是事故发生后检查的。使用触发器延长了一个事务的生命，如果检测到一个回滚事件那么代价可能是昂贵的。

　　SQL Server允许你定义检查单独字段完整性的字段级别的CHECK约束。此外，SQL Server允许你使用一个表级别CHECK约束来检查多个字段的值。虽然它们被归类为表级别约束，但是这些CHECK约束的类型实际上是在行级别上检查的。最后，CHECK约束通过检查一个定义好的情形来看它的值为TRUE还是FALSE。

　　让我们来看看一些例子。我将建立一张示例表EMPLOYEE。作为表定义的一部分，我们将执行一个公司规则：在我们的系统中没有一个员工被允许赚取超过$100,000或者少于$30,000。

　　　CREATE TABLE DBO.EMPLOYEE
　　(
　　EMPLOYEEID INT IDENTITY(1,1) NOT NULL,
　　FIRSTNAME VARCHAR(50) NOT NULL,
　　LASTNAME VARCHAR(50) NOT NULL,
　　IS_MANAGER BIT NULL,
　　SALARY MONEY NOT NULL,
　　BONUSPCT FLOAT NOT NULL
　　)
　　GO
　　ALTER TABLE DBO.EMPLOYEE
　　ADD CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEEID)
　　GO
　　ALTER TABLE DBO.EMPLOYEE
　　ADD CONSTRAINT DF_EMPLOYEE_BONUSPCT DEFAULT 0.00 FOR BONUSPCT
　　GO
　　ALTER TABLE DBO.EMPLOYEE
　　ADD CONSTRAINT CK_EMPLOYEE_SALARY CHECK (SALARY BETWEEN 30000 AND 100000)
　　GO
　　EXEC SP_HELPCONSTRAINT EMPLOYEE
　　GO

　　查看我们表中的约束，我们看到我们简单的字段级别约束已经定义好了：

图一

　　尝试把值插入到SALARY字段超出了我们定义的范围，这也被我们的数据库引擎成功捕捉到了。

　　INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME, SALARY)

　　SELECT 'GEORGE', 'WASHINGTON', 110000

　　GO

图二

　　到目前为止这些都进展得很顺利。但是，这里还有另外一个公司规则需要我们来执行。我们公司的管理人员可以赚取任何奖金比例但是他们是唯一被允许赚取他们工资5%或者更高比例奖金的员工。非管理人员只能赚取低于5%的任何比例奖金。让我们来试着通过一个表级别的CHECK约束来执行这个公司规则：

　　　ALTER TABLE DBO.EMPLOYEE
　　ADD CONSTRAINT CK_EMPLOYEE_BONUSPCT
　　CHECK (IS_MANAGER = 1 AND BONUSPCT = 5.00)
　　GO
　　INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME, IS_MANAGER, SALARY, BONUSPCT)
　　SELECT 'GEORGE', 'WASHINGTON', 1, 100000, 5.00
　　GO
　　INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME, IS_MANAGER, SALARY, BONUSPCT)
　　SELECT 'BEN', 'FRANKLIN', 0, 75000, 2.50
　　GO

图三

　　然后发生了什么事呢？员工Washington成功插入但是Franklin插入失败。那么，正如它被定义的，CHECK约束迫使所有的员工成为一个管理人员并且具有一个大于或等于5%的奖金比例。如果所有的员工必须是具有大于或者等于5%的奖金比例的管理人员，那么这就没有什么问题。然而，这不是我们想要执行的业务规则。我们想要确保如果一个员工不是管理人员，那么他就不能执行查果5%奖金的界限。因此我们应该怎样使CHECK约束有条件地执行完整性呢？你可能想使用一个触发器。尽管如此，正如我在开篇提到的，CHECK是用来检查TRUE/FALSE条件的。因此，你可以平衡这两种方法来产生一个可以执行条件检查的CHECK约束。让我们通过使用这些原则来重新创建我们的CHECK并试着重新插入员工Franklin。

　　　ALTER TABLE DBO.EMPLOYEE
　　DROP CONSTRAINT CK_EMPLOYEE_BONUSPCT
　　GO
　　ALTER TABLE DBO.EMPLOYEE
　　ADD CONSTRAINT CK_EMPLOYEE_BONUSPCT
　　CHECK (CASE WHEN IS_MANAGER 1 AND BONUSPCT = 5.00 THEN 1 ELSE 0 END = 0)
　　GO
　　INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME, IS_MANAGER, SALARY, BONUSPCT)
　　SELECT 'BEN', 'FRANKLIN', 0, 75000, 2.50
　　GO

　　现在试着使用正确的CHECK约束定义来插入Franklin，结果成功了。

图四

　　现在让我们尝试插入一个新的员工Jefferson。

　　 INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME, IS_MANAGER, SALARY, BONUSPCT)
　　SELECT 'THOMAS', 'JEFFERSON', NULL, 80000, 7.50
　　GO

图五

　　等待一秒钟，Jefferson没有被标记成一个管理人员但是他的引擎允许他的奖金比例变成7.5%。为什么会发生这种情况呢？再次回顾CHECK约束是用于检查TRUE/FALSE条件的。看看我们的模式，IS_MANAGER被声明成NULL。由于这个字段中的任何空值都会导致CHECK的IS_MANAGER条件等同于不知道的并且导致CASE表达求取我们的默认布尔值为零。当使用等于零的字段时，这是一个需要明白的地方。有很多种方法可以纠正这点。其中一种是把IS_MANAGER标记定义为NOT NULL从而把NULL值转化为零。如果你不能改变这个模式，那么另外的方法就是把CASE重新写到NULL IS_MANAGER 标记的账户中。下面是其中一种重写CASE的方法。(你可能有自己的更改;我的版本是为了达到例证的目的)。

　　　TRUNCATE TABLE DBO.EMPLOYEE
　　GO
　　ALTER TABLE DBO.EMPLOYEE
　　DROP CONSTRAINT CK_EMPLOYEE_BONUSPCT
　　GO
　　ALTER TABLE DBO.EMPLOYEE
　　ADD CONSTRAINT CK_EMPLOYEE_BONUSPCT
　　CHECK (CASE WHEN IS_MANAGER IS NULL AND BONUSPCT = 5.00 THEN 1
　　WHEN IS_MANAGER 1 AND BONUSPCT = 5.00 THEN 1
　　ELSE 0 END = 0)
　　GO
　　INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME, IS_MANAGER, SALARY, BONUSPCT)
　　SELECT 'JAMES', 'MADISON', NULL, 60000, 5.50
　　GO

　　执行一个新员工Madison,的脚本，我们可以看到现在这个引擎成功捕捉了这个条件。

图六

　　现在你可能在想“我打赌我能写一个纯量函数来读取我整个表并且执行一个真实的表级别CHECK以便检查超过的数目和总量”。你是对的，你可以做到。但是正如SQL Server MVP David Portas所提出的，这里也有风险。

　　正如你看到的，CHECK约束是执行单个和多个字段域完整性而不需要写专门的触发器或者存储过程代码的强有力的方法。