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

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

Une clé étrangère est une colonne partagée entre des tables pour établir un lien entre des données associées. Lorsque vous utilisez une clé étrangère, Spanner s'assure que cette relation est maintenue.

Le schéma de base de données suivant montre un schéma de base dans lequel les données d'une table sont liées à celles d'une autre table.

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.

En utilisant le schéma précédent comme exemple, cette section aborde les instructions CONSTRAINT du langage de définition de données (LDD) que vous pouvez utiliser pour gérer les relations entre les tables d'une base de données.

Par défaut, toutes les clés étrangères de Spanner sont des clés étrangères appliquées, qui renforcent l'intégrité référentielle. Dans Spanner, vous pouvez également choisir d'utiliser des clés étrangères informatives, qui ne valident ni n'appliquent l'intégrité référentielle. Pour en savoir plus, consultez Comparaison des clés étrangères et Choisir le type de clé étrangère à utiliser. Si aucune valeur n'est spécifiée, les clés étrangères des exemples de cette page sont des clés étrangères appliquées.

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

Supposons que vous ayez créé une table Customers dans votre base de données de commandes de produits de base. Vous avez maintenant besoin d'une table Orders pour stocker des informations sur les commandes passées par les clients. Pour garantir que toutes les commandes sont valides, vous ne souhaitez pas permettre au système d'insérer des lignes dans la table Orders, sauf s'il existe également une entrée correspondante dans la table Customers. Vous avez donc besoin d'une clé étrangère appliquée pour établir une relation entre les deux tables. Vous pouvez ajouter une colonne CustomerID à la nouvelle table et l'utiliser comme clé étrangère pour créer une relation avec la colonne CustomerID de la table Customers.

Lorsque vous créez une table avec une clé étrangère, vous utilisez REFERENCE pour établir une relation avec une autre table. La table contenant l'instruction REFERENCE est appelée table de référence. La table nommée dans l'instruction REFERENCE est la table référencée. La colonne nommée dans l'instruction REFERENCE est appelée colonne de référence.

L'exemple suivant montre comment utiliser l'instruction LDD CREATE TABLE pour créer la table Orders avec une contrainte de clé étrangère qui fait référence à CustomerID dans la table 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)
);

L'instruction précédente contient une clause CONSTRAINT qui présente les caractéristiques suivantes :

• La syntaxe de CONSTRAINT vous permet de nommer une contrainte, facilitant ainsi l'abandon de la table à 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 vous définissez la contrainte, est 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 Customers, l'insertion échoue.

Remarque : Les colonnes référencées de la table référencée correspondent souvent aux colonnes de clé primaire, mais ce n'est pas obligatoire. Une table de référence peut également faire référence à des colonnes secondaires d'une table référencée.

L'exemple 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, 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.

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

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

Vous devez également vous assurer que les clients ne peuvent commander que des produits existants. Si votre table comporte déjà des contraintes, vous devez les supprimer toutes. Dans Spanner, toutes les contraintes appliquées dans une table doivent être implémentées en même temps dans une seule instruction DDL par lot.

Si votre table ne comporte aucune contrainte existante, vous pouvez utiliser l'instruction LDD ALTER TABLE pour ajouter une contrainte de clé étrangère appliquée à la table Orders existante, comme illustré dans l'exemple suivant :

ALTER TABLE Orders
  ADD CONSTRAINT DB_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 Products. Si vous souhaitez que Spanner nomme ces contraintes à votre place, utilisez la syntaxe suivante :

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

Ajouter une clé étrangère avec une action de suppression à une nouvelle table

Reprenons l'exemple précédent où vous avez une table Customers dans une base de données de commandes de produits qui a besoin d'une table Orders. Vous souhaitez ajouter une contrainte de clé étrangère qui référence la table Customers. Toutefois, vous souhaitez vous assurer que lorsque vous supprimerez un enregistrement client à l'avenir, Spanner supprimera également toutes les commandes de ce client. Dans ce cas, vous devez utiliser l'action ON DELETE CASCADE avec la contrainte de clé étrangère.

L'instruction LDD CREATE TABLE suivante pour la table Orders inclut la contrainte de clé étrangère qui fait référence à la table Customers avec une action ON DELETE CASCADE.

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

L'instruction précédente contient une contrainte de clé étrangère avec une clause ON DELETE CASCADE. La colonne CustomerID est une clé étrangère qui fait référence au champ CustomerID de la table Customers. Cela signifie que chaque valeur CustomerID de la table Orders doit également exister dans la table Customers. Si quelqu'un tente de supprimer une ligne de la table Customers, toutes les lignes de la table Orders qui font référence à la valeur CustomerID supprimée sont également supprimées dans la même transaction.

Remarque : Si vous ne spécifiez pas d'action de clé étrangère, l'action par défaut est NO ACTION. Cela signifie que si quelqu'un tente de supprimer une ligne de la table Customers et que des lignes de la table Orders font référence à la valeur CustomerID supprimée, l'opération de suppression échoue et une erreur de non-respect de la contrainte de clé étrangère s'affiche.

Ajouter une clé étrangère avec une action de suppression à une table

Il est également recommandé de s'assurer que les commandes ne sont créées que pour des produits existants. Vous pouvez utiliser ALTER TABLE pour ajouter une autre contrainte de clé étrangère avec l'action ON DELETE CASCADE à la table des commandes, comme suit :

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

Si vous supprimez une ligne de la table Products, toutes les lignes de la table Orders qui font référence à la valeur ProductID supprimée sont également supprimées.

Remarque : L'ajout ou la suppression d'une action de clé étrangère sur une contrainte de clé étrangère existante n'est pas pris en charge. Vous devez ajouter une contrainte de clé étrangère avec une action. Pour en savoir plus, consultez Modifications de schéma de longue durée.

Utiliser des clés étrangères informatives (GoogleSQL uniquement)

Les clés étrangères informationnelles permettent à l'optimiseur de requêtes d'utiliser la relation de clé étrangère sans la surcharge entraînée par les vérifications de l'intégrité référentielle effectuées par les clés étrangères appliquées. Les clés étrangères informatives sont utiles lorsque l'application d'une intégrité référentielle stricte est peu pratique ou entraîne une surcharge de performances importante.

En reprenant l'exemple précédent, imaginons que vous souhaitiez modéliser les relations entre les tables Customers, Orders et Products. Toutefois, l'application d'une intégrité référentielle stricte dans les données des tables peut entraîner des goulots d'étranglement au niveau des performances, en particulier pendant les périodes d'achat de pointe avec des volumes de commandes élevés. De plus, les clients peuvent passer des commandes pour des produits qui ont été arrêtés et supprimés du tableau Products.

Vous pouvez créer la table Orders à l'aide de clés étrangères informatives :

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,
    CONSTRAINT FK_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID) NOT ENFORCED
) PRIMARY KEY (OrderID);

En créant une clé étrangère informationnelle avec NOT ENFORCED, vous permettez à une commande de référencer un client ou un produit inexistant. Il est préférable d'utiliser une clé étrangère informative plutôt qu'une contrainte de clé étrangère appliquée si un compte client est susceptible d'être supprimé ou si un produit est susceptible d'être arrêté. Avec une clé étrangère informative, Spanner n'effectue pas de validation de l'intégrité référentielle. Cela réduit la surcharge d'écriture, ce qui peut améliorer les performances pendant les périodes de pointe de traitement des commandes.

Vous pouvez autoriser l'optimiseur de requêtes à utiliser les relations pour générer des plans de requête efficaces. Cela peut améliorer les performances des requêtes qui joignent les tables sur les colonnes de clé étrangère. Pour en savoir plus, consultez Clé étrangère informationnelle pour l'optimisation des requêtes.

Attention : Si la logique de votre application nécessite une intégrité référentielle stricte (par exemple, pour empêcher les commandes de produits qui n'existent pas), vous devez implémenter des vérifications supplémentaires au niveau de l'application. Les résultats des requêtes peuvent être affectés en cas d'incohérences dans les données. Par exemple, une requête joignant Orders à Products peut renvoyer une commande avec un ProductID abandonné si l'optimiseur utilise une clé étrangère informative.

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

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

Intégrité référentielle avec des clés étrangères appliquées

Le principal motif de l'ajout de relations de clé étrangère appliquées est que 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. Certains clients ont commandé des produits, comme indiqué dans le tableau Orders. Grâce à la contrainte de clé étrangère appliquée en place, les données insérées dans la table Orders ont une intégrité référentielle.

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

Les exemples suivants montrent ce qui se passe lorsque vous essayez 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 vous essayez la modification suivante, compte tenu des exemples de données du diagramme précédent ?

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

  Dans ce cas, le système essaie d'insérer une ligne dans la table Orders avec un CustomerID (447) qui n'existe pas dans la table Customers. Si le système effectuait cette opération, vous auriez une commande non valide dans votre système. Toutefois, grâce à la contrainte de clé étrangère appliquée que vous avez ajoutée à votre table Orders, votre table est protégée. 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).
  

  Contrairement aux clés étrangères appliquées, les clés étrangères informatives n'appliquent pas l'intégrité référentielle. Si FK_CustomerOrder est une clé étrangère informative, l'instruction d'insertion réussit, car Spanner ne valide pas l'existence de la CustomerID correspondante dans la table Customers. Par conséquent, il est possible que les données ne respectent pas l'intégrité référentielle définie par FK_CustomerOrder.

• Tentative de suppression d'une ligne de la table Customers lorsque le client est référencé dans une contrainte de clé étrangère appliquée.

  Imaginons une situation dans laquelle un client résilie son compte sur votre boutique en ligne. Vous souhaitez supprimer le client de votre backend. Vous essayez donc de procéder à l'opération suivante.

  DELETE FROM Customers WHERE CustomerID = 721;
  

  Dans cet exemple, 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 vous essayez 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, commencez par supprimer toutes les entrées référençant le client dans Orders. Vous pouvez également définir la clé étrangère avec l'action ON DELETE CASCADE pour permettre à Spanner de gérer la suppression des entrées référencées.

  De même, si FK_CustomerOrder est une clé étrangère informative, l'action de suppression réussit, car Spanner ne garantit pas l'intégrité référentielle des clés étrangères informatives.

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

INFORMATION_SCHEMA de 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 ?

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 = 'YES';

Quelle est l'action référentielle définie avec la contrainte de clé étrangère ?

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

La clé étrangère est-elle appliquée ou non appliquée ?

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

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

Les rubriques suivantes vous expliquent comment utiliser des clés étrangères pour appliquer des relations plus complexes entre les tables.

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 appliquées indiquent que :

• Chaque valeur SongName de la table TopHits doit avoir une valeur correspondante dans la table Songs.

• Chaque paire de valeurs SingerFirstName et SingerLastName doit avoir une paire de valeurs FirstName et LastName correspondante dans le tableau Singers.

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

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 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éez TableA, sans clé étrangère.
2. Créez TableB avec une contrainte de clé étrangère sur 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é.

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

Étapes suivantes

• En savoir plus sur la prise en charge des clés étrangères dans Spanner

• En savoir plus sur le SCHÉMA D'INFORMATIONS de Spanner