SQL Constraints Question

Question

In Sql Server 2005, I have a table with two integer columns, call them Id1 and Id2. I need them to be unique with in the table (easy enough with a unique index that spans both columns). I also need them to be unique in the table if the values are transposed between the two columns.

For example, SELECT * FROM MyTable returns

Id1   Id2
---------
2     4
5     8
7     2
4     2  <--- values transposed from the first row

How do I make a constraint that would prevent the last row from being entered into the table because they are the transposed values from the first row?

Answer 1

Create a check constraint that is bound to a user defined function that performs a select on the table to check for the transposed value.

Create table mytable(id1 int, id2 int)
go

create Function dbo.fx_Transposed(@id1 int, @id2 int)
returns bit as 
Begin
    Declare @Ret bit
    Set @ret = 0
    if exists(Select 1 from MyTable 
     Where id2 = @id1 and id1 = @id2)
    Set @ret = 1
    Return @ret
End
GO
Alter table mytable add
CONSTRAINT [CHK_TRANSPOSE] CHECK 
 (([dbo].[fx_Transposed]([ID1],[ID2])=(0)))
GO
Insert into mytable (id1, id2) values (1,2)

Insert into mytable (id1, id2) values (2,1)

Answer 2

Does the order between Id1 and Id2 have any significance? If not and this is a large table it may be more performent to enforce Id1 < Id2 in addition to your unique index. This would impact any process inputing records so it may not be feasible.

Answer 3

I would create a trigger that executed on insert and update would verify using a select statement that the values were unique in the two columns when transposed and when not transposed. This would allow you to reject any changes to the table that would break your rules for uniqueness at the point of the change and you could remove the unique index because it only enforces part of the requirement.