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. 
<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>
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
{
 "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
            }
          }
        ]
      }
    }
  ]
}
-- 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