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

PolarDB for MySQL supports partitioned tables whose primary key does not include the partition key columns (UNIQUE CHECK IGNORE, UCI for short). UCI is available in Database engine 8.0.2.2.17 and later.

Scope of application

The kernel version of your cluster must meet the following requirements:

MySQL 8.0.1, and the revision version must be 8.0.1.1.46 or later.
MySQL 8.0.2, and the revision version must be 8.0.2.2.26 or later.

To verify the kernel version of your cluster, see Query the version number.

Parameter reference

Parameter

Scope

Description

loose_polar_allow_unique_check_ignore

Global

Controls whether the primary key or unique key of a partitioned table must contain the partition key columns (that is, whether the primary key and unique key are decoupled from the partition key). Valid values:

ON: Allows you to create partitioned tables whose primary key or unique key does not contain the partition key columns.
OFF (default): Prevents you from creating partitioned tables whose primary key or unique key does not contain the partition key columns.

Changes take effect immediately without a cluster restart.

Syntax

UCI is an attribute of PolarDB table_options when you create a table. Specifying UNIQUE CHECK IGNORE=1 indicates that the table skips uniqueness checks on the partition key columns. The relationship between the partition key and the primary key or unique key is no longer enforced, and you can choose any column as the partition key. The syntax is as follows:

CREATE TABLE [ schema. ]table_name
 table_definition table_options
   PARTITION BY ...
   SUBPARTITION BY ...

In this syntax, an option UNIQUE CHECK IGNORE = { 1| 0 } is added to table_options. UNIQUE CHECK IGNORE=1 indicates that the primary key and unique key do not need to contain all partition key columns, which allows for more flexible partition key selection. However, the uniqueness of primary keys and unique keys across partitions is not guaranteed. The uniqueness is enforced only within each partition, and duplicate values can exist across different partitions.

Note

The table attribute UNIQUE CHECK IGNORE can be used only on partitioned tables. Adding this attribute to a non-partitioned table causes an error.
The table attribute UNIQUE CHECK IGNORE is read-only. You cannot modify the value of UNIQUE CHECK IGNORE after the table is created.
A UCI partitioned table does not guarantee primary key uniqueness. To enforce primary key uniqueness, you must create a unique Global Secondary Indexes (GSIs) on the primary key columns. Otherwise, duplicate primary key values are allowed across partitions. For the specific procedure, see Example 2.
A UCI partitioned table does not guarantee the uniqueness of a unique index that does not contain the partition key columns. We recommend that you create it as a unique Global Secondary Indexes (GSIs).

Parameters

Parameter	Description
table_name	The name of the table to create.

Examples

Example 1

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)
    );

Example 2

// A unique global secondary index ensures primary key uniqueness
CREATE TABLE t1(
        a INT PRIMARY KEY,
        b INT UNIQUE,
        c INT,
        UNIQUE KEY i_a_g(a) GLOBAL
    ) UNIQUE CHECK IGNORE=1
    PARTITION BY HASH(c) PARTITIONS 11;