Liquibase update order when first init empty db

General Discussion
1

Hello, I’m planning to implement Liquibase to manage and apply database change history. I’ve configured the order of applying change logs by using includeAll to specify the order by folders, and within those folders, I’ve assigned priority to each file by adding numerical prefixes like 10_, 20_ to create and modify database objects with dependencies.

The situation where the problem occurred is as follows:

  1. Creation of table A and its primary key.
  2. Creation of table B and a foreign key referencing table A.
  3. Dropping the existing primary key of table A and creating a new primary key by adding one more column.
  4. Dropping the existing foreign key of table B and recreating it to reference the new primary key of table A.

There were no issues when applying changes 3 and 4 after 1 and 2 had already been applied. However, I encountered a problem when running liquibase update on a new database where the DATABASECHANGELOG and all data are empty. Since the changesets were executed in the order I specified by file, they actually ran in the order of 1, 3, 2, 4, resulting in an exception in step 2: “Referenced key does not exist” because the query in step 2 tries to create a foreign key referencing the primary key created in step 1.

I understand that there wouldn’t be a problem if all changes related to dependent database objects were consolidated into a single file. However, when managing queries by database object, how do you resolve such issues? I would appreciate any advice or examples of effective solutions.

2

I’m personally not a fan of includAll, but if you are going to use it I’d recommend having only 1 changeset per changelog, so steps 1, 2, 3, & 4 would each be in a separate file. This will allow you to sequence your changesets properly to handle object dependencies.

There are many, many ways to configure changelogs and changesets. You just need to figure out what works for you and your team/company.

1 Like