Fremdschlüssel

In diesem Dokument werden Fremdschlüssel in Spanner beschrieben und wie Sie damit die referenzielle Integrität in Ihrer Datenbank erzwingen können. In den folgenden Themen erfahren Sie mehr über Fremdschlüssel und deren Verwendung:

• Übersicht über Fremdschlüssel in Spanner
• Arten von Fremdschlüsseln
• Vergleich der Fremdschlüsseltypen
• Fremdschlüsseltyp auswählen
• Erzwungene Fremdschlüssel verwenden
• Informative Fremdschlüssel verwenden
• Sicherungsindexe
• Lang andauernde Schemaänderungen

Übersicht über Fremdschlüssel in Spanner

Fremdschlüssel definieren Beziehungen zwischen Tabellen. Mit Fremdschlüsseln können Sie dafür sorgen, dass die Datenintegrität dieser Beziehungen in Spanner erhalten bleibt.

Stellen Sie sich vor, Sie sind leitender Entwickler für ein E-Commerce-Unternehmen. Sie entwerfen eine Datenbank zur Verarbeitung von Kundenbestellungen. Die Datenbank muss Informationen zu jeder Bestellung, jedem Kunden und jedem Produkt speichern. Abbildung 1 zeigt die grundlegende Datenbankstruktur für die Anwendung.

Abbildung 1. Diagramm einer Auftragsverarbeitungsdatenbank

Sie definieren eine Customers-Tabelle zum Speichern von Kundendaten, eine Orders-Tabelle zum Erfassen aller Bestellungen und eine Products-Tabelle zum Speichern von Informationen zu jedem Produkt.

Abbildung 1 zeigt auch Links zwischen den Tabellen, die den folgenden realen Beziehungen entsprechen:

• Ein Kunde gibt eine Bestellung auf.

• Ein Produkt wird bestellt.

Sie entscheiden, dass Ihre Datenbank die folgenden Regeln erzwingt, um sicherzustellen, dass Bestellungen in Ihrem System gültig sind.

• Sie können keinen Auftrag für einen Kunden erstellen, der nicht existiert.

• Ein Kunde kann keine Bestellung für ein Produkt aufgeben, das Sie nicht führen.

Wenn Sie diese Regeln oder Einschränkungen erzwingen, bleibt die referenzielle Integrität Ihrer Daten erhalten. Wenn eine Datenbank die referenzielle Integrität aufrechterhält, schlagen alle Versuche fehl, ungültige Daten hinzuzufügen, die zu ungültigen Links oder Verweisen zwischen Daten führen würden. Die referenzielle Integrität verhindert Nutzerfehler. Standardmäßig verwendet Spanner Fremdschlüssel, um die referenzielle Integrität zu erzwingen.

Referenzielle Integrität mit Fremdschlüsseln definieren

Im Folgenden wird das Beispiel für die Auftragsverarbeitung noch einmal genauer betrachtet. Das Design wurde um weitere Details ergänzt, wie in Abbildung 2 dargestellt.

Abbildung 2. Diagramm eines Datenbankschemas mit Fremdschlüsseln

Im Design werden jetzt in jeder Tabelle Spaltennamen und -typen angezeigt. In der Tabelle Orders sind auch zwei Fremdschlüsselbeziehungen definiert. FK_CustomerOrder erwartet, dass alle Zeilen in Orders einen gültigen CustomerId haben. Der Fremdschlüssel FK_ProductOrder erwartet, dass alle ProductId-Werte in der Tabelle Orders gültig sind. In der folgenden Tabelle werden diese Einschränkungen den realen Regeln zugeordnet, die Sie erzwingen möchten.

Name des Fremdschlüssels	Einschränkung	Beschreibung aus der Praxis
FK_CustomerOrder	Erwartet, dass alle Zeilen in Orders einen gültigen CustomerId haben	Ein gültiger Kunde gibt eine Bestellung auf.
FK_ProductOrder	Erwartet, dass alle Zeilen in Orders einen gültigen ProductId haben	Es wurde eine Bestellung für ein gültiges Produkt aufgegeben

Spanner erzwingt Einschränkungen, die mit erzwungenen Fremdschlüsseln angegeben werden. Das bedeutet, dass Spanner jede Transaktion ablehnt, die versucht, eine Zeile in die Tabelle Orders einzufügen oder zu aktualisieren, die eine CustomerId oder ProductId enthält, die nicht in den Tabellen Customers und Products vorhanden ist. Außerdem schlagen Transaktionen fehl, die versuchen, Zeilen in den Tabellen Customers und Products zu aktualisieren oder zu löschen, wodurch die IDs in der Tabelle Orders ungültig werden würden. Weitere Informationen dazu, wie Spanner Einschränkungen validiert, finden Sie im Abschnitt Validierung von Transaktionseinschränkungen.

Im Gegensatz zu erzwungenen Fremdschlüsseln validiert Spanner keine Einschränkungen für informative Fremdschlüssel. Wenn Sie in diesem Szenario einen informativen Fremdschlüssel verwenden, wird eine Transaktion, die versucht, eine Zeile in die Tabelle Orders einzufügen oder zu aktualisieren, die eine CustomerId oder ProductId enthält, die nicht in den Tabellen Customers und Products gefunden wird, nicht validiert und die Transaktion schlägt nicht fehl. Im Gegensatz zu erzwungenen Fremdschlüsseln werden informative Fremdschlüssel nur von GoogleSQL und nicht von PostgreSQL unterstützt.

Merkmale von Fremdschlüsseln

Im Folgenden finden Sie eine Liste der Merkmale von Fremdschlüsseln in Spanner.

• Die Tabelle, die den Fremdschlüssel definiert, ist die referenzierende Tabelle und die Fremdschlüsselspalten sind die referenzierenden Spalten.

• Der Fremdschlüssel verweist auf die referenzierten Spalten der referenzierten Tabelle.

• Wie im Beispiel können Sie jede Fremdschlüsselbeschränkung benennen. Wenn Sie keinen Namen angeben, generiert Cloud Spanner einen Namen für Sie. Sie können den generierten Namen aus der INFORMATION_SCHEMA von Spanner abfragen. Einschränkungsnamen sind zusammen mit den Namen für Tabellen und Indexe auf das Schema beschränkt und müssen innerhalb des Schemas eindeutig sein.

• Die Anzahl der verweisenden und referenzierten Spalten muss identisch sein. Die Reihenfolge ist wichtig. Die erste verweisende Spalte verweist beispielsweise auf die erste referenzierte Spalte und die zweite verweisende Spalte auf die zweite referenzierte Spalte.

• Eine verweisende Spalte und ihr referenziertes Gegenstück müssen vom selben Typ sein. Sie müssen die Spalten indexieren können.

• Sie können keine Fremdschlüssel für Spalten mit der Option allow_commit_timestamp=true erstellen.

• Array-Spalten werden nicht unterstützt.

• JSON-Spalten werden nicht unterstützt.

• Ein Fremdschlüssel kann auf Spalten derselben Tabelle verweisen (ein selbstreferenzierender Fremdschlüssel). Ein Beispiel ist eine Employee-Tabelle mit einer ManagerId-Spalte, die auf die EmployeeId-Spalte der Tabelle verweist.

• Fremdschlüssel können auch zirkuläre Beziehungen zwischen Tabellen bilden, bei denen zwei Tabellen direkt oder indirekt aufeinander verweisen. Die referenzierte Tabelle muss vorhanden sein, bevor ein Fremdschlüssel erstellt werden kann. Das bedeutet, dass mindestens einer der Fremdschlüssel mit der ALTER TABLE-Anweisung hinzugefügt werden muss.

• Die referenzierten Schlüssel müssen eindeutig sein. Spanner verwendet den PRIMARY KEY der referenzierten Tabelle, wenn die referenzierten Spalten für einen Fremdschlüssel mit den Primärschlüsselspalten der referenzierten Tabelle übereinstimmen. Wenn Spanner den Primärschlüssel der referenzierten Tabelle nicht verwenden kann, wird ein UNIQUE NULL_FILTERED INDEX für die referenzierten Spalten erstellt.

• Fremdschlüssel verwenden keine von Ihnen erstellten sekundären Indexe. Stattdessen erstellen sie ihre eigenen Sicherungsindexe. Sicherungsindexe können in Abfrageauswertungen verwendet werden, einschließlich in expliziten force_index-Anweisungen. Sie können die Namen der Sicherungsindexe aus der INFORMATION_SCHEMA von Spanner abfragen. Weitere Informationen finden Sie unter Sicherungsindexe.

Arten von Fremdschlüsseln

Es gibt zwei Arten von Fremdschlüsseln: erzwungene und informative. Erzwungene Fremdschlüssel sind die Standardeinstellung und erzwingen die referenzielle Integrität. Informative Fremdschlüssel erzwingen keine referenzielle Integrität und eignen sich am besten, um das beabsichtigte logische Datenmodell für die Abfrageoptimierung zu deklarieren. Weitere Informationen finden Sie in den folgenden Abschnitten zu erzwungenen und informativen Fremdschlüsseln sowie in der Tabelle Vergleich von Fremdschlüsseltypen.

Erzwungene Fremdschlüssel

Erzwungene Fremdschlüssel, der Standardfremdschlüsseltyp in Spanner, erzwingen die referenzielle Integrität. Da erzwungene Fremdschlüssel die referenzielle Integrität erzwingen, schlagen Versuche fehl, Folgendes zu tun:

• Das Hinzufügen einer Zeile zu einer verweisenden Tabelle, die einen Fremdschlüsselwert hat, der in der referenzierten Tabelle nicht vorhanden ist, schlägt fehl.

• Das Löschen einer Zeile aus einer referenzierten Tabelle, auf die von Zeilen in der verweisenden Tabelle verwiesen wird, schlägt fehl.

Alle PostgreSQL-Fremdschlüssel werden erzwungen. GoogleSQL-Fremdschlüssel werden standardmäßig erzwungen. Da Fremdschlüssel standardmäßig erzwungen werden, ist die Verwendung des Schlüsselworts ENFORCED, um anzugeben, dass ein GoogleSQL-Fremdschlüssel erzwungen wird, optional.

Informative Fremdschlüssel

Informative Fremdschlüssel werden verwendet, um das beabsichtigte logische Datenmodell für die Abfrageoptimierung zu deklarieren. Die referenzierten Tabellenschlüssel müssen für informative Fremdschlüssel eindeutig sein, die referenzielle Integrität wird jedoch nicht erzwungen. Wenn Sie die referenzielle Integrität bei der Verwendung von informativen Fremdschlüsseln selektiv validieren möchten, müssen Sie die Validierungslogik auf der Clientseite verwalten. Weitere Informationen finden Sie unter Informative Fremdschlüssel verwenden.

Verwenden Sie das Schlüsselwort NOT ENFORCED, um anzugeben, dass ein GoogleSQL-Fremdschlüssel informativ ist. PostgreSQL unterstützt keine informativen Fremdschlüssel.

Vergleich der Fremdschlüsseltypen

Sowohl erzwungene als auch informative haben Vorteile. In den folgenden Abschnitten werden die beiden Arten von Fremdschlüsseln verglichen und einige Best Practices vorgestellt.

Wichtige Unterschiede bei Fremdschlüsseln

Im Folgenden werden einige der Unterschiede zwischen erzwungenen und informativen Fremdschlüsseln beschrieben:

• Erzwingung. Erzwungene Fremdschlüssel validieren und garantieren die referenzielle Integrität bei Schreibvorgängen. Informative Fremdschlüssel validieren oder garantieren keine referenzielle Integrität.

• Speicher: Erzwungene Fremdschlüssel erfordern möglicherweise zusätzlichen Speicherplatz für den Sicherungsindex in der eingeschränkten Tabelle.

• Durchsatz für Schreibvorgänge Erzwungene Fremdschlüssel können im Schreibpfad mehr Overhead verursachen als informative Fremdschlüssel.

• Abfrageoptimierung Beide Arten von Fremdschlüsseln können zur Abfrageoptimierung verwendet werden. Wenn der Optimierer informative Fremdschlüssel verwenden darf, spiegeln die Abfrageergebnisse möglicherweise nicht die tatsächlichen Daten wider, wenn die Daten nicht mit den informativen Fremdschlüsselbeziehungen übereinstimmen (z. B. wenn einige eingeschränkte Schlüssel keine übereinstimmenden referenzierten Schlüssel in der referenzierten Tabelle haben).

Tabelle mit Unterschieden bei Fremdschlüsseln

In der folgenden Tabelle sind die Unterschiede zwischen erzwungenen und informativen Fremdschlüsseln aufgeführt:

	Erzwungene Fremdschlüssel	Informative Fremdschlüssel
Keywords	ENFORCED	NOT ENFORCED
Von GoogleSQL unterstützt	Ja. Fremdschlüssel in GoogleSQL werden standardmäßig erzwungen.	Ja.
Unterstützt von PostgreSQL	Ja. Fremdschlüssel in PostgreSQL können nur erzwungen werden.	Nein.
Speicher	Erzwungene Fremdschlüssel erfordern Speicherplatz für bis zu zwei Sicherungsindexe.	Für informative Fremdschlüssel ist Speicherplatz für bis zu einen Sicherungsindex erforderlich.
Erstellt bei Bedarf Sicherungsindexe für referenzierte Tabellenspalten	Ja.	Ja.
Erstellt bei Bedarf Sicherungsindexe für verweisende Tabellenspalten	Ja.	Nein.
Unterstützung für Fremdschlüsselaktionen	Ja.	Nein.
Validiert und erzwingt referenzielle Integrität	Ja.	Nein. Wenn keine Validierung erfolgt, verbessert sich die Schreibleistung. Dies kann sich jedoch auf die Abfrageergebnisse auswirken, wenn informative Fremdschlüssel für die Abfrageoptimierung verwendet werden. Sie können die clientseitige Validierung oder einen erzwungenen Fremdschlüssel verwenden, um die referenzielle Integrität sicherzustellen.

Fremdschlüsseltyp auswählen

Anhand der folgenden Richtlinien können Sie entscheiden, welchen Fremdschlüsseltyp Sie verwenden möchten:

Wir empfehlen, mit erzwungenen Fremdschlüsseln zu beginnen. Erzwungene Fremdschlüssel sorgen dafür, dass die Daten und das logische Modell immer konsistent sind. Erzwungene Fremdschlüssel sind die empfohlene Option, sofern sie für Ihren Anwendungsfall geeignet sind.

Wir empfehlen, informative Fremdschlüssel in Betracht zu ziehen, wenn alle folgenden Bedingungen erfüllt sind:

• Sie möchten das logische Datenmodell, das durch den informativen Fremdschlüssel beschrieben wird, bei der Abfrageoptimierung verwenden.

• Die Aufrechterhaltung einer strengen referenziellen Integrität ist unpraktisch oder beeinträchtigt die Leistung erheblich. In den folgenden Fällen sollten Sie die Verwendung eines informativen Fremdschlüssels in Betracht ziehen:

  • Ihre Upstream-Datenquelle folgt einem Modell der Eventual Consistency. In diesem Fall werden Aktualisierungen im Quellsystem möglicherweise nicht sofort in Spanner übernommen. Da Aktualisierungen möglicherweise nicht sofort erfolgen, kann es zu kurzen Inkonsistenzen in Fremdschlüsselbeziehungen kommen.

  • Ihre Daten enthalten referenzierte Zeilen mit einer großen Anzahl von Referenzbeziehungen. Aktualisierungen dieser Zeilen können viele Ressourcen verbrauchen, da Spanner alle Zeilen validieren oder in einigen Fällen löschen muss, die mit der Aufrechterhaltung der referenziellen Integrität zusammenhängen. In diesem Szenario können sich Aktualisierungen auf die Spanner-Leistung auswirken und gleichzeitige Transaktionen verlangsamen.

• Ihre Anwendung kann potenzielle Dateninkonsistenzen und deren Auswirkungen auf Abfrageergebnisse verarbeiten.

Informative Fremdschlüssel verwenden

Die folgenden Themen beziehen sich nur auf informative Fremdschlüssel. Informationen zu Themen, die sowohl für informative als auch für erzwungene Fremdschlüssel gelten, finden Sie hier:

• Lang andauernde Schemaänderungen
• Sicherungsindexe

Neue Tabelle mit einem informativen Fremdschlüssel erstellen

Sie erstellen und entfernen informative Fremdschlüssel aus Ihrer Spanner-Datenbank mithilfe von DDL-Anweisungen. Fremdschlüssel werden einer neuen Tabelle mit der Anweisung CREATE TABLE hinzugefügt. Ebenso können Sie mit der Anweisung ALTER TABLE Fremdschlüssel zu einer vorhandenen Tabelle hinzufügen oder daraus entfernen.

Im folgenden Beispiel wird mit GoogleSQL eine neue Tabelle mit einem informativen Fremdschlüssel erstellt. Informative Fremdschlüssel werden von PostgreSQL nicht unterstützt.

GoogleSQL

CREATE TABLE Customers (
  CustomerId INT64 NOT NULL,
  CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

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) NOT ENFORCED
 ) PRIMARY KEY (OrderId);

PostgreSQL

Not Supported

Weitere Beispiele zum Erstellen und Verwalten von Fremdschlüsseln finden Sie unter Fremdschlüsselbeziehungen erstellen und verwalten. Weitere Informationen zu DDL-Anweisungen finden Sie in der DDL-Referenz.

Hinweis: Sie können Fremdschlüssel auch über die Google Cloud Console hinzufügen und entfernen. Wählen Sie auf der Oberfläche zur Tabellenerstellung Als Text bearbeiten aus und geben Sie die DDL ein, um Fremdschlüssel für Ihre Tabelle zu verwalten.

Informative Fremdschlüssel für die Abfrageoptimierung verwenden

Sowohl erzwungene Fremdschlüssel als auch informative Fremdschlüssel können vom Abfrageoptimierer verwendet werden, um die Abfrageleistung zu verbessern. Mit informativen Fremdschlüsseln können Sie optimierte Abfragepläne nutzen, ohne dass eine strikte referenzielle Integrität erzwungen werden muss.

Wenn Sie den Abfrageoptimierer aktivieren, um informative Fremdschlüsselinformationen zu verwenden, ist es wichtig zu wissen, dass die Korrektheit der Optimierung davon abhängt, dass die Daten mit dem logischen Modell übereinstimmen, das durch die informativen Fremdschlüssel beschrieben wird. Wenn Inkonsistenzen vorliegen, spiegeln die Abfrageergebnisse möglicherweise nicht die tatsächlichen Daten wider. Eine Inkonsistenz liegt beispielsweise vor, wenn ein Wert in einer eingeschränkten Spalte keinen übereinstimmenden Wert in einer referenzierten Spalte hat.

Standardmäßig verwendet der Abfrageoptimierer NOT ENFORCED Fremdschlüssel. Wenn Sie dies ändern möchten, setzen Sie die Datenbankoption use_unenforced_foreign_key_for_query_optimization auf „false“. Das folgende GoogleSQL-Beispiel veranschaulicht dies (informative Fremdschlüssel sind in PostgreSQL nicht verfügbar):

SET DATABASE OPTIONS (
    use_unenforced_foreign_key_for_query_optimization = false
);

Der boolesche Abfragehinweis @{use_unenforced_foreign_key} überschreibt die Datenbankoption pro Abfrage, die steuert, ob der Optimierer NOT ENFORCED-Fremdschlüssel verwendet. Das Deaktivieren dieses Hinweises oder der Datenbankoption kann bei der Fehlerbehebung von unerwarteten Abfrageergebnissen hilfreich sein. Im Folgenden wird die Verwendung von @{use_unenforced_foreign_key} gezeigt:

@{use_unenforced_foreign_key=false} SELECT Orders.CustomerId
    FROM Orders
    INNER JOIN Customers ON Customers.CustomerId = Orders.CustomerId;

Erzwungene Fremdschlüssel verwenden

Die folgenden Themen gelten nur für erzwungene Fremdschlüssel. Informationen zu Themen, die sowohl für informative als auch für erzwungene Fremdschlüssel gelten, finden Sie hier:

• Lang andauernde Schemaänderungen
• Sicherungsindexe

Neue Tabelle mit einem erzwungenen Fremdschlüssel erstellen

Sie erstellen und entfernen erzwungene Fremdschlüssel aus Ihrer Spanner-Datenbank mithilfe von DDL. Sie fügen einer neuen Tabelle Fremdschlüssel mit der Anweisung CREATE TABLE hinzu. Ebenso können Sie mit der Anweisung ALTER TABLE einen Fremdschlüssel zu einer vorhandenen Tabelle hinzufügen oder daraus entfernen.

Sie erstellen und entfernen Fremdschlüssel aus Ihrer Spanner-Datenbank mithilfe von DDL. Mit der Anweisung CREATE TABLE fügen Sie einer neuen Tabelle Fremdschlüssel hinzu. Ebenso können Sie mit der Anweisung ALTER TABLE einen Fremdschlüssel zu einer vorhandenen Tabelle hinzufügen oder daraus entfernen.

Im folgenden Beispiel wird eine neue Tabelle mit einem erzwungenen Fremdschlüssel erstellt.

GoogleSQL

CREATE TABLE Customers (
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

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) ENFORCED
) PRIMARY KEY (OrderId);

PostgreSQL

CREATE TABLE Customers (
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CustomerId)
);

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

Weitere Beispiele zum Erstellen und Verwalten von Fremdschlüsseln finden Sie unter Fremdschlüsselbeziehungen erstellen und verwalten.

Hinweis: Sie können Fremdschlüssel auch über die Google Cloud Console hinzufügen und entfernen. Wählen Sie in der Oberfläche zum Erstellen von Tabellen Als Text bearbeiten aus und geben Sie die DDL ein, um Fremdschlüssel für Ihre Tabelle zu verwalten.

Fremdschlüsselaktionen

Fremdschlüsselaktionen können nur für erzwungene Fremdschlüssel definiert werden.

Fremdschlüsselaktionen steuern, was mit der eingeschränkten Spalte geschieht, wenn die Spalte, auf die sie verweist, gelöscht oder aktualisiert wird. Spanner unterstützt die Verwendung der Aktionen ON DELETE CASCADE und ON DELETE SET NULL.

Wenn Sie mit der Aktion ON DELETE CASCADE eine Zeile löschen, auf die ein Fremdschlüssel verweist, werden alle Zeilen, die auf diesen Schlüssel verweisen, ebenfalls in derselben Transaktion gelöscht.

Wenn Sie mit der Aktion ON DELETE SET NULL eine Zeile löschen, auf die ein Fremdschlüssel verweist, werden die verweisenden Spalten in allen Zeilen, die auf diesen Schlüssel verweisen, in derselben Transaktion auf NULL gesetzt.

Sie können einen Fremdschlüssel mit einer Aktion hinzufügen, wenn Sie Ihre Datenbank mit DDL erstellen. Verwenden Sie die Anweisung CREATE TABLE, um einer neuen Tabelle Fremdschlüssel mit einer Aktion hinzuzufügen. Ebenso können Sie mit der Anweisung ALTER TABLE eine Fremdschlüsselaktion zu einer vorhandenen Tabelle hinzufügen oder eine Fremdschlüsselaktion entfernen. Im folgenden Beispiel wird eine neue Tabelle mit einer Fremdschlüsselaktion erstellt.

GoogleSQL

CREATE TABLE ShoppingCarts (
CartId INT64 NOT NULL,
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
CONSTRAINT FKShoppingCartsCustomers FOREIGN KEY(CustomerId, CustomerName)
  REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE,
) PRIMARY KEY(CartId);

PostgreSQL

CREATE TABLE ShoppingCarts (
CartId bigint NOT NULL,
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CartId),
CONSTRAINT fkshoppingcartscustomers FOREIGN KEY (CustomerId, CustomerName)
  REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE
);

Im Folgenden finden Sie eine Liste der Merkmale von Fremdschlüsselaktionen in Spanner.

• Fremdschlüsselaktionen sind ON DELETE CASCADE, ON DELETE SET NULL oder ON DELETE NO ACTION.

• Sie können ON DELETE SET NULL nicht für verweisende Spalten verwenden, die Teil des Primärschlüssels der verweisenden Tabelle sind, mit NOT NULL gekennzeichnet sind oder generierte Spalten sind.

• Sie können die INFORMATION_SCHEMA abfragen, um Fremdschlüsseleinschränkungen mit einer Aktion zu finden.

• Das Hinzufügen einer Fremdschlüsselaktion zu einer vorhandenen Fremdschlüsseleinschränkung wird nicht unterstützt. Sie müssen eine neue Fremdschlüsseleinschränkung mit einer Aktion hinzufügen.

Validierung von Einschränkungen

Die Einschränkungsvalidierung gilt nur für erzwungene Fremdschlüssel.

Spanner validiert erzwungene Fremdschlüsseleinschränkungen, wenn eine Transaktion festgeschrieben wird oder wenn die Auswirkungen von Schreibvorgängen für nachfolgende Vorgänge in der Transaktion sichtbar gemacht werden.

Ein in die referenzierende Spalte eingefügter Wert wird mit den Werten der referenzierten Tabelle und der referenzierten Spalten abgeglichen. Zeilen mit NULL-Referenzwerten werden nicht geprüft. Sie können sie also der Referenztabelle hinzufügen.

Spanner validiert alle anwendbaren erzwungenen Fremdschlüssel-Referenzeinschränkungen, wenn versucht wird, Daten entweder mit DML-Anweisungen oder einer API zu aktualisieren. Alle ausstehenden Änderungen werden rückgängig gemacht, wenn Einschränkungen ungültig sind.

Die Validierung erfolgt unmittelbar nach jeder DML-Anweisung. Beispielsweise müssen Sie die referenzierte Zeile einfügen, bevor Sie die referenzierenden Zeilen einfügen. Bei Verwendung einer Mutations-API werden Mutationen zwischengespeichert, bis die Transaktion festgeschrieben wird. Die erzwungene Fremdschlüsselvalidierung wird verschoben, bis die Transaktion festgeschrieben ist. In diesem Fall ist es zulässig, zuerst die referenzierenden Zeilen einzufügen.

Jede Transaktion wird auf Änderungen geprüft, die sich auf erzwungene Fremdschlüsseleinschränkungen auswirken. Für diese Auswertungen sind möglicherweise zusätzliche Anfragen an den Server erforderlich. Sicherungsindexe erfordern außerdem zusätzliche Verarbeitungszeit, um Transaktionsänderungen auszuwerten und die Indexe zu verwalten. Für jeden Index ist außerdem zusätzlicher Speicherplatz erforderlich.

Lange laufende kaskadierende Löschaktion

Wenn Sie eine Zeile aus einer referenzierten Tabelle löschen, muss Spanner alle Zeilen in den referenzierenden Tabellen löschen, die auf die gelöschte Zeile verweisen. Dies kann zu einem Kaskadeneffekt führen, bei dem ein einzelner Löschvorgang zu Tausenden anderer Löschvorgänge führt. Wenn Sie einer Tabelle eine Fremdschlüsseleinschränkung mit einer Löschkaskadenaktion hinzufügen oder eine Tabelle mit Fremdschlüsseleinschränkungen mit einer Löschkaskadenaktion erstellen, kann dies Löschvorgänge verlangsamen.

Lange laufende Aktion zum Löschen und Festlegen von Nullwerten

Wenn Sie eine Zeile aus einer referenzierten Tabelle löschen, aktualisiert Spanner alle Zeilen in referenzierenden Tabellen, die eine ON DELETE SET NULL-Aktion haben, indem die referenzierenden Spalten auf NULL gesetzt werden. Dies kann zu einem Kaskadeneffekt führen, bei dem ein einzelner Löschvorgang zu Tausenden von Aktualisierungsvorgängen führt. Wenn Sie einer Tabelle eine Fremdschlüsseleinschränkung vom Typ ON DELETE SET NULL hinzufügen oder eine Tabelle mit einer solchen Einschränkung erstellen, kann dies Löschvorgänge verlangsamen.

Mutationslimit für Fremdschlüssel-Löschkaskade überschritten

Das Löschen einer großen Anzahl von Datensätzen mithilfe einer Fremdschlüssel-Löschkaskade kann sich auf die Leistung auswirken. Das liegt daran, dass jeder gelöschte Eintrag das Löschen aller damit verbundenen Einträge auslöst. Wenn Sie eine große Anzahl von Datensätzen mithilfe einer Fremdschlüssel-Löschkaskade löschen müssen, löschen Sie die Zeilen explizit aus den untergeordneten Tabellen, bevor Sie die Zeile aus den übergeordneten Tabellen löschen. Dadurch wird verhindert, dass die Transaktion aufgrund des Mutationslimits fehlschlägt.

Mutationslimit für Fremdschlüssel-Löschvorgang „Set Null“ überschritten

Das Löschen von Zeilen, die eine große Anzahl von Aktualisierungen mit einer Fremdschlüsselaktion ON DELETE SET NULL auslösen, kann sich auf die Leistung auswirken. Das liegt daran, dass jede gelöschte Zeile in der referenzierten Tabelle die Aktualisierung aller Zeilen auslöst, die in der referenzierenden Tabelle darauf verweisen. Wenn Sie eine Zeile löschen müssen, die viele Aktualisierungen mit ON DELETE SET NULL auslöst, aktualisieren Sie die referenzierenden Zeilen explizit auf NULL oder löschen Sie sie, bevor Sie die referenzierte Zeile löschen. So wird verhindert, dass die Transaktion aufgrund des Mutationslimits fehlschlägt.

Ressourcennutzung für Löschaktionen für Fremdschlüssel

ON DELETE CASCADE und ON DELETE SET NULL haben je nach Schema unterschiedliche Ressourcen- und Mutationskosten:

• Basistabellenänderungen: ON DELETE SET NULL aktualisiert einzelne Spalten, sodass jede aktualisierte referenzierende Zeile als mehrere Änderungen zählt (für den Zeilenschlüssel und jede auf NULL gesetzte Spalte). ON DELETE CASCADE zählt als eine Änderung pro gelöschter Zeile oder als eine Änderung pro gelöschtem Schlüsselbereich, wenn die referenzierenden Spalten ein Präfix des Primärschlüssels der referenzierenden Tabelle sind.
• Indexmutationen: Bei beiden Aktionen wird eine Löschmutation pro Zeile im Sicherungsindex des Fremdschlüssels verwendet. Bei anderen sekundären Indexen in der verweisenden Tabelle ändert ON DELETE SET NULL nur Indexe, die die verweisenden Spalten enthalten, während ON DELETE CASCADE Einträge aus jedem sekundären Index in der Tabelle löscht.
• Lesevorgänge: ON DELETE SET NULL liest immer den Sicherungsindex für Fremdschlüssel, um übereinstimmende Zeilen zu finden. ON DELETE CASCADE kann diesen Lesevorgang überspringen und einen Schlüsselbereich direkt löschen, wenn die verweisenden Spalten ein Präfix des Primärschlüssels der verweisenden Tabelle sind.
• Kaskadierung: ON DELETE SET NULL wird nie auf verschachtelte untergeordnete Tabellen kaskadiert, da Primärschlüsselspalten nicht auf NULL gesetzt werden können. Es werden nur dann weitere Aktionen ausgelöst, wenn ein anderer Fremdschlüssel auf die aktualisierte Spalte verweist. ON DELETE CASCADE löscht die gesamte verweisende Zeile, sodass der Löschvorgang auch auf alle verschachtelten untergeordneten Tabellen mit ON DELETE CASCADE und auf nachgelagerte Fremdschlüssel kaskadiert wird.

Vergleich von erzwungenen Fremdschlüsseln und Tabellenverschachtelung

Die Tabellenverschachtelung von Spanner eignet sich gut für viele hierarchische Beziehungen, bei denen der Primärschlüssel der untergeordneten Tabelle die Primärschlüsselspalten der übergeordneten Tabelle enthält. Das Speichern von untergeordneten und übergeordneten Zeilen an einem Ort kann die Leistung erheblich verbessern.

Fremdschlüssel sind eine allgemeinere über-/untergeordnete Lösung, die zusätzliche Anwendungsfälle behandelt. Sie sind nicht auf Primärschlüsselspalten beschränkt und Tabellen können mehrere Fremdschlüsselbeziehungen haben, sowohl als übergeordnete Tabelle in einigen Beziehungen als auch als untergeordnete Tabelle in anderen. Eine Fremdschlüsselbeziehung impliziert jedoch nicht, dass sich die Tabellen auf der Speicherebene befinden.

Betrachten Sie ein Beispiel mit einer Orders-Tabelle, die so definiert ist:

Abbildung 3. Diagramm des Datenbankschemas mit erzwungenen Fremdschlüsseln

Das Design in Abbildung 3 unterliegt einigen Einschränkungen. Beispielsweise kann jede Bestellung nur einen Bestellposten enthalten.

Stellen Sie sich vor, Ihre Kunden möchten mehr als ein Produkt pro Bestellung bestellen können. Sie können Ihr Design verbessern, indem Sie eine OrderItems-Tabelle mit einem Eintrag für jedes vom Kunden bestellte Produkt einführen. Sie können einen weiteren erzwungenen Fremdschlüssel einführen, um diese neue 1:n-Beziehung zwischen Orders und OrderItems darzustellen. Sie wissen aber auch, dass Sie häufig Abfragen über Bestellungen und die zugehörigen Bestellpositionen ausführen möchten. Da die Zusammenstellung dieser Daten die Leistung steigert, sollten Sie die Eltern-Kind-Beziehung mit der Tabellenverschachtelungsfunktion von Spanner erstellen.

So definieren Sie die Tabelle OrderItems, die mit Orders verschachtelt ist.

GoogleSQL

CREATE TABLE Products (
ProductId INT64 NOT NULL,
Name STRING(256) NOT NULL,
Price FLOAT64
) PRIMARY KEY(ProductId);

CREATE TABLE OrderItems (
OrderId INT64 NOT NULL,
ProductId INT64 NOT NULL,
Quantity INT64 NOT NULL,
FOREIGN KEY (ProductId) REFERENCES Products (ProductId)
) PRIMARY KEY (OrderId, ProductId),
INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

PostgreSQL

CREATE TABLE Products (
ProductId BIGINT NOT NULL,
Name varchar(256) NOT NULL,
Price float8,
PRIMARY KEY(ProductId)
);

CREATE TABLE OrderItems (
OrderId BIGINT NOT NULL,
ProductId BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
FOREIGN KEY (ProductId) REFERENCES Products (ProductId),
PRIMARY KEY (OrderId, ProductId)
) INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

Abbildung 4 ist eine visuelle Darstellung des aktualisierten Datenbankschemas als Ergebnis der Einführung dieser neuen Tabelle, OrderItems, verschachtelt mit Orders. Hier sehen Sie auch die 1:n-Beziehung zwischen diesen beiden Tabellen.

Abbildung 4. Hinzufügen einer verschränkten OrderItems-Tabelle

In dieser Konfiguration können Sie mehrere OrderItems-Einträge in jeder Bestellung haben. Die OrderItems-Einträge für jede Bestellung sind verschachtelt und befinden sich daher am selben Ort wie die Bestellungen. Durch das physische Verschachteln von Orders und OrderItems kann die Leistung verbessert werden, da die Tabellen effektiv vorab verknüpft werden und Sie auf verwandte Zeilen zugreifen können, während die Festplattenzugriffe minimiert werden. Spanner kann beispielsweise Joins nach Primärschlüssel lokal ausführen, was den Laufwerkszugriff und den Netzwerkverkehr minimiert.

Wenn die Anzahl der Mutationen in einer Transaktion 80.000 überschreitet, schlägt die Transaktion fehl. Solche großen kaskadierenden Löschvorgänge funktionieren gut für Tabellen mit einer „interleaved in parent“-Beziehung, aber nicht für Tabellen mit einer Fremdschlüsselbeziehung. Wenn Sie eine Fremdschlüsselbeziehung haben und eine große Anzahl von Zeilen löschen müssen, sollten Sie die Zeilen zuerst explizit aus den untergeordneten Tabellen löschen.

Wenn Sie eine Nutzertabelle mit einer Fremdschlüsselbeziehung zu einer anderen Tabelle haben und das Löschen einer Zeile aus der referenzierten Tabelle das Löschen von Millionen von Zeilen auslöst, sollten Sie Ihr Schema mit einer Löschkaskadenaktion mit „In übergeordneter Tabelle verschachtelt“ entwerfen.

Hinweis: Google empfiehlt, dass Sie hierarchische Beziehungen entweder als verschränkte Tabellen oder als Fremdschlüssel darstellen, aber nicht beides. Die Verwendung von beiden ist redundant und kann zusätzlichen Speicherplatz und Rechenressourcen verbrauchen, um Einschränkungen zu prüfen und Sicherungsindexe zu verwalten.

Vergleichstabelle

In der folgenden Tabelle werden erzwungene Fremdschlüssel und das Verschachteln von Tabellen verglichen. Anhand dieser Informationen können Sie entscheiden, was für Ihr Design am besten geeignet ist.

Über- / Untergeordnet-Beziehungstyp	Tabellen verschränken	Erzwungene Fremdschlüssel
Kann Primärschlüssel verwenden	Ja	Ja
Kann Spalten ohne Primärschlüssel verwenden	Nein	Ja
Anzahl der unterstützten Eltern	0 .. 1	0 .. N
Speichert übergeordnete und untergeordnete Daten zusammen	Ja	Nein
Unterstützt das Löschen von Kaskaden	Ja	Ja
Null-Abgleichmodus	Übergibt, wenn sich alle referenzierenden Werte nicht von den referenzierten Werten unterscheiden. Nullwerte unterscheiden sich nicht von Nullwerten. Nullwerte unterscheiden sich von Nicht-Nullwerten.	Übergibt, wenn verweisende Werte null sind. Wird übergeben, wenn alle verweisenden Werte ungleich null sind und die referenzierte Tabelle eine Zeile mit Werten aufweist, die den verweisenden Werten entsprechen. Schlägt fehl, wenn keine übereinstimmende Zeile gefunden wurde.
Zeitpunkt der Durchsetzung	Pro Vorgang bei Verwendung der mutation API. Pro Anweisung bei Verwendung von DML.	Pro Transaktion bei Verwendung der Mutation API Pro Anweisung bei Verwendung von DML.
Kann entfernt werden	Nein. Die Tabellenverschachtelung kann nach dem Erstellen nicht mehr entfernt werden, es sei denn, Sie löschen die gesamte untergeordnete Tabelle.	Ja

Sicherungsindexe

Fremdschlüssel verwenden keine vom Nutzer erstellten Indexe. Stattdessen werden eigene Sicherungsindexe erstellt. Erzwungene und informative Fremdschlüssel erstellen Sicherungsindexe in Spanner unterschiedlich:

• Für erzwungene Fremdschlüssel kann Spanner bis zu zwei sekundäre Sicherungsindexe für jeden Fremdschlüssel erstellen, einen für die verweisenden Spalten und einen zweiten für die referenzierten Spalten.

• Für informative Fremdschlüssel kann Spanner bei Bedarf bis zu einen Sicherungsindex für die referenzierten Spalten erstellen. Informative Fremdschlüssel erstellen keinen Sicherungsindex für die verweisenden Spalten.

Sowohl bei erzwungenen als auch bei informativen Fremdschlüsseln verweist ein Fremdschlüssel in der Regel auf die Primärschlüssel der referenzierten Tabelle. Ein Index für die referenzierte Tabelle ist daher in der Regel nicht erforderlich. Aus diesem Grund haben informative Fremdschlüssel in der Regel keine Sicherungsindexe. Bei Bedarf ist der für die referenzierte Tabelle erstellte Sicherungsindex ein UNIQUE NULL_FILTERED-Index. Das Erstellen des Fremdschlüssels schlägt fehl, wenn vorhandene Daten gegen die Eindeutigkeitseinschränkung des Index verstoßen.

Informative Fremdschlüssel haben keinen Sicherungsindex für die referenzierende Tabelle. Bei erzwungenen Fremdschlüsseln ist der Sicherungsindex für die verweisende Tabelle NULL_FILTERED.

Wenn zwei oder mehr Fremdschlüssel denselben Sicherungsindex erfordern, erstellt Spanner für jeden einen einzelnen Index. Die Sicherungsindexe werden gelöscht, wenn die Fremdschlüssel, die sie verwenden, gelöscht werden. Sie können die Sicherungsindexe nicht ändern oder löschen.

Spanner verwendet das Informationsschema jeder Datenbank, um Metadaten zu Sicherungsindexen zu speichern. Zeilen in INFORMATION_SCHEMA.INDEXES mit dem SPANNER_IS_MANAGED-Wert true beschreiben Sicherungsindexe.

Abgesehen von SQL-Abfragen, die das Informationsschema direkt aufrufen, werden in derGoogle Cloud -Konsole keine Informationen zu den Sicherungsindexen einer Datenbank angezeigt.

Lang andauernde Schemaänderungen

Das Hinzufügen eines erzwungenen Fremdschlüssels zu einer vorhandenen Tabelle oder das Erstellen einer neuen Tabelle mit einem Fremdschlüssel kann zu Vorgängen mit langer Ausführungszeit führen. Bei einer neuen Tabelle ist die Tabelle erst beschreibbar, wenn der Vorgang mit langer Ausführungszeit abgeschlossen ist.

In der folgenden Tabelle sehen Sie, was in Spanner passiert, wenn ein erzwungener und ein informativer Fremdschlüssel in einer neuen oder vorhandenen Tabelle vorhanden ist:

Tabellentyp	Erzwungener Fremdschlüssel	Informativer Fremdschlüssel
Neu	Spanner füllt referenzierte Indexe nach Bedarf für jeden Fremdschlüssel aus.	Spanner füllt referenzierte Indexe nach Bedarf für jeden Fremdschlüssel aus.
Vorhanden	Spanner füllt die referenzierenden und referenzierten Indexe nach Bedarf auf. Spanner validiert auch vorhandene Daten in der Tabelle, um sicherzustellen, dass sie der Einschränkung für die referenzielle Integrität des Fremdschlüssels entsprechen. Die Schemaänderung schlägt fehl, wenn Daten ungültig sind.	Spanner füllt den referenzierten Index nach Bedarf auf und validiert keine vorhandenen Daten in der Tabelle.

Folgendes wird nicht unterstützt:

• Fremdschlüsselaktion zu einer vorhandenen erzwungenen Fremdschlüsseleinschränkung hinzufügen.
• Erzwingung eines vorhandenen Fremdschlüssels ändern

In beiden Fällen empfehlen wir stattdessen Folgendes:

1. Fügen Sie eine neue Einschränkung mit der erforderlichen Aktion oder Maßnahme hinzu.
2. Löschen Sie die alte Einschränkung.

Wenn Sie eine neue Einschränkung hinzufügen und die alte Einschränkung verwerfen, wird ein Problem mit einem lang andauernden Alter Constraint-Vorgang verhindert. Angenommen, Sie möchten einem vorhandenen Fremdschlüssel eine DELETE CASCADE-Aktion hinzufügen. Nachdem Sie den neuen Fremdschlüssel mit der Aktion ON DELETE CASCADE erstellt haben, ist die Auswirkung beider Einschränkungen eine DELETE CASCADE-Aktion. Anschließend können Sie die alte Einschränkung sicher löschen.

Das Löschen einer Einschränkung kann dazu führen, dass die Sicherungsindexe des Fremdschlüssels gelöscht werden, wenn die Indexe nicht von anderen Fremdschlüsseleinschränkungen verwendet werden. Wenn Sie die alte Einschränkung zuerst löschen, kann das Hinzufügen derselben Fremdschlüsseleinschränkung mit einer Aktion später zu Vorgängen mit langer Ausführungszeit führen, z. B. zum Backfilling von Indexen, zur Validierung von eindeutigen Indexeinschränkungen oder zur Validierung von referenziellen Fremdschlüsseleinschränkungen.

Hinweis: Wenn Sie die ältere Fremdschlüsseleinschränkung nicht löschen, müssen zusätzliche Fremdschlüsseleinschränkungen validiert werden, was zu unnötigem Aufwand führt. Wenn ein Verstoß gegen die Einschränkung UNIQUE die Erstellung des referenzierten Index verhindert, kann das Hinzufügen der neuen Einschränkung fehlschlagen.

Sie können INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.SPANNER_STATE abfragen, um den Erstellungsstatus des Fremdschlüssels zu prüfen.

Nächste Schritte

• Weitere Informationen zum Erstellen und Verwalten von Fremdschlüsselbeziehungen

• Weitere Informationen zum Informationsschema