U0400020: GaussDB does not support INSERT ON DUPLICATE KEY UPDATE for primary or unique keys

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: all GaussDB versions

Syntax Example

INSERT ON DUPLICATE KEY UPDATE is used for tables with a unique constraint (UNIQUE INDEX or PRIMARY KEY). If data inserted into a table violates the unique constraint, UPDATE is executed on the conflicting rows. In GaussDB, the primary key and unique index columns cannot be updated. This error is reported because UGO does not convert these columns.

CREATE TABLE duplicate_key_test (
    id INT PRIMARY KEY,
    info VARCHAR(10) UNIQUE
);
INSERT INTO duplicate_key_test VALUES (1, 'AA'), (2,'BB'), (3, 'CC');
INSERT INTO duplicate_key_test VALUES (10, 'DD'), (3, 'EE') ON DUPLICATE KEY UPDATE info = VALUES(info);

Suggestion

To update the unique key, run DELETE to delete the row with old key values and then run the INSERT statement to insert a new row, for example:

DELETE FROM duplicate_key_test WHERE id in (10, 3);
INSERT INTO duplicate_key_test VALUES (10, 'DD'), (3, 'EE');

In this scenario, the database cannot be used to avoid data conflicts. Use application code to check whether there are data conflicts.