Oracle Sequences and Identity Columns and MySQL Sequences and AUTO INCREMENT Columns - Oracle to Aurora MySQL Migration Playbook
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Oracle Sequences and Identity Columns and MySQL Sequences and AUTO INCREMENT Columns

Feature compatibility Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences

Two star feature compatibility

One star automation level

Sequences

MySQL doesn’t support sequences, identity columns have different syntax and options.

Oracle Usage

Sequences are database objects that serve as unique identity value generators. You can use them, for example, to automatically generate primary key values. Oracle treats sequences as independent objects. The same sequence can generate values for multiple tables.

You can configure sequences with multiple parameters to control their value-generating behavior. For example, the INCREMENT BY sequence parameter defines the interval between each generated sequence value. If more than one database user is generating incremented values from the same sequence, each user may encounter gaps in the generated values that are visible to them.

Oracle 18c introduced scalable sequences: a special class of sequences that are optimized for multiple concurrent session usage.

This introduces three new options when creating a new sequence:

  • SCALE — Turns on the sequence scalability feature.

    • EXTEND — Extends in additional 6 digits offset (as default) and the maximum number of digits in the sequence (maxvalue and minvalue).

    • NOEXTEND — sequence value will be padded to the max value. This is the default option when using the SCALE option.

  • NOSCALE - non-scalable sequence usage.

Oracle Sequence Options

By default, the initial and increment values for a sequence are both 1, with no upper limit.

  • INCREMENT BY — Controls the sequence interval value of the increment or decrement (if a negative value is specified). If the INCREMENT BY parameter isn’t specified during sequence creation, the value is set to 1. The increment cannot be assigned a value of 0.

  • START WITH — Defines the initial value of a sequence. The default value is 1.

  • MAXVALUE and NOMAXVALUE — Specifies the maximum limit for values generated by a sequence. It must be equal or greater than the START WITH parameter and must be greater in value than the MINVALUE parameter. The default for NOMAXVALUE is 1027 for an ascending sequence.

  • MINVALUE and NOMINVALUE — Specifies the minimum limit for values generated by a sequence. Must be less than or equal to the START WITH parameter and must be less than the MAXVALUE parameter. The default for NOMINVALUE is -1026 for a descending sequence.

  • CYCLE and NOCYCLE — Instructs a sequence to continue generating values despite reaching the maximum value or the minimum value. If the sequence reaches one of the defined ascending limits, it generates a new value according to the minimum value. If it reaches a descending limit, it generates a new value according to the maximum value. The default option is NOCYCLE.

  • CACHE and NOCACHE — Specifies the number of sequence values to keep cached in memory for improved performance. CACHE has a minimum value of 2. The NOCACHE parameter causes a sequence to not cache values in memory. Specifying neither CACHE nor NOCACHE will cache 20 values to memory. In the event of a database failure, all unused cached sequence values are lost and gaps in sequence values may occur.

  • SCALE and NOSCALE: Turns on the scalable sequences feature.

Examples

Create a sequence.

CREATE SEQUENCE SEQ_EMP
START WITH 100
INCREMENT BY 1
MAXVALUE 99999999999
CACHE 20
NOCYCLE;

Drop a sequence.

DROP SEQUENCE SEQ_EMP;

View sequences created for the current schema or user.

SELECT * FROM USER_SEQUENCES;

Use a sequence as part of an INSERT INTO statement.

CREATE TABLE EMP_SEQ_TST (COL1 NUMBER PRIMARY KEY, COL2 VARCHAR2(30));
INSERT INTO EMP_SEQ_TST VALUES(SEQ_EMP.NEXTVAL, 'A');

COL1  COL2
100   A

Query the current value of a sequence.

SELECT SEQ_EMP.CURRVAL FROM DUAL;

Manually increment the value of a sequence according to the INCREMENT BY specification.

SELECT SEQ_EMP.NEXTVAL FROM DUAL;

Alter an existing sequence.

ALTER SEQUENCE SEQ_EMP MAXVALUE 1000000;

Create a scalable sequence.

CREATE SEQUENCE scale_seq
MINVALUE 1
MAXVALUE 9999999999
SCALE;

select scale_seq.nextval as scale_seq from dual;

NEXTVAL
1010320001

Oracle 12c Default Values Using Sequences

Starting from Oracle 12c, you can assign a sequence to a table column with the CREATE TABLE statement and specify the NEXTVAL configuration of the sequence.

Generate DEFAULT values using sequences.

CREATE TABLE SEQ_TST ( COL1 NUMBER DEFAULT SEQ_1.NEXTVAL PRIMARY KEY, COL2 VARCHAR(30));

INSERT INTO SEQ_TST(COL2) VALUES('A');

SELECT * FROM SEQ_TST;

COL1  COL2
100   A

Oracle 12c Session Sequences

Starting from Oracle 12c, you can create sequences as session-level or global-level. By adding the SESSION parameter to a CREATE SEQUENCE statement, the sequence is created as a session-level sequence. Optionally, the GLOBAL keyword can be used to create a global sequence to provide consistent results across sessions in the database. Global sequences are the default. Session sequences return a unique range of sequence numbers only within a session.

The following example creates Oracle 12c SESSION and GLOBAL sequences.

CREATE SEQUENCE SESSION_SEQ SESSION;
CREATE SEQUENCE SESSION_SEQ GLOBAL;

Oracle 12c Identity Columns

Oracle 12c introduced support for automatic generation of values to populate columns in database tables. The IDENTITY type generates a sequence and associates it with a table column without the need to manually create a separate Sequence object. It relies internally on sequences and can be manually configured.

Sequences can be used as an IDENTITY type, which automatically creates a sequence and associates it with the table column. The main difference is that there is no need to create a sequence manually; the IDENTITY type does that for you. An IDENTITY type is a sequence that can be configured.

Create a table with an Oracle 12c Identity Column.

CREATE TABLE IDENTITY_TST (
  COL1 NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 100
  INCREMENT BY 10),
COL2 VARCHAR2(30));

Insert records using an Oracle 12c IDENTITY column (explicitly/implicitly).

INSERT INTO IDENTITY_TST(COL2) VALUES('A');
INSERT INTO IDENTITY_TST(COL1, COL2) VALUES(DEFAULT, 'B');
INSERT INTO IDENTITY_TST(col1, col2) VALUES(NULL, 'C');

SELECT * FROM IDENTITY_TST;

COL1  COL2
120   A
130   B

For more information, see CREATE SEQUENCE in the Oracle documentation.

MySQL Usage

Aurora MySQL supports automatic sequence generation using the AUTO_INCREMENT column property, similar to the Oracle IDENTITY column property. It doesn’t support table-independent sequence objects.

Any numeric column may be assigned the AUTO_INCREMENT property. To make the system generate the next sequence value, the application must not mention the relevant column’s name in the insert command, in case the column was created with the NOT NULL definition then also inserting a NULL value into an AUTO_INCREMENT column will increment it. In most cases, the seed value is 1 and the increment is 1.

Client applications use the LAST_INSERT_ID function to obtain the last generated value.

Each table can have only one AUTO_INCREMENT column. Make sure that the column is explicitly indexed or is a primary key (which is indexed by default).

The AUTO_INCREMENT mechanism is designed to be used with positive numbers only. Do not use negative values because they are misinterpreted as a complementary positive value. This limitation is due to precision issues with sequences crossing a zero boundary.

There are two server parameters used to alter the default values for new AUTO_INCREMENT columns:

  • auto_increment_increment — Controls the sequence interval.

  • auto_increment_offset — Determines the starting point for the sequence.

To reseed the AUTO_INCREMENT value, use ALTER TABLE <Table Name> AUTO_INCREMENT = <New Seed Value>.

Migration Considerations

Because Aurora MySQL doesn’t support table-independent SEQUENCE objects, applications that rely on its properties must use custom solutions to meet their requirements.

You can use Aurora MySQL AUTO_INCREMENT instead of Oracle IDENTITY for most cases. For AUTO_INCREMENT columns, the application must explicitly INSERT a NULL or a 0.

Note

Omitting the AUTO_INCREMENT column from the INSERT column list has the same effect as inserting a NULL value.

Make sure that the AUTO_INCREMENT columns are indexed (the following section explains why) and cannot have default constraints assigned to the same column. There is a critical difference between IDENTITY and AUTO_INCREMENT in the way the sequence values are maintained upon service restart. Application developers must be aware of this difference.

Sequence Value Initialization

Oracle stores the IDENTITY metadata in system tables on disk. Although some values may be cached and are lost when the service is restarted, the next time the server restarts, the sequence value continues after the last block of values that was assigned to cache. If you run out of values, you can explicitly set the sequence value to start the cycle over. As long as there are no key conflicts, it can be reused after the range has been exhausted.

In Aurora MySQL, an AUTO_INCREMENT column for a table uses a special auto-increment counter to assign new values for the column. This counter is stored in cache memory only and is not persisted to disk. After a service restart, and when Aurora MySQL encounters an INSERT to a table that contains an AUTO_INCREMENT column, it issues an equivalent to the following statement:

SELECT MAX(<Auto Increment Column>) FROM <Table Name> FOR UPDATE;
Note

The FOR UPDATE CLAUSE is required to maintain locks on the column until the read completes.

Aurora MySQL then increments the value retrieved by the statement above and assigns it to the in-memory autoincrement counter for the table.

By default, the value is incremented by one. You can change this default using the auto_increment_increment configuration setting. If the table has no values, Aurora MySQL uses the value 1. You can change the default using the auto_increment_offset configuration setting.

Every server restart effectively cancels any AUTO_INCREMENT = <Value> table option in CREATE TABLE and ALTER TABLE statements.

Unlike Oracle IDENTITY columns, which by default do not allow inserting explicit values, Aurora MySQL allows explicit values to be set. If a row has an explicitly specified AUTO_INCREMENT column value and the value is greater than the current counter value, the counter is set to the specified column value.

Examples

Create a table with an AUTO_INCREMENT column.

CREATE TABLE MyTable (Col1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Col2 VARCHAR(20) NOT NULL);

Insert AUTO_INCREMENT values.

INSERT INTO MyTable (Col2) VALUES ('AI column omitted');
INSERT INTO MyTable (Col1, Col2) VALUES (NULL, 'Explicit NULL');
INSERT INTO MyTable (Col1, Col2) VALUES (10, 'Explicit value');
INSERT INTO MyTable (Col2) VALUES ('Post explicit value');
SELECT * FROM MyTable;
Col1  Col2
1     AI column omitted
2     Explicit NULL
10    Explicit value
11    Post explicit value

Reseed AUTO_INCREMENT.

ALTER TABLE MyTable AUTO_INCREMENT = 30;
INSERT INTO MyTable (Col2) VALUES ('Post ALTER TABLE');
SELECT * FROM MyTable;
Col1  Col2
1     AI column omitted
2     Explicit NULL
10    Explicit value
11    Post explicit value
30    Post ALTER TABLE

Summary

The following table identifies similarities, differences, and key migration considerations.

Feature Oracle Aurora MySQL Comments

Create a table

CREATE TABLE IDENTITY_TST (
  COL1 NUMBER GENERATED BY DEFAULT
  AS IDENTITY
    (START WITH 100
    INCREMENT BY 10),
    COL2 VARCHAR2(30));
CREATE TABLE AUTO_TST (
  COL1 INT AUTO_INCREMENT PRIMARY KEY,
  COL2 VARCHAR(30));

Set the starting number

CREATE TABLE IDENTITY_TST (
  COL1 NUMBER GENERATED BY DEFAULT
  AS IDENTITY (
    START WITH 100
    INCREMENT BY 10),
COL2 VARCHAR2(30));
ALTER TABLE AUTO_TST
AUTO_INCREMENT = 100;

Or use the auto_increment_offset parameter

Set the interval

CREATE TABLE IDENTITY_TST (
  COL1 NUMBER GENERATED BY DEFAULT
  AS IDENTITY (
    START WITH 100
    INCREMENT BY 10),
COL2 VARCHAR2(30));

Set the auto_increment_increment parameter

Additional permitted values

DEFAULT, NULL

None

Independent SEQUENCE object

CREATE SEQUENCE

Not supported

Automatic enumerator column property

IDENTITY

AUTO_INCREMENT

Reseed sequence value

Recreate the sequence

ALTER TABLE

Column restrictions

Numeric

Numeric, indexed, and no DEFAULT

Controlling seed and interval values

CREATE/ALTER TABLE

auto_increment_increment and auto_increment_offset

Aurora MySQL settings are global and can’t be customized for each column as with Oracle.

Sequence setting initialization

Maintained through service restarts

Re-initialized every service restart

For more information, see Sequence Value Initialization.

Explicit values to column

Not supported

Supported

Aurora MySQL requires explicit NULL or 0 to trigger sequence value assignment. Inserting an explicit value larger than all others reinitializes the sequence.

For more information, see Using AUTO_INCREMENT, CREATE TABLE Statement, and InnoDB AUTO_INCREMENT Counter Initialization in the MySQL documentation.