MySQL: unique combination of two columns

Question

This is my friends table

userid(int) | friendid(int) | confirmed(tiny int)
--------------------
1 | 2 | 1
2 | 1 | 1
1 | 3 | 1
3 | 1 | 1
1 | 5 | 0
2 | 4 | 1
4 | 2 | 1
4 | 1 | 1
1 | 4 | 1

I have about 200k rows like this.

I want to delete duplicates. For example if userid=1 and friendid=2 then I don't want the row userid=2, friendid=1.

I want unique combinations across the two columns. This means if (1, 2) is there then (2, 1) should not be there and should be deleted.

Then I can make a friend list query like this

select * from friends where (userid=1 or friendid=1) and confirmed=1

My question is how to delete these duplicate rows?

Please also help me understand how to find the mutual friends of 2 users (userid of them may reside in either userid column or friendid in friends table).

Answer 1

You can use a non-standard MySQL construction and delete from a self-join:

delete f1.* 
from friends f1 
join friends f2 
    on f1.userid = f2.friendid 
   and f1.friendid = f2.userid 
   and f1.userid > f2.userid;

when you have cleaned up the table, next step is to prevent this from happening again. The obvious choice would be a CHECK constraint guaranteeing that userid < friendid:

alter table friends add constraint ... check(userid < friendid)

and a unique constraint on these two columns:

alter table friends add constraint ... unique (userid, friendid);

Unfortunately CHECK constraint is not (AFAIK) supported by MySQL. You can however mimic this with generated columns:

alter table friends 
    add leastid int as (least(userid, friendid)) 
    persistent after friendid;
alter table friends 
    add greatestid int as (greatest(userid, friendid)) 
    persistent after friendid;

Now we can add a unique constraint on the generated columns:

alter table friends add constraint ak1_friends 
    unique (leastid, greatestid);

insert into friends (userid, friendid) values (1,2);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'ak1_friends'
insert into friends (userid, friendid) values (2,1);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'ak1_friends'

To find common friends among for example 3 and 5 you can use a query like:

select f1.userid, f1.friendid, f2.userid, f2.friendid 
from friends f1 
join friends f2 
    on f1.userid in (f2.userid, f2.friendid) 
    or f1.friendid in (f2.userid, f2.friendid) 
where 5 in (f1.userid, f1.friendid) 
  and 3 in (f2.userid, f2.friendid);

Answer 2

Maybe the following will be useful for you: create a new table, SELECT everything you need from the old table, and INSERT it into the new table. You could use a self/left join to see the "mutual" friendships side by side as it were (the "case ... when ... then" is only added to make this easier to see).

-- the "oldtable" contains your test data
    select 
      O1.userid, O1.friendid
    , O2.userid, O2.friendid
    , case 
        when O1.userid = O2.friendid and O1.friendid = O2.userid then
          concat( 'mutual -> ', O1.userid, O1.friendid )
        else 
          ' not mutual '
      end friendship
    from oldtable O1
      left join oldtable O2 
        on O1.userid = O2.friendid and O1.friendid = O2.userid ;

-- output
userid  friendid  userid  friendid  friendship
2        1        1        2        mutual -> 21
1        2        2        1        mutual -> 12
3        1        1        3        mutual -> 31
1        3        3        1        mutual -> 13
4        2        2        4        mutual -> 42
2        4        4        2        mutual -> 24
1        4        4        1        mutual -> 14
4        1        1        4        mutual -> 41
1        5        null     null     not mutual 

Let's create a new table.

create table newtable (
  userid int
, friendid int
, confirmed int 
, constraint ukey unique (userid, friendid) -- does not help much, but ..
);

The following queries are derived from the first one. For the duplicates, we pick one of each (the one with the lower userid), and insert them into the new table.

insert into newtable
select 
  O1.userid
, O1.friendid
, O1.confirmed
from oldtable O1
  join oldtable O2 
    on O1.userid = O2.friendid and O1.friendid = O2.userid
where O1.userid < O2.userid ;

The "remaining" rows ie frienships without "duplicates" can be retrieved via a left join (and INSERTed into the new table).

insert into newtable
select 
  O1.userid
, O1.friendid
, O1.confirmed
from oldtable O1
  left join oldtable O2 
    on O1.userid = O2.friendid and O1.friendid = O2.userid
where O2.userid is null  
  and O2.friendid is null;

Now, the new table contains:

mysql> select * from newtable;
+--------+----------+-----------+
| userid | friendid | confirmed |
+--------+----------+-----------+
|      1 |        2 |         1 |
|      1 |        3 |         1 |
|      2 |        4 |         1 |
|      1 |        4 |         1 |
|      1 |        5 |         0 |
+--------+----------+-----------+
5 rows in set (0.00 sec)

The query you have mentioned in your question returns the following rows:

select * from newtable where (userid=1 or friendid=1) and confirmed=1;

userid  friendid    confirmed
1       2           1
1       3           1
1       4           1

See dbfiddle here (also tested w/ MySQL 5.7)

Additional constraint

In order to prevent recording "duplicate friendships" in the future, I'd probably add a trigger that checks the existing records in the "newtable" before inserting. Something like ...

delimiter $
create trigger uniquefriendships before insert on newtable
for each row
begin
  if exists (
    select *
    from newtable
    where userid = new.friendid and friendid = new.userid 
  )
  then
    signal sqlstate '45000' set message_text = ' uniquefriendships trigger: friendship exists! ';
  end if;
end$
delimiter ;

Testing:

-- okay
mysql> insert into newtable (userid, friendid) values (100,200) ;
Query OK, 1 row affected (0.00 sec)

-- fails due to unique constraint (duplicate)
mysql> insert into newtable (userid, friendid) values (100,200) ;
ERROR 1062 (23000): Duplicate entry '100-200' for key 'ukey'

-- fails due to trigger (friendship 100-200 exists)
mysql> insert into newtable (userid, friendid) values (200,100) ;
ERROR 1644 (45000):  uniquefriendships trigger: friendship exists! 

Answer 3

The simplest way is to DELETE from JOIN:

DELETE z.*
  FROM friends AS w
  JOIN friends AS z -- join table to itself
    ON ((w.userid = z.userid AND w.friendid = z.friendid)  -- plain dup
    OR (w.userid = z.friendid AND w.friendid = z.userid))  -- reverse dup
   AND w.id < z.id -- to protect row from joining with itself (and deletion)
;

Here I suppose your table have some autoincremented column id. If no you have to add it and fill with 1..n INTs.

If there is no plain dupes then id column is not necessary and query can be simplified like that:

DELETE z.*
  FROM friends AS w
  JOIN friends AS z -- join table to itself
    ON (w.userid = z.friendid AND w.friendid = z.userid)
   AND w.userid < z.userid -- to protect row from joining with itself
;

To test whether the query selects only desired rows you can replace DELETE z.* by SELECT w.*, z.* and check the resulting set of rows.