PolarDB:The primary key and unique key of a database in the DRDS mode

Primary keys

In PolarDB-X, primary keys are categorized into global primary keys and local primary keys based on the following criteria:

• If a primary key is globally unique, it is a global primary key.

• If a primary key is unique only in a table shard, it is a local primary key.

Single tables and broadcast tables

The primary key in a single table or a broadcast table is a global primary key that is globally unique.

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

## A single table
CREATE TABLE single_tbl(
 id bigint NOT NULL AUTO_INCREMENT, 
 name varchar(30), 
 PRIMARY KEY(id)
);

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

Database and table shards

Global primary keys

If the primary key of a table contains all shard keys of database and table shards, the primary key is a global primary key that is globally unique.

Example 2: The global primary key in database and table shards

In the user_tbl table, the database shard key is id, the table shard key is name, and the primary key column (id, name, addr) contains all shard keys. Therefore, the primary key of the table is a global primary key that is globally unique.

CREATE TABLE user_tbl(
 id bigint,
 name varchar(10),
 addr varchar(30),
 PRIMARY KEY(id, name, addr)
) DBPARTITION BY HASH(id) TBPARTITION BY HASH(name) TBPARTITIONS 4;

Local primary keys

If the primary key of a table does not contain all shard keys of the database and table shards, the primary key is a local primary key.

Example 3: The local primary key in database and table shards

The primary key column of the order_tbl table is order_id and does not contain the database shard key city. Therefore, the primary key of the table is a local primary key that is unique in the table but not globally unique.

CREATE TABLE order_tbl(
 order_id bigint,
 city varchar(50),
 name text,
 PRIMARY KEY(order_id)
) DBPARTITION BY HASH(city);

Example 4 : The local primary key that is not globally unique

A local primary key is unique only in a table but not globally unique. Therefore, duplicate local primary keys may exist. The order_tbl table in Example 3 is used in this example. The database shard key is city. Database sharding is performed and table sharding is not performed. Therefore, each database shard contains only one table.

1. Insert a data record into order_tbl. The data record is successfully inserted.

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

2. Insert a data record that has the same order_id value and the same city value into order_tbl. Because the city value is the same as that of the previously inserted data record, the system tries to insert the data record into the same table shard as the previously inserted data record. The SQL statement fails to be executed. An error that indicates a primary key conflict is reported. The result shows that data records that have the same primary key value cannot be inserted into the same table shard, and that the local primary key is unique in the table shard.

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

3. Insert a data record that has the same order_id value but a different city value into order_tbl. Because the city value is Shenzhen, which is different from that of the previously inserted data record, the systems tries to insert the data record into a different table shard.

   The SQL statement can be successfully executed. As a result, the order_tbl table contains two data records that have the same primary key value. This indicates that the local primary key is not globally unique.

   INSERT INTO order_tbl (order_id, city, name) VALUES (10001, "Shenzhen", "camera");
   Query OK, 1 row affected
   
   SELECT * FROM order_tbl;
   +----------+----------+--------+
   | order_id | city | name |
   +----------+----------+--------+
   | 10001 | Beijing | phone |
   | 10001 | Shenzhen | camera |
   +----------+----------+--------+
   2 rows in set

Example 5: If you execute a DDL statement on a table that contains duplicate primary keys, an error which indicates a primary key conflict may occur.

A table that uses a local primary key may have duplicate primary key values. When you perform data redistribution operations on the table, such as executing DDL statements to change the table type or synchronizing data to other databases, an error which indicates a primary key conflict may occur.

The order_tbl table contains two data records that have the same primary key value. These records are inserted into different table shards. Execute the DDL statement to change the sharded databases and tables to a single table. Data records whose city values are Beijing and data records whose city values are Shenzhen are inserted into the same table.

ALTER TABLE order_tbl SINGLE;
(4700, '[17399c0a2fc00000][30.221.117.14:8527][d25]ERR-CODE: [TDDL-4700][ERR_SERVER] server error by The DDL job has been rollback. Please check the ddl stmt. jobId: 1673540305653071872 ')

The DDL statement failed to be executed. This is because the primary key value in a single table must be unique. However, data records that have the same primary key value are inserted into the single table when you change the table type to a single table.

To avoid primary key conflicts caused by duplicate primary key values for tables that use local primary keys, you can perform the following operations:

• Use the AUTO_INCREMENT attribute to let PolarDB-X generate primary keys.

• Do not manually specify the primary key values on the business side.

  Important

  For tables that use local primary keys and have duplicate primary keys, you need to avoid primary key conflicts when you synchronize data to other databases. For example, when you synchronize a table that contains a local primary key to an AnalyticDB for MySQL database by using DTS, conflicts may occur if the AnalyticDB for MySQL database continues to use the primary key of the PolarDB-X table. To avoid this issue, you can set the primary key of the AnalyticDB for MySQL database to contain the primary key column and all shard keys of the PolarDB-X table.

Unique keys

Similar to primary keys, unique keys in PolarDB-X are categorized into global unique keys and local unique keys based on the following criteria:

• If a unique key is globally unique, it is a global unique key.

• If a unique key is unique only in a table shard, it is a local unique key.

This section describes how to determine in different scenarios whether the unique key of a table is a global unique key or a local unique key.

Single tables and broadcast tables

The unique key in a single table or a broadcast table is a global unique key that is globally unique.

Example 6: Global unique keys in a single table and a broadcast table

## A single table
CREATE TABLE single_tbl(
 serial_id bigint,
 name varchar(30), 
 UNIQUE KEY(serial_id)
);

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

Database and table shards

Global unique keys

If the unique key of a table contains all shard keys of database and table shards, the unique key is a global unique key that is globally unique.

Example 7: The global unique key in database and table shards

In the type_tbl table, the unique key column is (inner_id, type_id) and contains the only shard key type_id. Therefore, the unique key of the table is a global unique key.

CREATE TABLE type_tbl(
 type_id int,
 inner_id int,
 UNIQUE KEY(inner_id, type_id)
) DBPARTITION BY HASH(type_id);

The global secondary index in database and table shards is also a global unique key that is globally unique. For more information, see Global secondary index .

Example 8: The global unique key that consist of a unique global index

The table type_tbl2 contains a unique global index whose index column is serial_id. The serial_id column is globally unique and is a global unique key.

CREATE TABLE type_tbl2(
 type_id int,
 serial_id int,
 UNIQUE GLOBAL INDEX u_sid(serial_id) DBPARTITION BY HASH(serial_id)
) DBPARTITION BY HASH(type_id);

Local unique keys

If the unique key of a table does not contain all shard keys of database and table shards, the unique key is a local unique key.

Example 9: The local unique key in database and table shards

The unique key column of the info_tbl table is serial_id and does not contain the shard key order_time. Therefore, the unique key of the info_tbl table is a local unique key. The unique key is unique only in the table but not globally unique.

CREATE TABLE info_tbl(
 id int primary key auto_increment,
 serial_id int,
 	order_time date NOT NULL,
 UNIQUE KEY(serial_id)
) DBPARTITION BY YYYYMM(order_time);

Example 10: The local unique key that is not globally unique

Similar to local primary keys, local unique keys are not globally unique. Therefore, duplicate local unique keys may exist.

The info_tbl table in Example 9 is used in this example. The database shard key is order_time. Database sharding is performed and table sharding is not performed. Therefore, each database shard contains only one table.

1. Insert a data record into info_tbl. The data record is inserted.

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

2. Insert a data record that has the same serial_id value and an order_time value of 2022-01-02 into info_tbl. The order_time value is the same as that of the previously inserted data record, the system tries to insert the data record into the same table shard as the previously inserted data record. The SQL statement fails to be executed. An error that indicates a unique key conflict is reported. The result shows that data records that have the same unique key value cannot be inserted into the same table shard, and that the local unique key is unique in the table shard.

   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' ")

3. Insert a data record that has the same serial_id value and an order_time value of 2023-03-01 into info_tbl. Because the order_time value is different from that of the previously inserted data record, the systems tries to insert the data record into a different table shard. The data record is inserted. As a result, the serial_id table contains two data records that have the same unique key value. This indicates that the local unique key is not globally unique.

   INSERT INTO info_tbl(serial_id, order_time) VALUES (20001, '2023-03-01');
   Query OK, 1 row affected
   
   SELECT * FROM info_tbl;
   +--------+-----------+------------+
   | id | serial_id | order_time |
   +--------+-----------+------------+
   | 100006 | 20001 | 2023-03-01 |
   | 100001 | 20001 | 2022-01-01 |
   +--------+-----------+------------+

Example 11: If you execute a DDL statement on a table that has duplicate unique keys, an error that indicates a unique key conflict may occur.

Similar to tables that contain local primary key, a table that uses a local unique key may have duplicate unique key values. When you perform data redistribution operations on the table, such as executing DDL statements to change the table type or synchronizing data to other databases, an error that indicates a unique key conflict may occur.

The info_tbl table already contains two data records that have the same unique key value. These records are stored in different table shards. Execute the DDL statement to change the sharded databases and tables to a single table. This causes data redistribution in the info_tbl table.

ALTER TABLE info_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 '20001' for key 'PRIMARY' ")

The DDL statement failed to be executed. An error that indicates a unique key conflict is returned. This is because the unique key value in a single table must be unique. However, data records that have the same unique key value are inserted into the single table when you change the table type of info_tbl to a single table.

To avoid unique key conflicts caused by duplicate unique key values for tables that use local unique keys, you must ensure that the unique key values are unique.