Verweis auf historisierte Tabelle

[ceving]

Ich will Veränderungen am Datenbestand speichern. Die Idee ist, eine Tabelle mit zwei Schlüsseln zu machen. Der Primärschlüssel (sn) identifiziert alle aktuellen und historischen Zeilen der Tabelle. Und ein weiter Schlüssel (id) identifiziert nur die Person unabhängig von den Veränderungen ihrer Attribute. Dazu habe ich einen Zeitstempel für die Modifikation erstellt. Wann immer der Zeitstempel NULL ist, wurde keine Modifikation vorgenommen, es ist also der aktuell Wert.

DROP TABLE IF EXISTS person;
CREATE TABLE person (
  sn        serial     PRIMARY KEY,
  id        int        NOT NULL,
  modified  timestamp,
  name      text
);
CREATE UNIQUE INDEX version_id_key ON version (id) WHERE modified IS NULL;
DROP TABLE IF EXISTS account;
CREATE TABLE account (
  id        int        NOT NULL REFERENCES person(id),
  name      text
);

Leider funktioniert der Fremdschlüssel nicht. Ziel war es, dass die Konten nur auf die Identität der Person verweisen und nicht auf eine konkrete historische Ausprägung. Das Beispiel schmeißt den Fehler:

ERROR: there is no unique constraint matching given keys for referenced table “person”

Was ist nicht verstehe ist, warum es angeblich keinen Index gäbe, obwohl der UNIQUE-Index auf id angezeigt wird:

                                       Table "public.person"
  Column  |            Type             | Collation | Nullable |              Default               
----------+-----------------------------+-----------+----------+------------------------------------
 sn       | integer                     |           | not null | nextval('person_sn_seq'::regclass)
 id       | integer                     |           | not null | 
 modified | timestamp without time zone |           |          | 
 name     | text                        |           |          | 
Indexes:
    "person_pkey" PRIMARY KEY, btree (sn)
    "person_id_key" UNIQUE, btree (id) WHERE modified IS NULL

Was stimmt nicht mit dem Index?

Ja ich weiß, ich könnte zwei Tabellen pro Entität machen: eine für die veränderlichen und eine für die unveränderlichen Daten. Aber ich würde es gerne mit einer Tabelle lösen.

[castorp]

Das geht schlicht nicht:

@The Manual

the refcolumn list must refer to the columns of a non-deferrable unique or primary key constraint or be the columns of a non-partial unique index

(Hervorhebung ist von mir)

Eine übliche Art Dein Problem zu lösen ist, in der Tabelle person immer den aktuellen Stand zu speichern, und eine weiter Tabelle nur für die Änderungen (über die Zeit) zu speichern.

Von der Verwendung von serial wird übrigens mittlerweile abgeraten. Es ist besser Standard konformen identity Spalten zu verwenden.

[laurenz]

Mit einer Tabelle geht es nicht, außer sie ist partitioniert:

CREATE TABLE person (
  sn        bigint     GENERATED ALWAYS AS IDENTITY 
                       NOT NULL,
  id        integer    NOT NULL,
  modified  timestamp with time zone,
  name      text       NOT NULL
) PARTITION BY LIST (modified);

CREATE TABLE person_current PARTITION OF person (
  PRIMARY KEY (sn),
  UNIQUE (id)
) FOR VALUES IN (NULL);

CREATE TABLE person_history PARTITION OF person (
  PRIMARY KEY (sn)
) DEFAULT;

CREATE TABLE account ( 
  id        integer    REFERENCES person_current(id) ON DELETE CASCADE
                       NOT NULL,
  name      text       NOT NULL
);

Zeilen wandern automatisch von einer Partition in die andere, wenn sich modified ändert. Eine referenzierende Zeile in account wird automatisch gelöscht.

Man kann account.id auch ohne ON DELETE CASCADE definierten, dann gibt es einen Fehler, wenn man die referenzierende Zeile nicht vorher explizit gelöscht hat.