Unique Constraint On Two Fields, And Their Opposite

February 04, 2024 Post a Comment

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....). The

Solution 1:

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:

createtable dbo.T1 (
    Lft intnotnull,
    Rgt intnotnull,
    constraint CK_T1 CHECK (Lft < Rgt),
    constraint UQ_T1 UNIQUE (Lft,Rgt)
)
go
createtable dbo.T2 (
    Lft intnotnull,
    Rgt intnotnull
)
go
createview dbo.T2_DRI
with schemabinding
asselectCASEWHEN Lft<Rgt THEN Lft ELSE Rgt ENDas Lft,
        CASEWHEN Lft<Rgt THEN Rgt ELSE Lft ENDas Rgt
    from dbo.T2
go
createunique 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.

Solution 2:

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.

Solution 3:

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

createtable #test (
    a varchar(10) notnull, 
    b varchar(10) notnull, 
    bothascasewhen a > b then a +':'+ b else b +':'+ a end persisted unique nonclustered
    )

insert #testselect'apple', 'fruit'
insert #testselect'fruit', 'apple'

Gives

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