0

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?

Adam Paynter
  • 46,244
  • 33
  • 149
  • 164

2 Answers2

1

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)***
);
Adam Paynter
  • 46,244
  • 33
  • 149
  • 164
  • 1
    Exactly. In each revision one entity can be modified max one time :) If there are multiple changes to an entity in one tx, they will be merged and still result in one modification. – adamw Apr 27 '12 at 14:10
0

The primary key of audit table is the combination of original id(id) and revision number(rev) of the audit table. As the official documentation there can be at most one historic entry for a given entity instance at a given revision, which simply means unique combination of above two column.

Prateek Mazumdar
  • 1