Search code examples
mysqlsqldatabasehsqldbliquibase

Default Value ON UPDATE Liquibase

I am using Liquibase for generating a MySQL and a HSQLDB databases.
In several tables I have a column called 'last_modified' which is the TIMESTAMP of the last update on that particular record. 

<changeSet author="bob" id="7">
    <createTable tableName="myTable">
        <column autoIncrement="true" name="id" type="INT">
            <constraints nullable="false" primaryKey="true" />
        </column>
        <column name="name" type="VARCHAR(128)">
            <constraints nullable="false" />
        </column>
        <column name="description" type="VARCHAR(512)" />
        <column defaultValueBoolean="true" name="enabled" type="BIT">
            <constraints nullable="false" />
        </column>
        <column name="last_modified" type="TIMESTAMP"/>
    </createTable>
    <modifySql dbms="mysql">
        <append value=" engine innodb" />
    </modifySql>
</changeSet>

I noticed that if I use MySQL, the generated SQL for that column is: 

`last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

While if I use HSQLDB, in case of update nothing happens, but I would like to have the same behaviour of the MySQL database with a default value on update equals to the CURRENT_TIMESTAMP.

How can I set the CURRENT_TIMESTAMP as a default value ON UPDATE?

Solution

  • You can't do this with a default value. The MySQL behaviour is non-standard and not supported by other databases. The proper way to do this is with a TRIGGER which is defined as BEFORE UPDATE and sets the timestamp each time the row is updated.

    Update: From HSQLDB version 2.3.4 this feature is supported. For example: CREATE TABLE T1(ID INT, last_modified timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL). Note the NOT NULL constraint must appear after the DEFAULT and ON UPDATE clauses.