Implementing Auto‑Increment Primary Keys When Migrating MySQL to OB Oracle

Background – When moving a MySQL table that contains an auto‑increment column to OB Oracle, the target database does not support MySQL‑style auto‑increment directly. The article reviews two feasible solutions and validates them with real commands.

Solution 1: Custom Sequence

1. Create a test table in MySQL:

[email protected] [zlm]> desc t;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

2. Export the DDL with dbcat :

cd /opt/oceanbase_package/tools/dbcat-1.9.1-RELEASE/bin
./dbcat convert -H 10.186.60.68 -P 3332 --user=zlm --password=****** --database=zlm \
  --no-schema --no-quote --from mysql57 --to oboracle32x --table t --file /tmp

The generated DDL contains a pseudo‑SQL line that creates a sequence for the target table.

3. Create the sequence in OB Oracle:

ZLM[ZLM]> CREATE SEQUENCE SEQ_T_ID MINVALUE 1 MAXVALUE 999999 INCREMENT BY 1 START WITH 1;
ZLM[ZLM]> SELECT SEQUENCE_NAME, MIN_VALUE, LAST_NUMBER, CYCLE_FLAG, ORDER_FLAG, CACHE_SIZE FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER='ZLM';

4. Create the target table using the sequence as the default value for the primary key:

ZLM[ZLM]> CREATE TABLE "ZLM"."T" (
  "ID" NUMBER(19,0) DEFAULT SEQ_T_ID.NEXTVAL,
  "NAME" VARCHAR2(30 BYTE),
  CONSTRAINT "PRIMARY" PRIMARY KEY ("ID")
);

5. Migrate data with DataX (configuration omitted for brevity) and verify that new inserts automatically obtain the next sequence value, confirming successful auto‑increment behavior.

Solution 2: GENERATED BY DEFAULT AS IDENTITY

1. Re‑create the table with the identity clause:

ZLM[ZLM]> CREATE TABLE "ZLM"."T" (
  "ID" NUMBER(19,0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 999999,
  "NAME" VARCHAR2(30 BYTE),
  CONSTRAINT "PRIMARY" PRIMARY KEY ("ID")
);

2. Observe that Oracle automatically creates an internal sequence (e.g., ISEQ$$_50034_16 ) whose name cannot be directly controlled. The default value of the ID column points to SEQUENCE.NEXTVAL , not the internal name.

3. Insert attempts fail with unique‑constraint errors until the internal sequence has been advanced enough (by inserting dummy rows or manually selecting CURRVAL ), making this approach cumbersome for bulk migrations.

Limitations of the Identity Approach

The system‑generated sequence name cannot be aligned with the business table name.

START WITH must be set to the maximum existing auto‑increment value, requiring extra scripting.

Generated sequences cannot be dropped directly (error ORA‑32794).

Summary

Both methods achieve the goal of preserving auto‑increment semantics, but the custom‑sequence technique is simpler, more predictable, and easier to script for many tables. Therefore, the article recommends using custom sequences when migrating MySQL tables with auto‑increment columns to OB Oracle.