MySQL - Composite Unique Key with datetime

Question

I have a transaction table where several rows may be stored against three attributes (foo_id, bar_id), and the last attribute is deleted_at, the thought here is to ensure that I only have one active row at a time (deleted_at = '0000-00-00 00:00:00').

But I am getting the following error:

mysql> UPDATE epic_table SET deleted_at = NOW() WHERE (foo_id = '1' AND bar_id IN ('18'));
ERROR 1062 (23000): Duplicate entry '18-1-2015-08-08 16:35:46' for key 'epic_table_ibuk_1'

Here is the schema:

CREATE TABLE `epic_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `foo_id` bigint(20) unsigned NOT NULL,
  `bar_id` bigint(20) unsigned NOT NULL,
  `created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `epic_table_ibuk_1` (`foo_id`,`bar_id`,`deleted_at`),
  KEY `epic_table_ibfk_1` (`foo_id`),
  KEY `epic_table_ibfk_2` (`bar_id`),
  CONSTRAINT `epic_table_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`),
  CONSTRAINT `epic_table_ibfk_2` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8

The only thing that immediately stands out is that MySQL may not be considering the time portion of the datetime value, and therefore complaining about another row with 18-1-2015-08-08.

Any ideas how I can maintain the multiple row flexibility while being able to enforce that only one row is 'active' at any given time?

Answer 1

Changing from a comment to an answer...

The statement:

UPDATE epic_table SET deleted_at = NOW() 
WHERE (foo_id = '1' AND bar_id IN ('18'));

attempts to set deleted_at for all rows with foo_id = 1 and bar_id = 18. Since foo_id, bar_id and deleted_at combination is marked as unique, such change ends up violating the unique constraint.

Only a slight change may be needed to the statement like so, knowing that only one record may have deleted_at = '0000-00-00 00:00:00':

UPDATE epic_table SET deleted_at = NOW() 
WHERE (foo_id = '1' AND bar_id IN ('18') and deleted_at = '0000-00-00 00:00:00');