How to make a tuple unique in a SQL table?

Question

my problem is quite simple, but I can’t put words into it. I have a user table with identifiers for each of them.

I want to create a friendship table, but I do not want to have duplicate entries. This does not mean:

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

Am I clear enough?

At the moment, I have this for creating a table:

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 it's best to save each entry twice? Both ways?

Answer 1

, , , , id_user1, - id_user2. , , . :

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 ;

,

insert into yourtable values (1,2);

insert into yourtable values (2,1);

- .

, .

Answer 2

SQL, CHECK - , , , .

, mysql CHECK. , , , , - .

Answer 3

You can put a unique restriction 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)
);