Bookmark and Share
MySQL partition, primary keys and unique keys
(Publish Date: 2009-10-14 11:53am, Total Visits: 3292, Today: 1, This Week: 3, This Month: 4)

All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have. In other words, every unique key on the table must use every column in the table's partitioning expression. (This also includes the table's primary key, since it is by definition a unique key. This particular case is discussed later in this section.)

Here is an example. I have a table:

CREATE TABLE `universal_registration` (
  `ur_id` int(11) NOT NULL auto_increment,
  `site_id` int(11) unsigned NOT NULL,
  `realage_member_id` int(11) default NULL,
  `user_name` varchar(80) default NULL COMMENT 'The user_name chosen by the user.',
  `password` varchar(40) default NULL,
  `email` varchar(100) default NULL COMMENT 'The email address of the registrant.',
  `last_updated_date` datetime NOT NULL,
  `last_updated_by` varchar(20) NOT NULL,
  `phone` varchar(64) default NULL,
   `reg_url` varchar(255) default NULL,
  `is_auto_reg` tinyint(4) NOT NULL default '0',
  `product_notification` tinyint(4) default NULL,
  PRIMARY KEY  (`ur_id`),
  UNIQUE KEY `user_name` (`user_name`),
  UNIQUE KEY `email` (`email`),

  KEY `site_id_10` (`site_id`),
  KEY `realage_member_id` (`realage_member_id`),
  KEY `last_updated_date_key` (`last_updated_date`)
) ENGINE=InnoDB;

 

I can't create partitions on this table. I have to change the table definition as the following in order to use the partitions:

 

CREATE TABLE `universal_registration` (
  `ur_id` int(11) NOT NULL auto_increment,
  `site_id` int(11) unsigned NOT NULL,
  `realage_member_id` int(11) default NULL,
  `user_name` varchar(80) default NULL COMMENT 'The user_name chosen by the user.',
  `email` varchar(100) default NULL COMMENT 'The email address of the registrant.',
  `last_updated_date` datetime NOT NULL,
  `last_updated_by` varchar(20) NOT NULL,
  `phone` varchar(64) default NULL,
   `reg_url` varchar(255) default NULL,
  `is_auto_reg` tinyint(4) NOT NULL default '0',
  `product_notification` tinyint(4) default NULL,
  PRIMARY KEY  (`ur_id`),
  UNIQUE KEY `user_name` (`ur_id`,`user_name`),
  UNIQUE KEY `email` (`ur_id`,`email`),

  KEY `site_id_10` (`site_id`),
  KEY `realage_member_id` (`realage_member_id`),
  KEY `last_updated_date_key` (`last_updated_date`)
) ENGINE=InnoDB
partition by range(ur_id)
(
partition pt1 values less than (3666),
partition pt2 values less than (7332),
partition pt3 values less than (MAXVALUE)
);

Please refer this link:

http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys-unique-keys.html