UNIQUE CHECK IGNORE - PolarDB - Alibaba Cloud Documentation Center

PolarDB for MySQL supports the UNIQUE CHECK IGNORE (UCI) parameter that allows the partition table to ignore the uniqueness check for the partition key. This feature is supported for MySQL 8.0.2.2.17 and later.

Note

This feature is in canary release. To use this feature, go to Quota Center. Enter the polardb_mysql_uci quota ID to find the quota. Click Apply in the Actions column.
For more information about UCI, join the DingTalk group 24490017825.

Syntax

UCI is an attribute that you can set in the table_options parameter when you create PolarDB tables. If you set UNIQUE CHECK IGNORE=1 when you create a table, the table ignores the uniqueness check for the partition key and does not consider the relationship between the partition key and the primary key or unique key. You can select any column as the partition key. Syntax:

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

The UNIQUE CHECK IGNORE = { 1| 0 } option is added to the table_options parameter. UNIQUE CHECK IGNORE=1 specifies that the primary key and unique key do not need to contain all columns of the partition key. You can select any column as the partition key. However, this only ensures that the primary key and unique key are unique within a partition and can be repeated between different partitions.

Note

UNIQUE CHECK IGNORE can only be added to partitioned tables. If you add this attribute to non-partitioned tables, an error is reported.
UNIQUE CHECK IGNORE is a read-only attribute and cannot be modified after the table is created.
A partition table that is created with UNIQUE CHECK IGNORE=1 does not ensure the uniqueness of the primary key. To ensure the uniqueness of the primary key, you must create a unique global secondary index (GSI) for the primary key field. Otherwise, duplicate primary key field values may exist in different partitions. For more information, see Example 2.
A partition table that is created with UNIQUE CHECK IGNORE=1 does not ensure the uniqueness of the index on the partitioned table. We recommend that you create a unique global secondary index (GSI).

Parameters

Parameter	Description
table_name	The name of the table.

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

// Create a unique global secondary index ensures that the primary key is unique.
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;