U0000008: GaussDB Distributed unique keys must be a superset of distribution keys

Description

Database Type and Version

• Source database type and version: all types of the source database storing tables with unique constraints or unique indexes.
• Target database type and version: GaussDB Distributed

Syntax Example

If no distribution key is explicitly specified in CREATE TABLE for a distributed GaussDB database, the rules for selecting a default distribution key are as follows:

1. If there is no primary key or unique key, the distribution key is the first column that meets the data type requirements of the distribution key in the table.
2. If there is an independent primary or unique key, the distribution key is the first column of the primary or unique key.
3. If both the primary and unique keys are in a table, the rules vary depending on the GaussDB version and unique key form.
   • When a unique key is specified in the CREATE TABLE statement:
     • For distributed GaussDB V2.0-2.7, the first column in the primary key is used as the distribution key by default, and all unique keys in the table must contain the distribution key. Otherwise, this error is reported. You can run the following statement to create a table:

       create table distributed_table1 (
           id int primary key,
           name int,
           constraint pk_unique1 unique(name)
       );

       In the statement, GaussDB uses the id column as a distribution key by default. However, unique constraint pk_unique1 does not contain the id column, which is not supported by GaussDB Distributed V2.0-2.7. In this case, this error is reported.

     • In GaussDB Distributed V2.0-2.7 or later, a unique key in the CREATE TABLE statement can contain no distribution key. When processing such table creation statements, GaussDB automatically creates global secondary indexes.
   • When the CREATE INDEX or ALTER TABLE statement is used to add a unique constraint or unique index, a unique key must contain a distribution key in all GaussDB versions. For example:

     create table distributed_table2 (
         id int,
         name int,
         student int
     );
     create unique index pk_unique2 on distributed_table2(student);

     The CREATE TABLE statement does not contain a primary or unique key. Therefore, GaussDB uses the id column as a distribution key by default. When the CREATE UNIQUE INDEX statement is executed, pk_unique2 does not contain id. This is not supported by GaussDB. As a result, this error is reported.

Suggestion

Modify Support for unique indexes in a distributed (multi datanode) env. Convert the unique key to a non-unique index or add a distribution key to the unique key.