Was ist der Vorteil von Fremdschlüsseln in SQL?

Was ist ein Fremdschlüssel und warum ist er in relationalen Datenbanken so wichtig? Erfahren Sie in diesem Artikel alle Einzelheiten über Fremdschlüssel.

Eines der wichtigsten Merkmale relationaler Datenbanken ist die Möglichkeit, in verschiedenen Tabellen gespeicherte Daten zu verknüpfen. Diese Verknüpfungen, die so genannten Referenzen, fungieren im Wesentlichen als Verbindungen zwischen Tabellen. Sie werden mit Hilfe der FOREIGN KEY Einschränkung für Tabellenspalten erstellt.

In diesem Artikel erfahren Sie, was die Einschränkung FOREIGN KEY in SQL bewirkt. Ich werde erklären, wie man sie mit der Anweisung CREATE TABLE definiert, und wir werden einige Beispiele durchgehen. Als Nächstes werden wir über seine Vorteile und Funktionen sprechen. Im Einzelnen:

• Wir lernen die Beziehung zwischen der Primärtabelle (die der Fremdtabelle die Werte ihrer Primärschlüsselspalte(n) zur Verfügung stellt) und der Fremdtabelle (die die von der Primärtabelle bereitgestellte(n) Spalte(n) als ihren Fremdschlüssel verwendet) kennen.
• Wir werden besprechen, was passiert, wenn die Spaltenwerte der Primärtabelle gelöscht oder geändert werden. Es gibt verschiedene Möglichkeiten, die die FOREIGN KEY Einschränkung in solchen Situationen bietet - ich werde sie anhand einiger Beispiele erläutern.
• Zum Schluss werden wir die Kardinalitätsoptionen besprechen, die mit dem Constraint implementiert werden können. FOREIGN KEY

Fangen wir an.

Was ist eine Fremdschlüssel-Beschränkung in SQL?

Um das Konzept der FOREIGN KEY Einschränkung in SQL zu verstehen, können Sie es sich als eine Referenzverbindung zwischen Tabellen vorstellen, die als primäre (oder übergeordnete) und fremde (oder untergeordnete) Tabellen bezeichnet werden. Die Fremdtabelle verweist auf eine oder mehrere Spalten (den Primärschlüssel, der aus einer oder mehreren Spalten bestehen kann) in der Primärtabelle; auf diese Weise wird die Verbindung hergestellt. Wenn Sie Ihr Wissen über Primärschlüssel in SQL auffrischen möchten, empfehle ich Ihnen, den Artikel Was ist ein Primärschlüssel? zu lesen.

Ein Beispiel in Bildern

Ich glaube, dass einige visuelle Hilfsmittel hier hilfreich sein könnten. Schauen wir uns die folgenden Bilder an.

Hier haben wir die Airplane Tabelle (die primäre Tabelle) und die Flight Tabelle (die Fremdtabelle). Die Spalte AirplaneId, die eine Primärschlüsselspalte für die Tabelle Airplane ist, wird als Fremdschlüsselspalte in der Tabelle Flight Tabelle verwendet. Auf diese Weise wird die Beziehung zwischen diesen Tabellen hergestellt - die Spalte AirplaneId der Tabelle Flight Tabelle definiert, welches Flugzeug für jeden Flug verwendet wird.

Bitte beachten Sie, dass die Spalte AirplaneId der Fremdtabelle Flight nicht alle Werte enthalten muss, die in der Tabelle Airplane.AirplaneId gespeichert sind. Sie kann eine Teilmenge dieser Werte enthalten:

Die in der Spalte Flight.AirplaneId enthaltenen Werte verweisen direkt auf die in der Spalte Airplane.AirplaneId enthaltenen Werte. Daher müssen alle Aktualisierungs- oder Löschvorgänge an den Zeilen in der Primärtabelle Airplane müssen daher von der Fremdtabelle entsprechend berücksichtigt werden Flight. Die FOREIGN KEY Einschränkung bietet verschiedene Möglichkeiten, diese Aktualisierungen oder Löschungen zu implementieren, die später noch besprochen werden.

Und noch eine wichtige Sache. Das obige Beispiel zeigt eine Beziehung zwischen der Airplane und Flight Tabellen mit nur einer Spalte. Es ist auch möglich, mehrere Spalten zu verwenden, um solche Beziehungen zwischen Tabellen zu erstellen.

Hier haben wir uns entschieden, statt der Spalte AirplaneId für die Einschränkung FOREIGN KEY die Spalten AirplaneBrand und AirplaneModel zu verwenden, da sie auch jede Zeile der Tabelle Airplane Tabelle eindeutig identifizieren (unter der Annahme, dass unsere Fluggesellschaft nur ein Flugzeug jedes in der Tabelle aufgeführten Marken/Modellpaares besitzt).

Sie werden feststellen, dass wir alle Daten auch in eine Tabelle namens AirplaneFlight. Das ist richtig, aber dann könnten wir die Daten nicht in verschiedene Kategorien innerhalb einer Tabelle aufteilen. Das ist der Grund, warum Fremdschlüssel ein so wichtiger Teil des Datenbankdesigns sind. Sie ermöglichen es uns, zusammengehörige Daten in mehreren Tabellen zu platzieren und sie dann miteinander zu verknüpfen, um ihre Integrität zu wahren.

Wie man die Fremdschlüssel-Beschränkung in SQL definiert

Nun wollen wir unseren Entwurf in die Datenbank bringen. Dazu verwenden wir die Anweisung CREATE TABLE und definieren darin die Einschränkung FOREIGN KEY.

Schauen wir uns an, wie die Anweisung CREATE TABLE aussieht, wenn wir die Einschränkung FOREIGN KEY anhand des obigen Beispiels definieren, das die Spalte AirplaneId verwendet.

Zuerst müssen wir die Primärtabelle erstellen und ihre Primärschlüsselspalte definieren.

CREATE TABLE Airplane (
AirplaneId VARCHAR(10) NOT NULL,
AirplaneBrand VARCHAR(30) NOT NULL,
AirplaneModel VARCHAR(30) NOT NULL,
CONSTRAINT PK_AirplaneId PRIMARY KEY (AirplaneId)
);

Um die PRIMARY KEY Einschränkung zu überprüfen, lesen Sie Was ist ein Primärschlüssel in SQL?

Als nächstes erstellen wir die Fremdtabelle:

CREATE TABLE Flight (
FlightId VARCHAR(10) NOT NULL,
AirplaneId VARCHAR(10) NOT NULL,
PilotId INTEGER NOT NULL,
CONSTRAINT PK_FlightId PRIMARY KEY (FlightId),
CONSTRAINT FK_AirplaneId FOREIGN KEY (AirplaneId)
REFERENCES Airplane(AirplaneId)
);

Hier haben wir neben der Einschränkung PRIMARY KEY auch die Einschränkung FOREIGN KEY definiert. Wir haben ihr den Namen FK_AirplaneId gegeben. In Klammern haben wir die Spalte der Tabelle Flight Tabelle definiert, die die Einschränkung FOREIGN KEY implementiert. Nach dem Schlüsselwort REFERENCES folgen der Name der Primärtabelle und eine Spalte.

Auf diese Weise können wir unser Datenbankentwurfskonzept in eine funktionierende Datenbank umsetzen.

Es kann vorkommen, dass wir die Einschränkung FOREIGN KEY zu der Tabelle hinzufügen wollen, nachdem die Tabelle erstellt wurde. Flight Tabelle hinzufügen, nachdem die Tabelle erstellt wurde. Dazu verwenden wir die Anweisung ALTER TABLE:

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId);

Möglicherweise möchten wir auch eine FOREIGN KEY Einschränkung aufheben. Angenommen, wir wollen die oben definierte Einschränkung FOREIGN KEY in ihre mehrspaltige Version ändern. Wir müssen sie zuerst aufheben und dann den neuen Schlüssel erstellen:

ALTER TABLE Flight DROP FOREIGN KEY FK_AirplaneId;
ALTER TABLE Flight ADD FOREIGN KEY FK_Airplane (AirplaneBrand, AirplaneModel)
REFERENCES Airplane(AirplaneBrand, AirplaneModel);

Jetzt verwendetdie Tabelle<strong>Flight die mehrspaltige Einschränkung FOREIGN KEY .

Wenn Sie das Gefühl haben, dass Sie mehr Übung im Erstellen von Datenbanktabellen brauchen, sehen Sie sich unseren Kurs über die Grundlagen der Tabellenerstellung in SQL an.

Was sind die Vorteile von Fremdschlüsseln?

Nachdem wir nun alle Grundlagen der FOREIGN KEY Einschränkung, ihre Eigenschaften und die Möglichkeiten, die sie für die Datenbankfunktionalität bietet, kennengelernt haben, können wir uns nun näher mit ihren Vorteilen befassen.

Wie ich bereits erwähnt habe, können wir bei der Implementierung einer FOREIGN KEY Einschränkung zwischen der Primärtabelle und der Fremdtabelle unterscheiden.

Die Primärtabelle liefert eine Spalte oder einen Satz von Spalten, die von der Fremdtabelle verwendet werden. Mit anderen Worten: Die Spalte (oder der Satz von Spalten) der Fremdtabelle verweist auf eine Spalte (oder einen Satz von Spalten) in der Primärtabelle.

Der erste Vorteil der FOREIGN KEY -Beschränkung besteht also darin, dass sie die Existenz der referenzierten Zeile in der Primärtabelle sicherstellt. Wenn die referenzierte Zeile nicht in der primären Tabelle vorhanden ist, kann sie auch nicht in der fremden Tabelle vorhanden sein.

Ein weiterer Vorteil der FOREIGN KEY (FK)-Beschränkung ist, dass sie die Korrektheit des Verweises sicherstellt, selbst wenn die Werte der Primärtabelle geändert oder gelöscht werden. Es gibt eine Reihe von Aktionen, die angeben, was bei der Aktualisierung oder Löschung einer Primärschlüsselzeile (PK) zu tun ist. Schauen wir uns diese Aktionen einmal an.

Wenn eine Primärschlüsselzeile gelöscht wird

Es gibt eine Reihe von Optionen, die von der FOREIGN KEY Einschränkung angeboten werden, was bei der Löschung von Primärschlüsselzeilen aus der Primärtabelle zu tun ist.

Standardmäßig, wenn keine Aktion für die FOREIGN KEY Einschränkung angegeben ist, können Sie die betreffende Zeile in der primären (übergeordneten) Tabelle nicht löschen, wenn es Zeilen gibt, die auf sie in der fremden (untergeordneten) Tabelle verweisen.

Sie können die Aktion angeben, die beim Löschen der PK-Zeile(n) der Primärtabelle ausgeführt werden soll, indem Sie eine der folgenden Möglichkeiten wählen:

• ON DELETE CASCADE
• ON DELETE RESTRICT
• ON DELETE NO ACTION
• ON DELETE SET NULL
• ON DELETE SET DEFAULT

Gehen wir jede einzelne davon durch. Zuerst sehen wir uns die Syntax an, die alle diese Optionen gemeinsam haben.

ON DELETE-Syntax

Die Deklaration der Option ON DELETE folgt direkt auf die Deklaration der Einschränkung FOREIGN KEY.

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON DELETE ;

Schauen wir uns nun alle Optionen an.

ON DELETE KASKADE

Die Option ON DELETE CASCADE stellt sicher, dass, wenn eine Zeile aus der PK der Primärtabelle entfernt wird, auch die Zeile aus der Fremdtabelle, die auf sie verweist, entfernt wird.

Schauen wir uns das in Aktion an, indem wir unsere Airplane und Flight Tabellen. Zuerst haben wir die beiden Bezugstabellen:

Wir entfernen eine Zeile aus der Primärtabelle:

Und nun wird die Zeile, die auf die gelöschte Zeile verweist, aus der Fremdtabelle gelöscht.

Um die Option ON DELETE CASCADE zu implementieren, fügen Sie sie nach der FOREIGN KEY-Beschränkungserklärung hinzu:

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON DELETE CASCADE;

Sie sollten daran denken, die Option ON DELETE CASCADE mit der Einschränkung FOREIGN KEY zu implementieren, wenn Sie dies wünschen:

• Die Möglichkeit, Zeilen aus der Primärtabelle ohne Einschränkungen zu entfernen.
• Automatisches Löschen der Zeilen in der Fremdtabelle, wenn die Referenzzeile aus der Primärtabelle gelöscht wird.

BEIM LÖSCHEN EINSCHRÄNKEN

Die Option ON DELETE RESTRICT lässt das Löschen von Primärschlüsselzeilen in der Primärtabelle nicht zu. Wenn eine solche Aktion versucht wird, wird ein Fehler zurückgegeben.

Dies ist nützlich, um unerwünschte Änderungen an den Primär- und Fremdtabellen zu verhindern.

ON DELETE KEINE AKTION

Die Option ON DELETE NO ACTION ähnelt der Option ON DELETE RESTRICT; sie verhindert ebenfalls das Löschen der Zeilen der Primärtabelle und gibt einen Fehler aus, wenn eine solche Aktion versucht wird.

Der Unterschied zwischen ON DELETE NO ACTION und ON DELETE RESTRICT besteht darin, dass ON DELETE NO ACTION in einigen Datenbanken aufgeschoben werden kann, d.h. es kann bis nach der Transaktion verschoben werden, so dass der eventuelle Fehler nach Abschluss der Transaktion ausgelöst wird - d.h. im Laufe der Transaktion kann der Wert des Fremdschlüssels festgelegt werden, andernfalls wird der Fehler ausgelöst.

ON DELETE SET NULL

Die Option ON DELETE SET NULL tut genau das, was sie sagt. Wenn eine Zeile aus der Primärtabelle gelöscht wird, werden die Spaltenwerte, die auf sie in der Fremdtabelle verweisen, auf NULL gesetzt.

Schauen wir uns ein Beispiel unten an. Hier sind wieder die beiden Tabellen.

Wir entfernen eine Zeile aus der Primärtabelle.

Nun wird der Spaltenwert AirplaneId der Zeile, die auf die gelöschte Zeile verweist, in der Fremdtabelle auf NULL gesetzt.

Die Option ON DELETE SET NULL kann verwendet werden, wenn Sie die Löschung einer Zeile in der Primärtabelle zulassen möchten, aber nicht wollen, dass sie in die Fremdtabelle übertragen wird. Es gibt einen Fußabdruck der Löschung in der Fremdtabelle (der mit NULL gekennzeichnete Wert).

ON DELETE SET DEFAULT

Die Option ON DELETE SET DEFAULT ist der Option ON DELETE SET NULL sehr ähnlich. Hier wird nach dem Löschen der Zeile in der Primärtabelle der Wert der Spalte FK der Fremdtabelle auf den Standardwert anstelle von NULL gesetzt.

Bitte beachten Sie, dass ein Standardwert für die Fremdschlüsselspalte(n) angegeben werden muss.

Legen wir einen Standardwert für die Spalte AirplaneId der Tabelle Flight Tabelle:

ALTER TABLE Flight
ALTER AirplaneId
SET DEFAULT 'ABA340';

Jetzt können wir die Option ON DELETE SET DEFAULT verwenden. Nach dem Löschen der Zeile der Primärtabelle wird Folgendes passieren:

Sie sollten die Option ON DELETE SET DEFAULT verwenden, wenn Sie das Löschen von Zeilen in der Primärtabelle erlauben wollen. Der Grad der Kontrolle besteht hier darin, dass Sie wählen können, welcher Wert beim Löschen einer Zeile in der Primärtabelle in die Fremdtabelle eingefügt wird.

Wenn eine Primärschlüsselzeile aktualisiert wird

Die Aktionen, die die FOREIGN KEY -Beschränkung bei der Aktualisierung in der Primärtabelle bietet, ähneln den Aktionen beim Löschen. Das gilt auch für die Syntax.

ON UPDATE-Syntax

Die Deklaration der Option ON UPDATE folgt direkt auf die Deklaration der Einschränkung FOREIGN KEY.

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON UPDATE ;

Wenn keine Aktion für die Einschränkung FOREIGN KEY angegeben ist, ist es standardmäßig nicht möglich, die Zeilen der Primärtabelle (Elterntabelle) zu ändern, wenn es in der Fremdtabelle (Kindtabelle) Zeilen gibt, die darauf verweisen.

Sie können festlegen, was bei der Aktualisierung der Zeile(n) der Primärtabelle geschieht, indem Sie eine der folgenden Möglichkeiten wählen:

• ON UPDATE CASCADE
• ON UPDATE RESTRICT
• ON UPDATE NO ACTION
• ON UPDATE SET NULL
• ON UPDATE SET DEFAULT

Lassen Sie uns jede davon durchgehen.

BEI AKTUALISIERUNGSKASKADE

Mit der Option ON UPDATE CASCADE können Sie die Werte in der/den PK-Spalte(n) der Primärtabelle ändern. Diese Änderungen werden dann an die Fremdtabelle weitergegeben.

Schauen wir uns das in Aktion an, indem wir unsere Airplane und Flight Tabellen:

Wir ändern die Werte in der letzten Zeile der Primärtabelle.

Nun wird auch die Zeile, die darauf verweist, aktualisiert.

Um die Option ON UPDATE CASCADE zu implementieren, fügen Sie sie nach der FOREIGN KEY Einschränkungsdeklaration hinzu:

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON UPDATE CASCADE;

Diese Option erlaubt die Änderung der Primärschlüsselspalte(n) der Primärtabelle. Alle Änderungen werden dann an den/die Fremdschlüssel weitergegeben.

ON UPDATE RESTRICT

Wenn Sie jegliche Änderungen am PK der Primärtabelle verhindern wollen, verwenden Sie die Option ON UPDATE RESTRICT.

Bei jedem Versuch, die PK-Werte der Primärtabelle zu ändern, wird ein Fehler zurückgegeben.

ON UPDATE NO ACTION

Die Option ON UPDATE NO ACTION ist ähnlich wie die Option ON UPDATE RESTRICT. Es sind keine Änderungen an den PK-Werten der Primärtabelle erlaubt.

Der Unterschied besteht darin, dass einige Datenbanken es erlauben, die Überprüfung der Aktionen bis nach der Transaktion mit ON UPDATE NO ACTION zu verschieben. Wenn die PK-Werte der Primärtabelle nach Abschluss der Transaktion geändert werden, wird ein Fehler ausgegeben.

ON UPDATE SET NULL

Die Option ON UPDATE SET NULL erlaubt Änderungen an der PK der Primärtabelle. Bei solchen Aktualisierungen werden die Werte in den FK der Fremdtabelle auf NULL gesetzt.

Schauen wir uns ein Beispiel an und beginnen mit den beiden Tabellen:

Wir ändern die Werte in der letzten Zeile der Primärtabelle.

Und nun wird die Zeile, die darauf verweist, auf NULL gesetzt.

BEI AKTUALISIERUNG STANDARDWERT SETZEN

Die Option ON UPDATE SET DEFAULT erlaubt Änderungen an der PK der Primärtabelle. Infolgedessen werden die Werte im Fremdschlüssel auf ihren Standardwert gesetzt. Sie können diese Option nur verwenden, wenn ein Standardwert für die Fremdschlüsselspalte(n) deklariert ist.

Legen wir den Standardwert für die Spalte AirplaneId der Tabelle Flight Tabelle:

ALTER TABLE Flight
ALTER AirplaneId
SET DEFAULT 'ABA340';

Jetzt können wir die Option ON UPDATE SET DEFAULT verwenden. Nach der Aktualisierung der primären Tabellenzeile geschieht Folgendes:

Fremdschlüssel-Beschränkung Kardinalität

Eine weitere Funktion der Einschränkung FOREIGN KEY ist ihre Kardinalität. Durch die Einstellung der Kardinalität können wir entscheiden, wie sich die Fremdschlüsselspalte(n) auf die Primärschlüsselspalte(n) beziehen. Beziehungen können sein:

• Eins-zu-eins (ein PK-Zeilenwert kann in genau einer FK-Zeile erscheinen).
• Viele-zu-Eins (ein PK-Zeilenwert kann in einer oder mehreren FK-Zeilen erscheinen).
• Many-to-many (viele PK-Zeilenwerte können in vielen FK-Zeilen vorkommen).

Um die Eins-zu-Eins-Beziehung zu implementieren, sollten wir die Fremdschlüsselspalte(n) als UNIQUE deklarieren. In diesem Fall ist die Beziehung tatsächlich (eins oder null) -zu-eins, aber es wird sichergestellt, dass es höchstens eine Zeile in der Fremdtabelle gibt, die sich auf eine bestimmte Zeile der Primärtabelle bezieht.

Wenn hingegen die Fremdschlüsselspalte(n) der Fremdtabelle nicht als UNIQUE deklariert ist/sind, dann ist die Beziehung zwischen der/den Fremdschlüsselspalte(n) und der/den Primärschlüsselspalte(n) viele-zu-eins - viele Zeilen der Fremdtabelle können sich auf eine einzige Zeile der Primärtabelle beziehen.

Die Viele-zu-Viele-Beziehung wird mit einer separaten Tabelle implementiert, in der jedes Paar von Bezugszeilen gespeichert wird.

Beachten Sie, dass bei der Erstellung dieser Many-to-many-Beziehung die direkte Verbindung zwischen den Tabellen Airplane und Flight in eine separate Tabelle umgewandelt, die alle Beziehungen in AirplaneId-FlightId Paaren speichert.

Die Tabelle AirplaneFlight Tabelle hat einen Fremdschlüssel (AirplaneId), der sich auf die Spalte AirplaneId der Tabelle Airplane Tabelle verweist. Sie hat auch einen Fremdschlüssel (FlightId), der sich auf die Spalte FlightId der Tabelle Flight Tabelle verweist. Die Airplane und Flight sind nun indirekt miteinander verknüpft.

Möchten Sie mehr über Foreign Keys in SQL erfahren?

Die FOREIGN KEY -Beschränkung ist für das Design relationaler Datenbanken von entscheidender Bedeutung. Sie ermöglicht es uns, die Daten entsprechend unseren Anforderungen zu verknüpfen. Da sie einige Abhängigkeiten zwischen den Spalten von Primär- und Fremdtabellen schafft, können wir auch entscheiden, was ON UPDATE und ON DELETE mit den Zeilen der Primärtabelle machen sollen.

Durch die Verwendung der Einschränkung FOREIGN KEY können wir die Integrität, Korrektheit und Kompaktheit der Daten sicherstellen. Die von der Fremdtabelle verwendeten Werte müssen in der Primärtabelle vorhanden sein. Außerdem können wir Regeln für die Art der Beziehung aufstellen, d. h. eins-zu-eins, viele-zu-eins oder viele-zu-viele.

Wenn Sie noch mehr über Fremdschlüssel erfahren möchten, lesen Sie unseren Artikel Was ist ein Fremdschlüssel in SQL?

Wenn Sie mehr über die Struktur und das Design von Datenbanken erfahren möchten, empfehle ich Ihnen unseren Track über die Erstellung von Datenbankstrukturen, in dem alle wesentlichen Aspekte behandelt werden. Sie können diesen Track hier nachlesen, bevor Sie sich damit befassen.