Need Liquibase® support? Liquibase Pro is now available! Try it out free.
2018 XML Announcement

Change: ‘addLookupTable’

Creates a lookup table containing values stored in a column and creates a foreign key to the new table.

Available Attributes

NameDescriptionRequired ForSupportsSince
constraintNameName of the foreign-key constraint to create between the existing table and the lookup tableall
existingColumnNameName of the column containing the data to extractallall
existingTableCatalogNameall
existingTableNameName of the table containing the data to extractallall
existingTableSchemaNameall
newColumnDataTypeData type of the new table columninformix, mssql, h2, mysqlall
newColumnNameName of the column in the new table to createallall
newTableCatalogNameall3.0
newTableNameName of lookup table to createallall
newTableSchemaNameall
<changeSet author="liquibase-docs" id="addLookupTable-example">
    <addLookupTable constraintName="fk_address_state"
            existingColumnName="state"
            existingTableName="address"
            newColumnDataType="char(2)"
            newColumnName="abbreviation"
            newTableName="state"/>
</changeSet>
changeSet:
  id: addLookupTable-example
  author: liquibase-docs
  changes:
  - addLookupTable:
      constraintName: fk_address_state
      existingColumnName: state
      existingTableName: address
      newColumnDataType: char(2)
      newColumnName: abbreviation
      newTableName: state
{
  "changeSet": {
    "id": "addLookupTable-example",
    "author": "liquibase-docs",
    "changes": [
      {
        "addLookupTable": {
          "constraintName": "fk_address_state",
          "existingColumnName": "state",
          "existingTableName": "address",
          "newColumnDataType": "char(2)",
          "newColumnName": "abbreviation",
          "newTableName": "state"
        }
      }]
    
  }
}

SQL Generated From Above Sample (MySQL)

CREATE TABLE state AS SELECT DISTINCT state AS abbreviation FROM address WHERE state IS NOT NULL;

ALTER TABLE state MODIFY abbreviation CHAR(2) NOT NULL;

ALTER TABLE state ADD PRIMARY KEY (abbreviation);

ALTER TABLE address ADD CONSTRAINT fk_address_state FOREIGN KEY (state) REFERENCES state (abbreviation);

Database Support

DatabaseNotesAuto Rollback
DB2SupportedYes
DerbySupportedYes
FirebirdNot SupportedYes
H2SupportedYes
HyperSQLSupportedYes
InformixSupportedYes
MySQLSupportedYes
OracleSupportedYes
PostgreSQLSupportedYes
SQL ServerSupportedYes
SQLiteNot SupportedYes
SybaseSupportedYes
Sybase AnywhereSupportedYes