Fremdschlüsselbeziehungen erstellen und verwalten

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 einen Verknüpfung zwischen verwandten Daten. Wenn Sie einen Fremdschlüssel verwenden, stellt Spanner sicher, dass diese Beziehung aufrechterhalten wird.

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

Abbildung 1. Diagramm einer Auftragsverarbeitungsdatenbank

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

• In der Tabelle Customers werden die Namen jedes Kunden aufgezeichnet.
• Die Tabellen Orders verfolgen alle Bestellungen.
• In der Tabelle Products werden die Produktinformationen für jedes Produkt gespeichert.

Zwischen diesen Tabellen gibt es zwei Fremdschlüsselbeziehungen:

• Zwischen der Tabelle Orders und der Tabelle Customers wird eine Fremdschlüsselbeziehung definiert, um sicherzustellen, dass eine Bestellung nur dann angelegt werden kann, wenn eine entsprechender Kunde vorhanden ist.

• Eine Fremdschlüsselbeziehung zwischen der Tabelle Orders und der Tabelle Products stellt sicher, dass keine Bestellung für ein nicht vorhandenes Produkt erstellt werden kann.

Am Beispiel des vorherigen Schemas werden in diesem Thema die CONSTRAINT-Anweisungen der Definition Language (DDL), mit denen Sie Beziehungen zwischen Tabellen in einer Datenbank.

Fremdschlüssel beim Erstellen einer neuen Tabelle hinzufügen

Angenommen, Sie haben in Ihrem einfachen Produkt eine Customers-Tabelle erstellt. Bestelldatenbank. Sie benötigen jetzt eine Orders-Tabelle, um Informationen zum der Kundschaft. Um sicherzustellen, dass alle Bestellungen gültig sind, Lassen Sie das System Zeilen in die Tabelle Orders einfügen, es sei denn, es gibt auch einen übereinstimmender Eintrag in der Tabelle Customers. Daher benötigen Sie einen Fremdschlüssel, stellen eine Beziehung zwischen den beiden Tabellen her. Eine Möglichkeit besteht darin, CustomerID in die neue Tabelle und verwenden sie als Fremdschlüssel, um einen Beziehung zur Spalte CustomerID in der Tabelle Customers.

Wenn Sie eine neue Tabelle mit einem Fremdschlüssel erstellen, verwenden Sie REFERENCE um eine Beziehung zu einer anderen Tabelle herzustellen. Tabelle die die REFERENCE-Anweisung enthält, wird als Verweistabelle bezeichnet. Die Die in der REFERENCE-Anweisung angegebene Tabelle ist die referenzierte Tabelle. Die Spalte die in der REFERENCE-Anweisung angegeben ist, wird als Verweis-Spalte bezeichnet.

Das folgende Beispiel zeigt, wie Sie mit der DDL-Anweisung CREATE TABLE die Tabelle Orders mit einer Fremdschlüsseleinschränkung erstellen, die auf CustomerID in der Tabelle Customers.

GoogleSQL

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

PostgreSQL

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

Die vorherige Anweisung enthält eine CONSTRAINT-Klausel mit Eigenschaften:

• Die Verwendung der Syntax CONSTRAINT zum Benennen einer Einschränkung, sodass sie um die Tabelle mit dem von Ihnen gewählten Namen zu löschen.

• Die Einschränkung hat den Namen FK_CustomerOrder. Einschränkungsnamen gelten für und muss innerhalb des Schemas eindeutig sein.

• Die Tabelle Orders, für die Sie die Einschränkung definieren, ist der auf die Tabelle verweisen. Die Tabelle Customers ist die Tabelle, auf die verwiesen wird.

• Die referenzierende Spalte in der verweisenden Tabelle ist CustomerID. Es verweist auf das Feld CustomerID in der Tabelle Customers. Wenn jemand versucht, um eine Zeile in Orders mit einer CustomerID einzufügen, die in Customers, das Einfügen schlägt fehl.

Hinweis: Häufig entsprechen die Referenzspalten der Referenztabelle den Primärschlüsselspalten. Dies ist jedoch nicht erforderlich. Eine Referenztabelle kann auch auf nicht primäre Spalten einer Referenztabelle verweisen.

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 unter Eigenschaften einer Fremdschlüsselbeziehung ansehen.

GoogleSQL

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

PostgreSQL

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

Fremdschlüssel einer vorhandenen Tabelle hinzufügen

Achten Sie außerdem darauf, dass Kunden nur Produkte bestellen können, die existieren. Sie müssen zuerst die vorhandene Einschränkung löschen. Dann können Sie ALTER TABLE, um der Tabelle Orders eine weitere Fremdschlüsseleinschränkung hinzuzufügen: wie im Folgenden dargestellt:

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

Die Referenzspalte in Orders ist ProductID und verweist auf die Spalte ProductID in Produkten. Auch hier gilt: Wenn Cloud Spanner Sie die Namen -Einschränkungen, verwenden Sie die folgende Syntax:

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

Fremdschlüssel beim Erstellen einer neuen Tabelle mit einer Löschaktion hinzufügen

Erinnern Sie sich an das vorherige Beispiel, in dem Sie eine Customers-Tabelle in einem Produkt haben. die eine Orders-Tabelle benötigt. Sie möchten einen Fremdschlüssel Einschränkung, die auf die Tabelle Customers verweist. Sie sollten jedoch sicherstellen, dass Spanner, wenn Sie einen Kundendatensatz in Zukunft löschen, werden auch alle Bestellungen für diesen Kunden gelöscht. In diesem Fall verwenden Sie die Methode ON DELETE CASCADE-Aktion mit der Fremdschlüsseleinschränkung.

Die folgende DDL-Anweisung CREATE TABLE für die Tabelle Orders enthält den Fremdschlüsseleinschränkung, die auf die Tabelle Customers mit einer ON DELETE CASCADE-Aktion verweist.

GoogleSQL

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

PostgreSQL

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 einem ON DELETE CASCADE-Klausel angegeben werden. Die Spalte CustomerID ist ein Fremdschlüssel, der verweist auf das Feld CustomerID in der Tabelle Customers. Das bedeutet, dass jeder Der Wert für „CustomerID“ in der Tabelle „Orders“ muss auch in der Tabelle „Customers“ vorhanden sein . Wenn jemand versucht, eine Zeile aus der Tabelle Customers zu löschen, werden alle Zeilen in der Tabelle Orders, die auf den gelöschten Wert CustomerID verweisen, sind in derselben Transaktion gelöscht.

Hinweis: Wenn Sie keine Fremdschlüsselaktion angeben, ist die Standardaktion NO ACTION Wenn also jemand versucht, eine Zeile aus der Tabelle Customers enthält und es gibt Zeilen in Tabelle Orders, die auf den gelöschten CustomerID-Wert hat, schlägt der Löschvorgang mit einem Fremdschlüssel fehl Fehler aufgrund eines Einschränkungsverstoßes.

Fremdschlüssel mit einer Löschaktion zu einer vorhandenen Tabelle hinzufügen

Sie möchten außerdem sicherstellen, dass Aufträge nur für Produkte erstellt werden, die existieren. Sie können ALTER TABLE verwenden, um eine weitere Fremdschlüsseleinschränkung mit ON DELETE CASCADE-Aktion für die Auftragstabelle:

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

Wenn Sie eine Zeile aus der Tabelle Products löschen, werden alle Zeilen im Orders-Tabelle, die auf den gelöschten ProductID-Wert verweist.

Hinweis: Fremdschlüsselaktion in einem vorhandenen Fremdschlüssel hinzufügen oder verwerfen -Einschränkung wird nicht unterstützt. Sie müssen eine neue Fremdschlüsseleinschränkung mit eine Aktion ausführen. Weitere Informationen finden Sie unter Schemaänderungen mit langer Ausführungszeit.

Daten über Fremdschlüsselbeziehungen abfragen

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 wahrt

Der Hauptgrund für das Hinzufügen von Fremdschlüsselbeziehungen besteht darin, dass Spanner die referenzielle Integrität von Ihre Daten. Wenn Sie Daten so ändern, dass sie gegen eine Fremdschlüsseleinschränkung verstoßen, Die Aktualisierung schlägt mit einem Fehler fehl.

Betrachten Sie die Daten in Abbildung 2. Einige Kunden haben Produkte bestellt, wie in der Tabelle "Bestellungen" angegeben. Aufgrund der vorhandenen Fremdschlüssel garantiert, dass die in die Tabelle Orders eingefügten Daten referenzielle Integrität.

Abbildung 2. Beispieldaten in unserer Bestelldatenbank.

Die folgenden Beispiele zeigen, was passiert, wenn Sie versuchen, die Daten in einem die referenzielle Integrität beeinträchtigen würde.

• Fügen Sie der Tabelle Orders eine Zeile mit einem CustomerID-Wert hinzu, der nicht existiert in Customers

  Was passiert, wenn Sie die folgende Änderung anhand der Beispieldaten aus Diagramm zeichnen?

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

  In diesem Fall würde das System versuchen, in Orders eine Zeile mit einem CustomerID (447). Dieser Wert ist in der Tabelle Customers nicht vorhanden. Wenn das System würden Sie eine ungültige Bestellung in Ihrem System haben. Mit dem Parameter Fremdschlüsseleinschränkung, die Sie der Tabelle Orders hinzugefügt haben, ist Ihre Tabelle geschützt sind. Der INSERT schlägt mit der folgenden Meldung fehl, sofern die heißt FK_CustomerOrder.

  Foreign key constraint `FK_CustomerOrder` is violated on table `Orders`.
  Cannot find referenced values in Customers(CustomerID).
  

• Versuchen Sie, eine Zeile aus der Tabelle Customers zu löschen, wenn der Kunde auf die in einer Fremdschlüsseleinschränkung verwiesen wird.

  Stellen Sie sich eine Situation vor, in der ein Kunde das Abo Ihres Onlineshops beendet. Da Sie den Kunden aus Ihrem Backend entfernen möchten, versuchen Sie Folgendes: .

  DELETE FROM Customers WHERE CustomerID = 721;
  

  In diesem Beispiel erkennt Spanner über die Fremdschlüsseleinschränkung dass es in der Tabelle Orders noch Datensätze gibt, die auf den Kunden verweisen die Sie löschen möchten. In diesem Fall wird der folgende Fehler angezeigt.

  Foreign key constraint violation when deleting or updating referenced row(s): referencing row(s) found in table `Orders`.

  Löschen Sie zuerst alle verweisenden Einträge in Orders, um dieses Problem zu beheben. Ich den Fremdschlüssel auch mit der Aktion ON DELETE CASCADE definieren, Spanner steuert das Löschen von verweisenden Einträgen.

Eigenschaften einer Fremdschlüsselbeziehung anzeigen

INFORMATION_SCHEMA von Spanner enthält Informationen zu Fremdschlüsseln und ihre Back-up-Indexe. Im Folgenden finden Sie einige Beispiele für Fragen, die Sie durch Abfragen des INFORMATION SCHEMA beantworten können.

Weitere Informationen zum Sichern von Indizes finden Sie unter Sicherungsindex für Fremdschlüssel.

Welche Einschränkungen sind in meiner Datenbank definiert?

SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY';

Welche Fremdschlüssel sind in meiner Datenbank definiert?

SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.SPANNER_STATE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;

Welche Indexe sind sekundäre Indexe für Fremdschlüssel, die auch als Sicherungsindexe bezeichnet werden?

Fremdschlüssel-Sicherungsindexe werden von Spanner verwaltet. Daher ist die Abfrage von SPANNER_IS_MANAGED in der Ansicht INDEXES gibt alle unterstützenden Indexe 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_MANAGED
FROM INFORMATION_SCHEMA.INDEXES as i
WHERE SPANNER_IS_MANAGED = 'YES';

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

SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.DELETE_RULE,
  rc.UPDATE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;

Weitere Informationen finden Sie im Informationsschema.

Fremdschlüsselbeziehung entfernen

Die folgende DDL löscht eine Fremdschlüsseleinschränkung aus der Tabelle Orders.

ALTER TABLE Orders
  DROP CONSTRAINT FK_CustomerOrder;

Die Fremdschlüssel-Sicherungsindexe werden automatisch entfernt, wenn die Beschränkung selbst verworfen wird.

Unterstützung von komplexeren 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 Sicherungsindex entspricht. Die Referenztabelle enthält Fremdschlüssel des referenzierten Tabellenschlüssels.

Im folgenden Beispiel teilen die Fremdschlüsseldefinitionen Spanner mit Jeder SongName-Wert in der TopHits-Tabelle muss einen übereinstimmenden Wert in Tabelle „Songs“ und jedes SingerFirstName- und SingerLastName-Wertpaar muss ein übereinstimmendes Wertepaar FirstName und LastName in der Tabelle "sing" haben. .

GoogleSQL

CREATE TABLE TopHits (
  Rank INT64 NOT NULL,
  SongName STRING(MAX),
  SingerFirstName STRING(MAX),
  SingerLastName STRING(MAX),

  -- Song names must either be NULL or have matching values in Songs.
  FOREIGN KEY (SongName) REFERENCES Songs (SongName),

  -- Singer names must either be NULL or have matching values in Singers.
  FOREIGN KEY (SingerFirstName, SingerLastName)
  REFERENCES Singers (FirstName, LastName)

) PRIMARY KEY (Rank);

PostgreSQL

CREATE TABLE TopHits (
  Rank BIGINT NOT NULL,
  SongName VARCHAR,
  SingerFirstName VARCHAR,
  SingerLastName VARCHAR,

  -- Song names must either be NULL or have matching values in Songs.
  FOREIGN KEY (SongName) REFERENCES Songs (SongName),

  -- Singer names must either be NULL or have matching values in Singers.
  FOREIGN KEY (SingerFirstName, SingerLastName)
  REFERENCES Singers (FirstName, LastName),

  PRIMARY KEY (Rank)
);

Zirkelverweise

Gelegentlich haben Tabellen Zirkelverweise, möglicherweise aus Legacy-Gründen oder aufgrund von Denormalisierung. Spanner-Fremdschlüssel lassen Zirkelbezüge zu. Da eine referenzierte Tabelle vorhanden sein muss, bevor ein Fremdschlüssel darauf verweisen kann, muss einer der Fremdschlüssel mit einer ALTER TABLE-Anweisung hinzugefügt werden. Beispiel:

1. Erstellen Sie TabelleA ohne einen Fremdschlüssel
2. Erstellen Sie TabelleB mit einer Fremdschlüsseleinschränkung für TabelleA.
3. Verwenden Sie ALTER TABLE in TabelleA, um einen Fremdschlüsselverweis auf TabelleB zu erstellen.

Auf sich selbst referenzierende Tabellen

Ein spezieller Typ von Zirkelverweis ist eine Tabelle, die einen Fremdschlüssel definiert, der auf dieselbe Tabelle verweist. Das folgende Snippet zeigt beispielsweise einen Fremdschlüssel, der erzwingt, dass die Manager-ID eines Mitarbeiters auch ein Mitarbeiter ist.

GoogleSQL

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

PostgreSQL

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

Nächste Schritte

• Weitere Informationen zur Unterstützung von Fremdschlüsseln in Spanner

• Weitere Informationen zum INFORMATION-SCHEMA von Spanner.