4. PostgreSQL: Wechselseitige FOREIGN KEYs?

PostgreSQL: Wechselseitige FOREIGN KEYs?

  • H

    Hallo.
    Ich habe mal eine FRage zu SQL. Ich setze PostgreSQL ein und möchte Folgendes realisieren (Beispiel):
    - Es gibt eine Tabelle users mit den Spalten id und name
    - Es gibt eine Tabelle lieblingsessen mit den Spalten id, userid und speisen_name

    Nun sollen nur Datensätze in lieblingsessen angelegt werden dürfen, bei denen userid einen gültigen Wert aus users.id hat. Das ist ja soweit kein Problem. Jetzt will ich Folgende Funktionalität dazu: Es dürfen weder aus users Datensätze mit Daten aus lieblingsessen.userid, noch Datensätze aus lieblingsessen mit Daten aus users.id gelöscht werden, ohne das beide gleichzeitig in einer Transaktion gelöscht werden. Kurz gesagt: In der Datenbank müssen immer in lieblingsessen und users zusammengehörige Datensätze existieren.

    0
  • T

    Hallo,

    hast du schon eine Lösung gefunden?
    Diese Frage beschäftigt mich die ganze Zeit:

    In der Datenbank müssen immer in lieblingsessen und users zusammengehörige Datensätze existieren.

    Das Ganze müsste doch durch die referentielle Integrität gewährleistet sein.
    http://www.postgresql.org/docs/8.0/static/ddl-constraints.html

    Also:
    CREATE TABLE users(id INT PRIMARY KEY);
    CREATE TABLE lieblingsessen .. FOREIGN KEY user_id REFERENCES users(id) ..

    😕

    0
  • ?

    Ich würde sagen dass deine zusätzliche Einschränkung (Ein User muss immer min. ein Lieblingsessen haben und beide sollen gelöscht werden) Sache deiner Applikation ist. Diese sollte sich genau darum kümmern (in einer Tranksaktion).

    ms

    0
  • H

    ms@work schrieb:

    Ich würde sagen dass deine zusätzliche Einschränkung (Ein User muss immer min. ein Lieblingsessen haben und beide sollen gelöscht werden) Sache deiner Applikation ist. Diese sollte sich genau darum kümmern (in einer Tranksaktion).

    Verdammt! Ich denke immernoch im abgespeckten MySQL-Stil 😃 Du hast recht. Mit Transaktionen lässt sich das Problem wunderbar lösen 👍

    0
  • A

    Hallo,

    Fremdschlüssel sind doch genau deswegen da, damit sie einem helfen die referentielle Integrität der Daten zu sichern, ohne dass die Applikation da nachhelfen muss (da verstehe ich den Einwand von ms@work nicht). Das Problem mit Transaktionen zu lösen ist fast so ähnlich wie wenn man eine "SELECT * FROM tabelle" Abfrage ausführt, und die Ergebnisse mit Applikationscode filtert anstatt eine WHERE-Klausel zu verwenden. Ok, was anderes ist es wenn PostgreSQL den "ON DELETE CASCADE" Constraint bei Fremdschlüsseln nicht unterstützt. Kann ich mir aber nicht vorstellen.. Auf jeden Fall, würde ich an deiner Stelle "ON DELETE CASCADE" verwenden wenn es geht.

    0
  • ?

    Hey Aziz,

    ON DELETE CASCADE funktioniert wunderbar in PostgreSQL, nur reicht es für diesen Fall nicht aus.

    Wenn du dir die beiden Tabellen anschaust dann gibt es ja nur einen Fremdschlüssel in einer Tabelle => Lieblingsessen hat eine UserId die auf die Tabelle Users zeigt. Users hat keinen Fremdschlüssel der auf Lieblingsessen zeigt.

    Daher kann es auch nur ein ON DELETE CASCADE in eine Richtung geben. Soll heisen, das Löschen eines Users löscht auch das Lieblingsessen, aber ein Löschen eines Lieblingsessen löscht nicht den User.

    Daraus ergibt sich auch, dass ein User kein Lieblingsessen haben kann (technisch auf jeden Fall, im echten Leben eher selten).

    Daher die Lösung mit der Transaktion.

    ms

    0
  • A

    Danke für die Erklärung. Könnte man diese besondere Bedingung nicht auch mit Auslösern (Trigger) implementieren. Dadurch verlagern wir die Logik an einen einzigen Platz, und brauchen uns nicht mehr in der Applikation darum zu kümmern. Hab mich auch nur kurz theoretisch mit Triggern befasst; könnte mich wieder irren 🙂

    0
  • F

    Aziz schrieb:

    Danke für die Erklärung. Könnte man diese besondere Bedingung nicht auch mit Auslösern (Trigger) implementieren. Dadurch verlagern wir die Logik an einen einzigen Platz, und brauchen uns nicht mehr in der Applikation darum zu kümmern. Hab mich auch nur kurz theoretisch mit Triggern befasst; könnte mich wieder irren 🙂

    Jup, Trigger sind dafür geeignet (Wenn sie denn von der DBMS unterstützt werden).

    0
  • M

    Also ich sehe die Datenbank nicht als Ort wo man Applikationslogik implementieren sollte. Muß aber dazusagen, dass ich hauptsächlich im J2EE-Umfeld arbeite, daher meine Ansicht.

    Das mit dem Löschen ist kein Problem mit einem Trigger, aber es kann immer noch einen User ohne Lieblingsessen geben.
    Da wirst du ohne Transaktion nicht herumkommen.

    ms

    0
  • A

    ms schrieb:

    Das mit dem Löschen ist kein Problem mit einem Trigger, aber es kann immer noch einen User ohne Lieblingsessen geben.
    Da wirst du ohne Transaktion nicht herumkommen.

    Verstehe ich nicht ganz. Wenn ich ein Lieblingsessen lösche, dann soll ja ein Trigger ausgelöst werden, in dem auch der User gelöscht wird. Wie kann es da passieren, dass ein User nicht gelöscht wird? Sind Trigger nicht so atomar wie Transaktionen? Meinst du das?

    0
  • A

    So wie ich mir das gedacht habe funktioniert das Ganze wahrscheinlich garnicht. Hat man nämlich einen "ON DELETE CASCADE" Fremdschlüssel und einen Trigger, dann entsteht ja praktisch eine unendliche Schleife sobald ich versuche einen User zu löschen. Lösche ich einen User, dann aktiviere ich den "ON DELETE CASCADE" Constraint, welcher versucht die Lieblingsspeise des Users zu löschen. Wenn das aber passiert dann wird der Trigger für die Tabelle mit den Lieblingsspeisen ausgelöst, welcher versuchen wird den User zu löschen. 😮 🙂

    0
  • F

    Aziz schrieb:

    So wie ich mir das gedacht habe funktioniert das Ganze wahrscheinlich garnicht. Hat man nämlich einen "ON DELETE CASCADE" Fremdschlüssel und einen Trigger, dann entsteht ja praktisch eine unendliche Schleife sobald ich versuche einen User zu löschen. Lösche ich einen User, dann aktiviere ich den "ON DELETE CASCADE" Constraint, welcher versucht die Lieblingsspeise des Users zu löschen. Wenn das aber passiert dann wird der Trigger für die Tabelle mit den Lieblingsspeisen ausgelöst, welcher versuchen wird den User zu löschen. 😮 🙂

    Sollte nicht passieren. Der Trigger sollte nur feuern, wenn wirklich was gelöscht wurde: Wird ein User gelöscht, wird die Lieblingspeise gelöscht. Der DELETE-Trigger von Tabelle "Lieblingsspeise" versucht den User dieser Lieblingsspeise zu löschen. Dieser existiert aber nicht mehr, also kann auch kein DELETE-Trigger bei Tabelle "User" ausgelöst werden.

    0
  • F

    Aziz schrieb:

    ms schrieb:

    Das mit dem Löschen ist kein Problem mit einem Trigger, aber es kann immer noch einen User ohne Lieblingsessen geben.
    Da wirst du ohne Transaktion nicht herumkommen.

    Verstehe ich nicht ganz. Wenn ich ein Lieblingsessen lösche, dann soll ja ein Trigger ausgelöst werden, in dem auch der User gelöscht wird. Wie kann es da passieren, dass ein User nicht gelöscht wird? Sind Trigger nicht so atomar wie Transaktionen? Meinst du das?

    Er meint wohl, dass man auch die Lieblingsspeise löschen können muss, ohne dass
    der User gelöscht wird ( User mag eventuell seine Lieblingsspeise nicht ewig ).

    0
  • M

    Nein, eigentlich meinte ich, dass man einen User ohne Lieblingsspeise anlegen kann.

    Der Trigger ist ja nur ein Hilfsmittel um beim Löschen alles schön aufzuräumen, aber existieren darf der User ohne Lieblingsessen ja trotzdem.

    ms

    0
  • F

    ms schrieb:

    Nein, eigentlich meinte ich, dass man einen User ohne Lieblingsspeise anlegen kann.

    Der Trigger ist ja nur ein Hilfsmittel um beim Löschen alles schön aufzuräumen, aber existieren darf der User ohne Lieblingsessen ja trotzdem.

    ms

    Ok, verstehe. Würde Transaktionen (wenn möglich) nicht clientseitig ausführen (falls du das vorhast). SPs sind dafür besser geeignet. Dann wäre wieder Programmlogik in der DB, was du verhindern willst, IMHO ist es aber das kleinere Übel.

    0
  • M

    Finten schrieb:

    Ok, verstehe. Würde Transaktionen (wenn möglich) nicht clientseitig ausführen (falls du das vorhast). SPs sind dafür besser geeignet. Dann wäre wieder Programmlogik in der DB, was du verhindern willst, IMHO ist es aber das kleinere Übel.

    Eigentlich ist es ja egal wo die Transaktion passiert. Ob ich es jetzt im Javacode oder du in einer SP oder irgendwer im Client implementiert kommt aufs selbe raus.
    Wenns in einer SP ist sollte dann kein direkter Zugriff auf die Tabellen möglich sein.

    Programmlogik in der DB ist eine Ansichtssache.
    Ich nehme an, wenn du vom größeren Übel sprichst, dann meinst du die Transaktionsverwaltung im Clientcode. Daraus schließe ich dass du genauso ein 3 Schichtenmodell hast wie ich:
    Client (irgendeine Sprache) - Applikationslogik (SPs in der DB) - Datenhalter (Tabellen in der DB).
    Bei mir ist es
    Client (Java) - Applikationslogik (Java, z.B. EJBs übernehmen die Transaktionsverwaltung) - Datenbank

    Gibt also kein kleines Übel 🙂

    ms

    0
  • F

    Hi,
    das Problem mit clientgesteuerten Transaktionen ist folgendes:
    Wenn die Verbindung zwischen Client und Server unterbrochen wird, während eine Transaktion ausgeführt wird, kann der Client diese ja nicht beenden, was dazu führt, dass Tabellen gelockt sind und andere User vom Zugriff abgehalten werden, je nach Isolationslevel sogar vom Lese-Zugriff.

    Läuft die Transaktion dagegen innerhalb einer SP besteht das Problem nicht.

    0
  • M

    Verstehe, aber das ist doch eher eine Konfigurationsache.
    Weis nicht ob das so gut ist nach diesen Gesichtspunkten eine Architektur zu bevorzugen.

    Für diesen konkreten Fall fällt mir jetzt nur das Transactiontimeout ein, dass die Datensätze nach einer bestimmten Zeit wieder freigibt.

    ms

    0
  • F

    ms schrieb:

    Verstehe, aber das ist doch eher eine Konfigurationsache.
    Weis nicht ob das so gut ist nach diesen Gesichtspunkten eine Architektur zu bevorzugen.

    Für diesen konkreten Fall fällt mir jetzt nur das Transactiontimeout ein, dass die Datensätze nach einer bestimmten Zeit wieder freigibt.

    ms

    Geht natürlich auch über die Timeout-Steuerung. Wobei man dann darauf achten muss, dass er nicht zu klein ist, um regulär längere laufende Transaktionen nicht abzuschießen.

    SPs haben halt auch den Vorteil, dass der Code zentral modifizert werden kann ohne Client-Programme auszutauschen (Änderungen im laufenden Betrieb möglich). Wie wichtig das ist, hängt natürlich vom Projekt ab.

    Falls es für dich wichtig ist, dein Programm mit verschiedenen DBMS laufen zu lassen, ist dein Vorgehen natürlich besser.

    Ich spreche hier nur aus eigener Erfahrung und würde, (DBMS-Portierungsfähigkeit mal ausgenommen) immer der serverseitigen Implementierung den Vorrang geben. Wird aber vermutlich auch viele Leute geben, die dir das Gegenteil raten würden; gibt bestimmt auch viele, die mehr Ahnung haben als ich. 😉

    0
  • M

    Anscheinend haben wir die ganze Zeit ein wenig aneinander vorbeigeredet.
    Wenn du von Rich-Client sprichst hast du vollkommen recht.
    Mit J2EE befindet sich die Applikationslogik natürlich auch am Server, nur halt nicht in der Datenbank.

    Ist auch immer eine Frage des KnowHow.
    Ich würde mir ziemlich schwer tun eine Applikation rein mit SPs, Triggers, ... zu entwickeln, die mehr als nur Daten lädt und speichert. Alleine das Debuggen stelle ich mir ziemlich mühsam vor.

    ms

    0
Anmelden zum Antworten