Hibernate Envers + Liquibase: NULL not allowed for column "REV"

Question

I want to add Liquibase to an application that already uses Hibernate Envers, but with auto creation of the database tables. Hence, I need to write Liquibase changesets that contain the tables for my entities and the table needed for Envers.

Here's the changeset:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
     http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

<property name="long_type" value="bigint" dbms="postgresql"/>
<property name="long_type" value="long" dbms="h2"/>

<changeSet id="CreateBasicSchema" author="Steven Schwenke">

    <createSequence sequenceName="hibernate_sequence" startValue="0" incrementBy="1"/>

    <createTable tableName="revinfo">
        <column name="rev" type="integer">
            <constraints primaryKey="true"/>
        </column>
        <column name="revtstmp" type="bigint"/>
    </createTable>

    <createTable tableName="stuff">
        <column name="id" type="${long_type}">
            <constraints nullable="false" unique="true" primaryKey="true"/>
        </column>
        <column name="text" type="varchar(36)">
            <constraints nullable="false"/>
        </column>
    </createTable>

    <createTable tableName="stuff_aud">
        <column name="id" type="${long_type}">
            <constraints nullable="false" unique="true" primaryKey="true"/>
        </column>
        <column name="rev" type="integer">
            <constraints referencedTableName="revinfo"
                         foreignKeyName="fk_brands_stuff_revinfo"
                         referencedColumnNames="rev"
                         nullable="false"/>
        </column>
        <column name="revtype" type="integer">
            <constraints nullable="false"/>
        </column>
        <column name="stuff" type="varchar(36)">
            <constraints nullable="true"/>
        </column>
    </createTable>
</changeSet>

This causes the following exception:

o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 23502, SQLState: 23502
o.h.engine.jdbc.spi.SqlExceptionHelper   : NULL nicht zulässig für Feld "REV"
NULL not allowed for column "REV"; SQL statement:
/* insert org.hibernate.envers.DefaultRevisionEntity */ insert into revinfo (rev, revtstmp) values (null, ?) [23502-200]
org.springframework.dao.DataIntegrityViolationException: could not execute statement ...

After some research, I finally found my error and will post it as the answer to this question. Hope that this helps other developers encountering the same exception.

Steven Steven · Accepted Answer · 2020-05-28T09:06:21

The missing part is a simple autoIncrement for the ID of the revinfo table:

<createTable tableName="revinfo">
    <column name="rev" type="integer" autoIncrement="true">
        <constraints primaryKey="true"/>
    </column>
    <column name="revtstmp" type="bigint"/>
</createTable>

Without this, new entries in the revinfo table will have a null ID, which is exactly what the exception says.

Another error in the liquibase code above is that the id of the aud-table has a primary key just on id. This will lead to exceptions when updating entities. According to Vlad Mihalcea, there should be a combined key for the aud table:

<createTable tableName="stuff_aud">
    <column name="id" type="${long_type}">
        <constraints nullable="false" />
    </column>
    <column name="rev" type="integer">
        <constraints referencedTableName="revinfo"
                     foreignKeyName="fk_brands_stuff_revinfo"
                     referencedColumnNames="rev"
                     nullable="false"/>
    </column>
    <column name="revtype" type="integer">
        <constraints nullable="false"/>
    </column>
    <column name="stuff" type="varchar(36)">
        <constraints nullable="false"/>
    </column>
</createTable>
<addPrimaryKey tableName="stuff_aud" columnNames="id, rev" />

Hibernate Envers + Liquibase: NULL not allowed for column "REV"

1 Answers