Liquibase® version 3.8.5 is now available! Get it for free.
2018 XML Announcement
News All Previous Posts >>

Subscribe for email updates

- and/or -

Change: ‘addLookupTable’

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

Available Attributes

NameDescription
constraintNameName of the foreign-key constraint to create between the existing table and the lookup tableE.g. 'fk_address_state'
existingColumnNameName of the column containing the data to extractE.g. 'state'
existingTableCatalogName
existingTableNameName of the table containing the data to extractE.g. 'address'
existingTableSchemaName
newColumnDataTypeData type of the new table columnE.g. 'char(2)'Required for: informix, mariadb, mssql, mysql
newColumnNameName of the column in the new table to createE.g. 'abbreviation'
newTableCatalogName@ v3.0E.g. 'cat'
newTableNameName of lookup table to createE.g. 'state'
newTableSchemaNameE.g. 'public'
<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
DB2/LUWSupportedYes
DB2/zSupportedYes
DerbySupportedYes
FirebirdNot SupportedYes
H2SupportedYes
HyperSQLNot SupportedYes
INGRESSupportedYes
InformixSupportedYes
MariaDBSupportedYes
MySQLSupportedYes
OracleSupportedYes
PostgreSQLSupportedYes
SQL ServerSupportedYes
SQLiteNot SupportedYes
SybaseSupportedYes
Sybase AnywhereSupportedYes