Prevent Duplicate Entries Composite Index

Question

I have a Relationships table that I am trying to add a composite index to:

CREATE UNIQUE INDEX idx_relationship_userid_friend_id on Relationships(user_id, friend_id);

I would like to prevent duplicate entries for user_id and friend_id columns. i.e. Second entry of user_id = 26 and friend_id = 46 should give an error.

I ran the command above. When I run the command again, I get the following error: Duplicate entry '36-50' for key 'idx_relationship_userid_friend_id'

When I look at the structure in for INDEXES I see the following table:

Under table info next to Create syntax, I have the following code:

CREATE TABLE `Relationships` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `friend_id` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT '1',
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `app_common` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `owner_id` (`user_id`),
  KEY `app_common` (`app_common`),
  CONSTRAINT `Relationships_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `Users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `Relationships_ibfk_2` FOREIGN KEY (`app_common`) REFERENCES `AppCommon` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=79 DEFAULT CHARSET=latin1;

However, I am able to insert duplicate records in the database.

Question: How can I avoid duplicate entries?

score 0 · Answer 1 · edited Aug 09 '17 at 15:59

0

Use the Unique constraint to handle your issue, something like:

CONSTRAINT UC_user_friend UNIQUE (user_id, friend_id)

edited Aug 09 '17 at 15:59

Fabien

4,862
2
19
33

answered Aug 09 '17 at 14:39

StanislavL

56,971
9
68
98

Barmar · Answer 2 · 2017-08-09T14:50:33.133

0

Since your table already has duplicates, it gets an error trying to create the index, so it doesn't add it.

See How to delete duplicates on a MySQL table? for how to remove duplicates. Once you've done that you can add the unique index to prevent new duplicates from being added.

edited Aug 09 '17 at 14:50

answered Aug 09 '17 at 14:40

Barmar

741,623
53
500
612

Thanks. I tried running the following command, `ALTER IGNORE TABLE Relationships ADD UNIQUE (user_id, friend_id)`. I got an error. I believe it's because that command is deprecated. – user1107173 Aug 09 '17 at 14:46
You're right, it was removed in 5.7. https://dev.mysql.com/worklog/task/?id=7395. You need to use some other method to get rid of your duplicates before you can add the index. – Barmar Aug 09 '17 at 14:49

Prevent Duplicate Entries Composite Index

2 Answers2