addAutoIncrement

Converts an existing column to be an auto-increment (a.k.a ‘identity') column.

Available attributes

Attribute Description Required for Supports
catalogName Name of the catalog   all
columnDataType Current data type of the column to make auto-increment asany, h2, hsqldb, informix, ingres, mariadb, mysql, sybase, unsupported all
columnName Name of the column all all
defaultOnNull When using generationType 'BY DEFAULT' then defaultOnNull = true allows the identity to be used if the identity column is referenced, but a value of NULL is specified.   oracle
generationType Type of the generation in 'GENERATED %s AS IDENTITY'. ALWAYS | BY DEFAULT   all
incrementBy Integer value the increment increments at each call   all
schemaName Name of the schema   all
startWith Initial integer value of the increment   all
tableName Name of the table all all 
<changeSet author="liquibase-docs" id="addAutoIncrement-example">  
	<addAutoIncrement catalogName="cat"  
		columnDataType="int"
		columnName="id" 
		defaultOnNull="false"
		generationType="ALWAYS"
		incrementBy="1"  
		schemaName="public" 
		startWith="100"  
		tableName="person"/>  
</changeSet>
- changeSet:
    id: addAutoIncrement-example
    author: liquibase-docs
    changes:
    -   addAutoIncrement:
        catalogName: cat
        columnDataType: int
        columnName: id
        defaultOnNull: false
        generationType: ALWAYS
        incrementBy: 1
        schemaName: public
        startWith: 100
        tableName: person
{ 
	"changeSet": {
		"id": "addAutoIncrement-example",
		"author": "liquibase-docs",
		"changes": [
		{
			"addAutoIncrement": {
				"catalogName": "cat",
				"columnDataType": "int",
				"columnName": "id",
				"defaultOnNull": false,
				"generationType": "ALWAYS",
				"incrementBy": 1,
				"schemaName": "public",
				"startWith": 100,
				"tableName": "person"
			}
		}]
	}
}

SQL Example

ALTER TABLE cat.person MODIFY id INT AUTO_INCREMENT;  
ALTER TABLE cat.person AUTO_INCREMENT=100;

Database support

Database Notes Auto rollback
DB2/LUW Supported No
DB2/z Not Supported No
Derby Not Supported No
Firebird Not Supported No
H2 Supported No
HyperSQL Supported No
INGRES Supported No
Informix Supported No
MariaDB Supported No
MySQL Supported No
Oracle Not Supported No
PostgreSQL Supported No
Snowflake Not Supported No
SQL Server Not Supported No
SQLite Supported: If the column type is not INTEGER it is converted to INTEGER No
Sybase Supported No
Sybase Anywhere Supported No