How to make a tuple be unique in SQL table?

Question

my problem is pretty simple but I can't put words on it. I've got a user table with ids for each of them.

I want to make a friendship table, but I don't want to be able to have duplicate records. Meaning not this:

id_user1 | id_user2
---------|----------
   2     |    3
   3     |    2

Am I clear enough?

For the moment I have this for my table creation:

CREATE TABLE User(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    surname VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE Friends(
    id_user1 INT NOT NULL,
    id_user2 INT NOT NULL,
    PRIMARY KEY(id_user1, id_user2),
    FOREIGN KEY (id_user1) REFERENCES User(id),
    FOREIGN KEY (id_user2) REFERENCES User(id),
);

[EDIT 1:] Maybe the best practice is to save each record twice? Both ways?

Answer 1

You can do the check for duplicate in a trigger, but best is to ensure that e.g. the id with lower value goes to id_user1 and the other to id_user2. You can either do that in your application or with the help of trigger, e.g. you create primary key constraint on your table and :

DROP TRIGGER IF EXISTS yourtable_bi;

delimiter //

CREATE TRIGGER yourtable_bi
before insert on yourtable 
for each row 
begin 
declare x int; 
if (new.id_user1>new.id_user2) 
then 
set x:=NEW.id_user1; 
set NEW.id_user1:=NEW.id_user2; 
set NEW.id_user2:=x; 
end if; 
end//
delimiter ;

So, then after

insert into yourtable values (1,2);

if you try

insert into yourtable values (2,1);

it will try to insert the same record, and will fail because of the primary key constraint.

You also can do that for update if it is allowed on your table.

Answer 2

You can put a unique constraint on (LEAST(id1,id2), GREATEST(id1,id2)):

CREATE TABLE friends (
    id1 INT,
    id2 INT,
    min_id INT AS (LEAST(id1,id2)) VIRTUAL,
    max_id INT AS (GREATEST(id1,id2)) VIRTUAL,
    UNIQUE KEY (min_id,max_id)
);