CODEWITHSUNDEEP
sqlsql-server
Walid
Walid

Reputation: 3

How to make two columns unique As a combination?

I have table friends, defined as

UserId int PrimaryKey

OtherUserId int PrimaryKey

Currently, i have the following data

UserId OtherUserId

1 2

I do not want duplicate enteries like

UserId OtherUserId

2 1

how can i implement this using SQL Server?

Upvotes: 0

Views: 559

Answers (2)

Archie Azares
Archie Azares

Reputation: 135

Your question is a little broad. Please be advised that you can only have one primary key per table since it serves as the identification. As for the data, you could try to use auto increment and not null to avoid duplicate answers.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can do this by adding a computed column and then a unique index:

alter table friends UserId_least as (case when UserId < OtherUserId then UserId else OtherUserId end);

alter table friends UserId_greatest as (case when UserId < OtherUserId then OtherUserId else UserId end);

create unique index unq_friends_least_greatest
    on friends(UserId_least, UserId_greatest);

Upvotes: 1

Related Questions