Relationale Datenbanken und SQL
Tabellen, Schlüssel und referentielle Integrität
Ein normalisiertes Datenmodell wird in einer relationalen Datenbank als Menge von Tabellen umgesetzt. Jede Tabelle hat einen Primärschlüssel (PRIMARY KEY), der jede Zeile eindeutig identifiziert, und kann Fremdschlüssel (FOREIGN KEY) enthalten, die auf den Primärschlüssel einer anderen Tabelle verweisen. Die referentielle Integrität stellt sicher, dass ein Fremdschlüsselwert entweder NULL ist oder tatsächlich auf eine existierende Zeile in der referenzierten Tabelle zeigt — so können z. B. keine Bestellungen für nicht existierende Kunden gespeichert werden.
Datentypen
Die Wahl des passenden Datentyps beeinflusst Speicherplatz, Genauigkeit und zulässige Operationen:
| Datentyp | Verwendung |
|---|---|
| INTEGER | Ganzzahlen (z. B. ID, Stückzahl) |
| DECIMAL(p,s) | Exakte Beträge mit fester Anzahl Nachkommastellen (z. B. Geldbeträge) — im Gegensatz zu FLOAT keine Rundungsfehler |
| VARCHAR(n) | Zeichenketten variabler Länge |
| DATE | Datumswerte |
| BOOLEAN | Wahrheitswerte (true/false) |
SQL DDL (Data Definition Language)
Mit DDL-Befehlen wird die Struktur der Datenbank definiert:
CREATE TABLE— legt eine neue Tabelle mit Spalten, Datentypen und Constraints (PRIMARY KEY, FOREIGN KEY, NOT NULL) an.ALTER TABLE— ändert eine bestehende Tabellenstruktur (z. B. Spalte hinzufügen).DROP TABLE— entfernt eine Tabelle vollständig inklusive ihrer Daten.
SQL DML (Data Manipulation Language)
Mit DML-Befehlen werden Daten bearbeitet:
INSERT INTO— fügt neue Zeilen ein.UPDATE— verändert Werte in bestehenden Zeilen.DELETE FROM— entfernt Zeilen.SELECT— liest Daten aus, meist kombiniert mitWHERE(Filterbedingung),JOIN(Verknüpfung mehrerer Tabellen),GROUP BY(Gruppierung) undORDER BY(Sortierung).
Ein INNER JOIN liefert nur jene Zeilenkombinationen, für die in beiden verknüpften Tabellen ein passender Schlüsselwert existiert — Zeilen ohne Gegenstück (z. B. ein Fremdschlüsselwert ohne passenden Primärschlüssel) fallen weg.
Indizes
Ein Index ist eine zusätzliche Datenstruktur, die das Suchen nach Werten in einer Spalte massiv beschleunigt (ähnlich einem Stichwortverzeichnis in einem Buch), auf Kosten von zusätzlichem Speicherplatz und etwas langsameren Schreiboperationen (INSERT/UPDATE/DELETE müssen den Index mitpflegen). Primärschlüssel erhalten in der Regel automatisch einen Index.
Datentypen in PostgreSQL
- Ganzzahlen: smallint (2 Byte, -32768 bis +32767), integer (4 Byte, die übliche Wahl mit dem besten Verhältnis aus Bereich, Platz und Leistung) und bigint (8 Byte, wenn der Bereich von integer nicht reicht).
- numeric: Speichert Zahlen mit sehr vielen Ziffern und rechnet exakt (aber langsamer als Ganzzahlen); empfohlen für Geldbeträge. Die Genauigkeit (precision) ist die Gesamtzahl der signifikanten Ziffern, die Skala (scale) die Ziffern nach dem Komma. Die Zahl 23.5141 hat Genauigkeit 6 und Skala 4. Eine Spalte
numeric(3,1)speichert Werte von -99.9 bis 99.9 (zwei Stellen vor dem Komma) und rundet auf eine Nachkommastelle. Die grösste ausdrücklich angebbare Genauigkeit ist 1000; ohne Angaben ist die Spalte unbeschränkt. - real und double precision sind ungenau (variable Genauigkeit) und speichern manche Werte nur näherungsweise; Gleichheitsvergleiche damit können unerwartete Ergebnisse liefern.
- Zeichentypen:
char(n)füllt mit Leerzeichen auf. Es gibt keinen Geschwindigkeitsvorteil gegenübervarchar(n),char(n)ist wegen des Auffüllens meist sogar der langsamste der drei Zeichentypen.
Constraints
- NOT NULL ist funktional gleichwertig mit
CHECK (spalte IS NOT NULL), in PostgreSQL aber effizienter. - CHECK: Die Bedingung ist erfüllt, wenn der Ausdruck true oder NULL ergibt, denn die meisten Ausdrücke mit einem NULL-Operanden ergeben NULL. Wer NULL verhindern will, braucht zusätzlich NOT NULL. Eine CHECK-Bedingung darf mehrere Spalten derselben Zeile prüfen, aber keine Daten anderer Zeilen.
- UNIQUE: Legt automatisch einen eindeutigen B-Baum-Index an. Zwei NULL-Werte gelten standardmässig nicht als gleich, es sind also beliebig viele Zeilen mit NULL erlaubt (mit
NULLS NOT DISTINCTlässt sich das ändern; der SQL-Standard lässt das Standardverhalten offen). - Primärschlüssel: UNIQUE plus NOT NULL, höchstens einer pro Tabelle, kann aber aus mehreren Spalten bestehen.
- Integrität wird in der Datenbank durch Constraints sowie Primär- und Fremdschlüssel erzwungen, nicht durch die Disziplin der Anwender.
- ON DELETE CASCADE: Beim Löschen der referenzierten Zeile werden die verweisenden Zeilen mitgelöscht (zum Beispiel die 4 Positionen einer gelöschten Bestellung: aus 10 werden 6 Zeilen). Das passt für Bestandteile; bei eigenständigen Objekten ist RESTRICT besser. Weitere Regeln: siehe Datenmodellierung.
Abfragen im Detail
- Die Grundform lautet
SELECT <Spalten> FROM <Tabelle> WHERE <Bedingung>, zum BeispielSELECT name FROM kunde WHERE ort = 'Chur';. - DISTINCT direkt nach SELECT entfernt identische Ergebniszeilen; bei mehreren Spalten gilt erst die Kombination als doppelt. Wird zusätzlich eine Kennzahl je Gruppe gebraucht, nimmt man GROUP BY.
- GROUP BY und Aggregatfunktionen:
GROUP BY ortfasst Zeilen zu Gruppen zusammen,COUNT(*)zählt je Gruppe. - WHERE und HAVING: WHERE filtert Eingabezeilen vor dem Gruppieren, HAVING filtert die Gruppen danach. Eine Bedingung auf einen Gruppenwert («mehr als 20 Mitglieder je Ort») gehört daher in
HAVING COUNT(*) > 20. Eine Bedingung ohne Aggregat steht effizienter in WHERE. - Joins: Ein INNER JOIN liefert nur Kombinationen mit Treffer auf beiden Seiten (Beispiel: 4 Kunden mit je 3 Bestellungen ergeben 12 Zeilen). Ein LEFT (OUTER) JOIN behält jede Zeile der links genannten Tabelle mindestens einmal; fehlt rechts ein Partner, sind die Spalten der rechten Tabelle NULL. So erscheinen auch Abteilungen ohne Mitarbeiter. Ohne Verknüpfungsbedingung entsteht das kartesische Produkt (7 mal 5 Zeilen ergibt 35 Zeilen), ein Hinweis auf eine vergessene Bedingung.
- NULL: Ein Vergleich mit
= NULLergibt nie wahr; korrekt istIS NULLbzw.IS NOT NULL. - DELETE und UPDATE ohne WHERE betreffen jede Zeile; das System fragt nicht nach (aus 12 Zeilen werden 0).
DROP INDEXentfernt einen Index wieder.
Transaktionen
Eine Transaktion bündelt mehrere Schritte zu einer Alles-oder-nichts-Einheit: Entweder werden alle Schritte ausgeführt oder keiner wirkt sich auf die Datenbank aus (Beispiel: Abbuchung und Gutschrift einer Überweisung).
- Die Zwischenzustände sind für gleichzeitig laufende Transaktionen nicht sichtbar; erst nach dem Abschluss werden die Änderungen gemeinsam sichtbar.
- Eine abgeschlossene und bestätigte Transaktion ist dauerhaft gespeichert und geht auch bei einem Absturz kurz danach nicht verloren.
- In PostgreSQL wird eine Transaktion mit
BEGINundCOMMITumschlossen;ROLLBACKstatt COMMIT macht alle bisherigen Änderungen rückgängig (800 Franken, 150 abgebucht, Rollback: weiterhin 800). Jeder einzelne Befehl läuft ohne BEGIN in einer impliziten Transaktion. - Ein Savepoint markiert eine Stelle in der Transaktion.
ROLLBACK TOverwirft nur die späteren Schritte, frühere bleiben erhalten (1'000 Franken, 100 abgebucht, Savepoint, 300 abgebucht, Rollback zum Savepoint, Abschluss: 900 Franken). Der Savepoint bleibt danach definiert.
Indizes in PostgreSQL
CREATE INDEXerzeugt standardmässig einen B-Baum. Er eignet sich für Gleichheits- und Bereichsabfragen sowie sortierte Ausgaben und kann auch fürLIKEgenutzt werden, wenn das Muster konstant und am Anfang festgelegt ist (LIKE 'Mei%', nicht'%meier').- Ein Hash-Index speichert einen 32-Bit-Hashwert und eignet sich nur für einfache Gleichheitsvergleiche mit
=. - Das Erstellen eines Index auf einer grossen Tabelle kann lange dauern. Standardmässig sind Lesezugriffe parallel erlaubt, Schreibzugriffe werden blockiert;
CREATE INDEX CONCURRENTLYvermeidet das. - Indizes lassen sich jederzeit hinzufügen und entfernen. Selten genutzte Indizes sollten entfernt werden, da jeder Index bei Änderungen mitgepflegt werden muss.
Häufige Fehler
- FLOAT für Geldbeträge verwenden — führt zu Rundungsfehlern; DECIMAL ist hier korrekt.
- Referentielle Integrität mit Eindeutigkeit (UNIQUE) verwechseln — UNIQUE verhindert doppelte Werte in einer Spalte, referentielle Integrität sichert die Gültigkeit von Fremdschlüsselverweisen.
- Annehmen, Indizes seien immer kostenlos — sie beschleunigen Lesezugriffe, verlangsamen aber Schreiboperationen und benötigen Speicherplatz.