Database migrations are a critical component of modern enterprise software development. Applications frequently evolve over time, requiring schema changes, new indexes, additional constraints, auto-generated identifiers, and data transformations.

Tools such as Liquibase help automate these changes and ensure consistent deployments across environments. However, one of the most common challenges faced by development teams is maintaining compatibility across multiple database platforms such as Oracle, Microsoft SQL Server, and MySQL.

This article explores common migration challenges, including auto-increment columns, sequences, triggers, indexes, foreign keys, Maven integration, and Spring Batch schema management.

What Is Liquibase?

Liquibase is an open-source database migration tool that allows developers to describe database changes using XML, YAML, JSON, or SQL.

Instead of manually executing SQL scripts, developers define changesets that Liquibase executes automatically.

Example:

<changeSet id="001" author="developer">
    <createTable tableName="customer">
        <column name="id" type="bigint"/>
        <column name="name" type="varchar(255)"/>
    </createTable>
</changeSet>

Liquibase keeps track of executed changes using:

DATABASECHANGELOG
DATABASECHANGELOGLOCK

tables.

Multi-Database Challenges

Many enterprise products support multiple databases simultaneously:

• Oracle
• Microsoft SQL Server
• MySQL
• PostgreSQL

Although SQL is standardized, each database platform implements important features differently.

Common problem areas include:

• Auto-increment columns
• Sequences
• Triggers
• Indexes
• Constraint management
• Data type differences

Auto Increment Columns

One of the most common migration issues involves automatically generated identifiers.

MySQL

MySQL uses:

AUTO_INCREMENT

Example:

CREATE TABLE customer (
    id BIGINT AUTO_INCREMENT,
    name VARCHAR(255),
    PRIMARY KEY(id)
);

SQL Server

SQL Server uses:

IDENTITY(1,1)

Example:

CREATE TABLE customer (
    id BIGINT IDENTITY(1,1),
    name VARCHAR(255),
    PRIMARY KEY(id)
);

Oracle

Traditional Oracle versions use:

SEQUENCE
+
TRIGGER

Example:

CREATE SEQUENCE CUSTOMER_SEQ;

CREATE OR REPLACE TRIGGER CUSTOMER_TRG
BEFORE INSERT ON CUSTOMER
FOR EACH ROW
BEGIN
    SELECT CUSTOMER_SEQ.NEXTVAL
    INTO :NEW.ID
    FROM DUAL;
END;
/

Why addAutoIncrement Often Fails

A common Liquibase change:

<addAutoIncrement
    tableName="customer"
    columnName="id"/>

may work correctly in MySQL but fail in Oracle because Oracle does not support AUTO_INCREMENT.

For Oracle deployments, dedicated sequences and triggers are typically required.

Oracle Trigger Pitfalls

Many migration failures originate from invalid triggers.

Example:

ORA-04098:
trigger is invalid and failed re-validation

Common causes include:

Missing Sequence

ORA-02289:
sequence does not exist

Example:

SELECT customer_seq.NEXTVAL
INTO :NEW.id
FROM dual;

If the sequence does not exist before trigger creation, the trigger becomes invalid.

Creating Sequences for Existing Data

A common challenge occurs when existing tables already contain records.

Example:

SELECT MAX(id)
FROM customer;

The new sequence must start above the current maximum value.

Example:

CREATE SEQUENCE CUSTOMER_SEQ
START WITH 100001
INCREMENT BY 1;

Liquibase and Foreign Key Constraints

Liquibase simplifies foreign key creation.

Example:

<addForeignKeyConstraint
    baseTableName="orders"
    baseColumnNames="customer_id"
    referencedTableName="customer"
    referencedColumnNames="id"/>

However, migration failures often occur when data already violates referential integrity.

Example:

The ALTER TABLE statement conflicted
with the FOREIGN KEY constraint

Before adding constraints:

• Clean invalid records
• Verify parent rows exist
• Validate data consistency

Index Management

Indexes improve query performance.

Example:

<createIndex
    tableName="customer"
    indexName="CUSTOMER_IX1">
    <column name="email"/>
</createIndex>

Oracle ORA-01408

A common Oracle migration error:

ORA-01408:
such column list already indexed

This happens when Liquibase attempts to create an index whose columns already exist in another index.

Example:

(email)

already indexed.

Before creating new indexes:

SELECT *
FROM USER_INDEXES;

Verify existing indexes.

Redundant Indexes

Suppose a primary key exists:

PRIMARY KEY
(balance_id,
 consent_id,
 resource_id)

Creating another index:

(balance_id,
 consent_id,
 resource_id)

provides no benefit.

The primary key already creates an index.

Redundant indexes increase:

• Storage requirements
• Insert cost
• Update cost
• Maintenance effort

Database-Specific Changesets

Liquibase supports database-specific execution.

Example:

<changeSet
    id="001"
    author="developer"
    dbms="mysql">

or

<changeSet
    id="001"
    author="developer"
    dbms="oracle">

This allows custom logic for each platform.

Migrating SQL Server Identity Columns

A common challenge is converting existing columns to IDENTITY.

SQL Server does not allow:

ALTER COLUMN id IDENTITY

Instead:

1. Create temporary table.
2. Create new IDENTITY column.
3. Copy data.
4. Rename table.

Example:

CREATE TABLE customer_tmp (
    id BIGINT IDENTITY(1,1),
    name VARCHAR(255)
);

MySQL AUTO_INCREMENT Requirements

MySQL requires:

AUTO_INCREMENT

columns to be indexed.

Valid:

id BIGINT AUTO_INCREMENT PRIMARY KEY

Invalid:

id BIGINT AUTO_INCREMENT

without a key definition.

Maven and Liquibase

Liquibase is commonly integrated into Maven builds.

Example:

mvn liquibase:update

or

mvn liquibase:diff

Common Maven Error

Example:

The database URL has not been specified
either as a parameter or in a properties file

Solution:

Provide connection information:

mvn liquibase:update \
-Dliquibase.url=jdbc:mysql://localhost/db \
-Dliquibase.username=user \
-Dliquibase.password=password

Generating Upgrade Scripts

Many projects generate migration scripts during CI/CD builds.

Example:

mvn clean install \
-Dgenerate.sql=true

Generated scripts can then be reviewed before deployment.

Benefits:

• Safer releases
• DBA validation
• Auditability

Spring Batch Schema Initialization

Spring Batch automatically creates metadata tables when configured.

Property:

spring.batch.jdbc.initialize-schema=always

Available values:

always
embedded
never

always

Creates schema every startup.

embedded

Creates schema only for embedded databases.

never

Schema managed manually.

Production systems typically use:

spring.batch.jdbc.initialize-schema=never

Best Practices

Use Separate Changesets

Keep schema changes small and focused.

Good:

Create table
Create index
Create foreign key

Separate changesets.

Use Database-Specific Logic

Different databases require different implementations.

Examples:

• Oracle → Sequences + Triggers
• SQL Server → Identity
• MySQL → Auto Increment

Validate Existing Data

Before:

• Creating constraints
• Adding primary keys
• Adding foreign keys

Verify data quality.

Avoid Redundant Indexes

Review existing:

• Primary keys
• Unique constraints
• Indexes

before creating new ones.

Test on Every Supported Database

A migration that succeeds on MySQL may fail on:

• Oracle
• SQL Server

Always validate migrations on every supported platform.

Conclusion

Liquibase significantly simplifies database schema management, but multi-database support introduces unique challenges. Differences in auto-generated identifiers, sequences, triggers, constraints, and indexing strategies often require database-specific solutions.

By understanding how Oracle, SQL Server, and MySQL implement these features and by leveraging Liquibase changesets effectively, development teams can build reliable migration processes that support enterprise-scale applications while minimizing deployment risk.