Use UNIQUE CHECK IGNORE to ignore partition key uniqueness check - PolarDB

By default, MySQL requires every unique key (including the primary key) in a partitioned table to include all partition key columns. This restriction forces you to modify your table schema around the partitioning scheme, which can conflict with your actual query patterns and business logic — and creates friction when migrating schemas from databases that support global indexes, such as Oracle.

UNIQUE CHECK IGNORE (UCI) removes this restriction in PolarDB for MySQL. When enabled, you can choose any column as the partition key regardless of whether it appears in the primary key or unique key.

This feature requires MySQL 8.0.2.2.17 or later.

UCI is in canary release. To apply for access, go to Quota Center, search for quota ID polardb_mysql_uci, and click Apply in the Actions column. For support, join the DingTalk group 24490017825.

How it works

UCI is a table_options attribute set at table creation time. When UNIQUE CHECK IGNORE=1 is specified, PolarDB skips the check that normally requires primary key and unique key columns to include all partition key columns. This means:

The primary key and unique key do not need to contain the partition key columns.
Any column can be used as the partition key.
Uniqueness is enforced within each partition only. Duplicate values can exist across different partitions.

Syntax

CREATE TABLE [schema.]table_name
    table_definition
    UNIQUE CHECK IGNORE = {1 | 0}
    PARTITION BY ...
    [SUBPARTITION BY ...]

Parameters

Parameter	Value	Description
`UNIQUE CHECK IGNORE`	`1`	Disables the requirement that the primary key and unique key include all partition key columns. Uniqueness is checked within each partition only.
`UNIQUE CHECK IGNORE`	`0`	Default behavior. The primary key and unique key must include all partition key columns.

Limitations

Partitioned tables only. Adding UNIQUE CHECK IGNORE to a non-partitioned table causes an error.
Immutable after creation. UNIQUE CHECK IGNORE is a read-only attribute. It cannot be changed after the table is created.
No cross-partition primary key uniqueness. A table created with UNIQUE CHECK IGNORE=1 does not guarantee that primary key values are unique across partitions. To enforce global primary key uniqueness, create a unique global secondary index (GSI) on the primary key column. See Example 2 below.
No cross-partition index uniqueness. Unique indexes on a partitioned table are not globally unique. To enforce global index uniqueness, create a unique global secondary index (GSI).

Examples

Example 1: Basic usage with RANGE partitioning

Column c is used as the partition key even though it is not part of the primary key (a) or unique key (b).

CREATE TABLE t1(
    a INT PRIMARY KEY,
    b INT UNIQUE,
    c INT
) UNIQUE CHECK IGNORE=1
PARTITION BY RANGE(c) (
    PARTITION p0 VALUES LESS THAN (20),
    PARTITION p1 VALUES LESS THAN (40),
    PARTITION p2 VALUES LESS THAN (60)
);

Without a GSI, values of a are unique only within each partition. Two rows in different partitions can share the same value of a. See Example 2 for how to prevent this.

Example 2: Enforce global primary key uniqueness with a GSI

To guarantee that primary key values remain unique across all partitions, add a unique global secondary index on the primary key column.

CREATE TABLE t1(
    a INT PRIMARY KEY,
    b INT UNIQUE,
    c INT,
    UNIQUE KEY i_a_g(a) GLOBAL  -- GSI enforces global uniqueness of primary key column a
) UNIQUE CHECK IGNORE=1
PARTITION BY HASH(c) PARTITIONS 11;

What's next

Global secondary index