createIndex

The createIndex Change Type creates an index on an existing column or a set of columns.

Uses

You can typically use the createIndex Change Type when you want to add an index on one of the columns or a set of columns to increase the searchability of the records in your database.

If you set the clustered attribute to true, Liquibase will create a clustered index, which defines the order in which records are stored in a table. If you set the clustered attribute to false, Liquibase will create a non-clustered index, where the order of the rows will not match the order of the actual records.

When creating indexes, Liquibase uses the order of columns that you list in the createIndex Change Type. For example:

<changeSet author="bob" id="1_createTable_department">
   <createTable tableName="department">
      <column name="col_1" type="integer"/>
      <column name="col_2" type="integer"/>
      <column name="col_3" type="integer"/>
   </createTable>
  </changeSet>				
<changeSet author="bob" id="1_createIndex_index_one">
<createIndex indexName="index_one" tableName="department">
      <column name="col_1"/>
      <column name="col_2"/>
      <column name="col_3"/>
  </createIndex>
 </changeSet>				
<changeSet author="bob" id="2_createIndex_index_two">
<createIndex indexName="index_two" tableName="department">
      <column name="col_3"/>
      <column name="col_2"/>
      <column name="col_1"/> 
  </createIndex>
 </changeSet>
<changeSet author="bob" id="3_createIndex_index_three">								
<createIndex indexName="index_three" tableName="department">
      <column name="col_2"/>
      <column name="col_3"/>
      <column name="col_1"/>
 </createIndex>
</changeSet>

These changesets will produce the following (the updateSQL command example):

CREATE TABLE public.department (col_1 INT, col_2 INT, col_3 INT);
CREATE INDEX index_one ON public.department(col_1, col_2, col_3);
CREATE INDEX index_two ON public.department(col_3, col_2, col_1);
CREATE INDEX index_three ON public.department(col_2, col_3, col_1);

Running the `createIndex` Change Type

To create an index on the existing column or set of columns, follow these steps:

Step 1: Add the createIndex Change Type to your changeset with the needed attributes as it is shown in the examples.

Step 2: Deploy your changeset by running the update command.

liquibase update

Available attributes

Name	Description	Required for	Supports
`catalogName`	The name of the catalog.		all
`clustered`	The attribute that creates a clustered index.		all
`indexName`	The name of the index to create.	firebird, hsqldb	all
`schemaName`	The name of the schema.		all
`tableName`	The name of the table for which to add the index.	all	all
`tablespace`	The tablespace in which you create the index.		all
`unique`	The unique values in the index.		all

Nested properties

Name	Description	Required for	Supports	Multiple allowed
`columns` / `column`	The column(s) to add to the index.	all	all	yes

Nested property attributes

Name	Description	Required for	Supports
`name`	The name of the column. It can contain the direction by appending `ASC` or `DESC` to the name.	all	all
`computed`	The attribute to set to `true` if the value in `name` isn't actually a column name but a function. Boolean.		Since 3.3.0.
`descending`	The attribute which allows to specify that a column should be used in a descending order in the index. (i.e. ascending order)		Since 3.4.

XML example

<changeSet author="liquibase-docs" id="createIndex-example">  
    <createIndex clustered= "true" 
            indexName="idx_address"
            schemaName="public"
            tableName="person" 
            tablespace="A String" 
            unique="true"> 
        <column descending="true" name="address"/> 
    </createIndex>  
</changeSet>

YAML example

changeSet:  
  id:  createIndex-example
  author:  liquibase-docs 
  changes: 
  - createIndex:   
     clustered:  true  
     columns:  
     - column:
        descending:  true
        name:  address 
     indexName:  idx_address 
     schemaName:  public  
     tableName:  person 
     tablespace:  A String
     unique:  true

JSON example

{
 "databaseChangeLog":[
   {
     "changeSet":{
       "id":"createIndex-example",
       "author":"liquibase-docs",
       "changes":[
         {
           "createIndex":{
             "clustered":true,
             "columns":[
               {
                 "column":{
                   "descending":true,
                   "name":"address"
                 }
               }
             ],
             "indexName":"idx_address",
        	 "schemaName":"public",
        	 "tableName":"person",
        	 "tablespace":"A String",
        	 "unique":true
            }
          }
        ]
      }
    }
  ]
}

SQL example

-- changeset DocUser:1:createIndexChangeType
CREATE  UNIQUE  INDEX  idx_address  ON  department.person(address  DESC);

Database support

Database	Notes	Auto rollback
DB2/LUW	Supported	Yes
DB2/z	Supported	Yes
Derby	Supported	Yes
Firebird	Supported	Yes
H2	Supported	Yes
HyperSQL	Supported	Yes
INGRES	Supported	Yes
Informix	Supported	Yes
MariaDB	Supported	Yes
MySQL	Supported	Yes
Oracle	Supported	Yes
PostgreSQL	Supported	Yes
SQL Server	Supported	Yes
SQLite	Supported	Yes
Sybase	Supported	Yes
Sybase Anywhere	Supported	Yes