Créer et gérer des relations de clés étrangères

Cette page explique comment gérer les relations de clé étrangère dans votre base de données.

Les clés étrangères vous permettent de définir des relations entre les tables. Cloud Spanner garantit l'intégrité référentielle entre ces tables. Le diagramme suivant illustre un schéma de base de données simple, que nous allons utiliser dans ce guide.

Exemple de schéma de base de données montrant les relations de clé étrangère entre les tables.

Figure 1 : Schéma d'une base de données de traitement des commandes

Le schéma présenté à la figure 1 comporte trois tables :

  • La table Customers enregistre les noms de chaque client.
  • Les tables Orders permettent d'effectuer le suivi de toutes les commandes passées.
  • La table Products stocke les informations sur chaque produit.

Il existe deux relations de clé étrangère entre ces tables :

  • Une relation clé étrangère est définie entre la table Orders et la table Customers pour garantir qu'une commande ne puisse pas être créée, à moins qu'il n'y ait un client correspondant.

  • Une relation de clé étrangère entre les tables Orders et Products garantit qu'il n'est pas possible de créer une commande pour un produit qui n'existe pas.

Avec ce schéma comme référence, examinons les instructions LDD (langage de définition de données) que vous pouvez utiliser pour gérer ces contraintes dans votre base de données.

Ajouter une clé étrangère lors de la création d'une table

Supposons que nous ayons déjà créé la table Customers dans notre base de données simple de commandes de produits. Nous avons besoin d'une table Orders pour stocker des informations concernant les commandes passées par les clients. Pour garantir que toutes les commandes sont valides, nous ne souhaitons pas permettre au système d'insérer dans la table "Orders" des lignes qui n'auraient pas d'entrée correspondante dans notre table Customers.

Voici l'instruction LDD CREATE TABLE pour la table Orders qui inclut la contrainte de clé étrangère faisant référence à la table Customers.

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

L'instruction précédente contient une clause CONSTRAINT que nous pouvons décrire comme suit :

  • La syntaxe de CONSTRAINT vous permet de nommer une contrainte, facilitant ainsi l'abandon à partir du nom que vous avez choisi.

  • La contrainte porte le nom FK_CustomerOrder. Le champ d'application des noms de contrainte est limité au schéma et ces noms doivent être uniques au sein du schéma.

  • La table Orders, sur laquelle nous définissons la contrainte, est appelée la table référençante. La table Customers est la table référencée.

  • La colonne de référence de la table de référence est CustomerID. Elle fait référence au champ CustomerID de la table Customers. Si quelqu'un tente d'insérer une ligne dans Orders avec un CustomerID qui n'existe pas dans la table Customers, l'insertion échouera.

L'extrait de code suivant montre une autre instruction de création de table possible. Ici, la contrainte de clé étrangère est définie sans nom. Lorsque vous utilisez cette syntaxe, Cloud Spanner génère un nom à votre place. Pour découvrir les noms de toutes les clés étrangères, reportez-vous à la section Afficher les propriétés d'une relation de clé étrangère.

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

Ajouter une clé étrangère à une table existante

Il est également recommandé de s'assurer que les commandes ne peuvent être créées que pour des produits existants. Nous allons utiliser ALTER TABLE pour ajouter une autre contrainte de clé étrangère au tableau des commandes, comme suit :

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

La colonne de référence dans Orders est ProductID et fait référence à la colonne ProductID dans la table Products. Encore une fois, si vous souhaitez que Cloud Spanner nomme ces contraintes à votre place, utilisez la syntaxe suivante :

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

Interroger des données sur des relations de clé étrangère

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

Exemple d'intégrité référentielle

Le principal motif de l'ajout de relations de clé étrangère est que Cloud Spanner peut maintenir l'intégrité référentielle de vos données. Si vous modifiez les données d'une manière qui rompt une contrainte de clé étrangère, la mise à jour échouera avec un message d'erreur.

Examinons les données de la figure 2 ci-dessous. Certains clients ont commandé des produits, comme indiqué dans la table des commandes. Grâce aux clés étrangères en place, nous pouvons garantir que les données insérées dans la table Orders ont une intégrité référentielle.

Exemples de données pour les tableaux Clients, Produits et Commandes.

Figure 2. Exemples de données dans l'ordre de bases de données.

Voyons ce qui se passe lorsque nous essayons de modifier les données afin de perturber l'intégrité référentielle.

Ajouter une ligne dans la table Orders avec une valeur CustomerID qui n'existe pas dans Customers

Que se passe-t-il si nous essayons la modification suivante, compte tenu des exemples de données du diagramme précédente ?

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

Dans ce cas, nous essayons d'insérer une ligne dans la table Orders avec un CustomerID (447) qui n'existe pas dans la table Customers. Autoriser cette opération générerait une commande non valide dans notre système. En revanche, la contrainte de clé étrangère que nous avons définie de la table Orders à la table Customers agit comme une protection. L'élément INSERT échoue avec le message suivant, en supposant que la contrainte est appelée FK_CustomerOrder.

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

Tenter de supprimer une ligne de la table Customers lorsque le client est référencé dans une contrainte de clé étrangère

Imaginons une situation dans laquelle un client résilie son compte sur notre boutique en ligne. Nous souhaitons supprimer le client de notre backend. Nous essayons donc de procéder à l'opération suivante.

DELETE FROM Customers WHERE CustomerID = 721;

Dans cet exemple, Cloud Spanner détecte (via la contrainte de clé étrangère) qu'il existe encore des enregistrements dans la table Orders faisant référence à la ligne client que nous essayons de supprimer. Dans ce cas, l'erreur suivante s'affiche.

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

Pour résoudre ce problème, commençons par supprimer toutes les entrées référençant le client dans Orders. Une autre solution, possible à condition d'avoir défini le champ CustomerID comme NULLABLEn consiste à définir le champ CustomerID sur NULL pour supprimer la référence. CASCADE DELETE n'est pas compatible avec les clés étrangères.

Afficher les propriétés d'une relation de clé étrangère

INFORMATION_SCHEMA de Cloud Spanner contient des informations sur les clés étrangères et leurs index de sauvegarde. Voici quelques exemples de questions auxquelles vous pouvez répondre en interrogeant le SCHÉMA D'INFORMATION.

Pour plus d'informations sur les index de sauvegarde, consultez la section Index de sauvegarde de clés étrangères.

Quelles sont les contraintes définies dans ma base de données ?

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

Quelles clés étrangères sont définies dans ma base de données ?

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

Quels index sont des index secondaires pour les clés étrangères, également appelés index de sauvegarde ?

Cloud Spanner gère les index de sauvegarde de clé étrangère. Par conséquent, l'interrogation de SPANNER_IS_MANAGED sur la vue INDEXES renvoie tous les index de sauvegarde.

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 is true;

Pour en savoir plus, consultez la page Schéma d'informations.

Supprimer une relation de clé étrangère

Le LDD suivant supprime une contrainte de clé étrangère dans la table Orders.

ALTER TABLE Orders
  DROP CONSTRAINT FK_CustomerOrder;

Les index de stockage de clé étrangère sont supprimés automatiquement lorsque la contrainte elle-même est supprimée.

Compatibilité avec les relations de clé étrangère plus complexes

Plusieurs colonnes

Les clés étrangères peuvent faire référence à plusieurs colonnes. La liste des colonnes forme une clé correspondant à la clé primaire d'une table ou à un index de stockage. La table référençante contient les clés étrangères de la clé de table référencée.

Dans l'exemple suivant, les définitions de clés étrangères indiquent à Cloud Spanner que chaque valeur SongName de la table TopHits doit avoir une valeur correspondante dans la table Songs ; et chaque paire de valeurs SingerFirstName et SingerLastName doit avoir une paire de valeurs FirstName et LastName correspondante dans la table Singers.

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

Références circulaires

Les tables peuvent parfois présenter des dépendances circulaires, par exemple pour des raisons d'héritage de versions anciennes ou de dénormalisation. Les clés étrangères de Cloud Spanner permettent d'avoir des références circulaires. Étant donné qu'une table référencée doit préalablement exister pour qu'une clé étrangère puisse la référencer, l'une des clés étrangères doit être ajoutée à l'aide d'une instruction ALTER TABLE. Exemple :

  1. Créer une table TableA, sans clé étrangère
  2. Créez une table TableB avec une contrainte de clé étrangère sur la table TableA.
  3. Utilisez ALTER TABLE sur TableA pour créer une référence de clé étrangère à TableB.

Tables d'auto-référencement

Un type spécial de référence circulaire existe. Il consiste en une table qui définit une clé étrangère faisant référence à la même table. Par exemple, l'extrait suivant montre l'utilisation d'une clé étrangère de sorte que le ManagerId d'un employé soit également un employé.

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

Étape suivante