A foreign key
is used to link separate tables together. Sometimes times requirement is to drop the foreign key. Liquibase
base provides dropForeignKeyConstraint
tag to drop foreign key
constrain from an existing table.
Create Forign Key
<?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';
Drop Forign Key
<?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-4">
<dropForeignKeyConstraint baseTableCatalogName="appleDb" baseTableName="vendor" baseTableSchemaName="appleDb" constraintName="apple_id_fk" />
</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 has been dropped and 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';