Search code examples
sqlh2sql-merge

MERGE INTO table containing AUTO_INCREMENT columns

I've declared the following table for use by audit triggers:

CREATE TABLE audit_transaction_ids (id IDENTITY PRIMARY KEY, uuid VARCHAR UNIQUE NOT NULL, `time` TIMESTAMP NOT NULL);

  1. The trigger will get invoked multiple times in the same transaction.

  2. The first time the trigger is invoked, I want it to insert a new row with the current TRANSACTION_ID() and time.

  3. The subsequent times the trigger is invoked, I want it to return the existing "id" (I invoke Statement.getGeneratedKeys() to that end) without altering "uuid" or "time".

The current schema seems to have two problems.

  1. When I invoke MERGE INTO audit_transaction_ids (uuid, time) KEY(id) VALUES(TRANSACTION_ID(), NOW()) I get: org.h2.jdbc.JdbcSQLException: Column "ID" contains null values; SQL statement: MERGE INTO audit_transaction_ids (uuid, time) KEY(id) VALUES (TRANSACTION_ID(), NOW()) [90081-155]

  2. I suspect that invoking MERGE on an existing row will alter "time".

How do I fix both these problems?

Solution

  • MERGE is analogous to java.util.Map.put(key, value): it will insert the row if it doesn't exist, and update the row if it does. That being said, you can still merge into a table containing AUTO_INCREMENT columns so long as you use another column as the key.

    Given customer[id identity, email varchar(30), count int] you could merge into customer(id, email, count) key(email) values((select max(id) from customer c2 where c2.email='test@acme.com'), 'test@acme.com', 10). Meaning, re-use the id if a record exists, use null otherwise.

    See also https://stackoverflow.com/a/18819879/14731 for a portable way to insert-or-update depending on whether a row already exists.

    1. MERGE INTO audit_transaction_ids (uuid, time) KEY(id) VALUES(TRANSACTION_ID(), NOW())

    If you just want to insert a new row, use: INSERT INTO audit_transaction_ids (uuid, time) VALUES(TRANSACTION_ID(), NOW())

    MERGE without setting the value for the column ID doesn't make sense if ID is used as the key, because that way it could never (even in theory) update an existing rows. What you could do is using another key column (in the case above there is no column that could be used). See the documentation for MERGE for details.

    2. Invoking MERGE on an existing row will alter "time"

    I'm not sure if you talk about the fact that the value of the column 'time' is altered. This is the expected behavior if you use MERGE ... VALUES(.., NOW()), because the MERGE statement is supposed to update that column.

    Or maybe you mean that older versions of H2 returned different values within the same transaction (unlike most other databases, which return the same value within the same transaction). This is true, however with H2 version 1.3.155 (2011-05-27) and later, this incompatibility is fixed. See also the change log: "CURRENT_TIMESTAMP() and so on now return the same value within a transaction." It looks like this is not the problem in your case, because you do seem to use version 1.3.155 (the error message [90081-155] includes the build / version number).