diffChangeLog against two simple schemas drops non-existing foreign key, resulting in error
Description
Environment
Windows 10
PostgreSQL 10 (JDBC driver = postgresql-42.2.8.jar)
Attachments
5
Activity
Show:

Ron Pasch November 14, 2019 at 3:33 PMEdited
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.
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 tablebooks
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 tablebooks
must allow null.An index was added to column
email
in tableauthors
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 tableauthors_books
namedfk_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 tablebooks
that should be dropped, together with theauthor_id
column in the tablebooks
, but that change is not there. It does drop theauthor_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.