检查具有多个输入参数的约束 UDF 不起作用

2021-09-10 00:00:00 tsql sql-server

我正在尝试对表实施检查约束，这样就无法在存在两列(Int_1"和Int_2")已经具有我们想要的值的记录的情况下插入记录试图插入例如:

I'm trying to implement a check constraint on a table such that records can't be inserted where there exists a record for which two of the columns ("Int_1" and "Int_2") already have the value we're trying to insert E.g.:

ID     Name     Int_1     Int_2
1      Dave       1         2

将 (2, Steve, 2, 2) 插入上表是可以的，就像 (3, Mike, 1, 3) 一样，但不允许插入已经存在 Int_1 AND Int_2 的值，即 (4,Stuart, 1, 2) 是非法的.

Inserting (2, Steve, 2, 2) into the table above would be okay, as would (3, Mike, 1, 3), but inserting values where Int_1 AND Int_2 already exist is not allowed, i.e. (4, Stuart, 1, 2) is illegal.

我认为这样定义我的表格会起作用:

I thought defining my table thus would work:

CREATE TABLE [Table](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](255) NOT NULL,
    [Int_1] [int] NOT NULL,
    [Int_2] [int] NOT NULL,
    CONSTRAINT [chk_Stuff] CHECK (dbo.chk_Ints(Int_1, Int_2)=1))

其中:dbo.chk_Ints 的定义:

where: dbo.chk_Ints is defined:

CREATE FUNCTION [dbo].[chk_Ints](@Int_1 int,@Int_2 int)
RETURNS int
AS
BEGIN

DECLARE @Result int

IF NOT EXISTS (SELECT * FROM [Table] WHERE Int_1 = @Int_1 AND Int_2 = @Int_2)
BEGIN
    SET @Result = 1
END
ELSE 
BEGIN
    SET @Result = 0
END

RETURN @Result
END

GO

当使用上面的组合时，如果我尝试插入任何记录，SQL 会告诉我我违反了检查约束.我可以从表中删除所有行并尝试插入第一条记录，SQL 告诉我我已经打破了我的约束，这是我不可能做到的！

When using the combo above, if I try to insert any record whatsoever, SQL tells me I've broken my check constraint. I can remove all rows from the table and try to insert a first record, and SQL tells me I've broken my constraint, which I can't possibly have done!

我已经在互联网上搜索了一段时间，现在正在寻找 UDF 依赖于多个表列的检查约束示例，但无济于事.关于为什么这可能不起作用的任何想法?

I've scoured the internet for quite a while now looking for examples of check constraints where the UDF depends on multiple table columns, but to no avail. Any ideas as to why this might not work?

提前致谢:)

推荐答案

是的，这可能看起来令人费解，直到您意识到发生了什么，此时它变得非常明显.

Yes, this may seem baffling until you realise what's going on, at which point it becomes quite obvious.

为您尝试插入的行中的值调用该函数.但是想想函数是如何被调用的.调用它的是一个检查约束.

The function is called for the values that are in the row you are trying to insert. But think of how the function is being called. It is a check constraint that calls it.

接下来，考虑传递的参数.他们来自哪里?根据定义，检查约束从 Int_1 和 Int_2 列中获取它们.

Next, think of the parameters being passed. Where do they come from? According to the definition, the check constraint takes them from columns Int_1 and Int_2.

因此，它将它们作为列值传递.但是列值必须属于一行.在这种情况下是哪一行?您要插入的那个！

So, it passes them as column values. But column values must belong to a row. Which row is it in this case? The one you are trying to insert!

这意味着此时您的行已插入，只有交易仍在等待中.然而，该行在表中这一事实至关重要，因为这就是函数通过 1 结果发现和报告的内容.

That means your row is inserted at this point, only the transaction is still pending. And yet the fact that the row is in the table is crucial, because that's what the function finds and reports on with the 1 result.

因此，发生的事情是这样的:

Thus, what's happening is this:

  • 您正在尝试插入一行，

  • you are trying to insert a row,

函数看到该行并说具有给定参数的行已经存在，

the function sees that row and says that a row with the given parameters already exists,

检查约束通过禁止插入相应地反应"，

the check constraint "reacts" accordingly by prohibiting the insert,

插入回滚.

当然，既然您意识到了这一切，就很容易想出不同的检查重复项的逻辑.基本上，您的函数应该记住"新行已经在表中，因此它应该尝试确定它在表中的存在是否违反了您想要建立的任何规则.例如，您可以计算与给定参数匹配的行数，并查看结果是否不大于 1:

Of course, now that you realise all that, it is easy to come up with a different logic of checking for duplicates. Basically, your function should "keep in mind" that the new row is already in the table, and so it should try and determine whether its presence in the table violates any rules that you want to establish. You could, for instance, count the rows matching the given parameters and see if the result is not greater than 1:

IF (SELECT COUNT(*) FROM [Table] WHERE Int_1 = @Int_1 AND Int_2 = @Int_2) < 2
BEGIN
    SET @Result = 1
END
ELSE 
BEGIN
    SET @Result = 0
END

但是，在此作业的检查约束中使用函数的整个想法远不如仅在两列上添加唯一约束，如由@a_horse_with_no_name 建议.这样做:

However, the entire idea of using a function in a check constraint for this job is very much inferior to just adding a unique constraint on the two columns, as suggested by @a_horse_with_no_name. Do this:

ALTER TABLE [Table]
ADD CONSTRAINT UQ_Table_Int1_Int2 UNIQUE (Int_1, Int_2);

你可以忘记重复.

相关文章