Skip to content
Advertisement

What is the primary key of an audit table managed by Hibernate Envers?

I am using Hibernate Envers to audit some entities. I manually created the associated audit tables. However, I am having trouble determining what an audit table’s primary key should be. For example, consider a fictional table designed to store customers:

CREATE TABLE CUSTOMER
(
    CUSTOMER_ID   INTEGER,
    CUSTOMER_NAME VARCHAR(100),

    PRIMARY KEY (CUSTOMER_ID)
)

And you create the audit table:

CREATE TABLE CUSTOMER_REVISION
(
    REVISION_ID      INTEGER,
    REVISION_TYPE_ID INTEGER,
    CUSTOMER_ID      INTEGER,
    CUSTOMER_NAME    VARCHAR(100),

    PRIMARY KEY (???)
)

Here were the options I considered:

Primary key: REVISION_ID

This cannot be the primary key because multiple entities of the same class may be modified during the same revision.

Primary key: (REVISION_ID, CUSTOMER_ID)

This seems more likely, but I’m not sure if Envers will insert multiple records per customer per revision.

Primary key: (REVISION_ID, REVISION_TYPE_ID, CUSTOMER_ID)

This seems like overkill, but it may be possible that Envers will insert different types of records (add, modify or delete) per customer per revision.

Primary key: A new column

Perhaps the primary key must simply be another column containing a synthetic primary key.


What is the true primary key of an audit table managed by Hibernate Envers?

Answer

Judging by the examples in the documentation, it appears that the primary key in my example would be (REVISION_ID, CUSTOMER_ID). Here is the example in the documentation:

create table Address (
    id integer generated by default as identity (start with 1),
    flatNumber integer,
    houseNumber integer,
    streetName varchar(255),
    primary key (id)
);

create table Address_AUD (
    id integer not null,
    REV integer not null,
    flatNumber integer,
    houseNumber integer,
    streetName varchar(255),
    REVTYPE tinyint,
    ***primary key (id, REV)***
);
Advertisement