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?

in the 4th ID Contact1 and Contact2 is similar to 1st ID. – Nildarar May 13 '12 at 14:32 — Nildarar, May 13 '12 at 14:32
Ok. What does contact1 and contact2 represent? – Mikael Eriksson May 13 '12 at 14:39 — Mikael Eriksson, May 13 '12 at 14:39
Are the a FK's from another table? – Mikael Eriksson May 13 '12 at 14:40 — Mikael Eriksson, May 13 '12 at 14:40
yes, they are from Contacts table ... – Nildarar May 13 '12 at 14:42 — Nildarar, May 13 '12 at 14:42
Use a trigger to check the condition – Eranga May 13 '12 at 14:42 — Eranga, May 13 '12 at 14:42
how can i do this using unique index key? – Nildarar May 13 '12 at 14:49 — Nildarar, May 13 '12 at 14:49

score 1 · Answer 1 · answered May 13 '12 at 14:50

1

You can use a unique key in combination with a check constraint that makes you have the lower value in Contact1.

create table Relation
(
  ID int identity primary key,
  Contact1 int not null,
  Contact2 int not null,
  unique (Contact1, Contact2),
  check (Contact1 < Contact2)
)

answered May 13 '12 at 14:50

Mikael Eriksson

136,425
22
210
281

your solution is true but not the best, because user fill data and (contact1 < contact2) not always return true ! – Nildarar May 13 '12 at 14:55
That could be taken care of by you in code before you insert the rows to the table. No need to bother the user with that. – Mikael Eriksson May 13 '12 at 14:56

score 1 · Accepted Answer · answered May 13 '12 at 15:34

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.

score 0 · Answer 3 · answered May 13 '12 at 16:50

What logic/rule are you modeling? Without knowing the answer to this it's hard to know exactly what the problem is.

That being said, it looks like your table is denormalized and may make applying constraints complicated. If you're trying to simply enforce "a contact is unique to an id", then take out the Contact 2 column and put a unique constraint on Contact 1

Unique index key in multiple columns

3 Answers3