` 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\$1INCREMENT 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\$1INCREMENT 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](#chap-oracle-aurora-mysql.sql.identity.mysql.sequence). |
| 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\$1INCREMENT](https://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html), [CREATE TABLE Statement](https://dev.mysql.com/doc/refman/5.7/en/create-table.html), and [InnoDB AUTO\$1INCREMENT Counter Initialization](https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization) in the *MySQL documentation*.