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:
This cannot be the primary key because multiple entities of the same class may be modified during the same revision.
Primary key: (
This seems more likely, but I’m not sure if Envers will insert multiple records per customer per revision.
Primary key: (
This seems like overkill, but it may be possible that Envers will insert different types of records (
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?
Judging by the examples in the documentation, it appears that the primary key in my example would be (
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)*** );