CODEWITHSUNDEEP
mysqldatabase-partitioning
user704006
user704006

Reputation: 271

Mysql Table Partitioning

I am trying to create partition but couldn't succeeded. Here is my table structure

CREATE  TABLE `bb2`.`new_table` (

  `id` INT NOT NULL AUTO_INCREMENT ,

  `dt` DATE NOt NULL ,

  PRIMARY KEY (`id`) ,

  UNIQUE INDEX `date_UNIQUE` (`dt`) ) 

partition by range (to_days(dt))(
partition p0 values less than ( to_days('2011-01-01') ),
partition p1 values less than MAXVALUE
)

Have u any idea how can I create any type of partition without removing primary or changing table structure.

Upvotes: 1

Views: 115

Answers (1)

Ryan
Ryan

Reputation: 28187

A primary key must include all columns used in your partitions. See here.

This would work:

CREATE  TABLE `test`.`new_table2` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `dt` DATE NOt NULL ,
  PRIMARY KEY (`id`, `dt`) , <-- updated primary key
  UNIQUE INDEX `date_UNIQUE` (`dt`) ) 
     partition by range (to_days(dt))(
     partition p0 values less than ( to_days('2011-01-01') ),
     partition p1 values less than MAXVALUE
)

It keeps your table structure the same by augmenting the primary key.

Upvotes: 1

Related Questions