What is the primary key on a temporal table?
If I use the following table design to capture history...:

    CREATE TABLE MyTable (
        insertion_timestamp TIMESTAMP,
        deleted_flag BOOLEAN,
        natural_key INT,
        attribute VARCHAR
    );

... then what should the primary key be?


(The history mechanism is `INSERT`-only: updated rows are inserted with a different `insertion_timestamp`, and deleted rows are inserted with a different timestamp and the `deleted_flag` set to `true`.)

I'm thinking `PRIMARY KEY (insertion_timestamp, deleted_flag, natural_key)`, but the only reason for including `deleted_flag` is to account for the possibility of a row being inserted and then (soft) deleted immediately, sooner than the next tick of the granularity of the `TIMESTAMP` data type. This feels overly paranoid...

What is the primary key on a temporal table?
===

If I use the following table design to capture history...:

    CREATE TABLE MyTable (
        insertion_timestamp TIMESTAMP,
        deleted_flag BOOLEAN,
        natural_key INT,
        attribute VARCHAR
    );

... then what should the primary key be?


(The history mechanism is `INSERT`-only: updated rows are inserted with a different `insertion_timestamp`, and deleted rows are inserted with a different timestamp and the `deleted_flag` set to `true`.)

I'm thinking `PRIMARY KEY (insertion_timestamp, deleted_flag, natural_key)`, but the only reason for including `deleted_flag` is to account for the possibility of a row being inserted and then (soft) deleted immediately, sooner than the next tick of the granularity of the `TIMESTAMP` data type. This feels overly paranoid...
