Liquibase Foreign Key Constraint Fails

Liquibase the generateChangeLog command to create a ChangeLog file for an existing database. By default, the generateChangeLog command will generate a big ChangeLog file which will have all the tables, functions, views, columns, indexes, foreign keys, primary keys, unique constraints, data, stored procedures, triggers, sequences, etc.

Liquibase does not try to check how columns depend on another table or a table is having foreign key relationship hence Liquibase doesn't generate the changeset in the right order when trying to update the same ChangeLog into another database sometimes it results in error Cannot add or update a child row: a foreign key constraint fails.

Let's see the same with an example

assume we have SQL schema as below, The Taste table is a lookup table and the Apple table record has a foreign key constraint on the Taste table, so the correct order of execution of SQL script is

create taste table
insert record in taste table
create apple table
insert record in apple table

create DATABASE IF NOT EXISTS `appledb`;
USE `appledb`;
create TABLE `taste` (
 `taste_id` int NOT NULL auto_increment,
 `taste` varchar(2000) NOT NULL,
 PRIMARY KEY (`taste_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

insert into `taste` (`taste`) VALUES ('sweet'),('sour');


create TABLE `apple` (
 `apple_id` bigint NOT NULL auto_increment,
 `apple_name` varchar(255) DEFAULT NULL,
 `taste_id` int NOT NULL,
 PRIMARY KEY (`apple_id`),
 KEY `FKqg39etajdct3xxd813arauyit` (`taste_id`),
 CONSTRAINT `taste_id_taste_fk` FOREIGN KEY (`taste_id`) REFERENCES `taste` (`taste_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

insert into `apple` (`apple_name`, `taste_id` ) VALUES ('Macintosh',1),('Fuji',2);
commit;

Execute the above SQL script, it will create tables and insert records.

Let's generate an equivalent ChangeLog for the above schema.

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

Execute the command liquibase generateChangeLog, it will create a new changelog.mysql.xml file containing the creation and insertion changeset of the Apple and Taste table but without proper order. like below

<?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="shriksha (generated)" id="1696663935821-1">
		<createTable catalogName="appledb" tableName="apple">
			<column autoIncrement="true" name="apple_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="apple_name" type="VARCHAR(255)" />
			<column name="taste_id" type="INT">
				<constraints nullable="false" />
			</column>
		</createTable>
	</changeSet>
	<changeSet author="shriksha (generated)" id="1696663935821-2">
		<createTable catalogName="appledb" tableName="taste">
			<column autoIncrement="true" name="taste_id" type="INT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="taste" type="VARCHAR(2000)">
				<constraints nullable="false" />
			</column>
		</createTable>
	</changeSet>
	<changeSet author="shriksha (generated)" id="1696663935821-3">
		<addForeignKeyConstraint baseColumnNames="taste_id" baseTableCatalogName="appledb" baseTableName="apple" constraintName="taste_id_taste_fk" deferrable="false" initiallyDeferred="false" onDelete="RESTRICT" onUpdate="RESTRICT" referencedColumnNames="taste_id" referencedTableCatalogName="appledb" referencedTableName="taste" validate="true" />
	</changeSet>
	<changeSet author="shriksha (generated)" id="1696663935821-4">
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_id" valueNumeric="1" />
			<column name="apple_name" value="Macintosh" />
			<column name="taste_id" valueNumeric="1" />
		</insert>
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_id" valueNumeric="2" />
			<column name="apple_name" value="Fuji" />
			<column name="taste_id" valueNumeric="2" />
		</insert>
	</changeSet>
	<changeSet author="shriksha (generated)" id="1696663935821-5">
		<insert catalogName="appledb" tableName="taste"
			<column name="taste_id" valueNumeric="1" />
			<column name="taste" value="sweet" />
		</insert>
		<insert catalogName="appledb" tableName="taste">
			<column name="taste_id" valueNumeric="2" />
			<column name="taste" value="sour" />
		</insert>
	</changeSet>
	<changeSet author="shriksha (generated)" id="1696663935821-6">
		<createIndex catalogName="appledb" indexName="FKqg39etajdct3xxd813arauyit" tableName="apple">
			<column name="taste_id" />
		</createIndex>
	</changeSet>
</databaseChangeLog>

drop existing appleDb and try to create and insert data from the above ChangeLog file into the database, it will result into

liquibase.exception.DatabaseException: Cannot add or update a child row: a foreign key constraint fails (`appledb`.`apple`, CONSTRAINT `taste_id_taste_fk` FOREIGN KEY (`taste_id`) REFERENCES `taste` (`taste_id`) ON DELETE RESTRICT ON UPDATE RESTRICT)

the reason is as stated above, Taste is an lookup table and has Apple table has forign key dependency on it, hence its creation and insertion should be prioritized over Apple table, but Liquibase doesn't care about it while generating ChangeLog file.

we have already seen the correct way to handle these types of issues is to Generate Changelogs per Table, but if same ChangeLog is required to update, then there is a workaround by disabling foreign key checks at the time of insertion and enable again after all the data inserted for both table.

Disabling Foreign Key Check will pause Foreign Key Constraint until enabled again manually

Lets update the same ChangeLog as below

<?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="shriksha (generated)" id="1696663935821-1" runAlways="true">
		<sql>
			SET FOREIGN_KEY_CHECKS=0;
		</sql>
	</changeSet>
	<changeSet author="shriksha (generated)" id="1696663935821-2">
		<createTable catalogName="appledb" tableName="apple">
			<column autoIncrement="true" name="apple_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="apple_name" type="VARCHAR(255)" />
			<column name="taste_id" type="INT">
				<constraints nullable="false" />
			</column>
		</createTable>
	</changeSet>
	<changeSet author="shriksha (generated)" id="1696663935821-3">
		<createTable catalogName="appledb" tableName="taste">
			<column autoIncrement="true" name="taste_id" type="INT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="taste" type="VARCHAR(2000)">
				<constraints nullable="false" />
			</column>
		</createTable>
	</changeSet>
	<changeSet author="shriksha (generated)" id="1696663935821-4">
		<addForeignKeyConstraint baseColumnNames="taste_id" baseTableCatalogName="appledb" baseTableName="apple" constraintName="taste_id_taste_fk" deferrable="false" initiallyDeferred="false" onDelete="RESTRICT" onUpdate="RESTRICT" referencedColumnNames="taste_id" referencedTableCatalogName="appledb" referencedTableName="taste" validate="true" />
	</changeSet>
	<changeSet author="shriksha (generated)" id="1696663935821-5">
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_id" valueNumeric="1" />
			<column name="apple_name" value="Macintosh" />
			<column name="taste_id" valueNumeric="1" />
		</insert>
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_id" valueNumeric="2" />
			<column name="apple_name" value="Fuji" />
			<column name="taste_id" valueNumeric="2" />
		</insert>
	</changeSet>
	<changeSet author="shriksha (generated)" id="1696663935821-6">
		<insert catalogName="appledb" tableName="taste">
			<column name="taste_id" valueNumeric="1" />
			<column name="taste" value="sweet" />
		</insert>
		<insert catalogName="appledb" tableName="taste">
			<column name="taste_id" valueNumeric="2" />
			<column name="taste" value="sour" />
		</insert>
	</changeSet>
	<changeSet author="shriksha (generated)" id="1696663935821-7">
		<createIndex catalogName="appledb" indexName="FKqg39etajdct3xxd813arauyit" tableName="apple">
			<column name="taste_id" />
		</createIndex>
	</changeSet>
	<changeSet author="shriksha (generated)" id="1696663935821-8" runAlways="true">
		<sql>
			SET FOREIGN_KEY_CHECKS=1;
		</sql>
	</changeSet>
</databaseChangeLog>

execute the command liquibase update, it will create and insert records in tables. The same can be verified by running command

select * from appledb.apple ap, appledb.taste ta where ap.taste_id=ta.taste_id;

Liquibase Foreign Key Constraint Fails

Disabling Foreign Key Check will pause Foreign Key Constraint until enabled again manually

follow us on