Help Center/ Distributed Database Middleware/ User Guide (ME-Abu Dhabi Region)/ FAQs/ RDS-related Questions/ How Do I Handle Data with Duplicate Primary Keys in a Table?
Updated on 2022-02-22 GMT+08:00
View PDF
Share

How Do I Handle Data with Duplicate Primary Keys in a Table?

Scenario 1

The logical table of a DDM instance contains a data record whose type value of the primary key is a boundary value. When you enter a data record with the value exceeding the required range, data with duplicate primary keys is displayed.

Procedure

  1. Log in to the RDS console.
  2. On the Instance Management page, locate the target RDS MySQL DB instance and click its name.
  3. On the Basic Information page, choose Parameters in the left pane.
  4. Click the Parameters tab and enter sql_mode in the text box. Then click the expanding button in the Value column, select STRICT_ALL_TABLES or STRICT_TRANS_TABLES, and click Save.

    STRICT_ALL_TABLES and STRICT_TRANS_TABLES are both strict modes. The strict mode controls how MySQL handles invalid or missing values.

    • An invalid value might have the wrong data type for the column, or might be out of range.
    • A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition.
    • If the DDM instance version is earlier than 2.4.1.3, do not set sql_mode to ANSI_QUOTES. If you set it to ANSI_QUOTES, double quotation marks used for each string will be translated into an identifier during SQL execution, making the string invalid.

      For example, logic in select * from test where tb = "logic" cannot be parsed correctly.

    For more information about SQL modes, see Server SQL Modes.

  5. On the Instances page, restart the DDM instance.

Scenario 2

For sharded tables (hash\range\mod) with composite primary keys, the primary key is duplicated when you insert 0 and 1 to data with the sharding key.

Procedure

  1. Log in to the RDS console.
  2. On the Instance Management page, locate the target RDS MySQL DB instance and click its name.
  3. On the Basic Information page, choose Parameters in the left pane.
  4. Click the Parameters tab and enter sql_mode in the text box. Then click the expanding button in the Value column, select NO_AUTO_VALUE_ON_ZERO in the drop-down list, and click Save.
  5. On the Instances page, restart the DDM instance.
Parent topic: RDS-related Questions