問題描述

我正在嘗試對表實施檢查約束，這樣就無法在存在兩列(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);

你可以忘記重復.

這篇關于檢查具有多個輸入參數的約束 UDF 不起作用的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！