How to prevent logical duplicates in a unary one to many SQL relationship table

In a non directional unary relationship what would be the best option to prevent logical duplicates?

E.G.

Main table

ID
--
1
2

Relational table

ID1    ID2
-----------
1      2
2      1

Entry two in the relational table is already logically represented by the first entry as the relationship is non directional

Answer

In many cases, having both rows in the table is very handy for queries. That said, if you only want one, there here are two options.

First, insist that id1 < id2 and that the pair be unique:

alter table relations add constraint check (id1 < id2);
alter table relations add constraint unique (id1, id2);

This might have some unintended consequences. You are not able to insert (2, 1) into the table.

A second approach is to create a function-based unique index. Not all databases support this directly, but there is often similar syntax available. Most databases support least() and greatest(), so:

create unique index unq_id1_id2
    on (least(id1, id2), greatest(id1, id2));

In SQL Server, you can do this with computed columns:

alter table relations add least_id1_id2 as
    (case when id1 < id2 then id1 else id2 end) persisted;

alter table relations add greatest_id1_id2 as
    (case when id1 < id2 then id2 else id1 end) persisted;

create unique index unq_relations_id1_id2 on relations(least_id1_id2, greatest_id1_id2);

Here is a db<>fiddle.