EC
1510
MariaDBERRORNotablePartitioningHIGH confidence

PRIMARY KEY must include all columns in the partitioning function

Production Risk

Low — DDL error; the table will not be created as specified.

What this means

ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF (1510, SQLSTATE HY000) is raised when a PRIMARY KEY or UNIQUE KEY does not include all columns referenced in the partitioning function.

Why it happens
  1. 1Partitioning by a column that is not part of the PRIMARY KEY
  2. 2UNIQUE KEY that does not include all partition columns
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE t (id INT PRIMARY KEY, region_id INT)
PARTITION BY RANGE(region_id) (  -- region_id not in PRIMARY KEY
  PARTITION p0 VALUES LESS THAN (10)
);

expected output

ERROR 1510 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

Fix

Include the partition column in the PRIMARY KEY

Include the partition column in the PRIMARY KEY
-- Add region_id to the PRIMARY KEY:
CREATE TABLE t (id INT, region_id INT, PRIMARY KEY (id, region_id))
PARTITION BY RANGE(region_id) (
  PARTITION p0 VALUES LESS THAN (10),
  PARTITION p1 VALUES LESS THAN MAXVALUE
);

Why this works

MySQL requires that all partition columns be part of every unique key to guarantee row uniqueness within partitions.

Sources
Official documentation ↗

MySQL 8.0 — 1510 ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MariaDB errors