EC
1284
MySQLerrorddlhigh confidence

UNIQUE KEY must include all partitioning columns

Production Risk

Low — DDL fails; no data affected.

What this means

A UNIQUE KEY on a partitioned table does not include all columns used in the partitioning function, which MySQL requires to guarantee uniqueness across partitions.

Why it happens
  1. 1Defining a UNIQUE KEY that omits one or more partitioning columns
  2. 2Using a surrogate primary key that does not include the partition column
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE t (id INT, dt DATE, UNIQUE KEY(id)) PARTITION BY RANGE(YEAR(dt)) (PARTITION p0 VALUES LESS THAN (2020));

expected output

ERROR 1284 (HY000): The UNIQUE KEY needs to include all fields in the table's partitioning function

Fix

Include the partition column in the unique key

Include the partition column in the unique key
CREATE TABLE t (id INT, dt DATE, UNIQUE KEY(id, dt)) PARTITION BY RANGE(YEAR(dt)) (PARTITION p0 VALUES LESS THAN (2020));

Why this works

MySQL can then verify uniqueness within each partition.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 1284 ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF

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

← All MySQL errors