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

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:

Oracle Sequence Options

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

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:

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.

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;

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.

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));

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;

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

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