CODEWITHSUNDEEP
mysqlindexingpartitioning
delete
delete

Reputation: 19148

Error "A PRIMARY KEY must include all columns in the table's partitioning function"

When adding partitioning index on the column country_id to the following table, i get the error:

A PRIMARY KEY must include all columns in the table's partitioning function

I tried to add the column country_id to the PK containing then id and country_id. But then the similar error message occurs:

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

CREATE TABLE `geo_city` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `country_id` smallint(5) unsigned NOT NULL,
  `admin_zone_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(128) NOT NULL,
  `lat` double NOT NULL,
  `lng` double NOT NULL,
  `population` int(10) unsigned DEFAULT NULL,
  `timezone_id` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_city_id_per_country` (`id`,`country_id`),
  UNIQUE KEY `idx_unique_city_per_adminzone` (`admin_zone_id`,`name`),
  KEY `country_id` (`country_id`),
  KEY `name` (`name`),
  KEY `idx_lat_lng` (`lat`,`lng`),
  KEY `admin_zone_id` (`admin_zone_id`),
  KEY `population` (`population`),
  KEY `timezone_id` (`timezone_id`)
) ENGINE=InnoDB AUTO_INCREMENT=496831 DEFAULT CHARSET=utf8;

And then:

ALTER TABLE geo.geo_city PARTITION BY RANGE (country_id) (
                PARTITION p0 VALUES LESS THAN (2),
                PARTITION p1 VALUES LESS THAN (10),
                PARTITION p2 VALUES LESS THAN (20),
                PARTITION p3 VALUES LESS THAN (30),
                PARTITION pRemain VALUES LESS THAN MAXVALUE
            )

What to do now?

Upvotes: 1

Views: 10212

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Try this definition:

CREATE TABLE `geo_city` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `country_id` smallint(5) unsigned NOT NULL,
  `admin_zone_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(128) NOT NULL,
  `lat` double NOT NULL,
  `lng` double NOT NULL,
  `population` int(10) unsigned DEFAULT NULL,
  `timezone_id` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`, country_id),
  UNIQUE KEY `idx_unique_city_per_adminzone` (`admin_zone_id`,`name`, country_id),
  KEY `name` (`name`),
  KEY `idx_lat_lng` (`lat`,`lng`),
  KEY `admin_zone_id` (`admin_zone_id`),
  KEY `population` (`population`),
  KEY `timezone_id` (`timezone_id`)
) ENGINE=InnoDB AUTO_INCREMENT=496831 DEFAULT CHARSET=utf8;

As the error suggests, each unique key needs to include country_id. Note: I'm not confident that country_id is really a good choice for partitioning, given that countries are of such widely different sizes. However, the question is not about the merits of your particular partitioning scheme.

Upvotes: 1

Dylan Su
Dylan Su

Reputation: 6065

You need to add all partition key(s) into all primary key and unique keys.

Upvotes: 1

Related Questions