Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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

share|improve this answer
add comment

As per mysql explaination "all columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have". Why only unique/primary keys, why not other keys too ?

share|improve this answer
1  
This would be better posted as a new question. –  Paul White Feb 18 '13 at 13:09
    
Hi and welcome to the Stack Exchange network, where things operate a little differently than most forums. We encourage each topic to be one question, one or more answers, and a direct discussion of the validity of those answers (if need be) under each answer, and a discussion of clarity of the question on the question. After a workable answer is reached, we ask that you check the accept check under the appropriate answer. Since it sounds like you have a new question, maybe you can ask it as a new question using the link on the top right of almost every page? –  jcolebrand Feb 18 '13 at 17:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.