Transaktionen, Fremdschlüssel und INSERT in mehrere Tabellen

Hallo,

bin ein SQL-Neuling und bei meinem Versuch, eine erste Datenbank zu erstellen auf folgendes Problem gestoßen:

Gegeben sind zwei Tabellen mit jeweils SERIAL als primary key.

CREATE TABLE a 
(
  id SERIAL PRIMARY KEY,
  ...
) ;

CREATE TABLE b
(
  id SERIAL PRIMARY KEY,
  ...
);

Zwischen denen soll es eine n:m-Relation geben, die natürlich durch eine Zwischentabelle ab gelöst wird.

CREATE TABLE ab
(
  aid INTEGER REFERENCES a (id),
  aid INTEGER REFERENCES a (id)
);

Als umsichtiger Neuling achte ich selbstverständlich auf Datenintegrität und verwende Fremdschlüssel, um zu verhindern,
dass jemand Verknüpfungszeilen in ab einträgt, deren Werte in a oder b gar nicht existieren.

So weit so gut.

Möchte ich nun die Tabellen mit Daten füllen, muss ich das ja auf drei INSERTS verteilen, die ich ebenfalls bombensicher innerhalb einer Transaktion unterbringen will.

BEGIN;
INSERT INTO a (id,...) VALUES ...;
INSERT INTO a (id,...) VALUES ...;
INSERT INTO ab (aid,bid) VALUES (currval('a_id_seq'), currval('b_id_seq'));
COMMIT;

Sonst wäre es ja denkbar, das ein User Tabellen a und b füllt, und vor Ausführung des dritten INSERTS jemand anderes die Sequenz hochzählt, und damit das dritte INSERT die falschen Werte bekommt.

Das Problem ist aber, das durch die Transaktion tatsächlich noch gar nichts in Tabelle a und b steht (es ist nur vorgemerkt), und deshalb auch die Eintragung der currvals in “ab” fehlschlägt.

Wie löst man das richtig? Ich hatte schon die Idee, in einer temporären Tabelle vor der Transaktion einen Sequenzwert zu sichern, und diesen dann innerhalb der Transaktion auszulesen.
Gibt es einen saubereren Weg? (ohne zusätzliche Tabellen)

Vielen Dank,
Rainer

Hallo rainer198,

Du hast einiges noch nicht richtig verstanden!

  1. Der Typ SERIAL
    Wenn Du in eine Tabelle Daten einfügst, in denen ein oder mehrer Spalten vom Typ SERIAL sind, brauchst Du diesen Spalten keinen Wert zuweisen. Der Wert wird automatisch weitergezählt sobald der INSERT-Befehl die Daten in die Tabelle einfügt!

  2. Alle Befehl werden immer nacheinander abgearbeitet.

  3. Somit stehen Dir zum Zeitpunkt des Befehls “INSERT INTO ab (aid,bid) VALUES (currval(‘a_id_seq’), currval(‘b_id_seq’));” die aktuellen Werte zu Verfügung.

  4. Da eine andere Transaktion während deiner Transaktion die Werte von den SERIAL-Typen ändern kann, solltest du ein LOCK auf die beiden Tabellen setzen.

Dein Code würde dann wie folgt aussehen:

CREATE TABLE a
(
id SERIAL PRIMARY KEY,
a1 …

);

CREATE TABLE b
(
id SERIAL PRIMARY KEY,
b1 …

);

CREATE TABLE ab
(
id_a INTEGER REFERENCES a (id),
id_b INTEGER REFERENCES b (id)
);


BEGIN;
LOCK a,b;
INSERT INTO a (a1, …) VALUES (…);
INSERT INTO b (b1, …) VALUES (…);
INSERT INTO ab (id_a, id_b) VALUES (currval(‘a_id_seq’), currval(‘b_id_seq’));
COMMIT;


Damit bist Du auf der sicheren Seite. Die Feinheiten über die Befehle solltest Du in der DOKU nachlesen.



mfg

fristo

Hallo,

sicher habe ich einiges noch nicht richtig verstanden.
Das SERIAL eine abkürzende Schreibweise für das Anlegen einer Sequenz ist, und Werte dieser Spalten nicht gesetzt werden sollten, ist mir allerdings schon klar. Etwas anderes ist meinem Beispiel-Code auch nicht zu entnehmen (die Pünktchen nach VALUES stehen für andere Datenfelder).

Bin jetzt stutzig geworden, und habe mein Beispiel noch einmal implementiert. Nun funktioniert es komischerweise in der gewünschten Weise. Zuvor hatte postgres rumgemeckert, in a und b wären die currvals noch gar nicht eingetragen, was bei Fremdschlüsseln verboten ist.

Die zusätzliche Absicherung durch Locks werde ich auch vorsehen.

Danke,
Rainer.

Hallo rainer198,

nimm nicht immer alles ganz so wörtlich. Ich war auch mal “SQL-Neuling”, deswegen helfe ich auch “SQL-Neulinge”.

Habe ich auch so verstanden, ist auch nichts gegen zu sagen.

Meine Antwort lautete:
Wenn Du in eine Tabelle Daten einfügst, in denen ein oder mehrer Spalten vom Typ SERIAL sind, brauchst Du diesen Spalten keinen Wert zuweisen. Der Wert wird automatisch weitergezählt sobald der INSERT-Befehl die Daten in die Tabelle einfügt!

Das war mit dem “nicht verstanden” gemeint.


Ein Transaktion ist eine Absicherung, die da heisst: alles oder nichts. Sie verhindert, dass eine Datenbank inkonsistent wird, falls bei der Abarbeitung der Befehle zwischen “BEGIN” und “COMMIT” ein Fehler auftritt. Sie selbst kann also nichts in irgendwelche Tabellen hinein schreiben.



mfg

fristo