Unique Pairs in Either Order: Blocking A-B When B-A Exists

Unique pairs need a rule that treats A-B and B-A as the same row. A plain unique index cannot do that. It sees (1, 2) and (2, 1) as two different pairs.

The friendship that was stored twice

Think of a table of friendships, or related products, or matching accounts. User 1 is a friend of user 2. Later, user 2 adds user 1 back. If direction means nothing, you now have the same friendship twice.

The fix is simple. Store both ids as the user sent them. Then add two computed columns: the smaller id and the larger id. Put one unique index on that canonical pair. The order the user typed no longer matters.

DROP TABLE IF EXISTS dbo.PairDemo;

CREATE TABLE dbo.PairDemo (
    FirstId  int NOT NULL,
    SecondId int NOT NULL,
    LowId  AS CASE WHEN FirstId < SecondId THEN FirstId ELSE SecondId END PERSISTED,
    HighId AS CASE WHEN FirstId > SecondId THEN FirstId ELSE SecondId END PERSISTED,
    CHECK (FirstId <> SecondId));

CREATE UNIQUE INDEX UX_PairDemo ON dbo.PairDemo (LowId, HighId);

The CHECK constraint refuses a pair of an id with itself. Both ids are NOT NULL, so no half-filled pair can sneak in. The demo creates one table and drops it at the end.

Try the reversed pair

Insert the pair (1, 2). Then try (2, 1). I wrap the second insert in TRY and CATCH so you can see the error number instead of a red message.

INSERT dbo.PairDemo (FirstId, SecondId) VALUES (1, 2);

BEGIN TRY
    INSERT dbo.PairDemo (FirstId, SecondId) VALUES (2, 1);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ReversedPairError;
END CATCH;

The first insert works. The second fails with error 2601, a duplicate key in a unique index. The index sees the same canonical pair, 1 and 2, and says no.

Self pairs and extreme ids

Next, a pair of an id with itself, and a pair at the very edges of the int range. The last query shows what ended up in the table.

BEGIN TRY
    INSERT dbo.PairDemo (FirstId, SecondId) VALUES (3, 3);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS SelfPairError;
END CATCH;

INSERT dbo.PairDemo (FirstId, SecondId) VALUES (-2147483648, 2147483647);

SELECT FirstId, SecondId, LowId, HighId
FROM dbo.PairDemo
ORDER BY LowId, HighId;

The self pair fails with error 547, the CHECK violation. The extreme pair goes in without an overflow, because CASE only compares the ids and never does arithmetic on them. Two rows remain, the pair (1, 2) exactly as submitted and the extreme pair.

Why not use a sum or a product?

You will see a shortcut online: make the unique key the sum of the two ids. It looks clever. It is wrong, because different pairs share a sum.

SELECT a, b, a + b AS PairSum
FROM (VALUES (1, 4), (2, 3)) AS v (a, b);

Both pairs add up to 5. A unique key on the sum would reject a perfectly valid pair. Products collide the same way. Big ids can also overflow the int. The smaller-and-larger approach has none of these problems.

Before you add it to old data

If the table already has data, the unique index will fail to build when reversed duplicates exist. Find them first. Grouping by the canonical pair shows every relationship stored more than once.

CREATE TABLE #ExistingPairs (FirstId int, SecondId int);
INSERT #ExistingPairs VALUES (1, 2), (2, 1), (5, 6);

SELECT CASE WHEN FirstId < SecondId THEN FirstId ELSE SecondId END AS LowId,
       CASE WHEN FirstId > SecondId THEN FirstId ELSE SecondId END AS HighId,
       COUNT(*) AS TimesStored
FROM #ExistingPairs
GROUP BY CASE WHEN FirstId < SecondId THEN FirstId ELSE SecondId END,
         CASE WHEN FirstId > SecondId THEN FirstId ELSE SecondId END
HAVING COUNT(*) > 1;

Only the pair 1 and 2 appears, stored twice. Decide which row survives, and keep any status or date the other row carries.

One warning. Do this only when direction truly means nothing. A money transfer from A to B is not the same as B to A, and both should be allowed.

Also keep the unique index even if your application checks first. Two sessions can both pass the check and both insert. The index is the one rule every path must obey.

DROP TABLE IF EXISTS #ExistingPairs;
DROP TABLE IF EXISTS dbo.PairDemo;

Let the database enforce what your application only hopes for.

A reversed pair is not a new relationship, it is the same pair in another order.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.