Ich habe zwei Tabellen, SystemVariables und VariableOptions . SystemVariables sollte selbsterklärend sein, und VariableOptions enthält alle möglichen Auswahlmöglichkeiten für alle Variablen.
VariableOptions hat einen Fremdschlüssel, variable_id , der angibt, für welche Variable es eine Option ist. SystemVariables hat einen Fremdschlüssel, choice_id , der angibt, welche Option die aktuell ausgewählte Option ist.
Ich habe die zirkuläre Beziehung mit use_alter in choice_id und post_update in SystemVariables ' choice Beziehung verstanden. Ich möchte jedoch eine zusätzliche Datenbankeinschränkung hinzufügen, die sicherstellt, dass choice_id gültig ist (d. H. Es bezieht sich auf eine Option, die darauf zurückführt).
Die Logik, die ich brauche, unter der Annahme, dass sysVar eine Zeile in der Tabelle SystemVariables darstellt, ist im Grunde:
Aber ich weiß nicht, wie ich diese Art von Constraint mit SQL, deklarativen oder anderen Methoden erstellen soll. Bei Bedarf könnte ich dies nur auf Anwendungsebene validieren, aber ich möchte es wenn möglich auf Datenbankebene haben. Ich benutze Postgres 9.1.
Ist das möglich?
Sie können das ohne schmutzige Tricks implementieren. Erweitern Sie einfach den Fremdschlüssel , der auf die ausgewählte Option verweist, um variable_id zusätzlich zu choice_id einzufügen.
Hier ist eine funktionierende Demo. Temporäre Tabellen, damit Sie leicht damit spielen können:
%Vor%Die Auswahl einer verknüpften Option ist erlaubt:
%Vor%Aber es kommt nicht aus der Reihe:
%Vor%%Vor%
Voilá . Genau das, was du wolltest.
Ich denke, ich fand eine bessere Lösung in dieser späteren Antwort:
Adressierung der @ ypercubes Frage in den Kommentaren , Um Einträge mit unbekannter Verknüpfung zu vermeiden, machen Sie alle Schlüsselspalten NOT NULL , einschließlich Fremdschlüssel.
Die zirkuläre Abhängigkeit würde das normalerweise unmöglich machen. Es ist das klassische Chicken-Ei Problem: Eines von beiden muss zuerst da sein, um das andere zu spawnen. Aber die Natur hat einen Weg gefunden, und auch Postgres: aufschiebbare Fremdschlüsselbeschränkungen .
%Vor%Neue Variablen und zugehörige Optionen müssen in dieselbe Transaktion eingefügt werden:
%Vor% Die Einschränkung NOT NULL kann nicht zurückgestellt werden, sie wird sofort erzwungen. Aber die Fremdschlüsselbedingung kann , weil wir es so definiert haben. Es wird am Ende der Transaktion überprüft, wodurch das Hühnerei-Problem vermieden wird.
In diesem bearbeiteten -Szenario werden beide Fremdschlüssel zurückgestellt . Sie können Variablen und Optionen in beliebiger Reihenfolge eingeben.
Sie haben vielleicht bemerkt, dass die erste Fremdschlüsseleinschränkung keinen CASCADE -Modifikator hat. (Es wäre nicht sinnvoll, Änderungen an variableoptions.variable_id zu kaskadieren.
Auf der anderen Seite hat der zweite Fremdschlüssel einen CASCADE Modifikator und ist trotzdem als aufschiebbar definiert. Dies birgt einige Einschränkungen. Das Handbuch :
Andere referentielle Aktionen als die
NO ACTIONPrüfung können nicht zurückgestellt werden, selbst wenn die Einschränkung als aufschiebbar deklariert ist.
NO ACTION ist der Standardwert.
Referenzielle Integritätsprüfungen für INSERT werden also zurückgestellt, die deklarierten kaskadierenden Aktionen für DELETE und UPDATE jedoch nicht. Das Folgende ist in PostgreSQL 9.0 oder 9.1 nicht zulässig, da Einschränkungen nach jeder Anweisung erzwungen werden:
Details:
Seltsamerweise funktioniert das Gleiche in PostgreSQL 8.4 , während in der Dokumentation dasselbe Verhalten behauptet wird. Sieht in der alten Version wie ein Bug aus - auch wenn es auf den ersten Blick eher vorteilhaft als schädlich erscheint. Muss für neuere Versionen behoben worden sein.
BEARBEITEN: Die Version 0.7.4 von SQLAlchemy (veröffentlicht am selben Tag, an dem ich nach diesem Problem gefragt habe, 7/12 / '11!) enthält einen neuen autoincrement -Wert für Primärschlüssel das ist auch ein Teil der Fremdschlüssel, ignore_fk . Die Dokumentation wurde ebenfalls erweitert, um ein gutes Beispiel für das zu geben, was ich ursprünglich erreichen wollte.
Alles ist jetzt gut erklärt here .
Wenn Sie den Code sehen möchten, den ich vor der obigen Version entwickelt habe, überprüfen Sie den Überarbeitungsverlauf dieser Antwort.
Ich mag Zirkuläre Referenzen wirklich nicht. Es gibt normalerweise einen Weg, sie zu vermeiden. Hier ist ein Ansatz:
%Vor%Tags und Links python sql postgresql sqlalchemy database-design