Fremdschlüsselbeziehungen erstellen und verwalten

Auf dieser Seite wird beschrieben, wie Sie Fremdschlüsselbeziehungen in Ihrer Datenbank verwalten.

Mit Fremdschlüsseln können Sie Beziehungen zwischen Tabellen definieren. Cloud Spanner sorgt dafür, dass die referenzielle Integrität zwischen diesen Tabellen gewahrt bleibt. Das folgende Diagramm zeigt ein einfaches Datenbankschema, das in dieser Anleitung verwendet wird.

Beispiel für ein Datenbankschema, das die Beziehung zwischen Tabellen mit Fremdschlüsseln zeigt.

Abbildung 1. Diagramm einer Auftragsverarbeitungsdatenbank

Das in Abbildung 1 gezeigte 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 Tabelle Customers 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 Tabelle Products stellt sicher, dass keine Bestellung für ein nicht vorhandenes Produkt erstellt werden kann.

Mit diesem Schema als Referenz betrachten wir die Datendefinitionssprache (DDL), mit denen Sie diese Einschränkungen in Ihrer Datenbank verwalten können.

Fremdschlüssel beim Erstellen einer neuen Tabelle hinzufügen

Angenommen, wir haben bereits die Tabelle Customers in unserer einfachen Produktbestellungsdatenbank erstellt. Wir benötigen die Tabelle Orders, um Informationen zu den Bestellungen von Kunden zu speichern. Um sicherzustellen, dass alle Bestellungen gültig sind, darf das System keine Zeilen in die Tabelle "Bestellungen" einfügen, die keinen übereinstimmenden Eintrag in der Tabelle Customers haben.

Hier ist die CREATE TABLE-DDL-Anweisung für die Orders-Tabelle, die die Fremdschlüsseleinschränkung enthält, die auf Tabelle Customers verweist.

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);

Die obige Anweisung enthält eine CONSTRAINT-Klausel, die wir wie folgt beschreiben können:

  • Mit der CONSTRAINT-Syntax können Sie eine Einschränkung benennen und so leichter den ausgewählten Namen verwenden.

  • Die Einschränkung hat den Namen FK_CustomerOrder. Einschränkungsnamen gelten für das Schema und müssen innerhalb des Schemas eindeutig sein.

  • Die Tabelle Orders, auf der die Einschränkung definiert wird, wird als Referenztabelle bezeichnet. Die Tabelle Customers ist die Tabelle, auf die verwiesen wird.

  • Die Referenzspalte in der Referenztabelle ist CustomerID. Sie verweist auf das Feld CustomerID in der Tabelle Customers. Wenn jemand versucht, eine Zeile in Orders mit einem CustomerID einzufügen, der in Customers nicht vorhanden ist, schlägt das Einfügen fehl.

Das folgende Snippet zeigt eine alternative Tabellenerstellungsanweisung. Hier wird die Fremdschlüsseleinschränkung ohne Namen definiert. Wenn Sie diese Syntax verwenden, generiert Cloud Spanner einen Namen für Sie. Informationen zum Ermitteln der Namen aller Fremdschlüssel finden Sie unter Eigenschaften einer Fremdschlüsselbeziehung ansehen.

CREATE TABLE Orders (
  OrderID INT64 NOT NULL,
  CustomerID INT64 NOT NULL,
  ProductID INT64 NOT NULL,
  FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
) PRIMARY KEY (OrderID);

Fremdschlüssel einer vorhandenen Tabelle hinzufügen

Außerdem sollen Aufträge nur für vorhandene Produkte erstellt werden können. Wir verwenden ALTER TABLE, um der Auftragstabelle eine weitere Fremdschlüsseleinschränkung so hinzuzufügen:

ALTER TABLE Orders
  ADD CONSTRAINT FK_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID);

Die Referenzspalte in Orders ist ProductID und verweist auf die Spalte ProductID in Produkten. Wenn Sie Cloud Spanner mit einem dieser Namen versehen haben, können Sie noch einmal die folgende Syntax verwenden:

ALTER TABLE Orders
  ADD FOREIGN KEY (ProductID) REFERENCES Products (ProductID);

Daten über Fremdschlüsselbeziehungen abfragen

SELECT * FROM Orders
  INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
  INNER JOIN Products ON Orders.ProductsID = Products.ProductID;

Beispiel für referenzielle Integrität

Der wichtigste Grund für das Hinzufügen Fremdschlüsselbeziehungen ist, dass Cloud Spanner die referenzielle Integrität Ihrer Daten aufrechterhalten kann. Wenn Sie Daten so ändern, dass eine Einschränkung für einen Fremdschlüssel aufgehoben wird, schlägt die Aktualisierung mit einem Fehler fehl.

Sehen Sie sich die Daten in Abbildung 2 unten an. Einige Kunden haben Produkte bestellt, wie in der Tabelle "Bestellungen" angegeben. Aufgrund der Fremdschlüssel können wir garantieren, dass die in die Tabelle Orders eingefügten Daten eine referenzielle Integrität haben.

Beispieldaten für die Tabellen

Abbildung 2. Beispieldaten in unserer Bestelldatenbank.

Sehen wir uns an, was passiert, wenn wir versuchen, die Daten so zu ändern, dass die referenzielle Integrität beeinträchtigt wird.

Fügen Sie der Tabelle Orders eine Zeile mit einem Wert CustomerID hinzu, der in Customers nicht vorhanden ist.

Was passiert, wenn wir anhand der Beispieldaten aus dem vorherigen Diagramm die folgende Änderung vorzunehmen versuchen?

INSERT INTO Orders (OrderID, ProductID, Quantity, CustomerID)
  VALUES (19, 337876, 4, 447);

In diesem Fall versuchen wir, eine Zeile in Orders mit einer CustomerID (447) einzufügen, die in der Tabelle Customers nicht vorhanden ist. Wenn wir dies zulassen würden, hätten wir in unserem System eine ungültige Bestellung. Die Fremdschlüsselbeschränkung, die wir von der Tabelle Orders bis zur Tabelle Customers definiert haben, schützt uns jedoch, und das INSERT schlägt mit der folgenden Meldung fehl, vorausgesetzt, die Beschränkung heißt FK_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 auf den Kunden in einer Fremdschlüsseleinschränkung verwiesen wird.

Stellen wir uns eine Situation vor, in der ein Kunde unseren Online-Shop abonniert. Wir möchten den Kunden aus unserem Back-End entfernen und führen den folgenden Vorgang aus.

DELETE FROM Customers WHERE CustomerID = 721;

In diesem Beispiel erkennt Cloud Spanner über die Einschränkung für Fremdschlüssel, dass in der Tabelle Orders weiterhin Datensätze vorhanden sind, die auf die zu löschende Kundenzeile verweisen. 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, würden wir zuerst alle Referenzeinträge in Orders löschen. Eine andere Lösung, die CustomerID als NULLABLE definiert hätte, wäre, das Feld CustomerID auf NULL zu setzen, um die Referenz zu entfernen. CASCADE DELETE wird für Fremdschlüssel nicht unterstützt.

Eigenschaften einer Fremdschlüsselbeziehung anzeigen

Das INFORMATION_SCHEMA von Cloud Spanner enthält Informationen zu Fremdschlüsseln und ihren Sicherungsindizes. 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?

Fremdschlüssel-Sicherungsindexe werden von Cloud Spanner verwaltet , sodass die Abfrage für SPANNER_IS_MANAGED in der INDEXES-Ansicht alle Sicherungsindexe zurückgibt.

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 is true;

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 wird Cloud Spanner durch die Fremdschlüsseldefinitionen mitgeteilt, dass jeder SongName-Wert in der TopHits-Tabelle einen übereinstimmenden Wert in der Songs-Tabelle haben muss. Jedes SingerFirstName- und SingerLastName-Wertepaar muss ein übereinstimmendes FirstName- und LastName-Wertepaar in der Tabelle "Sänger" enthalten.

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);

Zirkelverweise

Gelegentlich haben Tabellen Zirkelverweise, möglicherweise aus Legacy-Gründen oder aufgrund von Denormalisierung. Cloud Spanner-Fremdschlüssel lassen Zirkelverweise 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:

  1. Erstellen Sie TabelleA ohne einen Fremdschlüssel
  2. Erstellen Sie TabelleB mit einer Fremdschlüsseleinschränkung für TabelleA.
  3. 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.

CREATE TABLE Employees (
  EmployeeId INT64 NOT NULL,
  EmployeeName STRING(MAX) NOT NULL,
  ManagerId INT64,
  FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId)
) PRIMARY KEY (EmployeeId);

Nächste Schritte