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