Liquibase addLookupTable is not creating foreign key

General Discussion
1

Hi all, i am new to Liquibase , I am learning addLookupTable from Liquibase docs, my understanding of lookup table is, that all the repeated data in the primary table will be put into a lookup table and a foreign key constraint will be created between both tables.

below is the changelog file I am using

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
	<changeSet author="wesome" id="1692599548266-1">
		<createTable catalogName="studentdb" tableName="student">
			<column autoIncrement="true" name="student_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="student_name" type="VARCHAR(255)" />
			<column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime" />
		</createTable>
	</changeSet>
	<changeSet author="wesome" id="1692701200879-2">
		<insert catalogName="studentdb" tableName="student">
			<column name="student_name" value="StudentA" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
	</changeSet>
	<changeSet author="wesome" id="1692701200879-3">
		<insert catalogName="studentdb" tableName="student">
			<column name="student_name" value="StudentB" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
	</changeSet>
	<changeSet author="wesome" id="1692701200879-4">
		<addLookupTable constraintName="fk_student_name" existingColumnName="student_name" existingTableName="student" newColumnDataType="VARCHAR(255)" newColumnName="student_name_lookup" newTableCatalogName="studentDb" newTableName="student_lookup" newTableSchemaName="studentDb" />
	</changeSet>
</databaseChangeLog>

liquibase.properties

changeLogFile=changelog.mysql.xml
liquibase.command.url:jdbc:mysql://localhost:3306/studentDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username : root
liquibase.command.password : rootroot

but the above script creates a student table and student_lookup table with all unique values from the student table, but there is no relation between them.
both the student table and student_lookup table have the same data, and the newly created student_lookup table doesn’t have a primary key as well.
what I am missing?

2

Hi @shrikant.sharma606

To troubleshoot this on your computer, please execute the liquibase update-sql command to see the SQL that Liquibase will generate from the changeset. This command will not update the database, and it will give you a way to understand the SQL Liquibase creates when it translates the change type into SQL.

Here is my test. I added the sample code from the addLookupTable documentation to my changelog, adjusted one of my existing tables to contain the state field, and ran the liquibase update-sql command.

Here is my output:

[h2_xml]% liquibase update-sql

Starting Liquibase at 14:02:36 (version 4.24.0 #14062 built at 2023-09-28 12:18+0000)
Liquibase Version: 4.24.0
Liquibase Open Source 4.24.0 by Liquibase
-- Lock Database
UPDATE PUBLIC.DATABASECHANGELOGLOCK SET LOCKED = TRUE, LOCKEDBY = 'Kevins2022MBP16.attlocal.net (192.168.1.197)', LOCKGRANTED = NOW() WHERE ID = 1 AND LOCKED = FALSE;

-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: changelog.xml
-- Ran at: 10/27/23 2:02 PM
-- Against: DBUSER@jdbc:h2:tcp://localhost:9090/mem:dev
-- Liquibase version: 4.24.0
-- *********************************************************************

-- Changeset changelog.xml::addLookupTable-example::liquibase-docs
CREATE TABLE PUBLIC.state AS SELECT DISTINCT state AS abbreviation FROM PUBLIC.person WHERE state IS NOT NULL;

ALTER TABLE PUBLIC.state ALTER COLUMN abbreviation SET NOT NULL;

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

ALTER TABLE PUBLIC.person ADD CONSTRAINT fk_address_state FOREIGN KEY (state) REFERENCES PUBLIC.state (abbreviation);

INSERT INTO PUBLIC.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('addLookupTable-example', 'liquibase-docs', 'changelog.xml', NOW(), 4, '9:38bbb237200a9dce305b801bb7b720c5', 'addLookupTable constraintName=fk_address_state, existingColumnName=state, existingTableName=person, newColumnName=abbreviation, newTableName=state', '', 'EXECUTED', NULL, NULL, '4.24.0', NULL);

-- Release Database Lock
UPDATE PUBLIC.DATABASECHANGELOGLOCK SET LOCKED = FALSE, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;

Liquibase command 'update-sql' was executed successfully.

The interesting part is the SQL code between the DATABASECHANGELOG table updates. This is the SQL code that Liquibase generated from the addLookupTable change.

-- Changeset changelog.xml::addLookupTable-example::liquibase-docs
CREATE TABLE PUBLIC.state AS SELECT DISTINCT state AS abbreviation FROM PUBLIC.person WHERE state IS NOT NULL;

ALTER TABLE PUBLIC.state ALTER COLUMN abbreviation SET NOT NULL;

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

ALTER TABLE PUBLIC.person ADD CONSTRAINT fk_address_state FOREIGN KEY (state) REFERENCES PUBLIC.state (abbreviation);