Unique combination of 2 columns, order independent

user_11452447 0
Viewed 195

I have a table like this, which represents friendships between persons in a social network

CREATE TABLE friendships
(
    requester INT,
    acceptor INT
);

Now I want unique combination of (requester, acceptor), order independent. Normal unique constraint, UNIQUE (requester, acceptor), is not enough for this case because if there is already, for example, one row with the combination (1, 2) the combination (2, 1) is still allowed, and I don't want that.

How to solve this? I'm using MySQL.

create-table unique-constraint database-design mysql sql
2 Answers

Starting in MySQL 8.0.13, you can create an index on an expression. That allows you to do:

create unique index unq_friendships_requestor_acceptor on
     friendships( (least(requestor, acceptor)) , (greatest(requestor, acceptor)) );

You should also declare these two columns as NOT NULL.

For older versions. GMB's answer is appropriate.

user_11440350

A simple method is:

create table friendships (
    requester int,
    acceptor int,
    check(requester < acceptor),
    unique (requester, acceptor)
);

With this setup, you just cannot insert type (2, 1). You need to make it (1, 2), and the unique constraint prevents duplicates.

If you want something more flexible, another solution is to use generated columns, that are available in recent MySQL versions:

create table friendships (
    requester int,
    acceptor int,
    v1 int generated always as (least(requester, acceptor)),
    v2 int generated always as (greatest(requester, acceptor)),
    unique (v1, v2)
);

This lets you insert either (2, 1) or (1, 2); once of the tuples is insterted, the other cannot.

Demo on DB Fiddlde:

insert into friendships(requester, acceptor) values(2, 1);
-- ok

insert into friendships(requester, acceptor) values(1, 2);
-- ERROR: Duplicate entry '1-2' for key 'friendships.v1'
user_106767160
Related