Migrating from Oracle to MySQL we've found some many incompatibility issues. We've some workarounds for many of them, however we still have one: The Sequences
We have this table
CREATE TABLE "HEADS"( "HEAD_ID" NUMBER NOT NULL ENABLE, "COLUMN" NUMBER NOT NULL ENABLE, "ROW" NUMBER NOT NULL ENABLE, "VALUE" VARCHAR2(200), "CREATED_BY" VARCHAR2(40), "CREATED_DATE" DATE, "MODIFIED_BY" VARCHAR2(40), "MODIFIED_DATE" DATE )
Notice HEAD_ID is not primary key, and thus, it's not unique. However, on a Stored Procedure we have this code:
declare var_id,sequences number; SELECT CRM_SEQ_GIS_FCEHEAD.NEXTVAL INTO sequences FROM DUAL; INSERT INTO HEADS ( HEAD_ID, COLUMN, ROW, VALUE, CREATED_BY, CREATED_DATE ) ( SELECT sequences, COLUMN, ROW, VALUE, CREATED_BY, SYSDATE FROM HEADS WHERE HEAD_ID = var_id );
Being var_id any given numeric value.
It uses a sequence to get the next value for HEAD_ID. What would be the approach to do this in MySQL?
We know that sequences equivalent in MySQL are the auto_increment columns. But for a column to be auto_increment it should be the primary key and be unique. The column in oracle is not PK nor UNIQUE. It has repeated values for HEAD_ID.