title: MySQL - Composite Unique Key with datetime
body: <p>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').</p>
  
  <p>But I am getting the following error:</p>
  
  <pre><code>mysql&gt; 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'
  </code></pre>
  
  <p>Here is the schema:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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 <code>18-1-2015-08-08</code>.</p>
  
  <p>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?</p>