# Liquibase addLookupTable is not creating foreign key

**URL:** <https://forum.liquibase.org/t/liquibase-addlookuptable-is-not-creating-foreign-key/8684>\
**Category:** General Discussion\
**Created:** [September 22, 2023, 3:33am UTC](https://forum.liquibase.org/t/liquibase-addlookuptable-is-not-creating-foreign-key/8684 "2023-09-22T03:33:52Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![shrikant.sharma606](https://avatars.discourse-cdn.com/v4/letter/s/65b543/32.png) [@shrikant.sharma606](https://forum.liquibase.org/u/shrikant.sharma606)\
**Post date:** [September 22, 2023, 3:33am UTC](https://forum.liquibase.org/t/liquibase-addlookuptable-is-not-creating-foreign-key/8684/1 "2023-09-22T03:33:52Z")

</div>

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

```auto
<?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

```auto
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?

---

<div class="post-metadata">

**Author:** ![kevin](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/kevin/32/244_2.png) [@kevin](https://forum.liquibase.org/u/kevin)\
**Post date:** [October 27, 2023, 7:13pm UTC](https://forum.liquibase.org/t/liquibase-addlookuptable-is-not-creating-foreign-key/8684/2 "2023-10-27T19:13:31Z")

</div>

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:

```auto
[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.

```auto
-- 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);

```