diffChangeLog against two simple schemas drops non-existing foreign key, resulting in error

Description

I created two simple databases to represent authors and books. There are small differences between the two databases. (The SQL statements to create both databases are attached)

  • author_id column in table books was removed.

  • The table authors_books was added to serve as a many-to-many relationship.

  • The {[email}} column in table authors must allow null.

  • The isbn column in table books must allow null.

  • An index was added to column email in table authors

There is no data in the databases, I simply created these simple tables to test liquibase.

After I created both databases, I added the PostgreSQL driver and created a liquibase.properties file to make it easier to execute the commands. This file is also attached.

The first command I did was liquibase.bat diffChangeLog to generate a diff.xml file.
The second command I did was liquibase.bat updateSQL > update.sql to generate a SQL file with the required SQL statements for updating.

In the diff.xml is a faulty dropForeignKeyConstraint on the table authors_books named fk_author_id. That constraint does not exist on that table yet (the next change in diff.xml creates it). There IS however a foreign key constraint with the same name in the table books that should be dropped, together with the author_id column in the table books, but that change is not there. It does drop the author_id column, but not the FK constraint. Of course, running the update.sql will result in an error:

ERROR: constraint "fk_author_id" of relation "authors_books" does not exist

All files involved have been attached to this issue.

Environment

Windows 10
PostgreSQL 10 (JDBC driver = postgresql-42.2.8.jar)

Attachments

5

Activity

Show:

Ron Pasch November 14, 2019 at 3:33 PM
Edited

Seems that liquibase is getting confused due to the name of the foreign key constraint. The one that needs to be dropped from the books table and the one that needs to be created on the authors_books table are named the same (fk_author_id). If I change the name of the constraint in one of the databases, then it works as expected. Doesn’t matter which I rename, either the fk_author_id on the books table in db1 or fk_author_id on the authors_books table in db2. As long as the constraints are named differently then liquibase does not confuse them, but this is of course a bug. Liquibase should take the table (and schema) where the constraint is on into account as well.

Details

Reporter

Components

Affects versions

Priority

Created November 10, 2019 at 12:31 PM
Updated November 14, 2019 at 3:34 PM