Auf dieser Seite wird beschrieben, wie Sie Fremdschlüsselbeziehungen in Ihrer Datenbank verwalten.
Ein Fremdschlüssel ist eine Spalte, die tabellenübergreifend genutzt wird, um eine Verknüpfung zwischen verwandten Daten herzustellen. Wenn Sie einen Fremdschlüssel verwenden, sorgt Spanner dafür, dass diese Beziehung aufrechterhalten wird.
Das folgende Diagramm zeigt ein einfaches Datenbankschema, bei dem Daten in einer Tabelle eine Beziehung zu Daten in einer anderen Tabelle haben.
Abbildung 1. Diagramm einer Auftragsverarbeitungsdatenbank
Das in Abbildung 1 dargestellte Schema enthält drei Tabellen:
- In der Tabelle
Customers
werden die Namen jedes Kunden aufgezeichnet. - Die Tabellen
Orders
verfolgen alle Bestellungen. - In der Tabelle
Products
werden die Produktinformationen für jedes Produkt gespeichert.
Zwischen diesen Tabellen gibt es zwei Fremdschlüsselbeziehungen:
Zwischen der Tabelle
Orders
und der TabelleCustomers
wird eine Fremdschlüsselbeziehung definiert, um sicherzustellen, dass eine Bestellung nur dann angelegt werden kann, wenn eine entsprechender Kunde vorhanden ist.Eine Fremdschlüsselbeziehung zwischen der Tabelle
Orders
und der TabelleProducts
stellt sicher, dass keine Bestellung für ein nicht vorhandenes Produkt erstellt werden kann.
Unter Verwendung des vorherigen Schemas als Beispiel werden in diesem Thema die CONSTRAINT
-Anweisungen der Data Definition Language (DDL) erläutert, mit denen Sie Beziehungen zwischen Tabellen in einer Datenbank verwalten können.
Fremdschlüssel beim Erstellen einer neuen Tabelle hinzufügen
Angenommen, Sie haben die Tabelle Customers
in Ihrer einfachen Datenbank für die Produktbestellung erstellt. Sie benötigen jetzt eine Orders
-Tabelle, um Informationen zu den Bestellungen von Kunden zu speichern. Damit alle Bestellungen gültig sind, darf das System keine Zeilen in die Tabelle Orders
einfügen, es sei denn, es gibt auch einen übereinstimmenden Eintrag in der Tabelle Customers
. Daher benötigen Sie einen Fremdschlüssel, um eine Beziehung zwischen den beiden Tabellen herzustellen. Eine Möglichkeit besteht darin, der neuen Tabelle eine CustomerID
-Spalte hinzuzufügen und sie als Fremdschlüssel zu verwenden, um eine Beziehung zur Spalte CustomerID
in der Tabelle Customers
zu erstellen.
Wenn Sie eine neue Tabelle mit einem Fremdschlüssel erstellen, stellen Sie mit REFERENCE
eine Beziehung zu einer anderen Tabelle her. Die Tabelle, die die Anweisung REFERENCE
enthält, wird als Referenztabelle bezeichnet. Die in der REFERENCE
-Anweisung genannte Tabelle ist die referenzierte Tabelle. Die in der Anweisung REFERENCE
angegebene Spalte wird als Referenzspalte bezeichnet.
Das folgende Beispiel zeigt, wie Sie mit der DDL-Anweisung CREATE TABLE
die Tabelle Orders
mit einer Fremdschlüsseleinschränkung erstellen, die auf CustomerID
in der Tabelle Customers
verweist.
GoogleSQL
CREATE TABLE Orders (
OrderID INT64 NOT NULL,
CustomerID INT64 NOT NULL,
Quantity INT64 NOT NULL,
ProductID INT64 NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
) PRIMARY KEY (OrderID);
PostgreSQL
CREATE TABLE Orders (
OrderID BIGINT NOT NULL,
CustomerID BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
ProductID BIGINT NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
PRIMARY KEY (OrderID)
);
Die vorherige Anweisung enthält eine CONSTRAINT
-Klausel mit den folgenden Eigenschaften:
Verwendung der
CONSTRAINT
-Syntax zum Benennen einer Einschränkung, was das Löschen der Tabelle mit dem von Ihnen ausgewählten Namen vereinfacht.Die Einschränkung hat den Namen
FK_CustomerOrder
. Einschränkungsnamen sind auf das Schema beschränkt und müssen innerhalb des Schemas eindeutig sein.Die Tabelle
Orders
, für die Sie die Einschränkung definieren, ist die verweisende Tabelle. Die TabelleCustomers
ist die referenzierte Tabelle.Die Referenzspalte in der Referenztabelle ist
CustomerID
. Sie verweist auf das FeldCustomerID
in der TabelleCustomers
. Wenn jemand versucht, inOrders
eine Zeile mit einerCustomerID
einzufügen, die inCustomers
nicht vorhanden ist, schlägt das Einfügen fehl.
Das folgende Beispiel zeigt eine alternative Anweisung zur Tabellenerstellung. Hier wird die Fremdschlüsseleinschränkung ohne Namen definiert. Wenn Sie diese Syntax verwenden, generiert Spanner einen Namen für Sie. Informationen zum Ermitteln der Namen aller Fremdschlüssel finden Sie unter Eigenschaften einer Fremdschlüsselbeziehung ansehen.
GoogleSQL
CREATE TABLE Orders (
OrderID INT64 NOT NULL,
CustomerID INT64 NOT NULL,
ProductID INT64 NOT NULL,
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
) PRIMARY KEY (OrderID);
PostgreSQL
CREATE TABLE Orders (
OrderID BIGINT NOT NULL,
CustomerID BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
ProductID BIGINT NOT NULL,
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
PRIMARY KEY (OrderID)
);
Fremdschlüssel einer vorhandenen Tabelle hinzufügen
Sie möchten auch dafür sorgen, dass Kunden nur vorhandene Produkte bestellen können. Zuerst müssen Sie die vorhandene Einschränkung entfernen. Anschließend können Sie ALTER TABLE
verwenden, um der Tabelle Orders
eine weitere Fremdschlüsseleinschränkung hinzuzufügen, wie hier gezeigt:
ALTER TABLE Orders
ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID);
Die Referenzspalte in Orders
ist ProductID
und verweist auf die Spalte ProductID
in Produkten. Auch hier gilt: Wenn Sie mit der Benennung dieser Einschränkungen durch Spanner einverstanden sind, verwenden Sie die folgende Syntax:
ALTER TABLE Orders
ADD FOREIGN KEY (ProductID) REFERENCES Products (ProductID);
Beim Erstellen einer neuen Tabelle einen Fremdschlüssel mit einer Löschaktion hinzufügen
Erinnern Sie sich noch einmal an das vorherige Beispiel, in dem Sie eine Customers
-Tabelle in einer Produktsortierdatenbank haben, die eine Orders
-Tabelle benötigt. Sie möchten eine Fremdschlüsseleinschränkung hinzufügen, die auf die Tabelle Customers
verweist. Sie möchten jedoch dafür sorgen, dass beim Löschen eines Kundendatensatzes künftig auch alle Aufträge für diesen Kunden von Spanner gelöscht werden. In diesem Fall möchten Sie die Aktion ON DELETE CASCADE
mit der Fremdschlüsseleinschränkung verwenden.
Die folgende DDL-Anweisung CREATE TABLE
für die Tabelle Orders
enthält die Fremdschlüsseleinschränkung, die auf die Tabelle Customers
mit der Aktion ON DELETE
CASCADE
verweist.
GoogleSQL
CREATE TABLE Orders (
OrderID INT64 NOT NULL,
CustomerID INT64 NOT NULL,
Quantity INT64 NOT NULL,
ProductID INT64 NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID)
REFERENCES Customers (CustomerID) ON DELETE CASCADE
) PRIMARY KEY (OrderID);
PostgreSQL
CREATE TABLE Orders (
OrderID BIGINT NOT NULL,
CustomerID BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
ProductID BIGINT NOT NULL,
FOREIGN KEY (CustomerID)
REFERENCES Customers (CustomerID) ON DELETE CASCADE,
PRIMARY KEY (OrderID)
);
Die vorherige Anweisung enthält eine Fremdschlüsseleinschränkung mit einer ON DELETE CASCADE
-Klausel. Die Spalte CustomerID
ist ein Fremdschlüssel, der auf das Feld CustomerID
in der Tabelle Customers
verweist. Das bedeutet, dass jeder CustomerID
-Wert in der Tabelle Orders
auch in der Tabelle Customers
vorhanden sein muss. Wenn jemand versucht, eine Zeile aus der Tabelle Customers
zu löschen, werden alle Zeilen in der Tabelle Orders
, die auf den gelöschten Wert CustomerID
verweisen, in derselben Transaktion ebenfalls gelöscht.
Einer vorhandenen Tabelle einen Fremdschlüssel mit einer Löschaktion hinzufügen
Außerdem sollten Sie darauf achten, dass nur für vorhandene Produkte Bestellungen erstellt werden. Sie können ALTER TABLE
verwenden, um der Tabelle „orders“ eine weitere Fremdschlüsseleinschränkung mit der Aktion ON DELETE CASCADE
hinzuzufügen:
ALTER TABLE Orders
ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID)
REFERENCES Products (ProductID) ON DELETE CASCADE;
Wenn Sie eine Zeile aus der Tabelle Products
löschen, werden alle Zeilen in der Tabelle Orders
gelöscht, die auf den gelöschten Wert ProductID
verweisen.
Daten über Fremdschlüsselbeziehungen abfragen
SELECT * FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Products ON Orders.ProductsID = Products.ProductID;
Beispiele für die Beibehaltung der referenziellen Integrität durch Spanner
Der Hauptgrund für das Hinzufügen von Fremdschlüsselbeziehungen besteht darin, dass Spanner die referenzielle Integrität der Daten aufrechterhalten kann. Wenn Sie Daten so ändern, dass eine Fremdschlüsseleinschränkung nicht mehr funktioniert, schlägt die Aktualisierung mit einem Fehler fehl.
Betrachten Sie die Daten in Abbildung 2. Einige Kunden haben Produkte bestellt, wie in der Tabelle "Bestellungen" angegeben. Aufgrund der vorhandenen Fremdschlüssel können Sie garantieren, dass die in die Tabelle Orders
eingefügten Daten referenzielle Integrität haben.
Abbildung 2. Beispieldaten in unserer Bestelldatenbank.
Die folgenden Beispiele zeigen, was passiert, wenn Sie versuchen, die Daten so zu ändern, dass die referenzielle Integrität beschädigt wird.
Fügen Sie der Tabelle
Orders
eine Zeile mit einemCustomerID
-Wert hinzu, der inCustomers
nicht vorhanden ist.Was passiert, wenn Sie anhand der Beispieldaten aus dem vorherigen Diagramm versuchen, die folgende Änderung vorzunehmen?
INSERT INTO Orders (OrderID, ProductID, Quantity, CustomerID) VALUES (19, 337876, 4, 447);
In diesem Fall würde das System versuchen, in
Orders
eine Zeile mit einemCustomerID
(447) einzufügen, der in der TabelleCustomers
nicht vorhanden ist. Wenn das System dies tun würde, wäre eine ungültige Bestellung in Ihrem System vorhanden. Mit der Fremdschlüsseleinschränkung, die Sie der TabelleOrders
hinzugefügt haben, ist die Tabelle geschützt. DerINSERT
schlägt mit der folgenden Meldung fehl, vorausgesetzt, die Einschränkung heißtFK_CustomerOrder
.Foreign key constraint `FK_CustomerOrder` is violated on table `Orders`. Cannot find referenced values in Customers(CustomerID).
Versuchen Sie, eine Zeile aus der Tabelle
Customers
zu löschen, wenn in einer Fremdschlüsseleinschränkung auf den Kunden verwiesen wird.Stellen Sie sich eine Situation vor, in der ein Kunde sich von Ihrem Onlineshop abmeldet. Sie möchten den Kunden aus Ihrem Back-End entfernen, also führen Sie den folgenden Vorgang aus.
DELETE FROM Customers WHERE CustomerID = 721;
In diesem Beispiel erkennt Spanner über die Fremdschlüsseleinschränkung, dass in der Tabelle
Orders
noch Datensätze vorhanden sind, die auf die Kundenzeile verweisen, die Sie löschen möchten. In diesem Fall wird der folgende Fehler angezeigt.Foreign key constraint violation when deleting or updating referenced row(s): referencing row(s) found in table `Orders`.
Um dieses Problem zu beheben, löschen Sie zuerst alle verweisenden Einträge in
Orders
. Sie können den Fremdschlüssel auch mit der AktionON DELETE CASCADE
definieren, damit Spanner verweisende Einträge löschen kann.
Eigenschaften einer Fremdschlüsselbeziehung anzeigen
Das INFORMATION_SCHEMA von Spanner enthält Informationen zu Fremdschlüsseln und deren Sicherungsindexen. Im Folgenden finden Sie einige Beispiele für Fragen, die Sie durch Abfragen des INFORMATION SCHEMA beantworten können.
Weitere Informationen zum Sichern von Indizes finden Sie unter Sicherungsindex für Fremdschlüssel.
Welche Einschränkungen sind in meiner Datenbank definiert?
SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY';
Welche Fremdschlüssel sind in meiner Datenbank definiert?
SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.SPANNER_STATE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;
Welche Indexe sind sekundäre Indexe für Fremdschlüssel, die auch als Sicherungsindexe bezeichnet werden?
Sicherungsindexe für Fremdschlüssel werden von Spanner verwaltet. Bei der Abfrage von SPANNER_IS_MANAGED
in der Ansicht INDEXES
werden daher alle Sicherungsindexe zurückgegeben.
SELECT i.TABLE_NAME, i.INDEX_NAME, i.INDEX_TYPE, i.INDEX_STATE,
i.IS_UNIQUE, i.IS_NULL_FILTERED, i.SPANNER_IS_MANAGED
FROM INFORMATION_SCHEMA.INDEXES as i
WHERE SPANNER_IS_MANAGED = 'YES';
Welche referenzielle Aktion wird mit der Fremdschlüsseleinschränkung definiert?
SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.DELETE_RULE,
rc.UPDATE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;
Weitere Informationen finden Sie im Informationsschema.
Fremdschlüsselbeziehung entfernen
Die folgende DDL löscht eine Fremdschlüsseleinschränkung aus der Tabelle Orders
.
ALTER TABLE Orders
DROP CONSTRAINT FK_CustomerOrder;
Die Fremdschlüssel-Sicherungsindexe werden automatisch entfernt, wenn die Beschränkung selbst verworfen wird.
Unterstützung von komplexeren Fremdschlüsselbeziehungen
Mehrere Spalten
Fremdschlüssel können auf mehrere Spalten verweisen. Die Liste der Spalten bildet einen Schlüssel, der dem Primärschlüssel einer Tabelle oder einem Sicherungsindex entspricht. Die Referenztabelle enthält Fremdschlüssel des referenzierten Tabellenschlüssels.
Im folgenden Beispiel weisen die Fremdschlüsseldefinitionen Spanner an, dass jeder SongName
-Wert in der TopHits-Tabelle einen übereinstimmenden Wert in der Songs-Tabelle haben muss und jedes SingerFirstName
- und SingerLastName
-Wertepaar ein übereinstimmendes FirstName
- und LastName
-Wert-Paar in der Tabelle " einfache" haben muss.
GoogleSQL
CREATE TABLE TopHits (
Rank INT64 NOT NULL,
SongName STRING(MAX),
SingerFirstName STRING(MAX),
SingerLastName STRING(MAX),
-- Song names must either be NULL or have matching values in Songs.
FOREIGN KEY (SongName) REFERENCES Songs (SongName),
-- Singer names must either be NULL or have matching values in Singers.
FOREIGN KEY (SingerFirstName, SingerLastName)
REFERENCES Singers (FirstName, LastName)
) PRIMARY KEY (Rank);
PostgreSQL
CREATE TABLE TopHits (
Rank BIGINT NOT NULL,
SongName VARCHAR,
SingerFirstName VARCHAR,
SingerLastName VARCHAR,
-- Song names must either be NULL or have matching values in Songs.
FOREIGN KEY (SongName) REFERENCES Songs (SongName),
-- Singer names must either be NULL or have matching values in Singers.
FOREIGN KEY (SingerFirstName, SingerLastName)
REFERENCES Singers (FirstName, LastName),
PRIMARY KEY (Rank)
);
Zirkelverweise
Gelegentlich haben Tabellen Zirkelverweise, möglicherweise aus Legacy-Gründen oder aufgrund von Denormalisierung. Spanner-Fremdschlüssel lassen zirkuläre Verweise zu.
Da eine referenzierte Tabelle vorhanden sein muss, bevor ein Fremdschlüssel darauf verweisen kann, muss einer der Fremdschlüssel mit einer ALTER TABLE
-Anweisung hinzugefügt werden. Beispiel:
- Erstellen Sie TabelleA ohne einen Fremdschlüssel
- Erstellen Sie TabelleB mit einer Fremdschlüsseleinschränkung für TabelleA.
- Verwenden Sie
ALTER TABLE
in TabelleA, um einen Fremdschlüsselverweis auf TabelleB zu erstellen.
Auf sich selbst referenzierende Tabellen
Ein spezieller Typ von Zirkelverweis ist eine Tabelle, die einen Fremdschlüssel definiert, der auf dieselbe Tabelle verweist. Das folgende Snippet zeigt beispielsweise einen Fremdschlüssel, der erzwingt, dass die Manager-ID eines Mitarbeiters auch ein Mitarbeiter ist.
GoogleSQL
CREATE TABLE Employees (
EmployeeId INT64 NOT NULL,
EmployeeName STRING(MAX) NOT NULL,
ManagerId INT64,
FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId)
) PRIMARY KEY (EmployeeId);
PostgreSQL
CREATE TABLE Employees (
EmployeeId BIGINT NOT NULL,
EmployeeName VARCHAR NOT NULL,
ManagerId BIGINT,
FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId),
PRIMARY KEY (EmployeeId)
);
Nächste Schritte
Weitere Informationen zur Unterstützung von Fremdschlüsseln in Spanner
Weitere Informationen zum Informationsschema von Spanner.