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 ihre Verwendung:

Fremdschlüssel in Spanner

Mit Fremdschlüsseln werden Beziehungen zwischen Tabellen definiert. Mithilfe von 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 Kundeninformationen, eine Orders-Tabelle zur Nachverfolgung 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, sorgen Sie für die referenzielle Integrität Ihrer Daten. 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

Im Folgenden wird das Beispiel für die Bestellabwicklung noch einmal betrachtet, 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. Für FK_CustomerOrder wird erwartet, 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 Es wird davon ausgegangen, dass alle Zeilen in Orders eine gültige CustomerId haben. Ein gültiger Kunde gibt eine Bestellung auf
FK_ProductOrder Es wird davon ausgegangen, 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 jede Transaktion in Spanner fehlschlägt, 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 in Spanner keine Einschränkungen für informative Fremdschlüssel überprüft. Wenn Sie in diesem Szenario also 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.

Eigenschaften von Fremdschlüsseln

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 Spalten des Fremdschlüssels sind die referenzierenden Spalten.

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

  • Wie im Beispiel können Sie jede Einschränkung für einen externen Schlüssel benennen. Wenn Sie keinen Namen angeben, generiert Spanner einen Namen für Sie. Sie können den generierten Namen über die INFORMATION_SCHEMA von Spanner abfragen. Einschränkungsnamen gelten zusammen mit den Namen für Tabellen und Indexe für das Schema 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 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, 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: erzwungene und informative. Erzwungene Fremdschlüssel sind standardmäßig aktiviert und erzwingen die referenzielle Integrität. Informationsfremdschlü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 der Fremdschlüsseltypen.

Erzwungene Fremdschlüssel

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

  • Wenn Sie einer Referenztabelle eine Zeile mit einem Fremdschlüsselwert hinzufügen, der in der referenzierten Tabelle nicht vorhanden ist, schlägt das Hinzufügen fehl.

  • Das Löschen einer Zeile aus einer referenzierten Tabelle, auf die Zeilen in der referenzierenden Tabelle verweisen, schlägt fehl.

Alle PostgreSQL-Fremdschlüssel werden erzwungen. GoogleSQL-Fremdschlüssel werden standardmäßig erzwungen. Da Fremdschlüssel standardmäßig erzwungen werden, ist es optional, das Keyword ENFORCED zu verwenden, um anzugeben, dass ein GoogleSQL-Fremdschlüssel erzwungen wird.

Informative Fremdschlüssel

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

Verwenden Sie das Keyword NOT ENFORCED, um anzugeben, dass ein GoogleSQL-Fremdschlüssel nur zu Informationszwecken dient. PostgreSQL unterstützt keine informativen Fremdschlüssel.

Vergleich der Fremdschlüsseltypen

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

Unterschiede zwischen Fremdschlüsseln auf hoher Ebene

Im Folgenden sind einige der Unterschiede zwischen erzwungenen und informativen Fremdschlüsseln aufgeführt:

  • Durchsetzung Erzwungene Fremdschlüssel validieren und gewährleisten die referenzielle Integrität bei Schreibvorgängen. Informationsfremdschlüssel validieren oder garantieren die referenzielle Integrität nicht.

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

  • Schreibdurchsatz Erzwungene Fremdschlüssel können im Schreibpfad mehr Overhead verursachen als informative Fremdschlüssel.

  • Abfrageoptimierung Beide Arten von Fremdschlüsseln können für die Abfrageoptimierung verwendet werden. Wenn der Optimierungsmechanismus die Verwendung von informativen Fremdschlüsseln zulassen 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 Sicherungsindizes. Für informative Fremdschlüssel ist Speicherplatz für bis zu einen Sicherungsindex erforderlich.
Erstellt bei Bedarf Unterstützungsindexe für referenzierte Tabellenspalten. Ja. Ja.
Erstellt bei Bedarf Sicherungsindexe für referenzierende Tabellenspalten Ja. Nein.
Unterstützung von Fremdschlüsselaktionen Ja. Nein.
Prüfen und Erzwingen der referentiellen Integrität Ja. Nein. Ohne Validierung wird die Schreibleistung verbessert, was sich jedoch auf die Abfrageergebnisse auswirken kann, 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 zu gewährleisten.

Typ des Fremdschlüssels 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. Durch erzwungene Fremdschlüssel bleiben die Daten und das logische Modell immer konsistent. Erzwungene Fremdschlüssel sind die empfohlene Option, sofern sie für Ihren Anwendungsfall nicht geeignet sind.

Wir empfehlen, informative Fremdschlüssel zu verwenden, wenn folgende Bedingungen erfüllt sind:

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

  • Die strikte Aufrechterhaltung der referenziellen Integrität ist unpraktisch oder wirkt sich erheblich auf die Leistung aus. Im Folgenden finden Sie einige Beispiele für die Verwendung eines informativen Fremdschlüssels:

    • Ihre vorgelagerte Datenquelle folgt einem Modell der Endkonsistenz. In diesem Fall werden Aktualisierungen, die im Quellsystem vorgenommen wurden, möglicherweise nicht sofort in Spanner übernommen. Da Aktualisierungen möglicherweise nicht sofort erfolgen, können kurzzeitige Inkonsistenzen in Fremdschlüsselbeziehungen auftreten.

    • Ihre Daten enthalten referenzierte Zeilen mit einer großen Anzahl von Verweisbeziehungen. Aktualisierungen dieser Zeilen können viele Ressourcen verbrauchen, 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 Aktualisierungen auf die Spanner-Leistung auswirken und gleichzeitige Transaktionen verlangsamen.

  • Ihre Anwendung kann mit potenziellen Dateninkonsistenzen und deren Auswirkungen auf Abfrageergebnisse umgehen.

Informative Fremdschlüssel verwenden

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

Neue Tabelle mit einem informativen Fremdschlüssel erstellen

Mit DDL-Anweisungen können Sie informative Fremdschlüssel in Ihrer Spanner-Datenbank erstellen und entfernen. 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. Informationsfremdschlü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 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. Mithilfe von informativen Fremdschlüsseln können Sie optimierte Abfragepläne nutzen, ohne den Overhead einer strengen Erzwingung der referenziellen Integrität.

Wenn Sie dem Abfrageoptimierer erlauben, Informationen zu informativen Fremdschlüsseln zu verwenden, ist es wichtig zu wissen, dass die Richtigkeit der Optimierung davon abhängt, ob 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 Spalte mit Einschränkung keinen übereinstimmenden 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“. Im folgenden GoogleSQL-Beispiel wird dies veranschaulicht (informative Fremdschlüssel sind in PostgreSQL nicht verfügbar):

SET DATABASE OPTIONS (
    use_unenforced_foreign_key_for_query_optimization = false
);

Der Hinweis @{use_unenforced_foreign_key} für boolesche Abfrageanweisungen überschreibt die Datenbankoption auf Abfragebasis. Diese Option steuert, ob der Optimierer NOT ENFORCED-Fremdschlüssel verwendet. Das Deaktivieren dieses Hinweises oder der Datenbankoption kann bei der Fehlerbehebung bei unerwarteten Abfrageergebnissen 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. Themen, die sowohl für informative als auch erzwungene Fremdschlüssel gelten, finden Sie unter den folgenden Links:

Neue Tabelle mit erzwungenem Fremdschlüssel erstellen

Mit DDL können Sie Fremdschlüssel in Ihrer Spanner-Datenbank erstellen, entfernen und erzwingen. 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.

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. 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.

Mit Fremdschlüsselaktionen wird festgelegt, 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 Aktion ON DELETE CASCADE für Fremdschlüssel eine Zeile löschen, die einen referenzierten Fremdschlüssel enthält, werden in derselben Transaktion auch alle Zeilen gelöscht, die auf diesen Schlüssel verweisen.

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 daraus 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 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.

Einschränkungsvalidierung

Die Validierung von Einschränkungen gilt nur für erzwungene Fremdschlüssel.

Spanner prüft die erzwungenen 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 erzwungenen 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 Fremdschlüsselüberprüfung 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 erzwungene 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 Speicherplatz erforderlich.

Lange dauernde Löschkaskadenaktion

Wenn Sie eine Zeile aus einer referenzierten Tabelle löschen, muss Spanner alle Zeilen in den referenzierten Tabellen löschen, die auf die gelöschte Zeile verweisen. Dies kann zu einem Dominoeffekt 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 der Löschkaskadenaktion hinzufügen oder eine Tabelle mit Fremdschlüsseleinschränkungen mit der Löschkaskadenaktion erstellen, können Löschvorgänge verlangsamt werden.

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

Das Löschen einer großen Anzahl von Einträgen mithilfe einer Löschkaskade für einen 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 Einträgen mithilfe einer Löschkaskade für einen Fremdschlüssel löschen möchten, müssen Sie die Zeilen aus den untergeordneten Tabellen explizit löschen, 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 über-/untergeordnete 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 wir 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.

Angenommen, Ihre Kunden möchten pro Bestellung mehr als ein Produkt bestellen können. 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 mit einer 1:n-Beziehung zwischen „Orders“ und der neuen, verschränkten Tabelle „OrderItems“

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 umfangreichen kaskadierenden Löschvorgänge funktionieren gut für Tabellen mit einer Beziehung vom Typ „in übergeordnete Tabelle verschachtelt“, 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 zuerst die Zeilen aus den untergeordneten Tabellen explizit 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 übergeordnetem Element 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. Für erzwungene und informative Fremdschlüssel werden in Spanner Sicherungsindizes unterschiedlich erstellt:

  • Für erzwungene Fremdschlüssel kann Spanner für jeden Fremdschlüssel bis zu zwei sekundäre Sicherungsindizes 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 informative Fremdschlüssel wird kein Sicherungsindex für die referenzierenden Spalten erstellt.

Sowohl erzwungene als auch informative Fremdschlüssel verweisen in der Regel auf die Primärschlüssel der referenzierten Tabelle. Daher ist in der Regel kein Index für die referenzierte Tabelle erforderlich. Aus diesem Grund haben informative Fremdschlüssel in der Regel keine Sicherungsindizes. Bei Bedarf ist der Sicherungsindex, der für die verwiesene Tabelle erstellt wird, ein UNIQUE NULL_FILTERED-Index. Das Erstellen des Fremdschlüssels schlägt fehl, wenn vorhandene Daten gegen die Eindeutigkeitsbeschränkung des Index verstoßen.

Informationsfremdschlüssel haben keinen Sicherungsindex für die referenzierende Tabelle. 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 einzelnen Index für jeden. 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.

Spanner verwendet das Informationsschema jeder Datenbank, um Metadaten zu unterstützenden Indexen zu speichern. Zeilen in INFORMATION_SCHEMA.INDEXES mit dem SPANNER_IS_MANAGED-Wert true beschreiben Indexe für die Sicherung.

Außer bei SQL-Abfragen, die das Informationsschema direkt aufrufen, werden in der Google Cloud Console keine Informationen zu den unterstützenden 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 lange laufende Vorgang abgeschlossen ist.

In der folgenden Tabelle wird beschrieben, was in Spanner passiert, wenn sich ein erzwungener und ein informativer Fremdschlüssel in einer neuen oder vorhandenen Tabelle befinden:

Tabellentyp Erzwungene Fremdschlüssel Informativer Fremdschlüssel
Neu Spanner füllt die referenzierten Indexe nach Bedarf für jeden Fremdschlüssel auf. Spanner füllt die referenzierten Indexe nach Bedarf für jeden Fremdschlüssel auf.
Vorhanden Spanner füllt die referenzierenden und referenzierten Indexe nach Bedarf auf. Außerdem validiert Spanner 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.
  • Änderung der Erzwingung eines vorhandenen Fremdschlüssels

In beiden Fällen empfehlen wir Folgendes:

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

Wenn Sie eine neue Einschränkung hinzufügen und die alte aufheben, wird das Problem Langwieriger Vorgang zum Ändern von Einschränkungen verhindert. Angenommen, Sie möchten einer vorhandenen Fremdschlüssel-Beziehung eine DELETE CASCADE-Aktion hinzufügen. Nachdem Sie den neuen Fremdschlüssel mit der Aktion ON DELETE CASCADE erstellt haben, führt die Kombination der beiden Einschränkungen zu einer 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 auch die Sicherungsindizes für Fremdschlüssel gelöscht werden, wenn die Indizes nicht von anderen Fremdschlüsseleinschränkungen verwendet werden. Wenn Sie also zuerst die alte Einschränkung löschen und dann dieselbe Fremdschlüsseleinschränkung mit einer Aktion hinzufügen, kann dies zu langwierigen Vorgängen wie dem Backfilling von Indexen, der Validierung von Einschränkungen für eindeutige Indexe oder der Validierung von referenziellen Fremdschlüsseleinschränkungen führen.

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

Nächste Schritte