MySQL – Fix Unique Key Not Working

MySQLmysql-5.5unique-constraint

I have the following table:

CREATE TABLE `timeline_lists` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `list_type` int(11) NOT NULL,
  `s_object_id` int(11) NOT NULL DEFAULT '0',
  `group_id` int(11) DEFAULT '0',
  `new_items_count` int(11) DEFAULT NULL,
  `last_accessed_on` datetime DEFAULT NULL,
  `last_updated_on` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`user_id`),
  KEY `unique_index` (`user_id`,`list_type`,`s_object_id`,`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

every time I run the following query a new record is added:

INSERT INTO `timeline_lists` (`id`,`user_id`,`list_type`,`s_object_id`,`group_id`,`new_items_count`,`last_accessed_on`,`last_updated_on`,`created_at`,`updated_at`) 
VALUES 
(NULL,18724,11,0,0,5,'2014-09-23 16:37:46',NULL,'2014-09-23 16:37:48','2014-09-23 16:37:48') 
ON DUPLICATE KEY UPDATE 
`timeline_lists`.`new_items_count`=VALUES(`new_items_count`),
`timeline_lists`.`last_accessed_on`=VALUES(`last_accessed_on`),
`timeline_lists`.`updated_at`=VALUES(`updated_at`)

I would expect that the unique index effect and the fields would just get updated instead, if I tun this query 5 times I would get 5 records in the DB instead of just 1.

what am I missing here?

Best Answer

There's no UNIQUE key on your table, only a PRIMARY KEY on an AUTO_INCREMENT column, which never trigger the ON DUPLICATE KEY because, as you probably already know, you're passing id = NULL.

Change this:

KEY `unique_index` (`user_id`,`list_type`,`s_object_id`,`group_id`)

to this:

UNIQUE INDEX `unique_index` (`user_id`,`list_type`,`s_object_id`,`group_id`)
Related Question