How to avoid MySQL composite primary key permutations?

CREATE TABLE Friends_Relations(
    buddy_id VARCHAR(255) NOT NULL,
    mate_id VARCHAR(255) NOT NULL,
    PRIMARY KEY (buddy_id, mate_id),
    FOREIGN KEY (buddy_id) REFERENCES Users(id) ON DELETE CASCADE,
    FOREIGN KEY (mate_id) REFERENCES Users(id) ON DELETE CASCADE
);

There’s this mutual friendship relation where (friend_A, friend_B) is the same as (friend_B, friend_A).

I’ve tried adding a unique key, but it was to no avail:

ALTER TABLE Friends_Relation ADD UNIQUE KEY (mate_id, buddy_id);

Is there a way to avoid these permutations ?

Answer

You can do:

create table friends_relations (
  buddy_id varchar(255) not null,
  mate_id varchar(255) not null,
  constraint uq1 unique (
    (least(buddy_id, mate_id)), (greatest(buddy_id, mate_id))
  ),
  primary key (buddy_id, mate_id)
);

Then if it won’t accept symmetric rows:

insert into friends_relations (buddy_id, mate_id) values (456, 123);
insert into friends_relations (buddy_id, mate_id) values (123, 456); -- fails

See running example at db<>fiddle.

There’s also another trick. What you can alternatively do is to enforce buddy_id < mate_id. This, however, will restrict the way you insert data. For example you can do:

CREATE TABLE Friends_Relations (
    buddy_id VARCHAR(255) NOT NULL,
    mate_id VARCHAR(255) NOT NULL,
    PRIMARY KEY (buddy_id, mate_id),
    FOREIGN KEY (buddy_id) REFERENCES Users(id) ON DELETE CASCADE,
    FOREIGN KEY (mate_id) REFERENCES Users(id) ON DELETE CASCADE,
    constraint ck1 CHECK (buddy_id < mate_id) -- added constraint
);

Then, when you insert:

insert into Friends_Relations (buddy_id, mate_id) values (123, 456); -- succeeds

insert into Friends_Relations (buddy_id, mate_id) values (456, 123); -- fails