addLookupTable
Creates a lookup table containing values stored in a column and creates a foreign key to the new table.
Available attributes
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
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
|