How to implement uniqueness where the order of the fields does not matter

Asked 2012-03-14T16:56:58.863

I think the following example will explain the situation best. Let's say we have the following table structure:

-------------------------------------
Member1   int      NOT NULL (FK)(PK)
Member2   int      NOT NULL (FK)(PK)
-------------------------------------
Statust   char(1)  NOT NULL

Here are the table contents for the table:

Member1    Member2    Status
----------------------------
  100        105        A

My question is how do I implement uniqueness so that the following INSERT statement will FAIL based on that one row already in the table.

INSERT status_table (Member1,Member2,Status) VALUES(105,100,'D');

Basically, I'm trying to model a relationship between two members. The Status field is the same whether we have (100,105) or (105,100).

I know I could use a before_insert and before_update trigger to check the contents in the table. But I was wondering if there was a better way to do it... Should my database model be different...

Edit Report

3 Answers

If you can make sure that all applications/users store the members' IDs in least-to-greatest order (the least MemberID in Member1 and the greatest in Member2), then you could simply add a Check constraint:

ALTER TABLE Status_table
  ADD CONSTRAINT Status_table_Prevent_double_pairs
    CHECK (Member1 < Member2)

If you don't want to do that or you want that extra info to be stored (because you are storing (just an example) that "member 100 invited (liked, killed, whatever) member 150" and not vice versa), then you could use @Tegiri's approach, modified a little (multiplying two big enough integers would be an overflow problem otherwise):

CREATE TABLE Status_table
( Member1 INT NOT NULL
, Member2 INT NOT NULL
, Status CHAR(1) NOT NULL
, MemberOne  AS CASE WHEN Member1 < Member2 THEN Member1 ELSE Member2 END
          --- a computed column
, MemberTwo  AS CASE WHEN Member1 < Member2 THEN Member2 ELSE Member1 END
          --- and another one
, PRIMARY KEY (Member1, Member2)
, UNIQUE (MemberOne, MemberTwo)
, ...                                    --- FOREIGN KEY details, etc 
) ;

answered 2012-03-14T18:04:30.410

One way to avoid a trigger try a UNIQUE computed column on Member1 and Member2:

create table test (Member1 int not null, Member2 int not null, Status char(1)
, bc as abs(binary_checksum(Member1))+abs(binary_checksum(Member2)) PERSISTED UNIQUE)

INSERT INTO test values(123, 456, 'A'); --succeeds
INSERT INTO test values(123, 789, 'B'); --succeeds
INSERT INTO test values(456, 123, 'D'); --fails with the following error:
--Msg 2627, Level 14, State 1, Line 1
--Violation of UNIQUE KEY constraint 'UQ__test__3213B1084A8F946C'. Cannot insert duplicate key in object 'dbo.test'

answered 2012-03-14T17:23:25.247

Instead of trying to have the table itself enforce this particular business logic, would it be better to have it encapsulated in a stored procedure? You certainly gain more flexibility in enforcing unique relationships between two members.

answered 2012-03-14T18:34:53.490

How to implement uniqueness where the order of the fields does not matter

3 Answers

Your Answer