Beziehungen zwischen Tabellen

  • Hallo,

    weiß jemand wie man in SQLite Beziehungen zwischen Datensätzen von zwei Tabellen festlegen kann?
    Ich hab schon Google befragt, es ist aber nix brauchbares rausgekommen.

  • Ein Beispiel nützt hier gar nichts, wenn Du die zu Grunde liegenden SQL Konzepte nicht verstehst.
    Tauch nochmal rein in die Doku von AspirinJunkie.

  • Hier ist erklärt wie man Fremdschlüssel bei der Tabellenerstellung mit sqlite deklariert, was prinzipiell bei anderen relationalen SQL Datenbanken genauso abläuft:

    http://www.sqlite.org/foreignkeys.html


    Die Frage ist nun ob du das Primär und Fremdschlüsselprinzip verstanden hast und nur nicht die Umsetzung verstehst, oder ob beides unklar ist. Beides sollte aber durch diesen und den wikipedia Link von Aspirinjunkie ausreichend erklärt werden. Zu beachten ist außerdem bei der Umsetzung von Beziehungen, dass man unterscheiden muss, ob es sich um 1:1, 1:n oder n:m Beziehungen handelt. Bei n:m Beziehungen zwischen 2 Tabellen benötigst du eine sogenannte JOIN Tabelle, also eine dritte Tabelle, welche die Beziehung zwischen den anderen darstellt.

    Legende: Primärschlüssel // Fremdschlüssel


    Beispiel einer n:m Beziehung:

    |Person| n---------->m |Haarfarbe|
    Personen dürfen in diesem Beispiel mehrere Haarfarben haben (z.b. pinke Strähnen in blondem Haar) und jede Haarfarbe darf selbstverständlich mehreren Personen zugeordnet werden.

    Person(P_ID,name,...)

    Haarfarbe(F_ID,bezeichnung,...)

    Person_Haarfarbe(P_ID,F_ID)
    // P_ID ist Fremdschlüssel zu Person und F_ID ist Fremdschlüssel zu Haarfarbe, beide Schlüssel stellen gleichzeitig auch den zusammengesetzten Primärschlüssel dar, damit Doubletten unmöglich, aber mehrere Personen mehrere Haarfarben haben dürfen


    Beispiel einer n:1 Beziehung:

    |Person| n---------->1 |Haarfarbe|
    Personen dürfen in diesem Beispiel genau eine Haarfarbe haben und jede Haarfarbe darf selbstverständlich mehreren Personen zugeordnet werden.

    Person(P_ID,name,..., F_ID)
    // F_ID ist Fremdschlüssel zu Haarfarbe, P_ID ist Primärschlüssel, somit kann jede Person nur einmal vorkommen und eine Haarfarbe haben

    Haarfarbe(F_ID,bezeichnung,...)

    Der Fremdschlüssel steht hierbei immer in der Tabelle, welche das n in der Beziehung beider Tabellen hat (hier also eine Haarfarbe hat mehrere Personen bzw. umgekehrt ausgedrückt zu n Personen gehört jeweils genau eine Haarfarbe)


    Beispiel einer 1:1 Beziehung:

    |Person| 1---------->1 |Haarfarbe|
    Eine 1:1 Beziehung ist im Prinzip eine 1:n Beziehung und kann nicht eindeutig durch Fremdschlüssel dargestellt werden. Man benötigt hier dann noch sogenannte Contraints (UNIQUE) oder Trigger die dafür sorgen, dass der Fremdschlüssel Wert nur einmalig zugeordnet werden kann.

    Person(P_ID,name,..., F_ID)
    // F_ID ist Fremdschlüssel zu Haarfarbe, P_ID ist Primärschlüssel, somit kann jede Person nur einmal vorkommen und somit genau eine Haarfarbe haben, Trigger oder Contstraints müssen verhindern, dass die Farbe mehreren Personen zugeordnet wird

    Haarfarbe(F_ID,bezeichnung,...)


    Details zu obigen Beziehungen findest du auch hier: http://de.wikipedia.org/wiki/Kardinali…modellierung%29

    Neben diesen recht einfachen Beziehungen gibt es noch komplexere Sachverhalte, wie z.B. ein Vorgesetzter der gleichzeitig selbst Vorgesetzte hat und auch zu den Mitarbeitern zählt (Rekursion)
    Details zur Rekursion und einigen anderen speziellen Beziehungen findest du hier: http://www.luo-darmstadt.de/wiki/index.php…lle_Beziehungen

    7 Mal editiert, zuletzt von misterspeed (10. Mai 2013 um 11:57)

  • Hmm alles ziemlich kompliziert. Evtl. hab ich es verstanden.
    Hab dann mal ein Beispielscript gemacht wie ich es verstanden hab.
    Nur irgendwie funktioniert es (natürlich) nicht.
    Normalerweise müsste beim zweiten Query in Row[1] bei "Nummer" eine 4 stehen, anstatt einer 2.

    Spoiler anzeigen
    [autoit]

    #include <SQLite.au3>
    #include <SQLite.dll.au3>

    [/autoit] [autoit][/autoit] [autoit]

    Global $hQuery, $aRow

    [/autoit] [autoit][/autoit] [autoit]

    _SQLite_Startup()

    [/autoit] [autoit][/autoit] [autoit]

    _SQLite_Open()
    _SQLite_Exec(-1, "CREATE TABLE Daten1 (Spalte1,Spalte2,Spalte3, Nummer REFERENCES Daten2(Nummer) ON UPDATE CASCADE ON DELETE CASCADE);")
    _SQLite_Exec(-1, "INSERT INTO Daten1 VALUES ('abc1','abc2','abc3','1');")
    _SQLite_Exec(-1, "INSERT INTO Daten1 VALUES ('def1','def2','def3','2');")
    _SQLite_Exec(-1, "INSERT INTO Daten1 VALUES ('ghi1','ghi2','ghi3','3');")

    [/autoit] [autoit][/autoit] [autoit]

    _SQLite_Exec(-1, "CREATE TABLE Daten2 (Nummer,Spalte2,Spalte3);")
    _SQLite_Exec(-1, "INSERT INTO Daten2 VALUES ('1','111a','111b');")
    _SQLite_Exec(-1, "INSERT INTO Daten2 VALUES ('2','222a','222b');")
    _SQLite_Exec(-1, "INSERT INTO Daten2 VALUES ('3','333a','333b');")

    [/autoit] [autoit][/autoit] [autoit]

    _SQlite_Query(-1, "SELECT * FROM Daten1;", $hQuery)
    While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
    _ArrayDisplay($aRow)
    WEnd
    _SQLite_QueryFinalize($hQuery)

    [/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

    _SQLite_Exec(-1, "UPDATE Daten2 SET Nummer='4' WHERE Nummer='2';")

    [/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

    _SQlite_Query(-1, "SELECT * FROM Daten1;", $hQuery)
    While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
    _ArrayDisplay($aRow)
    WEnd
    _SQLite_QueryFinalize($hQuery)

    [/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

    _SQLite_Exec(-1, "DROP TABLE Daten1;")
    _SQLite_Exec(-1, "DROP TABLE Daten2;")
    _SQLite_Exec(-1, "VACUUM;")
    _SQLite_Close()
    _SQLite_Shutdown()

    [/autoit]
  • Habs dir mal kommentiert und entsprechend angepasst.

    Spoiler anzeigen
    [autoit]


    #include <SQLite.au3>
    #include <SQLite.dll.au3>

    [/autoit] [autoit][/autoit] [autoit]

    Global $hQuery, $aRow

    [/autoit] [autoit][/autoit] [autoit]

    _SQLite_Startup()

    [/autoit] [autoit][/autoit] [autoit]

    _SQLite_Open()

    [/autoit] [autoit][/autoit] [autoit]

    _SQlite_Query(-1, "PRAGMA foreign_keys = ON;", $hQuery) ; aktiviert ForeignKey support, siehe doku
    _SQLite_QueryFinalize($hQuery)

    [/autoit] [autoit][/autoit] [autoit]

    _SQlite_Query(-1, "PRAGMA foreign_keys;", $hQuery) ; DEBUG: prüft ob ForeignKey aktiviert ist, 0=disabled, 1=enabled
    While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
    _ArrayDisplay($aRow)
    WEnd
    _SQLite_QueryFinalize($hQuery)

    [/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

    ; sqlite nutzt variant als Datentyp, das kann probleme machen, daher sollten die Datentypen der Spalten eindeutig deklariert werden,
    ; außerdem muss ein PrimaryKey deklariert werden, denn einen ForeignKey gibt es nur in Verbindung mit einem PK in der referenzierten Tabelle
    ; da du schlecht einen ForeignKey eines nicht existierenden Datensatz referenzieren kannst muss die ParentTable natürlich zuerst mit Daten befüllt werden,
    ; bei dir also Daten2, ansonsten bekommst du einen ForeignKey Missmatch beim befüllen von Daten1
    ; der Logik nach würde ich auch zuerst die ParentTable Daten2 erstellen, scheinbar kann mans aber auch "falsch rum" machen...

    [/autoit] [autoit][/autoit] [autoit]

    ; Nummer ist hier sowohl PK als auch FK, du könntest aber auch eine oder mehrere andere Spalten als PK deklarieren
    _SQLite_Exec(-1,"CREATE TABLE Daten1 (Spalte1 TEXT," & _
    "Spalte2 TEXT," & _
    "Spalte3 TEXT," & _
    "Nummer INTEGER PRIMARY KEY," & _
    "FOREIGN KEY(Nummer) REFERENCES Daten2(Nummer) ON UPDATE CASCADE ON DELETE CASCADE)" & _
    ";")

    [/autoit] [autoit][/autoit] [autoit]

    ; deine ParentTable, hier ist Nummer der PK
    _SQLite_Exec(-1, "CREATE TABLE Daten2 (Nummer INTEGER PRIMARY KEY," & _
    "Spalte2 TEXT," & _
    "Spalte3 TEXT)" & _
    ";")

    [/autoit] [autoit][/autoit] [autoit]

    _SQLite_Exec(-1, "INSERT INTO Daten2 VALUES ('1','111a','111b');")
    _SQLite_Exec(-1, "INSERT INTO Daten2 VALUES ('2','222a','222b');")
    _SQLite_Exec(-1, "INSERT INTO Daten2 VALUES ('3','333a','333b');")

    [/autoit] [autoit][/autoit] [autoit]

    _SQLite_Exec(-1, "INSERT INTO Daten1 VALUES ('abc1','abc2','abc3','1');")
    _SQLite_Exec(-1, "INSERT INTO Daten1 VALUES ('def1','def2','def3','2');")
    _SQLite_Exec(-1, "INSERT INTO Daten1 VALUES ('ghi1','ghi2','ghi3','3');")

    [/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

    _SQlite_Query(-1, "SELECT * FROM Daten1;", $hQuery)
    While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
    _ArrayDisplay($aRow)
    WEnd
    _SQLite_QueryFinalize($hQuery)

    [/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

    _SQLite_Exec(-1, "UPDATE Daten2 SET Nummer='4' WHERE Nummer='2';")

    [/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

    _SQlite_Query(-1, "SELECT * FROM Daten1;", $hQuery)
    While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
    _ArrayDisplay($aRow)
    WEnd
    _SQLite_QueryFinalize($hQuery)

    [/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

    _SQLite_Exec(-1, "DROP TABLE Daten1;")
    _SQLite_Exec(-1, "DROP TABLE Daten2;")
    _SQLite_Exec(-1, "VACUUM;")
    _SQLite_Close()
    _SQLite_Shutdown()

    [/autoit]

    4 Mal editiert, zuletzt von misterspeed (11. Mai 2013 um 00:46)

  • Ach super. Jetzt hab ich es komplett verstanden.
    Bei mir ist jetzt quasi die Tabelle Daten2 die Haupttabelle. Wie kann ich dann mit einer Query von "Daten2" die dazugehörigen Tupeln bekommen?

  • Mit einem select über beide Tabellen (join). Da FK von Daten1 = PK von Daten2 (JOIN Bedingung) kannst du dies in der select Anweisung prüfen (WHERE Klausel)

    Beispiel:

    [autoit]


    dim $reihen, $spalten, $aErgebnis
    _SQLite_GetTable2d ( -1, "SELECT * FROM (Daten1 t1, Daten2 t2) WHERE t1.Nummer = t2.Nummer;", $aErgebnis, $reihen, $spalten)
    _ArrayDisplay($aErgebnis)

    [/autoit]

    Ergebnis ist eine Tabelle, die alle 7 Spalten enthält. Willst du nicht alle Spalten schränkst du das select z.B. auf die Spalten ein die dich wirklich interessieren.

    [autoit]


    _SQLite_GetTable2d ( -1, "SELECT t1.Spalte1 AS T1S1, t1.Spalte3 AS T1S3, t2.Spalte3 AS T2S3, t2.Nummer AS UniqueKey FROM (Daten1 t1, Daten2 t2) WHERE t1.Nummer = t2.Nummer;", $aErgebnis, $reihen, $spalten)
    _ArrayDisplay($aErgebnis)

    [/autoit]

    Gibt natürlich noch zig andere Möglichkeiten für joins in der SQL Syntax, aber ich gestehe, dass ich da auch nicht wirklich den Durchblick habe und die oben genannte Möglichkeit für mich am besten nachvollziehbar ist. Zusätzlich zur join Bedingung kannst du natürlich noch weitere Vergleichsbedingungen in den WHERE Teil packen, das ist mit deinem nichts sagenden Datenbeispiel aber schwer. Ich empfehle dir wie in meinem ursprünglichen Beispiel Daten zu verwenden die man sich bildlich vorstellen und nachvollziehen kann um das ganze zu üben/lernen. Als erstes solltest du dir schriftlich Formulieren welche Daten es gibt und wie diese zusammenhängen, was die Attribute und was die Entitätstypen sind, welche Attribute der Entitätstypen als eindeutiger Schlüssel in Frage kommen, dann wie in einem meiner Links ein ERD Zeichnen (also diese Kästchen (Entitätstypen) mit den Beziehungen und Attributen), danach setzt du das in Tabellen um. Da du sowas nur einmal machen musst solltest du da auch etwas Zeit in die Datenmodulierung stecken und im Hinterkopf behalten, dass die Daten vielleicht später mal erweitert werden sollen. Wenns dich interessiert kannst du dich dann auch noch mit dem Thema "Normalisierung von Tabellen" beschäftigen und dein Modell ggf. optimieren.


    http://de.wikipedia.org/wiki/Normalisierung_%28Datenbank%29

    2 Mal editiert, zuletzt von misterspeed (11. Mai 2013 um 11:32)

  • Danke das reicht mir schon. Das hilft mir schonmal weiter bei meinem Problem. Ich will nur aus einer Fahrzeug-Datenbank die Fahrzeuge an den dazugehörigen Kunden binden in der Kunden-Datenbank.