Fremdschlüsselbeziehungen erstellen und verwalten  | Cloud Spanner  | Google Cloud (2023)

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

Ein Fremdschlüssel ist eine Spalte, die von Tabellen gemeinsam genutzt wird, um eine Verknüpfung zwischen verwandten Daten herzustellen. Wenn Sie einen Fremdschlüssel verwenden, stellt Spanner sicher, dass diese Beziehung beibehalten wird.

Das folgende Diagramm zeigt ein einfaches Datenbankschema, bei dem Daten in einer Tabelle eine Beziehung zu Daten in einer anderen Tabelle haben.

Abbildung 1.Diagramm eines Datenbankschemas für die Auftragsverarbeitung

Das in Abbildung 1 dargestellte Schema enthält drei Tabellen:

• DerKundenIn der Tabelle sind die Namen der einzelnen Kunden aufgeführt.
• DerAufträgeTabellen verfolgen alle getätigten Bestellungen.
• DerProdukteDie Tabelle speichert die Produktinformationen für jedes Produkt.

Zwischen diesen Tabellen bestehen zwei Fremdschlüsselbeziehungen:

• Zwischen den wird eine Fremdschlüsselbeziehung definiertAufträgeTisch und dieKundenTabelle, um sicherzustellen, dass eine Bestellung nur dann erstellt werden kann, wenn ein entsprechender Kunde vorhanden ist.

• Eine Fremdschlüsselbeziehung zwischen demAufträgeTisch und dieProdukteDie Tabelle stellt sicher, dass keine Bestellung für ein Produkt erstellt werden kann, das nicht existiert.

In diesem Thema wird am Beispiel des vorherigen Schemas die DataDefinition Language (DDL)ZWANGAnweisungen, die Sie zum Verwalten von Beziehungen zwischen Tabellen in einer Datenbank verwenden können.

Fügen Sie beim Erstellen einer neuen Tabelle einen Fremdschlüssel hinzu

Gehen Sie davon aus, dass Sie eine erstellt habenKundenTabelle in Ihrer einfachen Produktbestelldatenbank. Sie benötigen jetzt eineAufträgeTabelle zum Speichern von Informationen über die Bestellungen, die Kunden tätigen. Um sicherzustellen, dass alle Bestellungen gültig sind, möchten Sie nicht zulassen, dass das System Zeilen in die einfügtAufträgeTabelle, es sei denn, es gibt auch einen passenden Eintrag in derKundenTisch. Daher benötigen Sie einen Fremdschlüssel, um eine Beziehung zwischen den beiden Tabellen herzustellen. Eine Möglichkeit besteht darin, a hinzuzufügenKundennummerFügen Sie die Spalte der neuen Tabelle hinzu und verwenden Sie sie als Fremdschlüssel, um eine Beziehung mit der herzustellenKundennummerSpalte in derKundenTisch.

Wenn Sie eine neue Tabelle mit einem Fremdschlüssel erstellen, verwenden SieREFERENZum eine Beziehung zu einer anderen Tabelle herzustellen. Die Tabelle, die die enthältREFERENZDie Aussage heißtReferenzierungTisch. Der in der Tabelle benannteREFERENZAussage ist dieverwiesenTisch. Die Spalte, die im benannt istREFERENZDie Aussage heißtReferenzierungSpalte.

Das folgende Beispiel zeigt die Verwendung vonTABELLE ERSTELLENDDL-Anweisung zum Erstellen derAufträgeTabelle mit einer Fremdschlüsseleinschränkung, die referenziertKundennummerimKundenTisch.

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

CREATE TABLE Orders ( OrderID BIGINT NOT NULL, CustomerID BIGINT NOT NULL, Menge BIGINT NOT NULL, ProductID BIGINT NOT NULL, CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Kunden (CustomerID), PRIMARY KEY (OrderID));

Die vorherige Anweisung enthält aZWANGKlausel mit den folgenden Merkmalen:

• Nutzung derZWANGSyntax zum Benennen einer Einschränkung, wodurch es einfacher wird, die Tabelle mit dem von Ihnen gewählten Namen zu löschen.

• Die Einschränkung hat den NamenFK_CustomerOrder. Einschränkungsnamen sind auf das Schema beschränkt und müssen innerhalb des Schemas eindeutig sein.

• DerAufträgeDie Tabelle, für die Sie die Einschränkung definieren, ist die referenzierende Tabelle. DerKundenTabelle ist die referenzierte Tabelle.

• Die referenzierende Spalte in der referenzierenden Tabelle istKundennummer. Es verweist auf dieKundennummerFeld in derKundenTisch. Wenn jemand versucht, eine Zeile einzufügenAufträgemit einemKundennummerdas gibt es nicht inKunden, schlägt die Einfügung fehl.

Das folgende Beispiel zeigt eine alternative Anweisung zur Tabellenerstellung. Hier wird die Fremdschlüsseleinschränkung ohne Namen definiert. Wenn Sie diese Syntax verwenden, generiert Spanner einen Namen für Sie. Informationen zum Ermitteln der Namen aller Fremdschlüssel finden Sie unterEigenschaften einer Fremdschlüsselbeziehung anzeigen.

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

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

Fügen Sie einer vorhandenen Tabelle einen Fremdschlüssel hinzu

Außerdem möchten Sie sicherstellen, dass Kunden nur Produkte bestellen können, die es gibt. Zuerst müssen Sie die bestehende Einschränkung löschen. Dann können Sie es verwendenTABELLE ÄNDERNum eine weitere Fremdschlüsseleinschränkung hinzuzufügenAufträgeTabelle, wie im Folgenden gezeigt:

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

Die verweisende Spalte inAufträgeIstProdukt ID, und es verweist auf dieProdukt IDSpalte in Produkte. Auch hier gilt: Wenn Sie damit einverstanden sind, dass Spanner diese Einschränkungen für Sie benennt, verwenden Sie die folgende Syntax:

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

Fügen Sie beim Erstellen einer neuen Tabelle einen Fremdschlüssel mit einer Löschaktion hinzu

Erinnern Sie sich an das vorherige Beispiel, in dem Sie eine habenKundenTabelle in einer Produktbestellungsdatenbank, die eine benötigtAufträgeTisch. Sie möchten eine Fremdschlüsseleinschränkung hinzufügen, die auf verweistKundenTisch. Sie möchten jedoch sicherstellen, dass Spanner beim Löschen eines Kundendatensatzes in der Zukunft auch alle Bestellungen für diesen Kunden löscht. In diesem Fall möchten Sie die verwendenON CASCADE LÖSCHENAktion mit der Fremdschlüsseleinschränkung.

Die folgendeTABELLE ERSTELLENDDL-Anweisung für dieAufträgeDie Tabelle enthält die Fremdschlüsseleinschränkung, die auf verweistKundenTisch mit einemAUF DELETECASCADEAktion.

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) ON DELETE CASCADE) PRIMARY KEY (OrderID);

CREATE TABLE Orders ( OrderID BIGINT NOT NULL, CustomerID BIGINT NOT NULL, Quantity BIGINT NOT NULL, ProductID BIGINT NOT NULL, FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID) ON DELETE CASCADE, PRIMARY KEY (OrderID));

Die vorherige Anweisung enthält eine Fremdschlüsseleinschränkung mit einemON CASCADE LÖSCHENKlausel. DerKundennummerDie Spalte ist ein Fremdschlüssel, der auf die Spalte verweistKundennummerFeld in derKundenTisch. Das bedeutet, dass jederKundennummerWert in derAufträgeDie Tabelle muss auch in der vorhanden seinKundenTisch. Wenn jemand versucht, eine Zeile aus dem zu löschenKundenTabelle, alle Zeilen in derAufträgeTabelle, die auf die gelöschte Datei verweistKundennummerWert werden in derselben Transaktion ebenfalls gelöscht.

Fügen Sie einen Fremdschlüssel mit einer Löschaktion zu einer vorhandenen Tabelle hinzu

Sie möchten außerdem sicherstellen, dass Bestellungen nur für vorhandene Produkte erstellt werden. Sie können verwendenTABELLE ÄNDERNum eine weitere Fremdschlüsseleinschränkung hinzuzufügenON CASCADE LÖSCHENAktion wie folgt in die Auftragstabelle ein:

ALTER TABLE Orders ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID) ON DELETE CASCADE;

Löschen einer Zeile aus demProdukteDie Tabelle löscht alle Zeilen in derAufträgeTabelle, die auf die gelöschte Datei verweistProdukt IDWert.

Fragen Sie Daten über Fremdschlüsselbeziehungen ab

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

Beispiele dafür, wie Spanner die referenzielle Integrität aufrechterhält

Der Hauptgrund für das Hinzufügen von Fremdschlüsselbeziehungen besteht darin, dass Spanner diese beibehalten kannreferenzielle IntegritätIhrer Daten. Wenn Sie Daten so ändern, dass eine Fremdschlüsseleinschränkung aufgehoben wird, schlägt die Aktualisierung mit einem Fehler fehl.

Betrachten Sie die Daten in Abbildung 2. Einige Kunden haben Produkte bestellt, wie in der Bestelltabelle dargestellt. Aufgrund der vorhandenen Fremdschlüssel können Sie garantieren, dass die Daten, die in das eingefügt wurdenAufträgeTabelle hat referenzielle Integrität.

Figur 2.Beispieldaten für unsere Bestelldatenbank.

Die folgenden Beispiele zeigen, was passiert, wenn Sie versuchen, die Daten so zu ändern, dass die referenzielle Integrität beeinträchtigt wird.

• Fügen Sie eine Zeile hinzuAufträgeTisch mit aKundennummerWert, der nicht existiertKunden

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

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

  In diesem Fall würde das System versuchen, eine Zeile einzufügenAufträgemit einemKundennummer(447), das in der nicht existiertKundenTisch. Wenn das System dies tun würde, hätten Sie eine ungültige Bestellung in Ihrem System. Allerdings mit der Fremdschlüsseleinschränkung, die Sie zu Ihrem hinzugefügt habenAufträgeTisch, Ihr Tisch ist geschützt. DerEINFÜGENschlägt mit der folgenden Meldung fehl, sofern theconstraint aufgerufen wirdFK_CustomerOrder.

  Die Fremdschlüsseleinschränkung „FK_CustomerOrder“ ist in der Tabelle „Orders“ verletzt. In „Kunden“ (CustomerID) können keine referenzierten Werte gefunden werden.

• Versuchen Sie, eine Zeile aus dem zu löschenKundenTabelle, wenn der Kunde in einer Fremdschlüsseleinschränkung referenziert wird.

  Stellen Sie sich eine Situation vor, in der sich ein Kunde von Ihrem Online-Shop abmeldet. Sie möchten den Kunden aus Ihrem Backend entfernen und versuchen daher den folgenden Vorgang.

  DELETE FROM Customers WHERE CustomerID = 721;

  In diesem Beispiel erkennt Spanner anhand der Fremdschlüsseleinschränkung, dass noch Datensätze in der Datei vorhanden sindAufträgeTabelle, die auf die Kundenzeile verweist, die Sie löschen möchten. In diesem Fall wird der folgende Fehler angezeigt.

  Verletzung der Fremdschlüsseleinschränkung beim Löschen oder Aktualisieren referenzierter Zeilen: Verweis auf Zeile(n), die in der Tabelle „Orders“ gefunden wurden.

  Um dieses Problem zu beheben, löschen Sie alle verweisenden Einträge inAufträgeErste. Sie können den Fremdschlüssel auch mit definierenON CASCADE LÖSCHENAktion, mit der Spanner das Löschen referenzierender Einträge übernehmen kann.

Eigenschaften einer Fremdschlüsselbeziehung anzeigen

SpannersINFORMATION_SCHEMAenthält Informationen über Fremdschlüssel und deren Sicherungsindizes. Im Folgenden finden Sie einige Beispiele für Fragen, die Sie durch Abfragen des INFORMATIONSSCHEMAs beantworten können.

Weitere Informationen zum Sichern von Indizes finden Sie unterFremdschlüssel, die Indizes unterstützen.

Welche Einschränkungen sind in meiner Datenbank definiert?

SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPEFROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tcWHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY';

Welche Fremdschlüssel sind in meiner Datenbank definiert?

SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.SPANNER_STATEFROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;

Welche Indizes sind Sekundärindizes für Fremdschlüssel, auch Backing-Indizes genannt?

Fremdschlüssel-Backing-Indizes werden von Spanner verwaltet, also Abfragen nachSPANNER_IS_MANAGEDauf derINDIZESview gibt alle unterstützenden Indizes zurück.

SELECT i.TABLE_NAME, i.INDEX_NAME, i.INDEX_TYPE, i.INDEX_STATE, i.IS_UNIQUE, i.IS_NULL_FILTERED, i.SPANNER_IS_MANAGEDFROM INFORMATION_SCHEMA.INDEXES as iWHERE SPANNER_IS_MANAGED = 'YES';

Was ist die referenzielle Aktion, die mit der Fremdschlüsseleinschränkung definiert wird?

SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.DELETE_RULE, rc.UPDATE_RULEFROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;

Weitere Informationen finden Sie unterInformationsschema.

Entfernen Sie eine Fremdschlüsselbeziehung

Die folgende DDL löscht eine Fremdschlüsseleinschränkung aus demAufträgeTisch.

ALTER TABLE Orders DROP CONSTRAINT FK_CustomerOrder;

Die Fremdschlüssel-Backing-Indizes werden automatisch gelöscht, wenn die Einschränkung selbst gelöscht wird.

Unterstützung für komplexere Fremdschlüsselbeziehungen

Mehrere Spalten

Fremdschlüssel können auf mehrere Spalten verweisen. Die Liste der Spalten bildet einen Schlüssel, der dem Primärschlüssel einer Tabelle oder einem Hintergrundindex entspricht. Die referenzierende Tabelle enthält Fremdschlüssel des referenzierten Tabellenschlüssels.

Im folgenden Beispiel teilen die Fremdschlüsseldefinitionen Spannerthat jeweils mitLiedtitelDer Wert in der TopHits-Tabelle muss einen übereinstimmenden Wert in der Songs-Tabelle haben. und jedeSängerVornameUndSängerNachnameWertepaar muss übereinstimmenVornameUndFamilienname, NachnameWertepaar im Singerstable.

CREATE TABLE TopHits ( Rank INT64 NOT NULL, SongName STRING(MAX), SingerFirstName STRING(MAX), SingerLastName STRING(MAX), – Songnamen müssen entweder NULL sein oder übereinstimmende Werte in Songs haben. FOREIGN KEY (SongName) REFERENZEN Songs ( SongName), – Sängernamen müssen entweder NULL sein oder übereinstimmende Werte in Singers haben. FOREIGN KEY (SingerFirstName, SingerLastName) REFERENCES Singers (FirstName, LastName)) PRIMARY KEY (Rang);

CREATE TABLE TopHits ( Rank BIGINT NOT NULL, SongName VARCHAR, SingerFirstName VARCHAR, SingerLastName VARCHAR, -- Songnamen müssen entweder NULL sein oder übereinstimmende Werte in Songs haben. FOREIGN KEY (SongName) REFERENCES Songs (SongName), -- Sängernamen müssen entweder NULL sein oder übereinstimmende Werte in Songs haben NULL sein oder übereinstimmende Werte in Singers haben. FOREIGN KEY (SingerFirstName, SingerLastName) REFERENCES Singers (FirstName, LastName), PRIMARY KEY (Rang));

Zirkelverweise

Gelegentlich weisen Tabellen zirkuläre Abhängigkeiten auf, möglicherweise aus Legacy-Gründen oder aufgrund einer Denormalisierung. Spanner-Fremdschlüssel erlauben Zirkelverweise. Da eine referenzierte Tabelle vorhanden sein muss, bevor ein Fremdschlüssel darauf verweisen kann, muss einer der Fremdschlüssel mit einem hinzugefügt werdenTABELLE ÄNDERNStellungnahme. Hier ist ein Beispiel

1. Erstellen Sie TableA ohne Fremdschlüssel
2. Erstellen Sie TableB mit einer Fremdschlüsseleinschränkung für TableA.
3. VerwendenTABELLE ÄNDERNauf TableA, um einen Fremdschlüsselverweis auf TableB zu erstellen.

Selbstreferenzierende Tabellen

Eine spezielle Art von Zirkelverweis ist eine Tabelle, die einen Fremdschlüssel definiert, der auf dieselbe Tabelle verweist. Das folgende Snippet zeigt beispielsweise einen Fremdschlüssel, um zu erzwingen, dass die ManagerId eines Mitarbeiters auch ein Mitarbeiter ist.

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

CREATE TABLE Employees ( EmployeeId BIGINT NOT NULL, EmployeeName VARCHAR NOT NULL, ManagerId BIGINT, FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId), PRIMARY KEY (EmployeeId));

Was kommt als nächstes

• Lerne mehr überFremdschlüsselunterstützung in Spanner.

• Erfahren Sie mehr über Spanner'sINFORMATIONSSCHEMA.