确保在每个特定列值的表中都不会创建重复记录 [英] Ensuring no duplicate records being created in a table per particular column value

问题描述

请考虑以下情形
假设数据库表中有三个字段

Consider the following scenario Suppose there are three fields in a database table

------------------------------------------
PrmiaryKey | Column A | Column B
-----------------------------------------

我需要强制执行，因为B列中的值对于A列应该具有唯一值

I need to enforce that for values in Column B should have unique values for Column A

示例

Col B   Col A

12         13        (OK)    
14         15        (OK)      
15         16        (OK)    
12         13        (OK)    
15         16        (OK)    
14         17        (not OK)

由于先前值14在B列下的值为15，因此它的值不应与15不同。我需要从数据库端强制执行此行为。是否有一个特殊的约束，我需要解决这个问题

Since value 14 previously have value 15 under Column B. So it should not have a different value than 15. I need to enforce this behavior from database side. Is it there a particular constraint that i need to have to sort this out

在此先感谢。

推荐答案

您可以尝试放入检查约束：

CREATE FUNCTION dbo.CheckDuplicateEntry(@id INT, @colA INT, @colB INT)
RETURNS INT
AS
BEGIN
    DECLARE @ret INT

    IF EXISTS(SELECT 1 FROM Test WHERE ID <> @id AND ColB = @colB) BEGIN
        IF EXISTS(SELECT 1 FROM Test WHERE ID <> @id AND ColB = @colB AND ColA = @colA) BEGIN 
            SET @ret =  1
        END
        ELSE BEGIN
            SET @ret = 0
        END
    END
    ELSE BEGIN
        SET @ret = 1
    END

    RETURN @ret
END

---

---

ALTER TABLE [TableName]
  ADD CONSTRAINT ConstCheckDuplicateEntry 
  CHECK (dbo.CheckDuplicateEntry(ID, ColA, ColB) = 1);

这篇关于确保在每个特定列值的表中都不会创建重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！