Avoid/remove duplicate row in a table

Question

i have a table for friendship

CREATE TABLE IF NOT EXISTS `friendList` (
  `id` int(10) NOT NULL,
  `id_friend` int(10) NOT NULL,
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I have some entries like :

Entry A : 1-2

Entry B : 2-1

Entry C : 1-2

Entry A & B show that the user 1 is friend with user 2. But the entry C is useless, so i need to delete it.

Is there way i can delete all duplicate row or apply specific constraint to avoid that?

Thanks !

refer to this link http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql — user2936213, Dec 13 '13 at 04:51
The thing is that the id key is not unique and is not meant to be. I was looking for another solution than adding a unique primary key, but may be its just impossible. Thanks for your answer anyway. — user3093547, Dec 13 '13 at 04:54

score 0 · Accepted Answer · answered Dec 13 '13 at 04:56

0

You can add a unique index for those 2 columns to enforce uniqueness:

alter table friendList add unique index(id, id_friend);

answered Dec 13 '13 at 04:56

Szymon

42,577
16
96
114

Hi i want also the entry 3-1 or 1-3 to be possible i just dont want two row with the same x-y couple (x=given int, y=given int) – user3093547 Dec 13 '13 at 05:00
That is ok as 3-1 is not the same as 1-3. – Szymon Dec 13 '13 at 05:00
1

Yeah it worked, i think i just didn't understand well what unique index is then. Thanks a lot :) – user3093547 Dec 13 '13 at 05:04

Avoid/remove duplicate row in a table

1 Answers1