How to create unique combination of sql server database columns

Question

I'm trying to create relationships table which connects users.

I have the following structure:

1. id
2. fromuserid
3. touserid
4. state (none, send, accepted, rejected)

I want to have constraint that any pair from/to should be unique. I mean there shouldn't be an entry like

1, **123,124**, send 
2, **124,123**, send

First of all I created constraint that from != to. It works perfect:

CONSTRAINT [CK__FromUserId_ToUserId] CHECK (FromUserId != ToUserId),

Then I tried to create unique index, constraint etc, nothing helped.

CONSTRAINT [CK_FromUserId_ToUserId_Combination] UNIQUE (FromUserId, ToUserId)

or

CREATE UNIQUE NONCLUSTERED INDEX [IX_FromUserId_ToUserId_Combination] 
  ON USERLINKS (FromUserId, ToUserId)

Both works fine to reject records like:

1, 123,124,send 
2, 123,124,send

But none works to reject records like:

1, 123,124,send 
2, 124,123,send

Please advise

Gordon Linoff · Accepted Answer · 2014-02-10 02:00:57Z

1

You can do this with computed columns. Add the "leastId" and "greatestId" columns and then create the index on them:

alter table relationships
    add leastId as (case when fromuserid < touserid then fromuserid else touserid end);

alter table relationships
    add greatestId as (case when fromuserid < touserid then touserid else fromuserid end);

create unique index relatinonships_leastId_greastestId_Combination
    on relationships(leastId, greatestId, combination);

edited Feb 10, 2014 at 2:00

answered Feb 10, 2014 at 1:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eric.Y.Fan · Accepted Answer · 2014-02-10 01:58:58Z

1

Use "INSTEAD OF INSERT Triggers"

http://technet.microsoft.com/en-us/library/ms175089(v=sql.105).aspx

answered Feb 10, 2014 at 1:58

Eric.Y.Fan

2361 silver badge5 bronze badges

1 Comment

Ilmari Karonen Over a year ago

While your link might technically answer the question, it would be preferable if you could paraphrase the essential parts of the answer in your own words here. That way, the information will be right here, and will remain available even if the linked page is moved some day.

Collectives™ on Stack Overflow

How to create unique combination of sql server database columns

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related