Ensuring no duplicate records being created in a t

Question

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

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

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

Example

Col B   Col A

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

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

Thanks in Advance.

Answer 1

You could try putting a CHECK CONSTRAINT:

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);

Answer 2

A constraint operates on the fields within a row. The only "constraint" that takes into account values in other rows is a unique constraint...and that really just creates an unique index. There is no way to enforce your requirement using just constraints. You have to use a trigger.

create trigger TableTrigger_AIU
   on  Table
   after insert, update
as begin
declare
    @Dups   int;
    set NoCount on;

    select  @Dups = count(*)
    from    Inserted i
    join    Table t
        on  t.ColA = i.ColA
        and t.ColB <> i.ColB;

    if @Dups > 0 begin
        raise holy_blue_well_you_know;
    end;
end;