How do I add a unique index on a field in a partitioned MySQL DB?

Question

I have a table, foo, that is partitioned by 'created_at' and has its primary key as (id,created_at). I.e.:

CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `bar_id` int(11) DEFAULT NULL,
   ...

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (TO_DAYS(created_at))
(PARTITION p0 VALUES LESS THAN (733712) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (733773) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (733832) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (733893) ENGINE = InnoDB,
...
)

How do I create a unique index such that bar_id is unique across all partitions? If I try something like:

CREATE UNIQUE INDEX some_index USING BTREE ON foo (bar_id);

I receive the error:

ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

But, if I include the partitioning function (id, created_at) when building the index then I end up with an index that does not guarantee that bar_id is unique.

UPDATE

I should have mentioned that I already have a primary key on the table:

PRIMARY KEY (`id`,`created_at`)

Also, bar_id can be NULL.

Answer 1

The error message itself explains the problem. Please, read the manual, http://dev.mysql.com/doc/refman/5.5/en/partitioning-limitations-partitioning-keys-unique-keys.html

"The rule is : All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have. In simple words, every unique key on the table must use every column in the table's partitioning expression."