Sample Header Ad - 728x90

SQL Developer MySQL to Oracle migration auto_increment and identity columns

0 votes
1 answer
144 views
Ok, So in learning about migrating from MySQL 8 to Oracle 19 I have run into an issue. My tables in MySQL have auto_increment on the primary key. When I walk through the migration and get my master.sql script that has all of my metadata for object creation. I have noticed that the DDL for the tables has the identity column starting at 0 and not the auto_increment number from MySQL. This runs into an issue when inserting data if the number of rows in the table in Oracle is less than the identity column base value. I used Goldengate to insert the data and the sequence that is system generated for the identity column only gets bumped up for each insert of data. How can I get the correct value when using SQL Developer to do a migration? I have a work around in PL/SQL but would like to get the correct value form the migration process. Thanks
CREATE TABLE my_table (
  id NUMBER(10,0) GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 0 INCREMENT BY 1 MINVALUE 0 NOMAXVALUE ,
  created DATE NOT NULL,
  modified DATE NOT NULL,
  user_id NUMBER(10,0) NOT NULL
);
should be ``` CREATE TABLE my_table ( id NUMBER(10,0) GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 100 INCREMENT BY 1 MINVALUE 0 NOMAXVALUE , created DATE NOT NULL, modified DATE NOT NULL, user_id NUMBER(10,0) NOT NULL );
Asked by cptkirkh (13 rep)
May 2, 2024, 04:04 PM
Last activity: Jul 31, 2025, 09:03 AM