Oracle sequences and identity columns and MySQL sequences and AUTO INCREMENT columns
Oracle sequences and identity columns, as well as MySQL Sequences and AUTO_INCREMENT
columns, are database objects used to generate unique sequential values, often employed as primary keys or unique identifiers. The following sections provide detailed guidance on handling Oracle sequences and identity columns, and MySQL sequences and AUTO_INCREMENT columns when using Amazon DMS.
Feature compatibility | Amazon SCT / Amazon DMS automation level | Amazon SCT action code index | Key differences |
---|---|---|---|
|
|
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
andminvalue
). -
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 theINCREMENT 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
andNOMAXVALUE
— Specifies the maximum limit for values generated by a sequence. It must be equal or greater than theSTART WITH
parameter and must be greater in value than theMINVALUE
parameter. The default forNOMAXVALUE
is 1027 for an ascending sequence. -
MINVALUE
andNOMINVALUE
— Specifies the minimum limit for values generated by a sequence. Must be less than or equal to theSTART WITH
parameter and must be less than theMAXVALUE
parameter. The default forNOMINVALUE
is -1026 for a descending sequence. -
CYCLE
andNOCYCLE
— 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 isNOCYCLE
. -
CACHE
andNOCACHE
— Specifies the number of sequence values to keep cached in memory for improved performance.CACHE
has a minimum value of 2. TheNOCACHE
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
andNOSCALE
: 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
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 |
|
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 |
|
Additional permitted values |
DEFAULT, NULL |
None |
|
Independent |
|
Not supported |
|
Automatic enumerator column property |
|
|
|
Reseed sequence value |
Recreate the sequence |
|
|
Column restrictions |
Numeric |
Numeric, indexed, and no |
|
Controlling seed and interval values |
|
|
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