Gemeinsame Notizen
Quelle: https://pad.linuxhotel.de/p/SQL_Grundlagen_20200511
Quellen
Demo-Datenbanken:
https://github.com/catherinedevlin/opensourceshakespeare
https://github.com/GraemeHerrington/USDA-SR28-PostgreSQL
http://musicbrainz.org/doc/MusicBrainz_Database/Download
https://chinookdatabase.codeplex.com/releases/view/55681
http://www.dbis.informatik.uni-goettingen.de/Mondial/
Datenbank-Client
https://www.pgadmin.org/download/
https://www.heidisql.com/
https://dbeaver.io/
https://www.beekeeperstudio.io/
https://www.jetbrains.com/de-de/datagrip/
http://www.squirrelsql.org/
https://tableplus.com/
https://docs.microsoft.com/de-de/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver15
https://www.sequelpro.com/
https://popsql.com/
https://www.toadworld.com/products/toad-for-sql-server
Anmeldung an der DB
Hostname: sql.spielmannsolutions.com
Benutzername: user1, user2, user3, ...
Passwort: chaej0kooNae5LeengahTeej1, chaej0kooNae5LeengahTeej2, chaej0kooNae5LeengahTeej3, ...
Erste Abfrage
SELECT charname -- hole Spalte charname
FROM character; -- aus der Tabelle character
"Hole aus der Tabelle character die Spalte charname heraus und zeige sie mir als Tabelle."
Um alle Spalten einer Tabelle abzufragen, können wir den Spezialnamen * benutzen.
Beispiel:
SELECT *
FROM character;
Übung: Andere Spalten abfragen: charname, charid, abbrev, description, speechcount
Übung: mehrere Spalten gleichzeitig abfragen
Tabellen anlegen
Syntax:
CREATE TABLE <name> (
<spaltenname> <spaltentyp> <anmerkungen>,
....
);
Beispiel:
CREATE TABLE numbers (
number INTEGER PRIMARY KEY,
name VARCHAR(32)
);
Datentypen:
INTEGER: Ganzzahlen, positiv/negativ, Wertebereich
DECIMAL/NUMERIC(n, k): Festkommazahlen mit n Stellen insgesamt, davon k nach dem Komma
REAL, DOUBLE: Gleitkommazahlen, Vorsicht vor numerischen Problemen!
VARCHAR(n): Zeichenkette mit maximal n Zeichen
CHAR(n): Zeichenkette fester Länge
TEXT: Zeichenkette beliebiger Länge
DATE, TIME, DATETIME: Datumsangaben
BLOB: Binärdaten
https://www.postgresql.org/docs/9.5/datatype.html
https://dev.mysql.com/doc/refman/8.0/en/integer-types.html
Anmerkungen:
UNIQUE: jeder Wert in dieser Spalte muss einzigartig sein
NOT NULL: in dieser Spalte muss ein Wert vorhanden sein -- Vorsicht: wenn nicht NOT NULL angegeben ist, kann das Feld leer gelassen werden
PRIMARY KEY: hier erst einmal: UNIQUE und NOT NULL
Übung: Tabelle mit einer Spalte mit PRIMARY KEY ANLEGEN
Übung: Tabelle mit zwei Spalten anlegen
Übung: Tabelle mit n Spalten anlegen, davon eine PRIMARY KEY, eine NULL, eine NOT NULL, mit verschiedenen Datentypen (Tabelle (*))
Tabellen löschen
Syntax:
DROP TABLE <tabellenname>;
Vorsicht: SEHR DESTRUKTIV!
Daten einfügen
Syntax:
INSERT INTO <tabellenname>
VALUES
(<spalte1>, <spalte2>, <spalte3>, ...),
<zeile2>, ...;
INSERT INTO <tabellenname> (<spaltenname1>, <spaltenname2>, ...
VALUES
(<spalte1>, <spalte2>, ...),
<zeile2>, ...;
Eigentlich ist die Syntax mit VALUES ... "nur" ein Weg, eine Tabelle aufzuschreiben. Auch ein SELECT erzeugt eine Tabelle. Diese beiden Befehle kombinieren sich also:
INSERT INTO <tabellenname> (<spalten>, ...)
SELECT ...
Beispiele:
INSERT INTO numbers
VALUES
(6, 'sechs'),
(1, 'vier')
;
INSERT INTO numbers (number)
VALUES
(7),
(8)
;
CREATE TABLE numbers2 (
number INTEGER PRIMARY KEY,
name VARCHAR(32)
);
INSERT INTO numbers2
SELECT *
FROM numbers;
SELECT *
FROM numbers2;
Eigenschaften einer Datenbank
Eine SQL-Datenbank garantiert mir vier Eigenschaften: ACID
Atomarität: eine Abfrage wird entweder ganz ausgeführt oder gar nicht
Konsistenz: ich kann die (z.B. bei CREATE TABLE gesetzten) Eigenschaften einer DB nicht verletzen
Isolation: auch wenn mehrere Benutzer gleichzeitig auf einer DB arbeiten sind Teilzustände nicht vermischt
Dauerhaftigkeit: wenn eine Veränderung vorgenommen wurde, bleibt diese Dauerhaft im System
Abfragen mit Filtern, SELECT mit Bedingungen
Um in einer Abfrage nicht mehr ALLE Zeilen einer Tabelle zu holen, sondern nur "interessante" Zeilen, können wir Bedingungen an die Zeilen stellen. Im Ergebnis sind dann nur solche Zeilen aufgeführt, die die Bedingung erfüllen.
Beispiele:
SELECT *
FROM character
WHERE speechcount = 200
;
SELECT *
FROM character
WHERE speechcount > 200
;
SELECT *
FROM character
WHERE speechcount < 200
;
SELECT *
FROM character
WHERE speechcount <> 200
;
SELECT *
FROM character
WHERE speechcount != 200
;
SELECT *
FROM character
WHERE speechcount BETWEEN 5 AND 10
;
Übung: Selektieren des Datensatzes für Hamlet, Cleopatra, Romeo oder Julia (einzelne Abfragen)
Übung: Selektieren aller Charaktere mit genau 17 speeches.
Übung: Selektieren aller Charaktere mit 15 bis 20 speeches
Übung: Selektieren aller Charaktere, die eine Beschreibung (description) haben, d.h. wo die Beschreibung nicht NULL ist.
Lösungen: https://pad.linuxhotel.de/p/SQL_Grundlagen_20200511_SELECT_WHERE
NULL!?
Die letzte Aufgabe ging nicht:
SELECT *
FROM character
WHERE NULL = NULL
-- keine Zeilen
SELECT *
FROM character
WHERE NULL != NULL
-- auch keine Zeilen?!
Vergleiche gegen NULL liefern als Ergebnis immer NULL zurück. Da nur solche Zeilen im Ergebnis aufgeführt werden, die "true" enthalten, werden diese Zeilen nicht aufgeführt.
Wir brauchen eine spezielle Syntax:
SELECT *
FROM character
WHERE description IS NULL
SELECT *
FROM character
WHERE description IS NOT NULL
Löschen von Werten
Syntax:
DELETE FROM <tabelle> WHERE <bedingung>;
DELETE funktioniert im Grunde wie SELECT, nur dass die Zeilen nicht ausgegeben sondern gelöscht werden.
VORSICHT! DELETE FROM ist final, Wiederherstellen ist nicht möglich.
Wenn die Bedingung fehlt, werden ALLE ZEILEN GELÖSCHT!
Daher vorher immer SELECT * machen!
Beispiel:
SELECT *
FROM chars
WHERE description = '';
DELETE
FROM chars
WHERE description = '';
Daten bearbeiten
Syntax:
UPDATE <tabelle>
SET <spalte> = <wert>, ...
WHERE <bedingung>
Beispiel:
UPDATE chars
SET description = NULL
WHERE description = '';
Auch hier: VORSICHT MIT DER BEDINGUNG! Wenn die Bedingung fehlt, werden ALLE ZEILEN bearbeitet. Daher auch hier: vorher ein SELECT mit der entsprechenden Bedingung ausführen.
Hinweis: auch Tabellen können verändert werden. Das machen wir aber nur sehr selten, und deshalb hier nicht --> ALTER TABLE.
Aggregation
Oft interessieren uns nicht die Einzelwerte in einer Datenbank, sondern zusammengefasste Werte. Beispielsweise Zählungen, Summen, Min/Max, Durchschnitte, statistische Werte, ...
Beispiele:
SELECT COUNT(*)
FROM character
SELECT COUNT(description)
FROM character
SELECT SUM(speechcount)
FROM character
SELECT MAX(year) - MIN(year), MIN(year), MAX(year)
FROM work
Übung: Gesamtsumme aller speeches aus den Charakteren abfragen
Übung: Anzahl aller Werke
Übung: Minimum und Maximum der speeches
Übung: Die Anzahl der Werke vor 1600
Lösungen: https://pad.linuxhotel.de/p/SQL_Grundlagen_20200511_aggregation
Sortierung
Um die Ausgabe einer Abfrage zu sortieren benutzt man das magische Wort ORDER BY
Beispiel:
SELECT year, title
FROM work
ORDER BY year DESC, title
Es kann nach einer oder mehreren Spalten sortiert werden. Jede Spalte kann ASC (aufsteigend, Standard) oder DESC (absteigend) sein.
Übung: Alle Werke vor 1600 nach Jahr sortiert
Übung: Alle Werke nach 1605 nach Anzahl der Worte
Übung: Alle Werke nach Anzahl der Absätze
Lösungen: https://pad.linuxhotel.de/p/SQL_Grundlagen_20200511_order_by
Begrenzung mit LIMIT
Manchmal liefern Abfragen zu viele Daten, oder uns interessieren nur bestimmte Segmente einer Abfrage. Dann kann mit dem Schlüsselwort LIMIT die Ausgabe beschränkt werden. LIMIT wird einfach ans Ende einer Abfrage geschrieben.
Beispiel:
SELECT *
FROM character
ORDER BY speechcount DESC
LIMIT 5;
Übung: die fünf längsten Absätze
Übung: die fünf längsten Absätze aus Hamlet
Übung: die ersten drei Werke
Übung: die ersten drei Werke nach 1605
SELECT mit mehreren Bedingungen
Oft reicht eine einzelne Bedingung nicht aus, so dass mehrere Bedingungen angegeben werden sollen.
Beispiel:
SELECT *
FROM work
WHERE
(year < 1600) OR (totalwords > 25000)
SELECT *
FROM work
WHERE
(year < 1600) AND (totalwords > 25000)
Übung: alle Absätze aus Hamlet mit mehr als 1000 Zeichen
Übung: alle Absätze aus Macbeth mit mehr als 250 Wörtern
Übung: alle Werke zwischen 1600 und 1605
Lösungen: https://pad.linuxhotel.de/p/SQL_Grundlagen_20200511_andor
AND und OR zusammen
Die Reihenfolge und die Klammerung spielen bei `AND` und `OR` eine große Rolle. `AND` bindet stärker als als `OR`, so dass `AND`-Klauseln immer geklammert werden müssen. Um die Übersicht zu wahren, sollte aber in fast allen Fällen eine mehr-oder-weniger komplette Klammerung gemacht werden.
Beispiel:
SELECT * FROM work WHERE year < 1600 AND totalwords > 25000 OR totalwords < 10000; -- was bedeutet das?!
SELECT * FROM work WHERE (year < 1600 AND totalwords > 25000) OR totalwords < 10000;
SELECT * FROM work WHERE year < 1600 AND (totalwords > 25000 OR totalwords < 10000);
Übung: alle Absätze aus Hamlet mit mehr als 1000 Zeichen oder 300 Wörtern
Übung: alle Absätze aus Macbeth mit weniger als 100 Zeichen oder mehr als 250 Wörtern
Lösungen: https://pad.linuxhotel.de/p/SQL_Grundlagen_20200511_andor_andor
Textuelle Abfragen
Um in Text-Felder nach Mustern zu suchen, gibt es den LIKE-Operator. Dieser kann auf Text-Spalten angewendet werden, um nach einfachen Mustern zu suchen.
Bei der Suche wird _ durch ein einzelnes Zeichen und % durch null, eins oder mehrere Zeichen ersetzt. Im Suchmuster ist Groß- und Kleinschreibung wichtig!
Beispiele:
SELECT *
FROM work
WHERE title LIKE 'Henry V%II'
SELECT *
FROM work
WHERE title LIKE '%e'
SELECT *
FROM work
WHERE title LIKE '%Part%'
SELECT *
FROM work
WHERE title LIKE 'Henry __, Part I'
In Postgres kann Groß- und Kleinschreibung durch den Operator ILIKE ignoriert werden:
SELECT *
FROM work
WHERE title ILIKE 'henry%'
Übung: alle Absätze aus Macbeth, die das Wort love enthalten
Übung: alle Absätze aus Macbeth, die das Wort love enthalten und von einem Charakter mit 'mac' im Namen gesprochen werden
Übung: was ist wichtiger in Hamlet: Liebe oder Tod?
Lösungen: https://pad.linuxhotel.de/p/SQL_Grundlagen_20200511_LIKE
Aggregation und Gruppierung
Wenn wir eine Aggregation (z.B. mit count) vornehmen, so wird immer alles in einer Aggregation zusammengefasst. Die Aggregation kann dabei mit WHERE-Klauseln eingeschränkt werden, aber immer nur einmal pro Abfrage.
Beispiel:
SELECT count(*) FROM work WHERE year = 1600;
Oft interessieren uns aber mehrere Aggregationen hintereinander, für Daten mit einem bestimmten Merkmal.
Beispiel:
SELECT year, count(*) FROM work;
Hierzu gibt es die Markierung GROUP BY.
Beispiel:
SELECT year, count(*)
FROM work
GROUP BY year
ORDER BY year;
SELECT year, count(*), sum(totalwords)
FROM work
GROUP BY year
ORDER BY sum(totalwords);
---------------- Dienstag ----------------
Eine neue Datenbank
Datenbank: usda
Eine Datenbank des US Department of Agriculture mit Informationen über Lebensmittel und deren Inhaltsstoffe.
-- Tabelle mit Inhaltsangaben (ndb_no und nutr_no)
SELECT *
FROM nut_data nd
LIMIT 10
-- Tabelle mit Lebensmitteln und ihrer ndb_no
SELECT *
FROM food_des fd
LIMIT 100
-- Tabelle mit Inhaltsstoffen und ihrer nutr_no
SELECT *
FROM nutr_def nd
LIMIT 100
Problem: Interessante Sachen sind in verschiedenen Tabellen.
Lösung:
Eine Abfrage aus mehreren Tabellen.
SELECT food_des.ndb_no, long_desc, nutr_val, units, nutrdesc, nut_data.nutr_no
FROM food_des, nut_data, nutr_def
WHERE
food_des.ndb_no = nut_data.ndb_no
AND nut_data.nutr_no = nutr_def.nutr_no
AND nutr_val > 0
Anmerkungen:
Um eine Spalte zu verwenden, muss sie eindeutig definiert sein, entweder indem der Name nur einmal vorkommt, oder durch den "kompletten" Namen, also <tabelle>.<spaltenname>
Die Tabellennamen sind doof und ich will die nicht immer eingeben müssen. Deshalb kann man "Aliase" vergeben, die Tabellen also innerhalb der Abfrage (und nur für diese Abfrage) umbenennen.
-- die Tabelle food_des wird umbenannt in fd durch AS
SELECT *
FROM food_des AS fd
LIMIT 100
-- gleicher Effekt
SELECT *
FROM food_des fd
LIMIT 100
Wenn ich eine Tabelle umbenannt habe, steht der ursprüngliche Name NICHT MEHR zur Verfügung.
Frage: Aus welcher Tabelle kommen diese Daten denn jetzt genau?
Beispiel:
CREATE TABLE numbers (
id INTEGER PRIMARY KEY,
value NUMERIC(8, 4) NOT NULL
);
CREATE TABLE names (
id INTEGER,
name TEXT NOT NULL
);
INSERT INTO numbers
VALUES
(1, 1.4142),
(2, 3.1415),
(3, 10.3);
INSERT INTO names
VALUES
(1, 'Wurzel aus zwei'),
(2, 'π'),
(7, 'tau') -- gesetzliche Vorschrift: tau hat immer ID 7
;
SELECT *
FROM names;
SELECT *
FROM numbers;
SELECT *
FROM numbers, names;
WHERE numbers.id = names.id;
CROSS JOIN
Wenn in SQL zwei Tabellen verbunden werden, heißt das JOIN. Dieser spezifische JOIN heißt CROSS JOIN (Kreuzprodukt, kartesisches Produkt) und enthält alle Kombinationen aller Zeilen beider Tabellen.
Der CROSS JOIN ist genau das gleiche wie ein Komma.
Beispiel:
SELECT *
FROM (numbers CROSS JOIN names)
WHERE numbers.id = names.id;
genau identisch mit:
SELECT *
FROM numbers, names
WHERE numbers.id = names.id;
Vorsicht: Anzahl der Zeilen ist das Produkt aus den Teil-Anzahlen.
SELECT count(*)
FROM
food_des
CROSS JOIN nut_data
CROSS JOIN nutr_def
Anzahl wäre 246.681.349.200 = 7146 * 136 * 253825
Die Abfrage dauert zu lange zum Ausführen. Durch das Einführen einer WHERE-Bedingung wird die Anzahl der Zeilen aber wieder reduziert und so nutzbar gemacht.
Beispiel:
SELECT food_des.ndb_no, long_desc, nutr_val, units, nutrdesc, nut_data.nutr_no
FROM
food_des
CROSS JOIN nut_data
CROSS JOIN nutr_def
WHERE
food_des.ndb_no = nut_data.ndb_no
AND nut_data.nutr_no = nutr_def.nutr_no
AND nutr_val > 0
LIMIT 10;
Übung: alle Zahlen (mit Namen) kleiner als 3
Übung: Gewichtstabelle für alle Lebensmittel, die 'flour' im Namen enthalten
Übung: Inhaltsstoffe deines Lieblingsessens
Lösungen: https://pad.linuxhotel.de/p/SQL_Grundlagen_20200511_CROSS_JOIN
Probleme:
1. Die meisten Zeilen enthalten nur Unsinn.
2. Zeilen, die keine Entsprechung auf der anderen Seite haben, fehlen im Ergebnis.
INNER JOIN
Der INNER JOIN verbindet zwei Tabellen mit einer Bedingung, so dass die Zeilen der einen Tabelle zu den Zeilen der anderen Tabelle gehören. Die Bedingung gehört nun zum JOIN dazu, so dass es nicht möglich ist, "zu viele" Zeilen zu erzeugen.
Was passiert genau: Zu den Zeilen aus einer Tabelle werden die Zeilen einer anderen Tabelle hinzugefügt, und daraus entsteht eine neue Tabelle, die die Spalten beider Ursprungstabellen enthält.
Beispiel:
SELECT *
FROM
numbers
INNER JOIN names ON numbers.id = names.id;
Beispiel:
SELECT *
FROM
(food_des
INNER JOIN nut_data ON food_des.ndb_no = nut_data.ndb_no) AS food_nut
INNER JOIN nutr_def ON food_nut.nutr_no = nutr_def.nutr_no
LIMIT 10
Erklärung: im ersten Schritt wird aus zwei Tabellen (food_des und nut_data) eine neue Tabelle food_nut gemacht, die die Spalten beider vorherigen Tabellen enthält. Die Zeilen dieser neuen Tabelle entstehen aus den Zeilen der beiden anderen Tabellen und zwar so, dass die Bedingung erfüllt ist.
Vorsicht: die neue Tabelle enthält alle Spalten der vorherigen Tabellen, auch wenn diese den gleichen Namen haben.
Beispiel:
SELECT
nut_data.ndb_no, food_des.ndb_no, -- gleicher Name UND gleiche Werte
long_desc, nutr_val, units, nutrdesc, nut_data.nutr_no
FROM
food_des
INNER JOIN nut_data ON food_des.ndb_no = nut_data.ndb_no
INNER JOIN nutr_def ON nut_data.nutr_no = nutr_def.nutr_no
WHERE
nutr_val > 0
AND nut_data.ndb_no = '21120'
LIMIT 10
Übungen: wie oben, aber mit INNER JOIN
INNER JOIN mit USING
Oben wurde die Spalte ndb_no "dupliziert", also nebeneinander gelegt mit identischen Werten. Diese JOIN-Bedingung kann mittels USING ausgedrückt werden, und dabei werden die Spalten "übereinander gelegt", so dass im Ergebnis nur noch eine Spalte ndb_no vorhanden ist.
Beispiel:
SELECT ndb_no, long_desc, nutr_val, units, nutrdesc, nutr_no
FROM
food_des
INNER JOIN nut_data USING (ndb_no)
INNER JOIN nutr_def USING (nutr_no)
WHERE
nutr_val > 0
AND ndb_no = '01007'
LIMIT 10;
SELECT *
FROM
(numbers
INNER JOIN names USING (id));
Wichtig: Die JOIN-Bedingung ist jetzt immer ein =, die Werte müssen also exakt übereinstimmen. Und natürlich müssen die Namen übereinstimmen.
Übung: Finde das Lebensmittel, das am meisten Koffein enthält.
Lösung: https://pad.linuxhotel.de/p/SQL_Grundlagen_20200511_INNER_JOIN_USING
OUTER JOIN
Um die Zeilen zu finden, die keine Entsprechung beim JOIN haben, brauchen wir einen anderen JOIN-Typen, da INNER JOIN nur solche Zeilen ausgibt, die kombiniert werden können. Dieser JOIN heißt OUTER JOIN. Es gibt den LEFT OUTER JOIN, den RIGHT OUTER JOIN und den FULL JOIN.
Beispiel:
SELECT *
FROM
numbers LEFT OUTER JOIN names
USING (id);
SELECT *
FROM
numbers RIGHT OUTER JOIN names
USING (id);
SELECT *
FROM
numbers FULL OUTER JOIN names
USING (id);
Ein LEFT OUTER JOIN holt zu allen Zeilen der "linken" Tabelle die Zeilen der rechten Tabelle, die passen -- auch wenn es keine Entsprechung gibt. Ein LEFT OUTER JOIN enthält immer alle Zeilen der linken Tabelle. Wenn keine Entsprechung vorliegt, bleiben die nicht-kombinierten Zellen leer, also NULL.
Entsprechend RIGHT OUTER JOIN nur mit verdrehter Reihenfolge.
Beim FULL OUTER JOIN sind alle Zeilen beider Tabellen im Ergebnis, und falls möglich korrekt verknüpft.
Die JOIN-Bedingung kann ein ON oder ein USING sein, wie beim INNER JOIN.
Kleine Visualisierung: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
Übung: Gibt es Zahlen, die keinen Namen haben? Gibt es Namen, die keine Zahl haben? In einer Antwort, bitte!
Übung: Gibt es ein Lebensmittel, von dem wir gar nichts wissen?
Lösungen: https://pad.linuxhotel.de/p/SQL_Grundlagen_20200511_OUTER_JOIN
Selbstverbund, Self-join
Eine Tabelle kann in einem Join mehrmals vorkommen, insbesondere kann sie auch mit sich selbst gejoint werden.
CREATE TABLE employee (
id INTEGER PRIMARY KEY,
name VARCHAR(200),
rolle VARCHAR(100),
manager INTEGER
);
INSERT INTO employee
VALUES
(1, 'Ingo Wichmann', 'Geschäftsführer Linuxhotel', NULL),
(2, 'Johannes', 'Trainer', 1),
(3, 'Alexander', 'Teilnehmer', 2),
(4, 'Patrick', 'Teilnehmer', 2),
(5, 'Sandra', 'Teilnehmer', 2);
SELECT employee.name AS name, boss.name AS boss
FROM
employee
INNER JOIN employee AS boss ON employee.manager = boss.id
WHERE employee.id = 2
Übung: alle Teilnehmer
Übung: alle Teilnehmer unter Johannes
Übung: alle Trainer unter Ingo
Übung: alle Angestellten ohne Manager, und deren Untertanen
Lösungen: https://pad.linuxhotel.de/p/SQL_Grundlagen_20200511_SELFJOIN
PRIMARY KEYS und FOREIGN KEYS
Primärschlüssel und Fremdschlüssel
Primary Keys sind Spalten einer Tabelle, die einen Datensatz eindeutig identifizieren. Dezu gehört, dass sie UNIQUE und NOT NULL sind (wie bereits gesehen) und dass sie einen Index haben. Aber dazu gehört eben auch die Bedeutungsebene, dass ein Datensatz eindeutig durch den PK identifiziert werden.
PKs sollten abstrakt und möglichst kurz sein, damit Duplikation und Änderungen vermieden werden.
PKs können/sollten genutzt werden, um in JOINs die "richtigen" Datensätze zu verbinden. Wir haben das immer so gemacht.
Ein Foreign Key ist ein Verweis von einer Tabelle auf eine andere. Ein Feld mit FK kann nur dann befüllt werden, wenn das Ziel existiert. Ein Feld, auf das durch einen Foreign Key verwiesen wird, darf sich nicht in einen ungültigen Zustand verändern.
Wenn sich das Ziel eines FKs verändert (DELETE, UPDATE), gibt es verschiedene Möglichkeiten, wie die Datenbank die Konsistenz erhalten kann:
NO ACTION - die Veränderung wird verboten
CASCADE - die Veränderung wird übernommen, was sich durch die gesamte DB ziehen kann
SET NULL - der Verweis wird auf NULL gesetzt
SET DEFAULT - der Verweis wird auf einen Standardwert gesetzt
Ein Foreign Key der nicht auf NOT NULL gesetzt ist, darf mit NULL befüllt werden, dann existiert an dieser Stelle kein Verweis.
Beispiel:
CREATE TABLE numbers2
(
id INTEGER PRIMARY KEY,
value numeric(8, 4) NOT NULL
);
CREATE TABLE names2
(
id INTEGER REFERENCES numbers2(id),
name TEXT NOT NULL
);
Übung: Finde die Arbeiten, die sich mit deinem Lieblingslebensmittel befassen
Übung (PRO): Welches Lebensmittel ist am besten untersucht?
Übung (PRO+): Finde alle Arbeiten, die auf ein Nahrungsmittel deiner Wahl verweisen und finde zu jeder Arbeit die Anzahl der untersuchten Nahrungsmittel.
Lösungen: https://pad.linuxhotel.de/p/SQL_Grundlagen_20200511_PKFK