Understanding global vs local primary and unique keys in AUTO databases - PolarDB

In PolarDB-X AUTO databases, whether a primary key or unique key is global (unique across all partitions) or local (unique only within a single partition) depends on the table type and the relationship between the key columns and the partition key columns.

The core rule for manual partitioned tables:

A primary key or unique key is global only if its columns include every partition key column. Otherwise, it is local.

A unique global secondary index (GSI) is always global, regardless of the partition key.

Quick reference

Table type	Primary key scope	Unique key scope
Common table (SINGLE)	Global	Global
Broadcast table (BROADCAST)	Global	Global
Automatic partitioned table	Global	Global
Manual partitioned table — key columns include all partition key columns	Global	Global
Manual partitioned table — key columns do NOT include all partition key columns	Local	Local
Manual partitioned table — unique GSI	—	Global

Primary keys

PolarDB-X supports two kinds of primary keys:

Global primary key — unique across all partitions
Local primary key — unique only within a single partition; duplicate values can exist across different partitions

Common tables and broadcast tables

The primary key of a common table or a broadcast table is always a global primary key, because data is either stored in one place or fully replicated.

Example 1: Global primary keys in a common table and a broadcast table

-- A common table
CREATE TABLE single_tbl(
  id bigint NOT NULL AUTO_INCREMENT,
  name varchar(30),
  PRIMARY KEY(id)
) SINGLE;

-- A broadcast table
CREATE TABLE brd_tbl(
  id bigint NOT NULL AUTO_INCREMENT,
  name varchar(30),
  PRIMARY KEY(id)
) BROADCAST;

Partitioned tables

In an AUTO database, how you create a partitioned table determines whether PolarDB-X manages partitioning automatically or you define it manually:

Automatic partitioned table — created when you do not specify a partition key or partition algorithm
Manual partitioned table — created when you explicitly specify a partition key or partition algorithm

Automatic partitioned tables

All primary keys in an automatic partitioned table are global primary keys.

Example 2: Global primary key in an automatic partitioned table

-- An automatic partitioned table
CREATE TABLE auto_tbl(
  id bigint NOT NULL AUTO_INCREMENT,
  name varchar(30),
  PRIMARY KEY(id)
);

Manual partitioned tables

Whether the primary key is global or local depends on whether the primary key columns cover all partition key columns.

Global primary keys

If the primary key columns include all partition key columns, the primary key is global.

Example 3: Global primary key in a manual partitioned table

key_tbl is partitioned by (id, addr). The primary key (id, name, addr) contains both partition key columns, so it is a global primary key.

CREATE TABLE key_tbl(
  id bigint,
  name varchar(10),
  addr varchar(30),
  PRIMARY KEY(id, name, addr)
) PARTITION BY KEY(id, addr);

Local primary keys

If the primary key columns do not include all partition key columns, the primary key is local — unique only within each partition, not across the table.

Example 4: Local primary key in a manual partitioned table

list_tbl is partitioned by city. The primary key (order_id) does not include city, so it is a local primary key.

CREATE TABLE list_tbl(
  order_id bigint,
  city varchar(50),
  name text,
  PRIMARY KEY(order_id)
) PARTITION BY LIST(city)
(
  PARTITION p1 VALUES IN ("Beijing"),
  PARTITION p2 VALUES IN ("Shanghai"),
  PARTITION p3 VALUES IN ("Guangzhou"),
  PARTITION p4 VALUES IN ("Shenzhen"),
  PARTITION p5 VALUES IN(DEFAULT)
);

Example 5: Local primary keys allow duplicate values across partitions

Because list_tbl routes rows by city, rows with different city values land in different partitions — each enforcing the primary key constraint independently.

Insert a row with order_id = 10001 and city = "Beijing". It is stored in partition p1.

INSERT INTO list_tbl(order_id, city, name) VALUES (10001, "Beijing", "phone");
-- Query OK, 1 row affected

Insert another row with the same order_id and city = "Beijing". Both rows would go to partition p1, so a primary key conflict is reported.

INSERT INTO list_tbl(order_id, city, name) VALUES (10001, "Beijing", "book");
-- (1062, "ERR-CODE: [TDDL-4614][ERR_EXECUTE_ON_MYSQL] Error occurs when execute on GROUP 'TEST_DB_P00000_GROUP' ATOM 'dskey_test_db_p00000_group#polardbx-storage-0-master#11.167.60.147-1766#test_db_p00000': Duplicate entry '10001' for key 'PRIMARY' ")

Insert a row with the same order_id but city = "Shenzhen". This row goes to partition p4, so no conflict occurs — and now two rows with order_id = 10001 exist in the table.

INSERT INTO list_tbl (order_id, city, name) VALUES (10001, "Shenzhen", "camera");
-- Query OK, 1 row affected

SELECT * FROM list_tbl;
-- +----------+----------+--------+
-- | order_id | city     | name   |
-- +----------+----------+--------+
-- | 10001    | Beijing  | phone  |
-- | 10001    | Shenzhen | camera |
-- +----------+----------+--------+
-- 2 rows in set

Example 6: DDL operations that move data across partitions can fail with duplicate primary key errors

When you modify the partitioning policy in a way that merges partitions containing duplicate primary key values, PolarDB-X reports a conflict and the DDL fails.

Using list_tbl from Example 5 (which already has two rows with order_id = 10001 in partitions p1 and p4), the following statement tries to merge Beijing and Shenzhen into one partition:

ALTER TABLE list_tbl
PARTITION BY LIST (city)
(
  PARTITION p1 VALUES IN ("Beijing", "Shenzhen"),
  PARTITION p2 VALUES IN ("Shanghai"),
  PARTITION p3 VALUES IN ("Guangzhou"),
  PARTITION p5 VALUES IN(DEFAULT)
);
-- (4700, "ERR-CODE: [TDDL-4700][ERR_SERVER] server error by Failed to execute the DDL task. Caused by: ERR-CODE: [TDDL-5321][ERR_GLOBAL_SECONDARY_INDEX_BACKFILL_DUPLICATE_ENTRY] Duplicated entry '10001' for key 'PRIMARY' ")

The DDL fails because placing both rows with order_id = 10001 in the same partition violates the local primary key constraint.

To prevent this, use the AUTO_INCREMENT attribute and let PolarDB-X generate primary keys — avoid manually assigning primary key values in tables with local primary keys.

Important

If a table with local primary keys already contains duplicate primary key values, handle data conflicts carefully when synchronizing to downstream systems. For example, when replicating to AnalyticDB for MySQL via Data Transmission Service (DTS), if AnalyticDB for MySQL uses the PolarDB-X primary key columns, conflicts may occur. In this case, set the primary keys of AnalyticDB for MySQL to the full combination of primary key columns and partition key columns from the PolarDB-X table.

Unique keys

Unique keys follow the same global/local rules as primary keys:

Global unique key — unique across all partitions
Local unique key — unique only within a single partition; duplicate values can exist across different partitions

Common tables and broadcast tables

The unique key of a common table or a broadcast table is always a global unique key.

Example 7: Global unique keys in a common table and a broadcast table

-- A common table
CREATE TABLE single_tbl(
  serial_id bigint,
  name varchar(30),
  UNIQUE KEY(serial_id)
) SINGLE;

-- A broadcast table
CREATE TABLE brd_tbl(
  serial_id bigint,
  name varchar(30),
  UNIQUE KEY(serial_id)
) BROADCAST;

Partitioned tables

Automatic partitioned tables

All unique keys in an automatic partitioned table are global unique keys.

Example 8: Global unique key in an automatic partitioned table

-- An automatic partitioned table
CREATE TABLE auto_tbl(
  serial_id bigint,
  name varchar(30),
  UNIQUE KEY(serial_id)
);

Manual partitioned tables

Global unique keys

If the unique key columns include all partition key columns, the unique key is global.

Example 9: Global unique key in a manual partitioned table

hash_tbl is partitioned by type_id. The unique key (inner_id, type_id) includes the partition key column type_id, so it is a global unique key.

CREATE TABLE hash_tbl(
  type_id int,
  inner_id int,
  UNIQUE KEY(inner_id, type_id)
) PARTITION BY HASH(type_id);

Example 10: Global unique key using a global secondary index

A unique GSI is always a global unique key, regardless of the table's partition key. In key_tbl, u_sid is a unique GSI on serial_id — it guarantees that serial_id is unique across all partitions even though the table is partitioned by type_id.

CREATE TABLE key_tbl(
  type_id int,
  serial_id int,
  UNIQUE GLOBAL INDEX u_sid(serial_id) PARTITION BY HASH(serial_id)
) PARTITION BY HASH(type_id);

Local unique keys

If the unique key columns do not include all partition key columns, the unique key is local.

Example 11: Local unique key in a manual partitioned table

range_tbl is partitioned by order_time. The unique key (serial_id) does not include order_time, so it is a local unique key.

CREATE TABLE range_tbl(
  id int primary key auto_increment,
  serial_id int,
  order_time datetime NOT NULL,
  UNIQUE KEY(serial_id)
) PARTITION BY RANGE(order_time)
(
  PARTITION p1 VALUES LESS THAN ('2022-12-31'),
  PARTITION p2 VALUES LESS THAN ('2023-12-31'),
  PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

Example 12: Local unique keys allow duplicate values across partitions

Because range_tbl routes rows by order_time, rows with different order_time values land in different partitions — each enforcing the unique key constraint independently.

Insert a row with serial_id = 20001 and order_time = '2022-01-01'. It is stored in partition p1.

INSERT INTO range_tbl(serial_id, order_time) VALUES (20001, '2022-01-01');
-- Query OK, 1 row affected

Insert another row with the same serial_id and order_time = '2022-01-02'. Both rows go to partition p1, so a unique key conflict is reported.

INSERT INTO info_tbl(serial_id, order_time) VALUES (20001, '2022-01-02');
-- (1062, "ERR-CODE: [TDDL-4614][ERR_EXECUTE_ON_MYSQL] Error occurs when execute on GROUP 'D25_000001_GROUP' ATOM 'dskey_d25_000001_group#polardbx-storage-1-master#11.167.60.147-1766#d25_000001': Duplicate entry '20001' for key 'serial_id' ")

Insert a row with the same serial_id and order_time = '2024-01-01'. This row goes to partition p3, so no conflict occurs — and now two rows with serial_id = 20001 exist in the table.

INSERT INTO range_tbl(serial_id, order_time) VALUES (20001, '2024-01-01');
-- Query OK, 1 row affected

SELECT * FROM range_tbl;
-- +----+-----------+---------------------+
-- | id | serial_id | order_time          |
-- +----+-----------+---------------------+
-- |  2 |     20001 | 2024-01-01 00:00:00 |
-- |  1 |     20001 | 2022-01-01 00:00:00 |
-- +----+-----------+---------------------+
-- 2 rows in set

Example 13: DDL operations that move data across partitions can fail with duplicate unique key errors

When you change the partitioning policy in a way that places rows with duplicate unique key values into the same partition, PolarDB-X reports a conflict and the DDL fails.

Using range_tbl from Example 12 (which already has two rows with serial_id = 20001 in partitions p1 and p3), converting the table to a common table would pull all rows into one place:

ALTER TABLE range_tbl SINGLE;
-- (4700, "ERR-CODE: [TDDL-4700][ERR_SERVER] server error by Failed to execute the DDL task. Caused by: ERR-CODE: [TDDL-5321][ERR_GLOBAL_SECONDARY_INDEX_BACKFILL_DUPLICATE_ENTRY] Duplicated entry '200001' for key 'PRIMARY' ")

The DDL fails because a common table requires uniqueness across all rows, which the duplicate serial_id values violate.

To prevent this, make sure local unique key values are not duplicated before performing DDL operations that involve data redistribution.

Important

If a table with local unique keys already contains duplicate unique key values, manually deduplicate the source data before synchronizing to downstream systems to avoid unique key conflicts.

FAQ

Do I need a special statement to create a global primary key or a global unique key?

No. Use the same MySQL CREATE TABLE syntax you always would. Whether the key is global or local is determined by the relationship between the key columns and the partition key columns — not by any special keyword.

The table currently uses a local primary key. How can I guarantee globally unique primary keys?

Use the Sequence feature to generate globally unique values. Assign sequence-generated values to the primary key column instead of specifying values manually.