Liquibase: How to Set Foreign Key(s) Constraint in Column Tag?

JavaGradleLiquibase

Java Problem Overview


How can I configure foreign keys through column tag attributes foreignKeyName and references? The only example I've found demonstrates how to add foreign keys after the fact.

Java Solutions


Solution 1 - Java

Use a nested <constraints> tag in your column tag.

Example:

<changeSet id="SAMPLE_1" author="alice">
    <createTable tableName="employee">
        <column name="id" type="int" autoIncrement="true">
            <constraints primaryKey="true"/>
        </column>
        <column name="first_name" type="varchar(255)"/>
        <column name="last_name" type="varchar(255)">
            <constraints nullable="false"/>
        </column>
    </createTable>
</changeSet>

<changeSet id="create address table" author="bob">
    <createTable tableName="address">
        <column name="id" type="int" autoIncrement="true">
            <constraints primaryKey="true"/>
        </column>
        <column name="line1" type="varchar(255)">
            <constraints nullable="false"/>
        </column>
        <column name="line2" type="varchar(255)"/>
        <column name="city" type="varchar(100)">
            <constraints nullable="false"/>
        </column>
        <column name="employee_id" type="int">
            <constraints nullable="false" foreignKeyName="fk_address_employee" references="employee(id)"/>
        </column>
    </createTable>
</changeSet>

Solution 2 - Java

you have to add foreign constraint like:

- changeSet:
    id: create_account_table
    author: ankit
    changes:
    - createTable:
        tableName: account
        columns:
        - column:
            name: accn_id
            type: uuid
            constraints:
              primaryKey: true
              primaryKeyName: pk_account
- changeSet:
    id: create_table_abc
    author: ankit
    changes:
    - createTable:
        tableName: abc
        columns:
        - column:
            name: id
            type: uuid
            constraints:
              primaryKey: true
              primaryKeyName: pk_abc
        - column:
            name: accn_id
            type: UUID
            constraints:
              nullable: false
              foreignKeyName: fk_abc_account
              references: account(accn_id)

Solution 3 - Java

Maybe you can add foreign key as below:

<changeSet id="1" author="ozhanli">
    <!--
    Owner Entity.
    -->
    <createTable tableName="owner">
        <column name="id" type="bigint" autoIncrement="true">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="name" type="varchar(255)">
            <constraints nullable="true" />
        </column>
    </createTable>

    <!--
    Car Entity.
    -->
    <createTable tableName="car">
        <column name="id" type="bigint" autoIncrement="true">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="brand" type="varchar(255)">
            <constraints nullable="true" />
        </column>
        <column name="owner_id" type="bigint">
            <constraints nullable="true" />
        </column>
    </createTable>

    <!--
    Constraints for Car entity
    -->
    <addForeignKeyConstraint baseColumnNames="owner_id"
                             baseTableName="car"
                             constraintName="fk_car_owner_id"
                             referencedColumnNames="id"
                             referencedTableName="owner"/>
</changeSet>

Solution 4 - Java

For unidirectional relation, for cascade delete if you're using liquibase:

<addForeignKeyConstraint baseTableName="installment" baseColumnNames="account_id"
                                 constraintName="fk_account_installment"
                                 referencedTableName="account" referencedColumnNames="id"
                                 onDelete="CASCADE"/>

And on Entity:

@ManyToOne(cascade = {CascadeType.ALL})
@JoinColumn(name = "account_id", nullable = false)
@OnDelete(action = OnDeleteAction.CASCADE)
private Account account;

I'm still not sure why I needed @OnDelete on Entity which was needed to pass service level test although it was already specified in liquibase script. And If I remove cascade delete from liquibase, my integration test would fail. So I needed both. May be an experienced person can answer why declaration of cascade delete at both places was needed.

PS: If it helps, service level test are @SpringBootTest, and Integration Test are Cucumber tests with testcontainers.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionAriView Question on Stackoverflow
Solution 1 - JavaNathan VoxlandView Answer on Stackoverflow
Solution 2 - JavaankitView Answer on Stackoverflow
Solution 3 - JavaozhanliView Answer on Stackoverflow
Solution 4 - JavatyroView Answer on Stackoverflow