MySql – partition by range and unique key

MySQLpartitioningunique-constraint

i'm trying to create the following table: 

CREATE TABLE `s_relations_with_partition` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `source_persona_id` int(11) NOT NULL,
  `relation_type` int(11) NOT NULL,
  `message_id` int(11) DEFAULT NULL,
  `reply_to_message_id` int(11) DEFAULT NULL,
  `reshare_of_message_id` int(11) DEFAULT NULL,
  `target_object_id` int(11) DEFAULT NULL,
  `target_persona_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `sent_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`,`sent_at`),
  UNIQUE KEY `unique_target_persona` (`source_persona_id`,`relation_type`,`message_id`,`target_persona_id`),
  UNIQUE KEY `unique_target_object` (`source_persona_id`,`relation_type`,`message_id`,`target_object_id`),
  KEY `message_id_index` (`message_id`),
  KEY `reshare_of_message_id_index` (`reshare_of_message_id`),
  KEY `reply_to_message_id_index` (`reply_to_message_id`),
  KEY `source_and_target_object_index` (`source_persona_id`,`target_object_id`),
  KEY `source_target_persona_index` (`source_persona_id`,`target_persona_id`),
  KEY `target_persona_relation_type_message_id_index` (`target_persona_id`,`relation_type`,`message_id`),
  KEY `sent_at_index` (`sent_at`),
  KEY `source_persona_sent_at_index` (`source_persona_id`,`sent_at`),
  KEY `target_persona_sent_at_index` (`target_persona_id`,`sent_at`),
  KEY `target_object_sent_at_index` (`target_object_id`,`sent_at`)
) ENGINE=InnoDB 
PARTITION BY RANGE (sent_at) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-02-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-03-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-04-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-05-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-06-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-08-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-09-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-10-01 00:00:00') ),   
    PARTITION p10 VALUES LESS THAN (MAXVALUE)
);

and i get the following error:

A UNIQUE INDEX must include all columns in the table's partitioning function

adding sent_at (unix timestamp) to the unique index is not possible.

any ideas on how i can implement a date range partition on that table?

Best Answer

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

"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."

Related Question