Primärschlüssel und Fremdschlüssel
Kurz gesagt: Ein Primärschlüssel ist die Spalte, die jede Zeile einer Tabelle eindeutig bestimmt, und ein Fremdschlüssel ist eine Spalte, die den Primärschlüssel einer Zeile in einer anderen Tabelle enthält.
Problem
Wessen Bestellung ist das?
In der Lektion „Der Shop“ haben wir mit den Augen gesucht, wer Bestellung 60 aufgegeben hat und was darin war. Die Antwort war auf vier Tabellen verteilt: orders, customers, order_items und products.
Warum hält der Shop nicht alles in einer großen Tabelle? Und woher „weiß“ eine Tabelle, welche Zeile in einer anderen Tabelle zu ihr gehört?
Die Idee
Primärschlüssel
Fast jede Tabelle im Shop hat eine Spalte id. Die Ausnahme, order_items, steht in der Notiz am Ende der Lektion. Die Zahl ist für jede Zeile anders, wiederholt sich nie und ist nie leer. Anhand der Zahl lässt sich eine Zeile ohne jeden Zweifel finden:
Eine solche Spalte heißt Primärschlüssel. Namen reichen nicht: Zwei Kunden können denselben Namen haben, und ein Name kann sich ändern. Die Zahl bleibt.
SQLite sagt dir, welche Spalte der Primärschlüssel ist. Die Spalte pk ist für sie 1:
Die Idee
Fremdschlüssel
Eine Bestellung speichert nicht den Namen des Kunden. Sie behält nur seine id, in der Spalte customer_id:
customers
- PK
idinteger nametext *emailtext *citytextjoined_attext *
orders
- PK
idinteger - FK
customer_idinteger *→ customers ordered_attext *statustext *
orders.customer_id verweist auf customers.id. Eine Spalte, die den Primärschlüssel einer Zeile in einer anderen Tabelle enthält, heißt Fremdschlüssel.
Die Verweise einer Tabelle zeigt pragma_foreign_key_list:
Eine Zeile, ein Verweis: von der Spalte customer_id (from) zur Spalte id (to) der Tabelle customers.
Marias Angaben (Name, E-Mail) sind also einmal gespeichert. Wenn sie ihre E-Mail ändert, wird sie in einer Zeile von customers korrigiert, und ihre 12 Bestellungen verweisen weiter auf dieselbe id.
Probier es aus
Die Bestellpositionen
Auf welche Tabellen verweist order_items? Zeige seine Verweise.
Lösung
SELECT * FROM pragma_foreign_key_list('order_items');SELECT * FROM pragma_foreign_key_list('order_items'). Zwei Verweise: product_id verweist auf products.id und order_id auf orders.id. Eine Zeile von order_items sagt: „Diese Bestellung enthält dieses Produkt.“
Sieh es dir an
Nachschlagen nach Nummer
Mit WHERE suchen wir nicht mehr mit den Augen. Wer hat Bestellung 60 aufgegeben? Zuerst die Bestellung:
customer_id ist 1. Dann der Kunde mit dieser id:
Maria Ivanova. Es funktioniert, aber es braucht zwei Abfragen, und du trägst die Zahl 1 von Hand hinüber. Für eine Bestellung ist das erträglich. Für einen Bericht über 60 Bestellungen mit den Namen ihrer Kunden nicht. Die nächste Lektion fasst die beiden Schritte mit JOIN in einer Abfrage zusammen.
Probier es aus
Wessen Bewertung ist das?
Bewertung 13 hat die Note 1. Wer hat sie geschrieben? Zeige nur den Namen des Kunden.
Lösung
SELECT customer_id FROM reviews WHERE id = 13;
SELECT name FROM customers WHERE id = 12;Zwei Schritte: SELECT customer_id FROM reviews WHERE id = 13 ergibt 12, und SELECT name FROM customers WHERE id = 12 ergibt Boris Mitev. Die Kontrolle schaut auf die letzte Tabelle.
Übung
Zwei Aufgaben
1. Eine Bewertung verweist auf zwei Dinge. Zeige die Verweise der Tabelle reviews.
Die Verweise von reviews:
Lösung
SELECT * FROM pragma_foreign_key_list('reviews');SELECT * FROM pragma_foreign_key_list('reviews'). customer_id verweist auf customers und product_id auf products: wer sie geschrieben hat und über welches Produkt.
2. Die Kategorien bilden einen Baum: „Küche“ ist eine Unterkategorie von etwas. Der Fremdschlüssel parent_id verweist auf eine Zeile in derselben Tabelle categories. Finde den Namen der Elternkategorie von „Küche“.
Die Elternkategorie von „Küche“:
Lösung
SELECT parent_id FROM categories WHERE name = 'Küche';
SELECT name FROM categories WHERE id = 2;SELECT parent_id FROM categories WHERE name = 'Küche' ergibt 2, und SELECT name FROM categories WHERE id = 2 ergibt „Haushalt“. Eine Tabelle kann auf sich selbst verweisen. In der Lektion über viele Tabellen machst du das in einer Abfrage.
Genauer, für Neugierige
Ein Primärschlüssel kann aus mehreren Spalten bestehen. In order_items ist es das Paar (order_id, product_id): pk ist für seine Teile 1 und 2.
Ein Fremdschlüssel ist auch eine Regel: Die Datenbank lehnt eine Bestellung für einen Kunden ab, den es nicht gibt. In SQLite ist diese Regel standardmäßig aus und wird mit PRAGMA foreign_keys = ON eingeschaltet. Hier im Kurs ist sie an. In PostgreSQL werden Fremdschlüssel immer erzwungen. Im Modul über das Ändern von Daten siehst du sie bei der Arbeit.
Test