U0400011: GaussDB auto_increment column must be the first field of a primary key constraint or unique constraint

Description

Database Type and Version

• Source database type and version: MySQL 5.5, 5.6, 5.7, and 8.0
• Target database type and version: GaussDB Centralized V2.0-3.1 and later

Syntax Example

AUTO_INCREMENT is supported since GaussDB Centralized (B-compatible mode) V2.0-3.1. Therefore, during migration to GaussDB Centralized (B-compatible mode) of earlier versions, the default value of a column is set to a sequence value by default, and the column data is generated based on the sequence. This error is reported if the auto-increment column is not the first column of a primary key constraint or unique constraint.

Since GaussDB Centralized (B-compatible mode) V2.0-3.1, UGO does not convert AUTO_INCREMENT, for example:

CREATE TABLE auto_increment_test (
    id INT AUTO_INCREMENT,
    name VARCHAR(64)
);

Suggestion

If the AUTO_INCREMENT column after migration is not the first column of the primary key constraint or unique constraint, you need to modify the column.

1. Define the AUTO_INCREMENT column as a primary key or unique key, or add the AUTO_INCREMENT column to the first column of an existing primary key constraint or unique constraint. In this case, the original data integrity constraint is modified.
2. Comment out the AUTO_INCREMENT column. Ensure data is correct.
3. Change the AUTO_INCREMENT column to a sequence integer column. For details, see error U0400010 and GaussDB Developer Guide.