Liquibase Change Types addForeignKeyConstraint

Relational databases are connected with each other via a common key which is present in both of them, usually, it's the primary key of the main table, which has a copy in the secondary table, this is also known as foreign key. this foreign key links both tables together.

Liquibase base provides addForeignKeyConstraint tag to add foreign key constrain into an existing table.

<?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="appledb" tableName="apple">
			<column autoIncrement="true" name="apple_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="apple_name" type="VARCHAR(255)" />
		</createTable>
	</changeSet>
	<changeSet author="wesome" id="1692599548266-2">
		<createTable catalogName="appledb" tableName="vendor">
			<column name="vendor_id" type="BIGINT">
			</column>
			<column name="vendor_name" type="VARCHAR(255)" />
			<column name="apple_id" type="BIGINT">
			</column>
		</createTable>
	</changeSet>
	<changeSet author="wesome" id="1692599548266-3">
		<addForeignKeyConstraint baseColumnNames="apple_id" baseTableCatalogName="appledb" baseTableName="vendor" baseTableSchemaName="appledb" constraintName="apple_id_fk" onDelete="CASCADE" onUpdate="RESTRICT" referencedColumnNames="apple_id" referencedTableCatalogName="appledb" referencedTableName="apple" referencedTableSchemaName="appledb" validate="true" />
	</changeSet>
</databaseChangeLog>

liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

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

Run command liquibase update

The foreign key constraints can be verified by running the query

SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'appledb';

Add Multiple Foreign Key

Liquibase doesn't provide a specific tag to add multiple foreign keys, but more than one Foreign keys can be added via a separate createForignKey changeset.

<?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="appledb" tableName="apple">
			<column autoIncrement="true" name="apple_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="apple_name" type="VARCHAR(255)" />
		</createTable>
	</changeSet>
	<changeSet author="wesome" id="1692599548266-2">
		<createTable catalogName="appledb" tableName="supplier">
			<column autoIncrement="true" name="supplier_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="supplier_name" type="VARCHAR(255)" />
		</createTable>
	</changeSet>
	<changeSet author="wesome" id="1692599548266-3">
		<createTable catalogName="appledb" tableName="vendor">
			<column name="vendor_id" type="BIGINT">
			</column>
			<column name="vendor_name" type="VARCHAR(255)" />
			<column name="apple_id" type="BIGINT" />
			<column name="supplier_id" type="BIGINT" />
		</createTable>
	</changeSet>
	<changeSet author="wesome" id="1692599548266-4">
		<addForeignKeyConstraint baseColumnNames="apple_id" baseTableCatalogName="appledb" baseTableName="vendor" baseTableSchemaName="appledb" constraintName="apple_id_fk" onDelete="CASCADE" onUpdate="RESTRICT" referencedColumnNames="apple_id" referencedTableCatalogName="appledb" referencedTableName="apple" referencedTableSchemaName="appledb" validate="true" />
	</changeSet>
	<changeSet author="wesome" id="1692599548266-5">
		<addForeignKeyConstraint baseColumnNames="supplier_id" baseTableCatalogName="appledb" baseTableName="vendor" baseTableSchemaName="appledb" constraintName="supplier_id_fk" onDelete="CASCADE" onUpdate="RESTRICT" referencedColumnNames="supplier_id" referencedTableCatalogName="appledb" referencedTableName="supplier" referencedTableSchemaName="appledb" validate="true" />
	</changeSet>
</databaseChangeLog>

liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

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

Run command liquibase update

The foreign key constraints can be verified by running the query

SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'appledb';