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?

Possible duplicate of [How do I specify unique constraint for multiple columns in MySQL?](http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql) — Richard, Nov 22 '16 at 13:59
I don't think you can define such a constraint on the table itself. One possible workaround would be to set UNIQUE(id_user1, id_user2) and always save the smaller id in id_user1 and the greater id in id_user2 — jussius, Nov 22 '16 at 14:30
Interesting, but to me, just because ID 2 has marked ID 3 as a friend, doesn't mean that ID 3 has marked ID 2 as a friend; but maybe this table only gets populated when both have accepted. I guess it depends on the meaning. If you think of following a user... user 2 follows 3 but 3 doesn't follow 2 but maybe that's not the meaning... In some of these cases, sequence would matter. but if you're just trying to show friendship that both have accepted, then you're right you only need 1 pattern. But later if one could unfriend and the other keep, then you need both. — xQbert, Nov 22 '16 at 14:38

score 2 · Answer 1 · answered Nov 22 '16 at 14:31

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.

score 2 · Answer 2 · answered Nov 22 '16 at 14:59

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)
);

answered Nov 22 '16 at 14:59

Nick Barnes

19,816
3
51
63

This looks like a good way to do it, but I get an SQL syntax error when running your code. I can't find where it is, any hints? – ypicard Nov 23 '16 at 09:49
@ypicard: I'm guessing you're on an older version of MySQL. Judging by the [docs](https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html), this will only work in 5.7.8+. In 5.7.6 and 5.7.7, it should work if you change `VIRTUAL` to `STORED`. For anything earlier than that, you'd probably need to fill in these columns with triggers. – Nick Barnes Nov 23 '16 at 10:11

score -1 · Answer 3 · answered Nov 22 '16 at 13:56

As far as the SQL language is concerned, you're describing a CHECK constraint - a restriction on values that isn't one of the simple, defined constraints like a foreign key or uniqueness.

But I don't think mysql honors CHECK constraints. The usual advice I've seen is to use a trigger that throws an exception if any insert or update tries to violate your condition.

How to make a tuple be unique in SQL table?

3 Answers3