为什么t-sql允许我违反使用UDP的检查约束? [英] why is t-sql allowing me to violate a check constraint that uses a UDP?

问题描述

我在数据库中的一个表上有一个检查约束.我对检查的理解是，它设置了一个逻辑条件，该条件对于表中的记录必须为真.

I have a check constraint on a table in my DB. My understanding of a check is it sets a logical condition that must be true for records in a table.

USE [myDB]
GO

ALTER TABLE [dbo].[myTable]  WITH CHECK ADD  CONSTRAINT [oneProgramPerTest] CHECK  (([dbo].[howManyProgPerTest]([TestId])<(2)))
GO

ALTER TABLE [dbo].[myTable] CHECK CONSTRAINT [oneProgramPerTest]
GO

但是我能够对打破约束的表进行更新.更新后，此查询返回9条记录:

But I am able to do an update to the table that breaks the constraint. After the update, this query returns 9 records:

select COUNT (*) from myDB.dbo.myTable where myDB.[dbo].[howManyProgPerTest](testID)>1

可能会发生什么?

推荐答案

因此请注意在检查约束中使用UDF. 此博客文章描述了您的问题.总结:

Beware using UDFs in check constraints for this reason. This blog post describes your issue. To summarize:

只要您INSERT进入 桌子.但是，如果您更新一行并只为其中的某些设置了otherColumn 从0到1的行，则不会检查检查约束.

(A UDF) will on the surface do its job, as long as you INSERT into the table. But if you update a row and only set the otherColumn for some row from 0 to 1, then the check constraint will not be checked.

优化器足够聪明，可以了解更新不会更改我们在CHECK约束中引用的任何内容，所以为什么 麻烦检查约束吗?

The optimizer is smart enough to understand that the update doesn't change anything that we refer to in our CHECK constraint, so why bother checking the constraint?

最终结果是约束没有按照我们希望的那样做 做. 改用触发器(或其他方法).

End result here is that the constraint doesn't do what we want it to do. Use a trigger instead (or some other method).

(添加了强调)

这篇关于为什么t-sql允许我违反使用UDP的检查约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！