Verständnisfrage zu SQLite-Syntax

[Delbor]

Hi zusammen

Zur Zeit arbeite ich an einer SQLite-DB für meinen PDFOfficer. Zuerst hier mal mein DB-Modell, mit MySQL Workbench erstellt und anschliessend nach SQLite exportiert:

Parallel dazu bin ich dabei, mir mit Google Translate Passagen aus dem SQLite-Handbuch zu übersetzen, aktuell zum Thema CreateTable und gewissen Constraints. Aus den übersetzten Texten stelle ich mir Word-Dokumente als Lern- und Nachschlagequellen zusammen.
Dabei hat sich für mich nun eine Unklarheit ergeben. Aus meiner Google-Übersetzung:

Zitat:

Der übergeordnete Schlüssel einer Fremdschlüsseleinschränkung darf die Zeilen-ID nicht verwenden. Der übergeordnete Schlüssel darf nur benannte Spalten verwenden.

Der Originaltext:

Zitat:

The parent key of a foreign key constraint is not allowed to use the rowid. The parent key must used named columns only.

Unter dem 'übergeordneten Schlüssel' verstehe ich in jedem Fall den Primärschlüssel der Elterntabelle, und nach meinem Verständnis ist der in der Elterntabelle gleichzeitig die ZeilenID. Der zweite Satz ist automatisch erfüllt: Ups, Kommmando zurück - ich bins, der das automatisch macht. Ich hab das soeben mal durchgespielt. Nach benennen des ersten Feldes wird Integer als Datentyp vorgeschlagen, und bei verlassen dieses Feldes werden die Haken bei Primarykey und NotNull gesetzt. Was da passiert, wenn ich einen andern Datentyp wähle, habe ich noch nie probiert...
Nun ja, dadurch, dass ich das PK-Feld anlege, habe ich eine benannte Spalte.

Meine Verständnisfrage ist also eigentlich lediglich: Was soll das mit der Zeilen-Id, die nicht verwendet werden darf? Zumal ich ja gerade die bei einem Insert über mehrere Tabellen abfrage? (LastRowId)

Gruss
Delbor

[Fritzew]

Sqlite hat per default ein Feld rowid (Integer) als primären index
dieser darf nicht als fremdschlüssel verwendet werden.

Hat Deine Tabelle einen integrierten primary key sind diese intern identisch.

Die Aussage

Zitat:

The parent key of a foreign key constraint is not allowed to use the rowid. The parent key must used named columns only.

bezieht sich nur darauf. Wenn Du eine PK mit Namen hast is alles gut

[Delbor]

Hi Fritzew

Danke für deine prompte Antwort!

Gruss
Delbor

[TurboMagic]

Wozu hat eigentlich SQLite diese automatische ID Spalte?
Für Tabellen ohne definierten PK?
Aber wer erstellt denn Tabellen ohne PK?

[Delbor]

Hi Turbo Magic

Ich kann noch nicht behaupten, alles verstanden zu haben. Nur soviel: Wenn das deklarierte PK-Feld vom Typ INTEGER, aber kein Int oder Bigint ist, ist es ein Alias für die RowId. Für den Fall, dass du ebenso wie ich mit dem Englisch mehr oder wenigger auf Kriegsfuss stehst, findest du im Anhang mal ein gezipptes Worddokument mit meinen bisherigen Übersetzungen.
SQLite - Tabelle erstellen.zip

Gruss
Delbor

[jobo]

Ganz allgemein ist es nicht empfehlenswert, db spezifische Mechanismen bei der Modellierung zu verwenden. Wenn SQLite das mit der ROWID freiwillig in Form eines Alias macht, soll es so sein, aber selbst macht man es halt nicht, egal welche DB.
Eigentlich hat jede DB einen ureigenen Mechanismus, um eine Zeile selber zu adressieren und die meisten Systeme dokumentieren den auch, rowid bspw. gibt es glaub ich in verschiedenen DB.

Und nochwas:
Was auch immer man mit einem grafischen Tool macht, es ist interessant, die Create Anweisung selber zu schreiben und natürlich auch die gewünschten Constraints (PK, FK, usw..)
Die spielt man ein und verwendet dann ein Tool, um die "Ergebnisse" wieder auszulesen (DML). Das ist manchmal sehr erhellend. Die Differenz zwischen beiden Scripten spiegelt z.B. die ganzen Defaults, die das System "freiwillig" annimmt.

Will sagen, entscheidend und richtig und wichtig ist nicht unbedingt, was (irgend-)ein grafisches Tool macht oder in "vorauseilendem Gehorsam" einträgt/ausblendet, sondern die Defaults und Vorgaben der Engine. Die sollten natürlich gemäß Doku erfolgen.
Defaults sind am Ende dann auch nur ein Komfortangebot und können durch explizite Angaben überschrieben werden (beim CREATE oder per ALTER)

Am Ende kann man natürlich gerne Tools für den Entwurf einsetzten. Den Feinschliff macht man vielleicht besser per Hand und inkludiert Defaults in das Script.
Damit ist ein Script basiertes Datenmodell dann auch stabil bei Versionswechsel der DB, es ist besser verständlich und versionierbar.

[Delbor]

Hi jobo

Zitat von jobo:

Ganz allgemein ist es nicht empfehlenswert, db spezifische Mechanismen bei der Modellierung zu verwenden. Wenn SQLite das mit der ROWID freiwillig in Form eines Alias macht, soll es so sein, aber selbst macht man es halt nicht, egal welche DB.
Eigentlich hat jede DB einen ureigenen Mechanismus, um eine Zeile selber zu adressieren und die meisten Systeme dokumentieren den auch, rowid bspw. gibt es glaub ich in verschiedenen DB.

Wenn ich mit Workbench ein Modell zeichne, kann ich mir die SQL dazu von Workbench erstellen lassen. Mit ForwardEngineering wird das Script für MySQL und daraus eine ebensolche Datenbank erzeugt, per Export kann ich ein Plugin wählen(wenn installiert), das mir das Script für SQLite erstellt. Grundsätzlich sind die beiden Scripte fast gleich - beide beruhen auf SQL92, nur das für SQLite noch einige Zeilen vorangestellt werden:

Delphi-Quellcode:

-- Creator:       MySQL Workbench 6.3.8/ExportSQLite Plugin 0.1.0
-- Author:        Roger
-- Caption:       New Model
-- Project:       Name of the project
-- Changed:       2019-09-13 18:52
-- Created:       2019-07-31 10:50
PRAGMA foreign_keys = OFF;

-- Schema: PdfOfficerDB
ATTACH "PdfOfficerDB.sdb" AS "PdfOfficerDB";
BEGIN;

Diese Zeilen kann ich so nicht brauchen. Schon diese Zuweisung ist falsch(wenn ich das Manual richtig versanden hab):
 PRAGMA foreign_keys = OFF; Ausser, dass Delphi dies so nicht schlucken wird (da fehlt noch ':'). müsste OFF durch ON ersetzt werden. Ausserdem sieht das von der Workbench erzeugte Script so aus:

Delphi-Quellcode:

CREATE TABLE "PdfOfficerDB"."tblStrassen"("StrassenID" INTEGER PRIMARY KEY NOT NULL,                        
                                          "Strasse" VARCHAR(85));

Auch das ist falsch (die doppelten Anführungszeichen), sowohl offensichtlich für SQLite(*) als auch für Pascal. In welcher Sprache dies so eingesetzt werden kann,entzieht sich meiner Kenntnis.

Zitat von jobo:

Und nochwas:
Was auch immer man mit einem grafischen Tool macht, es ist interessant, die Create Anweisung selber zu schreiben und natürlich auch die gewünschten Constraints (PK, FK, usw..)

Das habe ich mehr oder weniger so vor. Vor allem entdeckte ich einige Fehler, die ich beim Entwurf mit Workbench gemacht habe. So habe ich einige PK-Felder mit 'unsigned datatype' erstellt, andere nicht - notwendig wäre es wohl nicht. Ich werde mir aber das Manual nochmal vornehmen, um mir über allfällige Konsequenzen klar zu werden.
Ansonsten wird es für jede zu erstellende Tabelle und für jeden Index eine eigene Prozedur/Funktion geben, wobei Teile des exportierten Scripts die jeweils in diesen einzusetzenden SQL-Statements liefern.

Gruss
Delbor

* Ich meinte, ich hab das aus dem Manual, doch ich konnte die Stelle nicht mehr finden

[jobo]

Die Problematik mit den Anführungsstrichen wäre ein gutes Beispiel für ungewollte Effekte, die durch Toolfehler oder Fehlkonfiguration eines Tools entstehen können.
Diese oder ähnliche Form von Delimiterangaben für Feldnamen sind idR überflüssig, wenn man auf Leerzeichen und nationale Zeichen in den Feldnamen verzichtet. Auch wenn wir im Jahr 2000 ++ leben, ist die Verwendung von reinen ASCII Bezeichnern immer ne gute Wahl (plus einheitliche Sprache).

Die Pragmaanweisungen muss man prüfen. Wenn man keine Foreign Keys will, schaltet man sie aus, wenn man sie will, schaltet man sie ein. Sollte eigentlich so laufen, wie dort geschrieben.
Die pauschale Abschaltung von Foreign Keys würde man höchstens für einen komplizierten Import mit rekursiven Bezügen machen. Und am Ende dann die Bezüge aktivieren. Wofür braucht man sonst Foreign Key Definitionen?

Was das Tool ausspuckt ist ein komplettes Create Script. Also mehr als eine Anweisung. Soetwas läuft idR nicht mit den normalen Delphi Query Komponenten. Ein solches Script wird in diesem Fall hier z.B. mit SQLITE.EXE eingespielt oder sonst irgendeinem scriptfähigen Tool per Kommandozeile oder interaktiv. Für Delphi gibt es vielleicht auch dafür Scriptkomponenten, weiß ich nicht.
Ansonsten muss man das Script in seine Einzelteile (Einzelanweisungen) zerlegen, wenn es mit Delphi Queries eingespielt werden soll.

Pragma Foreign Keys = Off scheint im Übrigen genau so ein Fall zu sein, wo ein Tool eine Defaulteinstellung einer DB nachahmt. Genau auf sowas bezog sich mein Beitrag, man sollte genau schauen, "wer" aus welchem Antrieb bestimmte Dinge im Datenmodell setzt.