Unique constraint on two fields, and their opposite

Question

I have a data structure, where I have to store pairs of elements. Each pair has exactly 2 values in it, so we are employing a table, with the fields(leftvalue, rightvalue....). These pairs should be unique, and they are considered the same, if the keys are changed.

Example: (Fruit, Apple) is the same as (Apple, Fruit).

If it is possible in an efficient way, I would put a database constraint on the fields, but not at any cost - performance is more important.

We are using MSSQL server 2008 currently, but an update is possible.

Is there an efficient way of achieving this?

Worth mentioning that any constraint will have a cost performance on the insert. Although your retrieval queries maybe quicker, depending on the constraint, so it might even out... — BJury, Apr 16 '14 at 08:08
If left value and right value are interchangeable (which is usually implied if `(left,right)` and `(right,left)` are "equal"), can you not apply a constraint to the table such that `left` is always `<` `right`? — Damien_The_Unbeliever, Apr 16 '14 at 08:09
By performance, do you mean insert performance? It is impossbile to index or contstrain data without effecting insert performance, there is always some cost. — Jodrell, Apr 16 '14 at 08:09
The primary goal is to achieve read performance, but inserts should not suffer too much. Damien's solution looks like a good idea. — Robert, Apr 16 '14 at 08:23
@Robert read performance would never be hurt on a constraint. If anything it can only get quicker. — BJury, Apr 16 '14 at 08:32
Yes, the point of the constraint would be better indexing :) - and enforcing correct values of course — Robert, Apr 16 '14 at 08:39

score 4 · Accepted Answer · answered Apr 16 '14 at 08:13

4

Two solutions, both really about changing the problem into an easier one. I'd usually prefer the T1 solution if forcing a change on consumers is acceptable:

create table dbo.T1 (
    Lft int not null,
    Rgt int not null,
    constraint CK_T1 CHECK (Lft < Rgt),
    constraint UQ_T1 UNIQUE (Lft,Rgt)
)
go
create table dbo.T2 (
    Lft int not null,
    Rgt int not null
)
go
create view dbo.T2_DRI
with schemabinding
as
    select
        CASE WHEN Lft<Rgt THEN Lft ELSE Rgt END as Lft,
        CASE WHEN Lft<Rgt THEN Rgt ELSE Lft END as Rgt
    from dbo.T2
go
create unique clustered index IX_T2_DRI on dbo.T2_DRI(Lft,Rgt)
go

In both cases, neither T1 nor T2 can contain duplicate values in the Lft,Rgt pairs.

answered Apr 16 '14 at 08:13

Damien_The_Unbeliever

234,701
27
340
448

The T1 solution sounds good, I'll wait a little more for other answers, but it looks like the answer I'll accept :) – Robert Apr 16 '14 at 08:19
@Robert if you're able to change your logic so that they are always entered in the right order (ie Apple, Fruit and never Fruit, Apple) then do this. If you can't consider a sproc to insert so you can do this. If you can't do that, then use one of the other more 'hacky' solutions! – BJury Apr 16 '14 at 08:25
@Robert, would it not be necessary to store if `Left` and `Right` were reversed or, is "handedness" of the pair actually irrelavent? – Jodrell Apr 16 '14 at 08:49
In the current case, "handedness" is irrelevant. There is a case, where it would be relevant tough, but for now, it is not. – Robert Apr 16 '14 at 09:00
@Robert, in that case, my answer adds nothing to this margianlly earlier answer +1. – Jodrell Apr 16 '14 at 09:04
Yours is a good answer, just not for the current scenario. When I need the order, I'll remember it ^^ – Robert Apr 16 '14 at 09:07

Jodrell · Answer 2 · 2014-04-16T09:33:09.613

If you always store the values in order but store the direction in another column,

CREATE TABLE [Pairs]
(
    [A] NVarChar(MAX) NOT NULL,
    [B] NVarChar(MAX) NOT NULL,
    [DirectionAB] Bit NOT NULL,
    CONSTRAINT [PK_Pairs] PRIMARY KEY ([A],[B]) 
)

You can acheive exaclty what you want with one clustered index, and optimize your lookups too.

So when I insert the pair 'Apple', 'Fruit' I'd do,

INSERT [Pairs] VALUES ('Apple', 'Friut', 1);

Nice and easy. Then I insert 'Fruit', 'Apple',

INSERT [Pairs] VALUES ('Apple', 'Fruit', 0); -- 0 becuase order is reversed.

The insert fails because this is a primary key violation. To further illustrate, the pair 'Coconuts', 'Bananas' would be stored as

INSERT [Pairs] VALUES ('Bananas', 'Coconuts', 0);

For additional lookup performance, I'd add the index

CREATE NONCLUSTERED INDEX [IX_Pairs_Reverse] ON [Pairs] ([B], [A]);

If you can't control inserts to the table, it may be necessary to ensure that [A] and [B] are inserted correctly.

CONSTRAINT [CK_Pairs_ALessThanB] CHECK ([A] < [B])

But this may be an unnecessary performance hit, depending on how controlled your inserts are.

score 1 · Answer 3 · answered Apr 16 '14 at 08:15

1

One way would be to create a computed column that combines the two values and put a unique constraint upon it:

create table #test (
    a varchar(10) not null, 
    b varchar(10) not null, 
    both as case when a > b then a + ':' + b else b + ':' + a end persisted unique nonclustered
    )

so

insert #test
select 'apple', 'fruit'
insert #test
select 'fruit', 'apple'

Gives

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'UQ__#test_____55252CB631EC6D26'. Cannot insert duplicate key in object 'dbo.#test'.
The statement has been terminated.

answered Apr 16 '14 at 08:15

BJury

2,526
3
16
27

Nice but you also have to ensure that your separator doesn't exist in the domain of your values, or that other occurrences are in some way escaped. This won't allow us to insert both of the pairs (`apple:z`,`fruit`) and (`apple`,`z:fruit`), despite all four values being distinct. – Damien_The_Unbeliever Apr 16 '14 at 08:24
For sure, it's not the greatest. It could work if you knew the underlying data types, using padding and whatnot, but yeah... To be fair if you were doing it properly, you'd enforce an ordering! – BJury Apr 16 '14 at 08:30

score 1 · Answer 4 · edited May 23 '17 at 10:33

1

Unique constraint on two/more fields is possible but on their opposite no...

SQL Server 2005 Unique constraint on two columns

Unique constraint on multiple columns

How do I apply unique constraint on two columns SQL Server?

http://www.w3schools.com/sql/sql_unique.asp

edited May 23 '17 at 10:33

Community

1
1

answered Apr 16 '14 at 08:16

Zolfaghari

1,259
1
15
14

Unique constraint on two fields, and their opposite

4 Answers4

Linked