Fremdschlüssel

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

Übersicht über Fremdschlüssel in Spanner

Mit Fremdschlüsseln werden Beziehungen zwischen Tabellen definiert. Mit Fremdschlüsseln können Sie dafür sorgen, dass die Datenintegrität dieser Beziehungen in Spanner aufrechterhalten wird.

Stellen Sie sich vor, Sie sind der Lead-Entwickler für ein E-Commerce-Unternehmen. Sie entwerfen eine Datenbank zur Verarbeitung von Kundenaufträgen. Die Datenbank muss Informationen über jede Bestellung, jeden Kunden und jedes Produkt speichern. Abbildung 1 zeigt die grundlegende Datenbankstruktur für die Anwendung.

Grundstruktur der Auftragsverarbeitungsdatenbank.

Abbildung 1. Diagramm einer Auftragsverarbeitungsdatenbank

Sie definieren eine Customers-Tabelle zum Speichern von Kundendaten, eine Orders-Tabelle zum Nachverfolgen 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 zugeordnet sind:

  • Ein Kunde gibt eine Bestellung auf.

  • Es wird eine Bestellung für ein Produkt aufgegeben.

Sie entscheiden, dass Ihre Datenbank die folgenden Regeln erzwingen soll, 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, halten Sie die referenzielle Integrität Ihrer Daten aufrecht. Wenn eine Datenbank die referenzielle Integrität aufrechterhält, schlagen alle Versuche fehl, ungültige Daten hinzuzufügen, die zu ungültigen Verknüpfungen 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

Sehen wir uns noch einmal unser Beispiel für die Bestellabwicklung an, wobei dem Design weitere Details hinzugefügt wurden (siehe Abbildung 2).

Datenbankschema mit Fremdschlüsseln

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 setzt voraus, dass alle Zeilen in Orders ein gültiges CustomerId haben. Der Fremdschlüssel FK_ProductOrder setzt voraus, dass alle ProductId-Werte in der Tabelle Orders gültig sind. In der folgenden Tabelle werden diese Einschränkungen den tatsächlichen Regeln zugeordnet, die erzwungen werden sollen.

Name des Fremdschlüssels Einschränkung Beschreibung aus der Praxis
FK_CustomerOrder Erwartet, dass alle Zeilen in Orders eine gültige CustomerId haben Ein gültiger Kunde gibt eine Bestellung auf
FK_ProductOrder Erwartet, dass alle Zeilen in Orders eine gültige 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 fehlgeschlagen, die versucht, eine Zeile in die Tabelle Orders einzufügen oder zu aktualisieren, deren CustomerId oder ProductId nicht in den Tabellen Customers und Products enthalten ist. Außerdem schlägt sie fehl, wenn versucht wird, Zeilen in den Tabellen Customers und Products zu aktualisieren oder zu löschen, wodurch die IDs in der Tabelle Orders ungültig werden. Weitere Informationen dazu, wie Spanner Einschränkungen validiert, finden Sie im Abschnitt Überprüfung der Transaktionsbeschränkung.

Im Gegensatz zu erzwungenen Fremdschlüsseln werden Einschränkungen für informelle Fremdschlüssel in Spanner nicht validiert. 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, deren CustomerId oder ProductId nicht in den Tabellen Customers und Products enthalten ist, 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.

Fremdschlüsselmerkmale

Im Folgenden finden Sie eine Liste der Eigenschaften 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 Tabelle referenziert.

  • Wie im Beispiel können Sie jede Fremdschlüsselbeschränkung benennen. Wenn Sie keinen Namen angeben, generiert Spanner einen Namen für Sie. Sie können den generierten Namen über 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 referenzierende Spalte verweist beispielsweise auf die erste referenzierte Spalte und die zweite referenzierende 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.

  • Fremdschlüssel können nicht für Spalten mit der Option allow_commit_timestamp=true erstellt werden.

  • Array-Spalten werden nicht unterstützt.

  • JSON-Spalten werden nicht unterstützt.

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

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

  • Die referenzierten Schlüssel müssen eindeutig sein. Spanner verwendet das 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.

  • Bei Fremdschlüsseln werden keine von Ihnen erstellten sekundären Indexe verwendet. Stattdessen erstellen sie ihre eigenen Sicherungsindizes. Backing-Indexe können in Abfrageauswertungen verwendet werden, einschließlich expliziter force_index-Anweisungen. Sie können die Namen der Sicherungsindizes über INFORMATION_SCHEMA von Spanner abfragen. Weitere Informationen finden Sie unter Indexe sichern.

Arten von Fremdschlüsseln

Es gibt zwei Arten von Fremdschlüsseln: erzwingende und informative. Fremdschlüssel werden standardmäßig erzwungen und sorgen für referenzielle Integrität. Informative Fremdschlüssel erzwingen keine referenzielle Integrität und werden am besten verwendet, um das beabsichtigte logische Datenmodell für die Abfrageoptimierung zu deklarieren. Weitere Informationen finden Sie in den Abschnitten zu erzwingenden und informellen Fremdschlüsseln sowie in der Vergleichstabelle der 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, Folgendes zu tun, fehl:

  • Das Hinzufügen einer Zeile zu einer Referenztabelle mit einem Fremdschlüsselwert, 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 referenzierenden 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 zur Angabe, dass ein GoogleSQL-Fremdschlüssel erzwungen wird, optional.

Informative Fremdschlüssel

Informational-Fremdschlüssel werden verwendet, um das beabsichtigte logische Datenmodell für die Abfrageoptimierung zu deklarieren. Referenzierte 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 selektiv validieren möchten, wenn Sie informative Fremdschlüssel verwenden, müssen Sie die Validierungslogik auf der Clientseite verwalten. Weitere Informationen finden Sie unter Informative Fremdschlüssel verwenden.

Mit dem Schlüsselwort NOT ENFORCED geben Sie an, dass ein GoogleSQL-Fremdschlüssel nur zu Informationszwecken dient. PostgreSQL unterstützt keine informativen Fremdschlüssel.

Vergleich von Fremdschlüsseltypen

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

Allgemeine Unterschiede bei Fremdschlüsseln

Im Folgenden finden Sie einige der wichtigsten Unterschiede zwischen erzwingenden und informellen Fremdschlüsseln:

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

  • Speicher: Für erzwungene Fremdschlüssel ist möglicherweise zusätzlicher Speicherplatz für den Sicherungsindex in der eingeschränkten Tabelle erforderlich.

  • Durchsatz für Schreibvorgänge Erzwungene Fremdschlüssel können im Schreibpfad mehr Aufwand 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. Das ist beispielsweise der Fall, 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 detaillierten Unterschiede zwischen erzwungenen und informativen Fremdschlüsseln aufgeführt:

Erzwungene Fremdschlüssel Informative Fremdschlüssel
Keywords ENFORCED NOT ENFORCED
Unterstützt von GoogleSQL 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 Für erzwungene Fremdschlüssel ist Speicherplatz für bis zu zwei Sicherungsindexe erforderlich. Für informative Fremdschlüssel ist Speicherplatz für bis zu einem Sicherungsindex erforderlich.
Erstellt bei Bedarf Unterstützungsindexe für referenzierte Tabellenspalten. Ja. Ja.
Erstellt bei Bedarf Sicherungsindexe für Spalten der referenzierenden Tabelle. Ja. Nein.
Unterstützung von Fremdschlüsselaktionen Ja. Nein.
Referenzielle Integrität wird validiert und erzwungen Ja. Nein. Wenn keine Validierung erfolgt, wird die Schreibgeschwindigkeit verbessert, aber die Abfrageergebnisse können beeinträchtigt werden, wenn informational foreign keys zur Abfrageoptimierung verwendet werden. Sie können die referenzielle Integrität mit einer clientseitigen Validierung oder einem erzwungenen Fremdschlüssel sicherstellen.

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. Durchgesetzte Fremdschlüssel sorgen dafür, dass die Daten und das logische Modell jederzeit konsistent sind. Die Verwendung von erzwungenen Fremdschlüsseln wird empfohlen, sofern sie für Ihren Anwendungsfall geeignet sind.

Wir empfehlen, informative Fremdschlüssel zu verwenden, wenn alle folgenden Bedingungen zutreffen:

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

  • Die Aufrechterhaltung einer strengen referenziellen Integrität ist unpraktisch oder wirkt sich erheblich auf die Leistung aus. Hier sind einige Beispiele für Situationen, in denen Sie einen informativen Fremdschlüssel verwenden sollten:

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

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

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

Informative Fremdschlüssel verwenden

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

Neue Tabelle mit einem informativen Fremdschlüssel erstellen

Sie erstellen und entfernen informational foreign keys in Ihrer Spanner-Datenbank mithilfe von DDL-Anweisungen. Fremdschlüssel werden einer neuen Tabelle mit der Anweisung CREATE TABLE hinzugefügt. In ähnlicher Weise 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.

Informative Fremdschlüssel zur Abfrageoptimierung verwenden

Sowohl erzwungene Fremdschlüssel als auch informative Fremdschlüssel können vom Abfrageoptimierer verwendet werden, um die Abfrageleistung zu verbessern. Wenn Sie informative Fremdschlüssel verwenden, können Sie optimierte Abfragepläne nutzen, ohne dass die strikte Durchsetzung der referenziellen Integrität erforderlich ist.

Wenn Sie den Query-Optimierer so konfigurieren, dass er Informationen zu informativen Fremdschlüsseln verwendet, ist es wichtig zu wissen, dass die Richtigkeit der Optimierung davon abhängt, dass die Daten mit dem logischen Modell übereinstimmen, das durch die informativen Fremdschlüssel beschrieben wird. Wenn Inkonsistenzen vorhanden sind, spiegeln die Abfrageergebnisse möglicherweise nicht die tatsächlichen Daten wider. Ein Beispiel für eine Inkonsistenz ist, wenn ein Wert in einer eingeschränkten Spalte keinen entsprechenden Wert in einer referenzierten Spalte hat.

Standardmäßig verwendet das Abfrageoptimierungstool NOT ENFORCED-Fremdschlüssel. Wenn Sie das ä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 Hinweis für die Abfrageanweisung @{use_unenforced_foreign_key} überschreibt die Datenbankoption auf Abfragebasis, mit der gesteuert wird, ob das Optimierungstool NOT ENFORCED-Fremdschlüssel verwendet. Das Deaktivieren dieses Hinweises oder der Datenbankoption kann bei der Fehlerbehebung unerwarteter Abfrageergebnisse hilfreich sein. Im Folgenden wird gezeigt, wie @{use_unenforced_foreign_key} verwendet wird:

@{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 beziehen sich nur auf erzwungene Fremdschlüssel. Informationen zu Themen, die sowohl für informative als auch für erzwungene Fremdschlüssel gelten, finden Sie unter:

Neue Tabelle mit einem erzwungenen Fremdschlüssel erstellen

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

Fremdschlüssel werden mithilfe von DDL erstellt und aus Ihrer Spanner-Datenbank entfernt. Fremdschlüssel werden einer neuen Tabelle mit der Anweisung CREATE TABLE hinzugefügt. In ähnlicher Weise können Sie mit der Anweisung ALTER TABLE einen Fremdschlüssel zu einer vorhandenen Tabelle hinzufügen oder daraus entfernen.

Im Folgenden finden Sie ein Beispiel für die Erstellung einer neuen Tabelle mit einem erzwungenen Fremdschlüssel.

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.

Fremdschlüsselaktionen

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

Fremdschlüsselaktionen steuern, was mit der eingeschränkten Spalte passiert, wenn die Spalte, auf die sie verweist, gelöscht oder aktualisiert wird. Spanner unterstützt die Verwendung der Aktion ON DELETE CASCADE. Wenn Sie mit der Fremdschlüsselaktion ON DELETE CASCADE eine Zeile löschen, die einen referenzierten Fremdschlüssel enthält, werden alle Zeilen, die auf diesen Schlüssel verweisen, in derselben Transaktion ebenfalls gelöscht.

Sie können einen Fremdschlüssel mit einer Aktion hinzufügen, wenn Sie Ihre Datenbank mit DDL erstellen. Mit der Anweisung CREATE TABLE können Sie einer neuen Tabelle Fremdschlüssel mit einer Aktion hinzufügen. In ähnlicher Weise können Sie mit der Anweisung ALTER TABLE einer vorhandenen Tabelle eine Fremdschlüsselaktion hinzufügen oder eine Fremdschlüsselaktion entfernen. Im Folgenden finden Sie ein Beispiel für die Erstellung einer neuen Tabelle mit einer Fremdschlüsselaktion.

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 Eigenschaften von Fremdschlüsselaktionen in Spanner.

  • Fremdschlüsselaktionen sind entweder ON DELETE CASCADE oder ON DELETE NO ACTION.

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

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

Einschränkungsvalidierung

Die Validierung von Einschränkungen 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 Wert, der in die Referenzspalte eingefügt wird, wird mit den Werten der referenzierten Tabelle und der referenzierten Spalten abgeglichen. Zeilen mit NULL-Verweiswerten werden nicht aktiviert, das heißt, sie können der Verweistabelle hinzugefügt werden.

Spanner validiert alle anwendbaren referenziellen Fremdschlüsseleinschränkungen, wenn versucht wird, Daten entweder über DML-Anweisungen oder über ein 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. Sie müssen beispielsweise die referenzierte Zeile einfügen, bevor Sie die entsprechenden Zeilen einfügen. Bei Verwendung einer Mutations-API werden Mutationen zwischengespeichert, bis die Transaktion festgeschrieben wird. Die erzwungene Validierung des Fremdschlüssels wird so lange zurückgestellt, bis die Transaktion festgeschrieben wird. In diesem Fall ist es zulässig, zuerst die referenzierenden Zeilen einzufügen.

Jede Transaktion wird auf Änderungen geprüft, die sich auf die Einschränkungen von Fremdschlüsseln auswirken. Für diese Bewertungen sind möglicherweise zusätzliche Anforderungen an den Server erforderlich. Sicherungsindizes benötigen außerdem zusätzliche Verarbeitungszeit, um Transaktionsänderungen auszuwerten und die Indexe zu verwalten. Für jeden Index ist zusätzlicher Speicher 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 Tausende anderer Löschvorgänge zur Folge hat. Das Hinzufügen einer Fremdschlüsseleinschränkung mit der Aktion „Delete Cascade“ zu einer Tabelle oder das Erstellen einer Tabelle mit Fremdschlüsseleinschränkungen mit der Aktion „Delete Cascade“ kann Löschvorgänge verlangsamen.

Mutationslimit für das kaskadierende Löschen von Fremdschlüsseln überschritten

Das Löschen einer großen Anzahl von Datensätzen mithilfe einer Löschkaskade für Fremdschlüssel kann sich auf die Leistung auswirken. Das liegt daran, dass beim Löschen eines Datensatzes alle zugehörigen Datensätze gelöscht werden. Wenn Sie eine große Anzahl von Datensätzen mit einer Löschkaskade für Fremdschlüssel löschen müssen, löschen Sie die Zeilen explizit aus den untergeordneten Tabellen, bevor Sie die Zeile aus den übergeordneten Tabellen löschen. So wird verhindert, dass die Transaktion aufgrund des Mutationslimits fehlschlägt.

Vergleich von erzwungenen Fremdschlüsseln und Tabellenverschränkung

Die Tabellenverschränkung von Spanner ist eine gute Wahl 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, die in einigen Beziehungen als übergeordnete und in anderen als untergeordnete Beziehungen gelten. 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:

Datenbankschema mit Fremdschlüsseln

Abbildung 3. Diagramm des Datenbankschemas mit erzwungenen Fremdschlüsseln

Das Design in Abbildung 3 unterliegt einigen Einschränkungen. Beispielsweise kann jeder Auftrag nur ein Auftragselement enthalten.

Nehmen wir einmal an, Ihre Kunden möchten pro Bestellung mehr als ein Produkt bestellen. Sie können Ihr Design verbessern, indem Sie eine OrderItems-Tabelle einführen, die einen Eintrag für jedes Produkt enthält, das der Kunde bestellt hat. 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 für Aufträge und ihre jeweiligen Auftragselemente ausführen möchten. Da die gemeinsame Speicherung dieser Daten die Leistung steigert, sollten Sie die über-/untergeordnete Beziehung mithilfe der Tabellenverschränkungsfunktion von Spanner erstellen.

So definieren Sie die Tabelle OrderItems, die mit Orders verschränkt 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.

Datenbankschema, das eine 1: n-Beziehung zwischen Orders und der neuen, verschränkten OrderItems-Tabelle zeigt

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

In dieser Konfiguration können mehrere OrderItems-Einträge in jedem Auftrag vorhanden sein und die OrderItems-Einträge für jeden Auftrag sind verschränkt und befinden sich daher zusammen mit den Aufträgen. Das physische Verschränken von Orders und OrderItems auf diese Weise kann die Leistung verbessern, indem die Tabellen effektiv vorab zusammengeführt werden und Sie zusammen auf ähnliche Zeilen zugreifen können, während Zugriffe auf das Laufwerk minimiert werden. Spanner kann beispielsweise Joins lokal nach Primärschlüssel durchführen, um den Zugriff auf das Laufwerk und den Netzwerkverkehr zu minimieren.

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 Beziehung vom Typ „interleaved in parent“, nicht aber 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 Delete Cascade-Aktion mit „interleaved in parent“ (in übergeordneter Tabelle verschachtelt) entwerfen.

Vergleichstabelle

In der folgenden Tabelle sind die Vergleiche von erzwungenen Fremdschlüsseln und Tabellenverschränkung zusammengefasst. 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. Tabellenverschränkungen können nach dem Erstellen nicht entfernt werden, es sei denn, Sie löschen die gesamte untergeordnete Tabelle. Ja

Indizes sichern

Bei Fremdschlüsseln werden keine von Nutzern erstellten Indexe verwendet. Stattdessen erstellen sie ihre eigenen Sicherungsindizes. Erzwungene und informative Fremdschlüssel erstellen Sicherungsindizes in Spanner unterschiedlich:

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

  • Für informative Fremdschlüssel kann Spanner bei Bedarf einen Sicherungsindex für die referenzierten Spalten erstellen. Für die referenzierenden Spalten von informativen Fremdschlüsseln wird kein Sicherungsindex erstellt.

Sowohl bei erzwungenen als auch bei informativen Fremdschlüsseln verweist ein Fremdschlüssel in der Regel auf die Primärschlüssel der referenzierten Tabelle, sodass ein Index für die referenzierte Tabelle in der Regel nicht erforderlich ist. Aus diesem Grund haben informative Fremdschlüssel in der Regel keine Sicherungsindizes. 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 Eindeutigkeitsbeschränkung des Index verstoßen.

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

Wenn zwei oder mehr Fremdschlüssel denselben Sicherungsindex erfordern, erstellt Spanner einen einzigen Index für alle. Die Sicherungsindizes werden gelöscht, wenn die sie verwendenden Fremdschlüssel gelöscht werden. Sie können die Backing-Indexe nicht ändern oder löschen.

In Spanner wird das Informationsschema jeder Datenbank verwendet, um Metadaten zu unterstützenden Indexen zu speichern. Zeilen in INFORMATION_SCHEMA.INDEXES mit dem SPANNER_IS_MANAGED-Wert true beschreiben Sicherungsindexe.

Außerhalb von SQL-Abfragen, die das Informationsschema direkt aufrufen, werden in derGoogle Cloud -Konsole keine Informationen zu den zugrunde liegenden Indexen 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 lang andauernden Vorgängen führen. Im Fall einer neuen Tabelle kann die Tabelle erst geschrieben werden, 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 einer vorhandenen Tabelle vorhanden ist:

Tabellentyp Erzwungener Fremdschlüssel Informativer Fremdschlüssel
Neu Spanner füllt referenzierte Indexe nach Bedarf für jeden Fremdschlüssel auf. Spanner füllt referenzierte Indexe nach Bedarf für jeden Fremdschlüssel auf.
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 referenziellen Integritätsbedingung 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:

  • Einer vorhandenen erzwungenen Fremdschlüsseleinschränkung eine Fremdschlüsselaktion hinzufügen.
  • Die 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 Durchsetzung hinzu.
  2. Löschen Sie die alte Einschränkung.

Wenn Sie eine neue Einschränkung hinzufügen und die alte entfernen, wird das Problem Vorgang zum Ändern der Einschränkung mit langer Ausführungszeit vermieden. Angenommen, Sie möchten einem vorhandenen Fremdschlüssel die Aktion DELETE CASCADE hinzufügen. Nachdem Sie den neuen Fremdschlüssel mit der Aktion ON DELETE CASCADE erstellt haben, ist die Wirkung beider Einschränkungen eine DELETE CASCADE-Aktion. Anschließend können Sie die alte Einschränkung problemlos entfernen.

Wenn Sie eine Einschränkung löschen, können auch die Sicherungsindizes für Fremdschlüssel gelöscht werden, sofern die Indizes nicht von anderen Einschränkungen für Fremdschlüssel 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 langwierigen Vorgängen führen, z. B. zum Auffüllen von Indexen, zum Validieren von eindeutigen Indexeinschränkungen oder zum Validieren von referenziellen Fremdschlüsseleinschränkungen.

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

Nächste Schritte