Existing primary keys and foreign keys are dropped and recreated again

[Guschtel]

Environment

Liquibase Version: 4.29.2

Liquibase Integration & Version: Gradle

Liquibase Extension(s) & Version: org.liquibase.ext:liquibase-hibernate6:4.29.2

Database Vendor & Version: Oracle / H2

Operating System Type & Version: Windows

Infrastructure Type/Provider: local desktop

Description

When creating a liquibase changelog between JPA Entities and an Oracle database, the generated diff-changelog contains changeset entries that drop existing unique indexes and primary keys and recreates them the exact same way, even though there should be no such changes.

Steps To Reproduce

Create a JPA Entity with the corresponding annotations, for example:

Entity
@Table(
  name = "FALL",
  uniqueConstraints = {
    @UniqueConstraint(name = "UC_FALL_KEY_QUARTAL", columnNames = { "FALL_KEY", "QUARTAL" }),
  },
  indexes = {
    @Index(name = "FALLPK", columnList = "ID"),
    @Index(name = "IXUC_FALL_KEY_QUARTAL", columnList = "FALL_KEY, QUARTAL"),
  }
)
public class FallEntity extends ImportEntityBase {

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_FALL_GEN")
  @SequenceGenerator(name = "SEQ_FALL_GEN", allocationSize = 1, sequenceName = "SEQ_FALL")
  @Column(name = "ID", nullable = false, updatable = false)
  private Long id;

...

Then run the diff command and see that the changes contains a lot of differences, of which only some are expected, for example:

Ergebnisse des Vergleichs:
Liquibase command 'diff' was executed successfully.
Reference Database: null @ hibernate:spring:de.kvbawue.asv.shared.adapter.jpa,de.kvbawue.asv.fallbearbeitung.adapter.jpa,de.kvbawue.asv.dataload.adapter.jpa?dialect=org.hibernate.dialect.OracleDialect&hibernate.physical_naming_strategy=de.kvbawue.abrechnungssysteme.core.adapter.jpa.naming.PhysicalNamingStrategy&hibernate.implicit_naming_strategy=de.kvbawue.abrechnungssysteme.core.adapter.jpa.naming.ImplicitNamingStrategy
Comparison Database: ASV_FEATURE @ jdbc:oracle:thin:@srvdbe016e:1521/svc_dbe016e_okv2 (Default Schema: ASV_FEATURE)
Compared Schemas:  -> ASV_FEATURE
Product Name:
     Reference:   'Hibernate'
     Target: 'Oracle'
Product Version:
     Reference:   '6.4.4.Final'
     Target: 'Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0'
Missing Column(s): 
     FALL.TEMP
Unexpected Column(s): 
Changed Foreign Key(s): 
     FK_FALL_GNR_OPS_CODE_FALL_GNR_ID(FALL_GNR_OPS_CODE[FALL_GNR_ID] -> FALL_GNR[ID])
          deleteRule changed from 'null' to 'importedKeyRestrict'
          updateRule changed from 'null' to 'importedKeyRestrict'
          validate changed from 'null' to 'true'
...
Changed Primary Key(s): 
     FALLPK on null.FALL(ID)
          validate changed from 'null' to 'true'
...
Liquibase command 'diff' was executed successfully.

BUILD SUCCESSFUL in 17s

Then run the diff-changelog command and no changesets are generated at all.
This is fixed in #717

When applying this fix, run the diff-changelog and then you will get diff-changelog changesets that contain unnecessary alter table statements similar to the following examples:

-- changeset hermchr:1726590541556-1 dbms:oracle
ALTER TABLE FALL_GNR_OPS_CODE DROP CONSTRAINT FK_FALL_GNR_OPS_CODE_FALL_GNR_ID;

-- changeset hermchr:1726590541556-2 dbms:oracle
ALTER TABLE FALL_GNR_OPS_CODE ADD CONSTRAINT FK_FALL_GNR_OPS_CODE_FALL_GNR_ID FOREIGN KEY (FALL_GNR_ID) REFERENCES FALL_GNR (ID);

and

-- changeset hermchr:1714384768211-10 dbms:oracle
ALTER TABLE FALL DROP PRIMARY KEY DROP INDEX;

-- changeset hermchr:1714384768211-11 dbms:oracle
CREATE INDEX FALLPK ON FALL(ID);

-- changeset hermchr:1714384768211-12 dbms:oracle
ALTER TABLE FALL ADD CONSTRAINT FALLPK PRIMARY KEY (ID) USING INDEX FALLPK;

(The examples given here are from different runs)

Expected/Desired Behavior

No unnecessary alter table statements are created

Additional Context

Related issues:

• Existing indexes and unique constraints are dropped then recreated again. #436

[Guschtel]

@filipelautert following up our conversation in #436 (comment) the linked PR solves the remaining issues that we were having when creating a diff between the existing Schema in an Oracle Database and the JPA Entities in our project.

Thank you for your work in that pr, without it I would probably not have been able to find and solve the issue.

[filipelautert]

@Guschtel thanks! I'm coming back from PTO and will try to review your 2 prs still this week.

[Guschtel]

@filipelautert no rush. We've been using our locally patched version of the code for a few weeks now. So far, it looks good for our usecases.