sqlite: Liste von Foreign Keys statt nur einem Key

Installation und Anwendung von Datenbankschnittstellen wie SQLite, PostgreSQL, MariaDB/MySQL, der DB-API 2.0 und sonstigen Datenbanksystemen.
Antworten
Sylvan
User
Beiträge: 3
Registriert: Donnerstag 6. September 2012, 06:40

Hallo,

Ich fang am besten mal mit einem kleinem Beispiel an:

Code: Alles auswählen

import sqlite3

DB = sqlite3.connect('Test.db')
Cursor = DB.cursor()

Cursor.execute("""CREATE TABLE if not exists Autos ( 
    CarID INTEGER PRIMARY KEY,
    Marke TEXT,
	Modell TEXT)""")

Cursor.execute("""CREATE TABLE if not exists Fahrer ( 
	Name TEXT,
    Autos INTEGER,
    FOREIGN KEY(Autos) references Autos(CarID))""")

Cursor.execute('INSERT INTO Autos (Marke, Modell) VALUES (?,?)', ["VW", "Golf"])
Cursor.execute('INSERT INTO Autos (Marke, Modell) VALUES (?,?)', ["Opel", "Astra"])
Cursor.execute('INSERT INTO Autos (Marke, Modell) VALUES (?,?)', ["Ford", "Mondeo"])
Cursor.execute('INSERT INTO Autos (Marke, Modell) VALUES (?,?)', ["Lada", "Niva"])

Cursor.execute('INSERT INTO Fahrer (Name) VALUES (?)', ["Klaus"])
Cursor.execute('INSERT INTO Fahrer (Name) VALUES (?)', ["Ingo"])

DB.commit()
DB.close()
Ich habe also 2 Tabellen: Autos und Fahrer.
Autos werden einem Fahrer in seinem Feld "Autos" über ihre CarID zugewiesen.
Wenn es nur eine 1:1 Beziehung gibt wäre der Weg über ForeignKey die wünschenswerte Beziehung.
Aber jeder Fahrer kann mehrere Autos haben!
Daher nun meine eigentliche Frage:

Wie bekomme ich eine ForeignKey-Beziehung mit einer 1:unendlich Beziehung hin?
Was ich bisher gelesen habe ist, ist das in sqlite dafür Strings genommen werden wo die IDs kommagetrennt (oder anders) eingetragen werden.
In dem Fall weiß ich aber nicht wie ich dann effektiv folgende gewünschte Features umsetze:
  • Hinzufügen von Werten zu der ID-Liste
  • Auslesen der Anzahl der IDs in der ID-Liste
  • Prüfen ob eine ID in der ID-Liste vorkommt
  • Entfernen von IDs aus der ID-Liste
Die wirklichen Features von ForeignKeys wie z.b. Cascade on Update oder ähnliches werde ich damit wohl aber sowieso nicht erreichen - oder?
Hoffe jemand kann hilfreiche Tipps geben wie ich das am besten umsetzen könnte.

Achja: Die einfache Umkehr dass man in dem Fall einfach einem Auto einen Fahrer zuweist und man somit nur noch einen Key hat ist zwar logisch soll in dem Fall aber nicht verwendet werden.

Eine zweite Frage hätte ich außerdem:
Prüft sqlite auf korrekte Datentypen?
Wenn ich einem Integer-Feld einen String zuweise wird er auch so in das Feld geschrieben und ist wieder auslesbar obwohl der Typ ja eindeutig falsch ist.
Würde ich z.B in meinem oberen Beispiel einem Fahrer als Wert für "Autos" den String "irgendwas" übergeben funktioniert das obwohl der Datentyp ja eigentlich Integer ist.
BlackJack

@Sylvan: Wo hast Du das mit den Zeichenketten mit durch Komma getrennten IDs denn gelesen? Das wird weder bei SQLite noch einem anderen DBMS so gemacht.

Du musst entweder mehrere Einträge für *einen* Fahrer mit mehreren Autos in die `Fahrer`-Tabelle schreiben, was unschön ist, weil es die Normalformen verletzt, oder eine weitere Tabelle einführen. Deine `Fahrer`-Tabelle hat keinen Primärschlüssel („primary key” / PK), das ist in der Regel keine gute Idee. Also da sollte eine ID (= PK) und der Fahrername drin stehen. Und dann eine weitere Tabelle in der Auto- und Fahrer-IDs zusammen geführt werden.

SQLite prüft nicht auf Typen. Versucht aber Rückgabewerte entsprechend des Tabellen-Schemas umzuwandeln. Also wenn da INT für eine Spalte steht, dann versucht SQLite Dir auch einen Wert von diesem Typ zu liefern, sofern das möglich ist.

Bezüglich der Namensgebung auf Python-Seite solltest Du mal einen Blick in den Style Guide for Python Code (PEP 8 ) werfen.
Sylvan
User
Beiträge: 3
Registriert: Donnerstag 6. September 2012, 06:40

Danke dir für deine Tipps.

Die Geschichte mit den redundanten Einträgen will ich natürlich vermeiden.
Den Tipp mit der zusätzlichen Tabelle werde ich wohl beherzigen finde es aber schade dass es tatsächlich keine Array-artige Lösung gibt weil es damit meiner Meinung nach sehr elegant umsetzbar wäre.
Sylvan hat geschrieben:Deine `Fahrer`-Tabelle hat keinen Primärschlüssel („primary key” / PK), das ist in der Regel keine gute Idee.
Ist mir bewusst und war in dem Beispiel extra ausgelassen worden damit keiner auf die Idee kommt den Tipp zu geben die ForeignKey-Beziehung einfach umzudrehen. Ansonsten verwende ich in der Regel natürlich so gut wie immer Primärschlüssel.
Sylvan hat geschrieben:Bezüglich der Namensgebung auf Python-Seite solltest Du mal einen Blick in den Style Guide for Python Code (PEP 8 ) werfen.
Konkret hast du das angebracht weil ich das Datenbank-Objekt in Großbuchstaben geschrieben habe was impliziert dass es eine Konstante ist?
Sylvan hat geschrieben:Wo hast Du das mit den Zeichenketten mit durch Komma getrennten IDs denn gelesen?
Ich habe natürlich das Web durchsucht bevor ich hier euch nerve und da bin ich in diversen Foren auf derartige Lösungen gestoßen. Ich freue mich ja das es tatsächlich nicht so gemacht wird da ich mit sowas erhebliche Bauchschmerzen hätte.

Also danke dir nochmal für Hilfe
BlackJack

@Sylvan: Arrays als Datentyp für eine Spalte gibt es bei diversen DBMS als Erweiterung zum SQL-Standard. Aber das ist halt nicht mehr Standard und damit von DBMS zu DBMS unterschiedlich anzusprechen.

Wenn jedes Auto nur einen Besitzer haben kann, dann wäre es ürbrigens tatsächlich am einfachsten die Fahrer-ID zum Auto in der `Auto`-Tabelle als Fremdschlüssel zu speichern.

Bei der Namensgebung meinte ich `DB` und `Cursor`.
Benutzeravatar
/me
User
Beiträge: 3554
Registriert: Donnerstag 25. Juni 2009, 14:40
Wohnort: Bonn

Sylvan hat geschrieben:Den Tipp mit der zusätzlichen Tabelle werde ich wohl beherzigen finde es aber schade dass es tatsächlich keine Array-artige Lösung gibt weil es damit meiner Meinung nach sehr elegant umsetzbar wäre.
Das sehe ich anders. Wenn du bei einer kommaseparierten Liste einen Eintrag hinzufügen möchtest, dann musst du den bestehenden Eintrag lesen, ihn ändern und dann zurückschreiben. Gleiches gilt beim Löschen. Ein Index lässt sich damit auch nicht verwenden. Mit einer sauberen relationalen Beziehung hast du diese Probleme nicht.
Sylvan
User
Beiträge: 3
Registriert: Donnerstag 6. September 2012, 06:40

Die kommaseparierte Liste schied ja sowieso aus.
Ein richtiges Array (oder eine Queue, Stack oder Liste) hätte nicht das Problem das die komplette Liste gelesen oder neu geschrieben werden müsste.
Ein Index wäre tatsächlich damit nicht möglich aber die Elemente selbst könnten Fremdschlüssel darstellen.
In PostgreSQL ist ein Array ja auch implementiert.
Benutzeravatar
/me
User
Beiträge: 3554
Registriert: Donnerstag 25. Juni 2009, 14:40
Wohnort: Bonn

Sylvan hat geschrieben:In PostgreSQL ist ein Array ja auch implementiert.
Das ändert aber nichts daran, dass es für das Handling von referenziellen Beziehungen unhandlich ist. Wie sagt die Dokumentation von PostgreSQL doch so schön: "To search for a value in an array, you must check each value of the array."
Benutzeravatar
Hyperion
Moderator
Beiträge: 7478
Registriert: Freitag 4. August 2006, 14:56
Wohnort: Hamburg
Kontaktdaten:

Sylvan hat geschrieben: Aber jeder Fahrer kann mehrere Autos haben!
Daher nun meine eigentliche Frage:

Wie bekomme ich eine ForeignKey-Beziehung mit einer 1:unendlich Beziehung hin?
`1:n` ist doch ganz einfach. In der Tabelle auf der `n`-Seite fügst Du ein Fremdschlüsselfeld als Referenz auf die `1`-Seite ein. In diesem Falle wäre das also ein Feld `Fahrer` in der Tabelle `Autos`, welches Du dann noch als FK definierst.

Sollte es sich um eine `n:m`-Beziehung handeln, so musst Du aus der Relation eine eigenständige Tabelle machen, die jeweils die kompletten PK der beiden beteiligten Entitäten als PK übernimmt und gleichzeitig als FK auf den jeweiligen Ursprung verweist.

Bei einer `1:1`-Beziehung kannst Du Dir aussuchen, in welcher Tabelle Du die Referenz unterbringst (was der wesentliche Unterschied zu `1:n` ist). Ein Ausnahme wäre da noch eine schwache Entität - da muss die Referenz wiederum in die Tabelle der schwachen Entität.

Hier noch mal das ganze als Beispiel:
Bild

Und das dazugehörige SQL: Link
encoding_kapiert = all(verstehen(lesen(info)) for info in (Leonidas Folien, Blog, Folien & Text inkl. Python3, utf-8 everywhere))
assert encoding_kapiert
Antworten