Liquibase + H2 + Junit Primary Key Sequence starts over

Question

I managed to integrate Liquibase into our Maven build to initialize a H2 inmemory database with a few enrys. Those rows have the primary key generated using a sequence table which works as expected (BigInt incremented values starting from 1).

My issue is that when i try to persist a new entity into that table from within a Junit integration test i get a "unique key constraint violation" because that new entity has the same primary key as the very first row inserted using the Liquibase changelog-xmls.

So the initialisation works perfectly fine as expected. The maven build uses the liquibase changelog-xmls

For now i just wipe the according tables completly before any integration tests with an own Runner... but that wont be a possibility in the furture. Its currently quite a chalange to investigate such issues since there is not yet much specific information on Liquibase available.

Update Workaround

While id prefer below answer using H2 brings up the problem that below changeset wont work because the required minValue is not supported.

<changeSet author="liquibase-docs" id="alterSequence-example">
  <alterSequence 
        incrementBy="1"
        maxValue="371717"
        minValue="40"
        ordered="true"
        schemaName="public"
        sequenceName="seq_id"/>

As a simple workaround i now just drop the existing sequence that was used to insert my testdata in a second changeSet:

 <changeSet id="2" author="Me">

    <dropSequence
        sequenceName="SEQ_KEY_MY_TBL"/>

    <createSequence 
        sequenceName="SEQ_KEY_MY_TBL" 
        incrementBy="1" 
        startValue="40"/>
</changeSet>

This way the values configured in the changelog-*.xml will be inserted using the sequence with an initial value of 1. I insert 30 rows so Keys 1-30 are used. After that the sequence gets dropped and recreated with a higher startValue. This way when persisting entities from within a Junit based integration Test the new entities will have primary keys starting from 40 and the previous unique constraint problem is solved.

Not H2 will probably soon release a version supporting minValue/maxValue since the according patch already exists.

Update:

Maybe we should mention this still is just a Workaround, anyone knows if H2 supports a Sequence with Liquibase that wont start over after DB-Init?

Answer 1

You should instruct liquibase to set the start value for those sequences to a value beyond those you have used for the entries you created. Liquibase has an alterSequence element for this. You can add such elements at the end of your current liquibase script.