addLookupTable

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

Available attributes

Name Description Required for Supports
constraintName Name of the foreign-key constraint to create between the existing table and the lookup table all
existingColumnName Name of the column containing the data to extract all all
existingTableCatalogName all
existingTableName Name of the table containing the data to extract all all
existingTableSchemaName all
newColumnDataType Data type of the new table column informix, mariadb, mssql, mysql all
newColumnName Name of the column in the new table to create all all
newTableCatalogName all
newTableName Name of lookup table to create all all
newTableSchemaName all 
<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"
            }
        }]
    }
}
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

Database Notes Auto rollback
DB2/LUW Supported Yes
DB2/z Supported Yes
Derby Supported Yes
Firebird Not Supported Yes
H2 Supported Yes
HyperSQL Not 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 Not Supported Yes
Sybase Supported Yes
Sybase Anywhere Supported Yes