Unique index key in multiple columns

Question

I need to define unique index key in my table using SQL Server.

For example:

ID    Contact1     Contact2    RelationType
-------------------------------------------------------------------
1     1            2           sister       // 1 is the 2nd sister
2     3            4           brother      // 3 is the 4th brother
3     5            1           father       // 5 is the 1st father
4     2            1           sister       // bad entry !!!

Now, how can i prohibit inserting a wrong data like the 4th ID in the table above using an unique index key?

Answer 1

You could create a computed column that contains a character representation of the two numbers (smaller one first) combined then create a unique constraint on the computed column.

case when Contact1 > Contact2 then convert(varchar, Contact2) + convert(varchar, Contact1)
else convert(varchar, Contact1) + convert(varchar, Contact2)

This solution would allow entering 5, 3 but not 3, 5 IF 5, 3 already exists.