Nehmen wir an, wir haben den Auftrag erhalten, eine kleine Datenbank zu erzeugen, welche Kunden und deren Bestelungen verwaltet.
Wir sitzen mit Stift und Block da und skizzieren mit dem Kunden vor Ort seine gewünschten Felder, die in der Datenbank verwaltet werden sollen.
Daraus ergibt sich, dass wir folgende Informationen in der Datenbank speichern sollen (stark vereinfacht):
Kundennummer, Kundenname, Bestellnummer, Bestelldatum.
Um jetzt eine übersichtliche Datenbank anzulegen, haben wir uns folgende zwei Tabellen skizziert:
Die KUNDEN-Tabelle und die BESTELLUNGEN-Tabelle.
—————-[KUNDEN]———————
KUNDENNUMMER |KUNDENNAME
100 |MAX MUSTERMANN
200 |THOMAS BAUER
300 |STEFAN LUDWIG
—————-[BESTELLUNGEN]—————
KUNDENNUMMER| BESTELLNUMMER |BESTELLDATUM
100 |100 |1.9.25
200 |105 |20.9.25
300 |107 |30.9.25
Was sind primary Keys, wozu brauche ich sie und wie lege ich sie an?
Um jede Zeile in einer Tabelle eindeutig identifizieren zu können, brauchen wir eine eindeutige Spalte, die niemals zwei gleiche Einträge besitzt. Schauen wir uns die Tabelle KUNDEN an, fällt uns auf, dass dies die Spalte KUNDENNUMMER sein könnte. Jetzt könnten wir die Tabellen einfach anlegen, dem Kunden sagen, stell sicher, dass du niemals zwei gleiche Kundennummern vergibst und dies würde eine Weile gut gehen, bis – warum auch immer – ein Eintrag in der Tabelle KUNDEN erzeugt wird, der eine dort schon vorhandene KUNDENNUMMER erneut verwendet. Wir hätten eine Inkonsistenz. Warum? Weil der Kunde z. B. gewohnt ist, in seiner Datenbank nach der KUNDENNUMMER zu suchen und erwartet, er erhalte immer nur einen Treffer. Diesmal würde er zwei erhalten. Wie lässt sich dies vermeiden?
Datenbanken bieten mit der Funktion PRIMARY KEYS die Möglichkeit, eine Spalte zu definieren, die eindeutig der Identifizierung dient. Die Datenbank verhindert dann, dass in dieser Spalte eine ID/Nummer/Zahl, mehr als einmal vorkommen darf. Zur Definition einer Spalte gibt es mehrere Möglichkeiten:
1, Wir nutzen eine vorhandene Spalte, die tatsächliche Daten des Kunden enthält, für die Identifizierung (hier: die Spalte KUNDENNUMMER).
Dies hat den Vorteil, dass die Tabelle nicht um eine weitere Spalte ergänzt werden muss. Anlegen würden wir die Tabelle mit folgendem Code:
CREATE TABLE KUNDEN (
Kundennummer INT PRIMARY KEY,
Kundenname VARCHAR(100)
);
Man beachte den Zusatz „PRIMARY KEY“.
Damit legen wir fest, dass die Spalte KUNDENNUMMER der PRIMARY KEY ist und eindeutig sein muss.
Heißt für uns aber, dass wir weiterhin beim Anlegen von Kunden uns eine Kundennummer ausdenken müssen und diese definieren.
Das führt dazu, dass wir ggf. mal unabsichtlich Lücke einbauen, uns vertippen usw. Dafür bietet sich die zweite und deutlich gängiere Methode an:
2, Wir lassen die Datenbank das Feld KUNDENNUMMER mit einer fortlaufenden ID befüllen und markieren ebenso diese Spalte als PRIMARY KEY.
CREATE TABLE KUNDEN (
Kundennummer INT PRIMARY KEY AUTO_INCREMENT,
Kundenname VARCHAR(100)
);
Nun noch eine Besonderheit: Wie eingangs erwähnt, dient die Funktion PRIMARY KEY dazu, eine Zeile in einer Datenbanktabelle eindeutig zu identifizieren, indem die Datenbank sicherstellt, dass keine neue Zeile in der Tabelle zulässig ist, die in der Spalte KUNDENNUMMER eine bereits vorhandene KUNDENNUMMER erneut verwendet. Voraussetzung dazu ist, dass wir in unserer Datenbank eine Spalte besitzen, die eindeutig ist. Die KUNDENNUMMER ist dafür geeignet.
Gibt es jedoch keine geeeignete Spalte oder möchte man sich nicht darauf festlegen, da sich ggf. irgendwann das Nummernschema bzw. der Nummernkreis ändert, oder man schlichtweg davon unabhängig sein sill, gibt es künstliche Primary Keys, die Surrogate Keys.
Surrogate Keys sind künstliche Schlüssel, die in der Regel als zusätzliche Spalte mit AUTO_INCREMENT hinzugefügt werden, wenn keine natürliche, eindeutige Spalte (wie die Kundennummer) existiert oder man diese nicht verwenden möchte. Sie dienen ausschließlich der eindeutigen Identifizierung der Zeile in der Tabelle, nicht der repräsentativen Bedeutung der Daten.
Der Surrogate-Key ist letztlich nur eine weitere Spalte in der Tabelle mit gleicher Funktion wie PRIMARY KEY mit dem Unterschied, dass er eben nur den Zweck hat, eine Zeile der Tabelle eindeutig zu identifizieren und nicht durch den Nutzer der Datenbank mit Werten befüllt wird.
Die obige Tabelle würden wir mit einem Surrogate-Key wie folgt erzeugen und die Tabelle würde dann so aussehen:
CREATE TABLE KUNDEN (
Kundennummer INT AUTO_INCREMENT,
Id INT AUTO_INCREMENT,
Kundenname VARCHAR(100)
PRIMARY KEY (Id)
);
Man beachte den Unterschied, wir legen die Spalte „Id“ mit AUTO_INCREMENT zum automatischen Hochzählen an, definieren aber erst am Ende der Anweisung, „PRIMARY KEY ….“, dass es sich hierbei um den künstlichen Schlüssel handelt, der sich wie ein Primary Key verhält.
—————-[KUNDEN]———————
ID |KUNDENNUMMER |KUNDENNAME
1 |100 |MAX MUSTERMANN
2 |200 |THOMAS BAUER
3 |300 |STEFAN LUDWIG
Surrogate Keys (künstliche Schlüssel) werden besonders dann verwendet, wenn es keine gute natürliche Schlüsselkombination gibt oder wenn man die Flexibilität benötigt, um in Zukunft das Nummernsystem zu ändern.
Surrogate Keys erleichtern die die Wartung, da sie nicht auf natürlichen Daten basieren, die sich möglicherweise ändern könnten.
Nun zur zweiten Frage, was ist der foreign Key und warum sollten wir ihn nutzen?
In unserer Datenbank arbeitet der Kunde bei SQL-Abfragen mit der Kundennummer. Z.B.
SELECT * FROM BESTELLUNGEN WHERE KUNDENNUMMER = 100;
Ohne die Funktion-Foreign-Keys, ist es möglich, dass Inkonsistenzen entstehen. Also z.B. nicht alle Kundennummern, auch in der Tabelle Bestellungen vorhanden sind.
Folgender Fehler bei der Bearbeitung der Tabelle könnte verursacht werden (ohne Foreign Keys):
—————-[BESTELLUNGEN]—————
KUNDENNUMMER| BESTELLNUMMER |BESTELLDATUM
100 |100 |1.9.25
200 |105 |20.9.25
300 |107 |30.9.25
301 |108 |01.10.25
Man beachte die letzte Zeile und prüfe oben die Einträge in unserer KUNDEN-Tabelle.
Korrekt, wir haben eine Bestellungen hinterlegt, die auf einen nicht existierenden Kunden zeigt. Die Kundennummer 301 ist nicht vergeben.
Immer wenn ein Zusammenspiel von mehreren Tabellen erfolgt, sorgt ein FOREIGN KEY dafür, dass die „Schlüssel“ – hier die KUNDENNUMMER – über alle beteiligten Tabellen, stimmig sind, also die Integrität gewahrt wird. Dies verhindert z.B. auch das versehentliche Löschen von bestimmten Einträgen, wenn dadurch die Integrität beeinträchtigt wird.
Ohne Foreign Key können wir eine Bestellung für eine nicht existierende KUNDENNUMMER (z.B. 301) hinzufügen, was zu Dateninkonsistenz führt. Mit einem Foreign Key wird dieses Szenario verhindert, da die Datenbank sicherstellt, dass jede Bestellnummer nur einer existierenden Kundennummer zugeordnet werden kann.
Es lässt sich auch problemlos ohne FOREIGN KEYS arbeiten, die Prüf- und Kontrollpflichten für die Bearbeitung von Daten, obligt dann aber dem Nutzer/Anwender/Programmierer. Deutlich sinnvoller ist es, diese Aufgabe aber der Datenbank zu übertragen. Weiterer sinnvoller Nebeneffekt ist, dass durch die Definition von FOREIGN KEYS, auch eine Art Dokumentation innerhalb der Datenbank besteht. Es also gleich erkennbar ist, welche Verbindungen einzelne Tabellenspalten zueinander haben. Ohne FOREIGN KEYS könnten wir in o.g. Tabelle z.B. in der Tabelle KUNDEN, Max Mustermann löschen. Mit FOREIGN KEYS nicht, da die DB erkennt, dass zu Max Mustermann, noch Bestellungen existieren.