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

XML example

<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>

YAML example

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

JSON example

{  "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 example

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