Fremdschlüssel

In diesem Thema werden Fremdschlüssel in Cloud Spanner beschrieben und wie sie verwendet werden können, um die referenzielle Integrität in Ihrer Datenbanklösung zu erzwingen.

Einführung

Mit Fremdschlüsseln können Sie Beziehungen zwischen Tabellen definieren. Cloud Spanner stellt sicher, dass die Datenintegrität dieser Beziehungen 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.

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, das Kunden bestellen können.

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

• Eine Bestellung wurde von einem Kunden aufgegeben

• Es wurde eine Bestellung für ein Produkt aufgegeben

Sie entscheiden, dass Ihre Datenbank die folgenden Regeln erzwingen soll, um sicherzustellen, dass Bestellungen in unserem 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 wir diese Regeln oder Einschränkungen erzwingen, sagen wir, dass wir die referenzielle Integrität unserer Daten aufrechterhalten. 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. Cloud Spanner erzwingt die referenzielle Integrität durch Fremdschlüssel.

Erzwingen der referenziellen Integrität mit Fremdschlüsseln

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

Abbildung 2. Diagramm unseres 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 stellt sicher, dass alle Zeilen in Orders ein gültiges CustomerID haben. Der Fremdschlüssel FK_ProductOrder sorgt dafür, 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	Stellt sicher, dass alle Zeilen in Orders eine gültige CustomerID haben	Eine Bestellung wurde von einem gültigen Kunden aufgegeben
FK_ProductOrder	Stellt sicher, dass alle Zeilen in Orders eine gültige ProductID haben	Es wurde eine Bestellung für ein gültiges Produkt aufgegeben

Jede Transaktion in Cloud Spanner schlägt fehl, 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 Cloud Spanner Einschränkungen validiert, finden Sie unter Überprüfung der Transaktionsbeschränkung unten.

Definieren von Fremdschlüsseln

Fremdschlüssel werden mithilfe von DDL erstellt und aus Ihrer Cloud 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 Fremdschlüssel.

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

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

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

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

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

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

• Wie im obigen Beispiel können Sie jede Einschränkung für einen externen Schlüssel benennen. Wenn Sie keinen Namen angeben, generiert Cloud Spanner automatisch einen Namen. Der generierte Name kann über die INFORMATION_SCHEMA von Cloud Spanner abgefragt werden. 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 auf die erste referenzierte Spalte, die zweite auf die zweite Spalte usw.

• Eine verweisende Spalte und ihr referenziertes Gegenstück müssen vom selben Typ sein. Die Spalten müssen ebenfalls indexierbar sein.

• 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 referenzieren (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. Daher muss mindestens einer der Schlüssel mit der Anweisung ALTER TABLE hinzugefügt werden.

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

• Cloud Spanner kann möglicherweise auch den Primärschlüssel der Referenztabelle verwenden, obwohl dies seltener vorkommt. Ist dies nicht der Fall, wird von Cloud Spanner ein Wert von NULL_FILTERED INDEX für die referenzierenden Spalten erstellt.

• Bei Fremdschlüsseln werden keine von Ihnen erstellten sekundären Indexe verwendet. Sie erstellen ihre eigenen Sicherungsindizes. Diese Backing-Indexe können in Abfrageauswertungen verwendet werden, einschließlich expliziter Anweisungen vom Typ "force_index". Der Name der Sicherungsindizes kann über INFORMATION_SCHEMA von Cloud Spanner abgefragt werden. Weitere Informationen finden Sie unter Indexe sichern.

Lang andauernde Schemaänderungen

Das Hinzufügen eines 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.

Beim Erstellen einer neuen Tabelle mit einem Fremdschlüssel muss Cloud Spanner die referenzierten Indexe nach Bedarf für jeden Fremdschlüssel anpassen.

Wenn Sie einer vorhandenen Tabelle einen Fremdschlüssel hinzufügen, muss Cloud Spanner die referenzierenden und referenzierten Indexe nach Bedarf auffüllen. Darüber hinaus validiert Cloud 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.

Eine der oben genannten Schemaänderungen kann fehlschlagen, wenn der Index, auf den verwiesen wird, aufgrund eines UNIQUE-Einschränkungsverstoßes nicht erstellt werden kann.

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

Einschränkungsvalidierung für eine Transaktion

Cloud Spanner prüft die Bedingungen für Fremdschlüssel, 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(n) 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.

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

Indizes sichern

Bei Fremdschlüsseln werden keine von Nutzern erstellten Indexe verwendet. Sie erstellen ihre eigenen Sicherungsindizes.

Cloud Spanner kann 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. Ein Fremdschlüssel verweist jedoch in der Regel auf die Primärschlüssel der referenzierten Tabelle, sodass der zweite Index in der referenzierten Tabelle in der Regel nicht benötigt wird.

Der Sicherungsindex für die referenzierte Tabelle ist ein UNIQUE NULL_FILTERED-Index. Das Erstellen des Fremdschlüssels schlägt fehl, wenn vorhandene Daten gegen die Eindeutigkeitsbeschränkung des Index verstoßen. Der Sicherungsindex für die referenzierende Tabelle beträgt NULL_FILTERED.

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

Die Back-End-Indexe für Fremdschlüssel können in Abfrageauswertungen verwendet werden. Ihre Namen können von Cloud Spanner INFORMATION_SCHEMA abgefragt werden.

Vergleich von Fremdschlüsseln und Tabellenverschränkung

Die Tabellenverschränkung von Cloud 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. Die gemeinsame Anordnung untergeordneter Zeilen mit den übergeordneten Zeilen 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.

Sehen wir uns ein Beispiel an, das das zuvor in diesem Thema erläuterte Schema zur Bestellverarbeitung verwendet. Zur Erinnerung: Die Tabelle Orders wurde so definiert:

Abbildung 3. Diagramm unseres Datenbankschemas mit Fremdschlüsseln

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

Nehmen wir einmal an, unsere Kunden möchten uns mitteilen, dass sie pro Bestellung mehr als ein Produkt bestellen möchten. Wir können unser Design verbessern, indem wir eine OrderItems-Tabelle einführen, die einen Eintrag für jedes Produkt enthält, das der Kunde bestellt hat. Wir können einen weiteren Fremdschlüssel einführen, um diese neue 1: n-Beziehung zwischen Orders und OrderItems darzustellen. Wir wissen aber auch, dass wir häufig Abfragen für Aufträge und ihre jeweiligen Auftragselemente ausführen möchten. Die gemeinsame Speicherung dieser Daten würde die Leistung steigern. Daher erstellen wir die über-/untergeordnete Beziehung mithilfe der Tabellenverschränkungsfunktion von Cloud Spanner.

So definieren wir die Tabelle OrderItems, die mit Orders verschränkt ist.

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;

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 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. Cloud Spanner kann beispielsweise Joins lokal nach Primärschlüssel durchführen, um den Zugriff auf das Laufwerk und den Netzwerkverkehr zu minimieren.

Hinweis: Google empfiehlt, dass Sie hierarchische Beziehungen entweder als verschränkte Tabellen oder als Fremdschlüssel darstellen, aber nicht beides. Die Verwendung beider Funktionen ist redundant und kann zusätzliche Speicher- und Rechenressourcen verbrauchen, um Einschränkungen zu überprüfen und Sicherungsindexe zu verwalten.

Fazit

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

Nächste Schritte

• Weitere Informationen zum Erstellen und Verwalten von Fremdschlüsselbeziehungen

• Weitere Informationen zum Informationsschema von Cloud Spanner