Liquibase® version 3.8.2 is now available! Get it for free.
2018 XML Announcement
News 5 Ways to Fix a Bad Database Change in Liquibase
04 Dec 2019

- and/or -

All Previous Posts >>

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, mariadb, mssql, 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"
            newTableCatalogName="cat"
            newTableName="state"
            newTableSchemaName="public"/>
</changeSet>
changeSet:
  id: addLookupTable-example
  author: liquibase-docs
  changes:
  - addLookupTable:
      constraintName: fk_address_state
      existingColumnName: state
      existingTableName: address
      newColumnDataType: char(2)
      newColumnName: abbreviation
      newTableCatalogName: cat
      newTableName: state
      newTableSchemaName: public
{
  "changeSet": {
    "id": "addLookupTable-example",
    "author": "liquibase-docs",
    "changes": [
      {
        "addLookupTable": {
          "constraintName": "fk_address_state",
          "existingColumnName": "state",
          "existingTableName": "address",
          "newColumnDataType": "char(2)",
          "newColumnName": "abbreviation",
          "newTableCatalogName": "cat",
          "newTableName": "state",
          "newTableSchemaName": "public"
        }
      }]
    
  }
}

SQL Generated From Above Sample (MySQL)

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

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

ALTER TABLE public.state ADD PRIMARY KEY (abbreviation);

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

Database Support

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