FOREIGN KEY ausgeben?

[AlexII]

Hallo,

ich hab ein Problem mit der Foreign Key Ausgabe, bzw. mit der Ausgabe die hinter einem FK steht.

Also ich hab drei Tabellen:

Zitat:

Buch(BuchID, Titel, Autor)
Leser(LeserID, Name, Vorname)
Ausleihe(BuchID, LeserID, Ausleihdatum, Rückgabedatum)

BuchID und LeserID sind in der Tabelle Ausleihe Fremdschlüssel.

Nun meine Frage wie ich in der Tabelle Ausleihe den Titel des Buches und den Namen des Entliehers ausgebe? Mir gelingt die SELECT-Abfrage nicht. Vor allem wenn ein Buch nicht entliehen wurde und der Name des Entlieher leer sein muss.

Bin für jede Hilfe dankbar!

[DeddyH]

Ich nehme an, SQLite beherrscht JOINs?

SQL-Code:

SELECT
  B.Titel, L.Name, L.Vorname
FROM
  Buch B
JOIN
  Ausleihe A ON A.BuchID = B.ID
JOIN
  Leser L ON L.ID = A.LeserID
ORDER BY
  B.Titel, L.Name, L.Vorname

[edit] Diese Abfrage nutzt INNER JOINs, d.h. Du bekommst nur Datensätze geliefert, die in allen 3 Tabellen Entsprechungen aufweisen. Ersetzt Du JOIN durch LEFT JOIN, dann bekommst Du alle Bücher. Gibt es keinen Entleiher, wird dieser mit NULL zurückgegeben. [/edit]

[Sir Rufo]

Die PK Felder würde ich immer einfach als ID angeben:

Buch( ID, Titel, Autor )
Leser( ID, Name, Vorname )
Ausleihe( Buch_ID, Leser_ID, Ausleihdatum, Rückgabedatum )

Warum, dazu gleich mehr.

Warum sollte in der Tabelle Ausleihe ein Eintrag vorhanden sein, wenn das Buch nicht ausgeliehen ist?

Hier mal die Abfrage über alle Bücher, und wenn ausgeliehen, dann mit den Leser-Informationen:

Code:

SELECT Buch.ID, Buch.Titel, Buch.Autor, Ausleihe.Ausleihedatum, Ausleihe.Rückgabedatum, Leser.Name, Leser.Vorname
FROM Buch
LEFT JOIN Ausleihe ON Ausleihe.Buch_ID = Buch.ID
LEFT JOIN Leser ON Ausleihe.Leser_ID = Leser.ID

und jetzt stellt man fest, dass man das sehr schön lesen kann (Buch.ID statt Buch.BuchID - da fängt man ja zu stottern an)

[AlexII]

Ist mir irgendwie schwer zu verstehen, darf ich vllt ein anderes Beispiel zeigen (mit zwei Tabellen), das bisschen einfacher ist, wie ich sehe muss wohl klein anfangen.

Buch(ID, FK_Leser_ID, Titel, Autor)
Leser(ID, Name, Vorname)

Nun möchte ich alle Bücher anzeigen, und wenn entliehen dann mit den Namen der Leser.

Danke noch mal!

[DeddyH]

In den beiden Tabellen gibt es aber keine Info, ob das Buch entliehen ist.

[mkinzler]

SQL-Code:

select
    b.ID, b.Titel, b.Autor,
    IfNull( '', l.Name || l.vorname) as Leiher
from
    buecher b
        left join Leser l on l.Id = b.LeserID;

[AlexII]

Zitat:

Zitat von DeddyH (Beitrag 1188361)

In den beiden Tabellen gibt es aber keine Info, ob das Buch entliehen ist.

Das heißt hier muss ich kein FK verwenden?

[mkinzler]

Sagen wir mal so, in deinem Fall wird ja nur hinterlegt, wer das Buch gerade ausgeliehen hat. Es gibt keine Historie.

[AlexII]

Zitat:

Zitat von mkinzler (Beitrag 1188364)

Sagen wir mal so, in deinem Fall wird ja nur hinterlegt, wer das Buch gerade ausgeliehen hat. Es gibt keine Historie.

Was heißt das genauer? :gruebel:

[mkinzler]

Sobald das Buch zurückgegeben wird, weiss man nicht mehr, ob es schon einmal verliehen war. Bei der 1. Lösung ist das Problemlos möglich.

[DeddyH]

Vielleicht als einführender Lesestoff: http://de.wikipedia.org/wiki/SQL#Abf...pften_Tabellen

[Sir Rufo]

Hier mal etwas zum anschauen http://sqlfiddle.com/#!2/25451/2/1

Such dir jetzt das aus, was das richtige Ergebnis für dich ist.

(ja ich weiß, ein externer Link ist doof, darum hier noch als Text reibkopiert ;))

Tabellen

Code:

CREATE TABLE Buch
   (`ID` int, `Leser_ID` int, `Titel` varchar(40) , `Autor` varchar(40) )
;
   
INSERT INTO Buch
   (`ID`, `Leser_ID`, `Titel`, `Autor`)
VALUES
   (1,null,'Liegen','The Foo'),
   (2,1,'Sitzen','The Foo'),
   (3,2,'Stehen','The Foo'),
   (4,null,'Fliegen','The Foo')
;

CREATE TABLE Leser
   (`ID` int, `Name` varchar(10) , `Vorname` varchar(10) )
;

INSERT INTO Leser
   (`ID`, `Name`, `Vorname`)
VALUES
   (1, 'Lustig', 'Peter'),
   (2, 'Klein', 'Erna')
;

Abfragen

Code:

SELECT 
  Buch.Titel, 
  Buch.Autor, 
  Leser.Name, 
  Leser.Vorname
FROM
  Buch
JOIN Leser
  ON Leser.ID = Buch.Leser_ID;

SELECT 
  Buch.Titel, 
  Buch.Autor, 
  Leser.Name, 
  Leser.Vorname
FROM
  Buch
LEFT JOIN Leser
  ON Leser.ID = Buch.Leser_ID;

[Sir Rufo]

und weil es so schön ist auch nochmal für die Ausgangsfrage http://sqlfiddle.com/#!2/45ebf/1/1

Tabellen

Code:

CREATE TABLE Buch
   (`ID` int, `Titel` varchar(40) , `Autor` varchar(40) )
;
   
INSERT INTO Buch
   (`ID`, `Titel`, `Autor`)
VALUES
   (1,'Liegen','The Foo'),
   (2,'Sitzen','The Foo'),
   (3,'Stehen','The Foo'),
   (4,'Fliegen','The Foo')
;

CREATE TABLE Leser
   (`ID` int, `Name` varchar(10) , `Vorname` varchar(10) )
;

INSERT INTO Leser
   (`ID`, `Name`, `Vorname`)
VALUES
   (1, 'Lustig', 'Peter'),
   (2, 'Klein', 'Erna')
;

CREATE TABLE Ausleihe
   (`Buch_ID` int, `Leser_ID` int, `AusleihDatum` date, `RueckgabeDatum` date)
;

INSERT INTO Ausleihe
   (`Buch_ID`,`Leser_ID`,`AusleihDatum`,`RueckgabeDatum`)
VALUES
   (2,1,'2012-10-01',null),
   (3,2,'2012-10-01',null)
;

Abfragen

Code:

SELECT 
  Buch.Titel, 
  Buch.Autor, 
  Leser.Name, 
  Leser.Vorname,
  Ausleihe.AusleihDatum,
  Ausleihe.RueckgabeDatum
FROM
  Buch
JOIN Ausleihe
  ON Buch.ID = Ausleihe.Buch_ID
JOIN Leser
  ON Leser.ID = Ausleihe.Leser_ID;

SELECT 
  Buch.Titel, 
  Buch.Autor, 
  Leser.Name, 
  Leser.Vorname,
  Ausleihe.AusleihDatum,
  Ausleihe.RueckgabeDatum
FROM
  Buch
LEFT JOIN Ausleihe
  ON Buch.ID = Ausleihe.Buch_ID
LEFT JOIN Leser
  ON Leser.ID = Ausleihe.Leser_ID;

[DeddyH]

Und wenn man Aliase verwendet, kann man Tabellen sogar mit sich selbst verknüpfen. Das gehört zwar nicht zu diesem konkreten Problem, ich wollte es aber der Vollständigkeit halber erwähnt haben.

[AlexII]

Was mache ich wenn ich den Leser lösche der mir noch Bücher schuldet. Bis jetzt wird er bei mir gelöscht, soll aber nicht so sein. Wie bilde ich die Abhängigkeit der Tabellen?

[DeddyH]

Kennt SQLite Löschregeln (ON DELETE SET NULL|CASCADE|NO ACTION)?

[Sir Rufo]

Zitat:

Zitat von DeddyH (Beitrag 1188393)

Kennt SQLite Löschregeln (ON DELETE SET NULL|CASCADE|NO ACTION)?

AFAIK nur über Trigger

[AlexII]

Zitat:

Zitat von DeddyH (Beitrag 1188393)

Kennt SQLite Löschregeln (ON DELETE SET NULL|CASCADE|NO ACTION)?

Ja, aber mein Befehl

Delphi-Quellcode:

FOREIGN KEY(fk_leser_id) REFERENCES leser(id) ON DELETE CASCADE);

wird nicht ausgeführt. Keine Ahnung wieso. Muss wohl Foreign Key aktivieren. Das geht mit der Kommandozeile, aber keine Ahnung wie ich auf meine *.db Datei gelange. Werd bald verrückt. :roteyes:

[p80286]

Man könnte ja auch einfach nachfragen, ob der zu löschende Leser (warum eigentlich löschen?) noch Ausleihen offen hat

Delphi-Quellcode:

Rueckgabedatum is null

Gruß
K-H

[Sir Rufo]

Zitat:

Zitat von AlexII (Beitrag 1188396)

Zitat:

Zitat von DeddyH (Beitrag 1188393)

Kennt SQLite Löschregeln (ON DELETE SET NULL|CASCADE|NO ACTION)?

Ja, aber mein Befehl

Delphi-Quellcode:

FOREIGN KEY(fk_leser_id) REFERENCES leser(id) ON DELETE CASCADE);

wird nicht ausgeführt. Keine Ahnung wieso. Muss wohl Foreign Key aktivieren. Das geht mit der Kommandozeile, aber keine Ahnung wie ich auf meine *.db Datei gelange. Werd bald verrückt. :roteyes:

Manchmal hilft es die Doku zu lesen
Und das einfach per SQL Statement an die DB

[AlexII]

Hab alles laut Doku gemacht, aber irgendwie funktioniert das Cascaden nicht. :gruebel:

[Sir Rufo]

Dann zeig doch mal die Code Stelle aus deiner Anwendung wo du das Einschaltest.

Oder sollen wir vorbeikommen und deinen Kühlschrank leerfuttern?

[DeddyH]

[OT]

Zitat:

Zitat von Sir Rufo (Beitrag 1188402)

Oder sollen wir vorbeikommen und deinen Kühlschrank leerfuttern?

Deal, bin dabei, ich hab Hunger :lol: [/OT]

[AlexII]

Wie gesagt ich aktiviere den FK, siehe Bild, aber die Leser mit ausgeliehenen Büchern werden trotzdem Gelöscht.

Delphi-Quellcode:

FOREIGN KEY(fk_leser_id) REFERENCES leser(id) ON DELETE CASCADE);

[Sir Rufo]

Darum sagte ich ja auch RTFM

Und Augen auf, wenn es da heißt

Zitat:

Zitat von sqlite.org - Enabling Foreign Key Support

Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys command.

Also, wo schaltest du von deiner Anwendung aus dieses Verhalten ein?

[AlexII]

Hier

Delphi-Quellcode:

procedure TForm1.DBErstellenMMClick(Sender: TObject);
var dbFile: String;
    db: TSQLiteDatabase;
    sSQL, sSQL1: String;
begin
  dbFile := ExtractFilePath(Application.ExeName) + 'Database.db';
  db := TSQLiteDatabase.Create(dbFile);
  try

    // Tabelle leser wird, sofern sie existiert, gelöscht
    if db.TableExists('leser') then
    begin
      sSQL1 := 'DROP TABLE leser';
      db.execSQL(sSQL1);
    end;

    // Tabelle buch wird, sofern sie existiert, gelöscht
    if db.TableExists('buch') then
    begin
      sSQL := 'DROP TABLE buch';
      db.execSQL(sSQL);
    end;

    // Enable foreign key constraints
    db.execSQL('PRAGMA foreign_keys = ON;');  <---- HIER

    // Tabelle leser wird angelegt
    sSQL1 := 'CREATE TABLE leser ..... usw;

    // Tabelle buch wird angelegt
    sSQL := 'CREATE TABLE buecher ([id] INTEGER PRIMARY KEY NOT NULL, [titel] VARCHAR (255) NOT NULL,';
    sSQL := sSQL + '[fk_leser_id] INTEGER, FOREIGN KEY(fk_leser_id) REFERENCES leser(id) ON DELETE RESTRICTED);';

    // Create Befehl wird ausgeführt
    db.execSQL(sSQL1);
    db.execSQL(sSQL);

    // Transaktion beginnt
    db.BeginTransaction;

    // leser Tabelle füllen
    sSQL1 := 'INSERT INTO leser ... usw.;

    // buch Tabelle füllen
    sSQL := 'INSERT INTO buch ... usw.;

    // Insert Befehl wird ausgeführt
    db.ExecSQL(sSQL1);
    db.ExecSQL(sSQL);

    // Transaktion endet
    db.Commit;

  finally
    db.Free;
  end;

  ShowData();
end;

[Sir Rufo]

Laut Doku muss das aber auch "RESTRICT" und nicht "RESTRICTED" lauten.

[AlexII]

Zitat:

Zitat von Sir Rufo (Beitrag 1188410)

Laut Doku muss das aber auch "RESTRICT" und nicht "RESTRICTED" lauten.

Hab mich verschrieben, aber die Stelle wo ich es aktiviere ist richtig, oder?

[AlexII]

Es hat geklappt :dancer:

Lösung: man muss den FK immer wieder aktivieren, in diesem Fall in der DELETE Funktion.

[Sir Rufo]

Obwohl ich ja eher statt Restrict Cascade nehmen würde, denn Restrict verhindert eigentlich das löschen und Cascade verbreitet das löschen

[Jumpy]

Naja es sollten in dem Beispiel ja keine Leser gelöscht werden, wenn die noch Bücher zuhause (oder Schulden auf'm Konto) haben. Genaugenommen, sollten Leser eigentlich nie gelöscht werden, weil das die Ausleihistorie der Bücher kaputt macht. Stattdessen werden Ausweise gesperrt oder Ausleihkonten deaktiviert.

Aber ich denke mal es geht eh nur um ein Übungsbeispiel...
...wobei auch bei Übungsbeispielen, sollte man es besser gleich richtig machen.

[AlexII]

Danke euch! Ihr habt mich gerettet. :coder::bouncing4::cheers:

[Sir Rufo]

Zitat:

Zitat von AlexII (Beitrag 1188412)

Es hat geklappt :dancer:

Lösung: man muss den FK immer wieder aktivieren, in diesem Fall in der DELETE Funktion.

Kann es sein, dass deine ganzen Datenbank-Zugriffe ungefähr so gekapselt sind:

Delphi-Quellcode:

procedure IrgendeinBeliebigerZugriffAufDieDatenbank;
var 
  dbFile : String;
  db : TSQLiteDatabase;
begin
  dbFile := ExtractFilePath(Application.ExeName) + 'Database.db';
  db := TSQLiteDatabase.Create( dbFile );
  try

    { ganz viele Aktionen mit der DB }

  finally
    db.Free;
  end;
end;

Dann ist es auch nicht verwunderlich, warum du den Foreign Key Befehl vor dem DELETE ständig neu übergeben musst.
Diese Einstellung gilt nur solange, bis du die Verbindung zur DB wieder trennst, und das machst du bei jeder Methode.

Verbindung aufbauen
Sachen abarbeiten
Verbindung trennen (jetzt ist auf jeden Fall nicht mehr eingeschaltet)