Reverse Database Tables Relationship Direction by Liquibase

The requirement changes happen as a principal part of the software development lifecycle. Have you ever faced a change that needs your tables’ relationship reversion?

Intention

Consider a situation that you have designed your database and gone all the way through production deployment. Then, tables are populated with the production grade of data. After that, the new requirements arrive. You might figure out the old relationships don’t work anymore and you need to change them. It actually happened in one of my projects so that I needed to reverse a “one-to-one” relationship to “many-to-one” in another way around. And of course, I must guaranty data integrity. Here we will see how to do the changes using Liquibase without corrupting the data.

Primitive Design

Assume we are working on a personal habit management system that manages and measures people’s habits. In the first version of our database design, we have a habit_composition table in “one-to-one” relationship to person. That is to say, we keep the general habits definition of each person in this table. Indeed, we define the details of each habit in atomic_habit tables as you can see in the following figure:

Consider how a “one-to-one” relationship is defined by habit_composition_id column in person table. We can use the following Liquibase change-set to build that design:

databaseChangeLog:
  - changeSet:
      id: create_tables
      author: shahram
      changes:
        # --------- habit_composition TABLE ---------
        - createTable:
            tableName: habit_composition
            columns:
              - column:
                  name: id
                  type: bigint(20)
                  autoIncrement: true
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: composition_type
                  type: varchar(255)
                  constraints:
                    nullable: false

        # --------- person TABLE ---------
        - createTable:
            tableName: person
            columns:
              - column:
                  name: ID
                  type: bigint(20)
                  autoIncrement: true
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: habit_composition_id
                  type: bigint(20)
                  constraints:
                    foreignKeyName: fk_person_habit_composition_id
                    references: habit_composition(id)
                    nullable: false
              - column:
                  name: first_name
                  type: varchar(255)
                  constraints:
                    nullable: true
              - column:
                  name: last_name
                  type: varchar(255)
                  constraints:
                    nullable: false

        # --------- atomic_habit TABLE ---------
        - createTable:
            tableName: atomic_habit
            columns:
              - column:
                  name: ID
                  type: bigint(20)
                  autoIncrement: true
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: habit_composition_id
                  type: bigint(20)
                  constraints:
                    foreignKeyName: fk_atomic_habit_habit_com_id
                    references: habit_composition(id)
                    nullable: false
              - column:
                  name: name
                  type: varchar(255)
                  constraints:
                    nullable: false
              - column:
                  name: clue
                  type: varchar(255)
                  constraints:
                    nullable: true
              - column:
                  name: process
                  type: varchar(255)
                  constraints:
                    nullable: true
              - column:
                  name: reward
                  type: varchar(255)
                  constraints:
                    nullable: true

Evolving

After a while of running, we figure out that there are new requirements that are not addressed in the current design. We considered a fixed habit definition for each person, the real world is different though. People are changing and they extend their abilities and their habits definitely. Subsequently, it would be clear we should change our primitive design. We need to address the people’s changes. To achieve that goal, we will add a creation_time column to habit_comopsition table to show when a new habit definition is taking into account. Also, for each person, we need to have multiple rows in habit_composition table to log all of the old compositions. This way people can watch their progress of developing better habits!

As a result, we need a new design as the following figure shows:

The following Liquibase change-set will do all the changes we need to apply:

databaseChangeLog:
  - changeSet:
      id: reverse_relationship
      author: shahram
      changes:
        # ------- DROP ForeignKeyConstraint fk_person_habit_composition_id -------
        - dropForeignKeyConstraint:
            constraintName: fk_person_habit_composition_id
            baseTableName: person

        # ------- ADD Column person_id -------
        - addColumn:
            tableName: habit_composition
            columns:
              - column:
                  name: person_id
                  type: bigint(20)
                  constraints:
                    nullable: true

        # ------- ADD ForeignKeyConstraint fk_habit_compos_person -------
        - addForeignKeyConstraint:
            constraintName: fk_habit_compos_person
            baseTableName: habit_composition
            baseColumnNames: person_id
            referencedTableName: person
            referencedColumnNames: id

        # ------- Backfill person_id values -------
        - sql:
            dbms: mysql
            sql: |
              update
                  habit_composition
                  inner join person 
                     on habit_composition.id = person.habit_composition_id
              set
                  habit_composition.person_id = person.id;

        # ------- addNotNullConstraint column person_id -------
        - addNotNullConstraint:
            tableName: habit_composition
            columnName: person_id
            columnDataType: bigint(20)
            constraintName: const_person_id_not_null

        # ------- DROP Column habit_composition_id -------
        - dropColumn:
            columnName: habit_composition_id
            tableName: person

        # ------- addColumn creation_time -------
        - addColumn:
            tableName: habit_composition
            columns:
              - column:
                  name: creation_time
                  type: timestamp
                  constraints:
                    nullable: false

Let’s review the above steps one by one:

1. DROP ForeignKeyConstraint fk_person_habit_composition_id: We need to get rid of the old relationship foreign key on column habit_composition_id. To get to that point, we need first get rid of the ForeignKeyConstraint on it.
2. ADD Column person_id: As you see, we add the new foreign key column here. The most important point, we must consider this column nullable at this step. Otherwise, we cannot define a foreign key on it when there is no data to populate.
3. ADD ForeignKeyConstraint fk_habit_compos_person: The definition of the new foreign key.
4. Backfill person_id values: We need to backfill our new foreign key column for the corresponding value of the target table. This step runs the required SQL to insert new data.
5. Add addNotNullConstraint column person_id: After backfilling process is done, we will have all the needed data in place. So it is time to add a not-null constraint to our column to avoid failing in data integrity in the future.
6. DROP Column habit_composition_id: We do not need the old foreign key column anymore.
7. Add addColumn creation_time: We add a new timestamp column to habit_composition table to keep the time of each habit change.

Summary

In this quick review, we saw how it is possible to reverse a database relationship even when the database is operational with live data. These steps will be reusable to apply other kinds of changes between database tables’ relationships.