Clés étrangères

Ce document décrit les clés étrangères dans Spanner et explique comment les utiliser pour appliquer l'intégrité référentielle dans votre base de données. Les rubriques suivantes vous aident à en savoir plus sur les clés étrangères et à les utiliser :

Présentation des clés étrangères dans Spanner

Les clés étrangères définissent les relations entre les tables. Vous pouvez utiliser des clés étrangères pour vous assurer que l'intégrité des données de ces relations dans Spanner est préservée.

Imaginez que vous êtes développeur principal pour une entreprise d'e-commerce. Vous concevez une base de données pour traiter les commandes des clients. La base de données doit stocker des informations sur chaque commande, client et produit. La figure 1 illustre la structure élémentaire de la base de données de l'application.

Structure élémentaire de la base de données de traitement des commandes.

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

Vous définissez une table Customers pour stocker les informations sur les clients, une table Orders pour suivre toutes les commandes et une table Products pour stocker les informations sur chaque produit.

La figure 1 montre également les liens entre les tables qui correspondent aux relations réelles suivantes :

  • Un client passe une commande.

  • Une commande est passée pour un produit.

Vous décidez que votre base de données applique les règles suivantes pour vous assurer que les commandes de votre système sont valides.

  • Vous ne pouvez pas créer une commande pour un client qui n'existe pas.

  • Un client ne peut pas commander un produit que vous ne vendez pas.

Lorsque vous appliquez ces règles, ou contraintes, vous maintenez l'intégrité référentielle de vos données. Lorsqu'une base de données maintient l'intégrité référentielle, toutes les tentatives d'ajout de données non valides, qui entraîneraient des liens ou des références non valides entre les données, échouent. L'intégrité référentielle permet d'éviter les erreurs des utilisateurs. Par défaut, Spanner utilise des clés étrangères pour appliquer l'intégrité référentielle.

Définir l'intégrité référentielle avec des clés étrangères

L'exemple suivant examine à nouveau l'exemple de traitement des commandes, avec plus de détails ajoutés à la conception, comme illustré dans la figure 2.

Schéma de base de données avec des clés étrangères

Figure 2. Diagramme d'un schéma de base de données avec des clés étrangères

La conception affiche désormais les noms et les types de colonnes dans chaque tableau. La table Orders définit également deux relations de clé étrangère. FK_CustomerOrder s'attend à ce que toutes les lignes de Orders aient un CustomerId valide. La clé étrangère FK_ProductOrder s'attend à ce que toutes les valeurs ProductId de la table Orders soient valides. Le tableau suivant met en correspondance ces contraintes avec les règles réelles que vous souhaitez appliquer.

Nom de la clé étrangère Contrainte Description réaliste
FK_CustomerOrder S'attend à ce que toutes les lignes de Orders aient un CustomerId valide. Un client valide passe une commande
FK_ProductOrder S'attend à ce que toutes les lignes de Orders aient un ProductId valide. Une commande a été passée pour un produit valide.

Spanner applique les contraintes spécifiées à l'aide de clés étrangères appliquées. Cela signifie que Spanner fait échouer toute transaction qui tente d'insérer ou de mettre à jour une ligne dans la table Orders qui comporte un CustomerId ou un ProductId introuvable dans les tables Customers et Products. Elle fait également échouer les transactions qui tentent de mettre à jour ou de supprimer des lignes dans les tables Customers et Products, ce qui invaliderait les ID de la table Orders. Pour en savoir plus sur la façon dont Spanner valide les contraintes, consultez la section Validation des contraintes de transaction.

Contrairement aux clés étrangères appliquées, Spanner ne valide pas les contraintes sur les clés étrangères informatives. Cela signifie que si vous utilisez une clé étrangère d'information dans ce scénario, une transaction qui tente d'insérer ou de mettre à jour une ligne dans la table Orders qui comporte un CustomerId ou un ProductId introuvable dans les tables Customers et Products n'est pas validée et la transaction n'échoue pas. De plus, contrairement aux clés étrangères appliquées, les clés étrangères informatives ne sont compatibles qu'avec GoogleSQL, et non avec PostgreSQL.

Caractéristiques des clés étrangères

Voici une liste des caractéristiques des clés étrangères dans Spanner.

  • La table qui définit la clé étrangère est la table de référence, et les colonnes de clé étrangère sont les colonnes de référence.

  • La clé étrangère fait référence aux colonnes référencées de la table référencée.

  • Comme dans l'exemple, vous pouvez nommer chaque contrainte de clé étrangère. Si vous ne spécifiez pas de nom, Spanner en génère un pour vous. Vous pouvez interroger le nom généré à partir de la table INFORMATION_SCHEMA de Spanner. Les noms de contraintes sont limités au schéma, ainsi que les noms des tables et des index, et doivent être uniques dans le schéma.

  • Le nombre de colonnes de référence et référencées doit être identique. L'ordre est important. Par exemple, la première colonne de référence fait référence à la première colonne référencée et la deuxième colonne de référence fait référence à la deuxième colonne référencée.

  • Une colonne de référence et sa contrepartie référencée doivent être du même type. Vous devez pouvoir indexer les colonnes.

  • Vous ne pouvez pas créer de clés étrangères sur des colonnes avec l'option allow_commit_timestamp=true.

  • Les colonnes de table ne sont pas acceptées.

  • Les colonnes JSON ne sont pas acceptées.

  • Une clé étrangère peut faire référence à des colonnes de la même table (clé étrangère autoréférencée). Par exemple, une table Employee avec une colonne ManagerId qui fait référence à la colonne EmployeeId de la table.

  • Les clés étrangères peuvent également former des relations circulaires entre des tables où deux tables se référencent mutuellement, directement ou indirectement. La table référencée doit exister avant de créer une clé étrangère. Cela signifie qu'au moins une des clés étrangères doit être ajoutée à l'aide de l'instruction ALTER TABLE.

  • Les clés référencées doivent être uniques. Spanner utilise la PRIMARY KEY de la table référencée si les colonnes référencées pour une clé étrangère correspondent aux colonnes de clé primaire de la table référencée. Si Spanner ne peut pas utiliser la clé primaire de la table référencée, il crée un UNIQUE NULL_FILTERED INDEX sur les colonnes référencées.

  • Les clés étrangères n'utilisent pas les index secondaires que vous avez créés. Ils créent plutôt leurs propres index de soutien. Les index de soutien peuvent être utilisés dans les évaluations de requêtes, y compris dans les directives force_index explicites. Vous pouvez interroger les noms des index de soutien à partir de la table INFORMATION_SCHEMA de Spanner. Pour en savoir plus, consultez Index de soutien.

Types de clés étrangères

Il existe deux types de clés étrangères : appliquées et informatives. Les clés étrangères appliquées sont utilisées par défaut et appliquent l'intégrité référentielle. Les clés étrangères d'information n'appliquent pas l'intégrité référentielle et sont mieux utilisées pour déclarer le modèle de données logique prévu pour l'optimisation des requêtes. Pour en savoir plus, consultez les sections suivantes sur les clés étrangères appliquées et informatives, ainsi que le tableau de comparaison des types de clés étrangères.

Clés étrangères appliquées

Les clés étrangères appliquées, qui sont le type de clé étrangère par défaut dans Spanner, appliquent l'intégrité référentielle. Étant donné que les clés étrangères appliquées appliquent l'intégrité référentielle, elles entraînent l'échec des tentatives d'effectuer les opérations suivantes :

  • L'ajout d'une ligne à une table de référence dont la valeur de clé étrangère n'existe pas dans la table référencée échoue.

  • La suppression d'une ligne d'une table référencée qui est référencée par des lignes de la table de référence échoue.

Toutes les clés étrangères PostgreSQL sont appliquées. Les clés étrangères GoogleSQL sont appliquées par défaut. Étant donné que les clés étrangères sont appliquées par défaut, l'utilisation du mot clé ENFORCED pour spécifier qu'une clé étrangère GoogleSQL est appliquée est facultative.

Clés étrangères d'information

Les clés étrangères informatives sont utilisées pour déclarer le modèle de données logique prévu pour l'optimisation des requêtes. Bien que les clés de table référencées doivent être uniques pour les clés étrangères d'information, l'intégrité référentielle n'est pas appliquée. Si vous souhaitez valider de manière sélective l'intégrité référentielle lorsque vous utilisez des clés étrangères d'information, vous devez gérer la logique de validation côté client. Pour en savoir plus, consultez Utiliser des clés étrangères informatives.

Utilisez le mot clé NOT ENFORCED pour spécifier qu'une clé étrangère GoogleSQL est informative. PostgreSQL n'est pas compatible avec les clés étrangères d'information.

Comparaison des types de clés étrangères

Les modes appliqué et informatif présentent tous deux des avantages. Les sections suivantes comparent les deux types de clés étrangères et incluent quelques bonnes pratiques.

Différences générales concernant les clés étrangères

De manière générale, voici quelques-unes des différences entre les clés étrangères appliquées et informatives :

  • Application. Les clés étrangères appliquées valident et garantissent l'intégrité référentielle lors des écritures. Les clés étrangères informatives ne valident pas et ne garantissent pas l'intégrité référentielle.

  • Stockage. Les clés étrangères appliquées peuvent nécessiter un espace de stockage supplémentaire pour l'index de soutien sur la table contrainte.

  • Débit en écriture. Les clés étrangères appliquées peuvent entraîner plus de frais généraux dans le chemin d'écriture que les clés étrangères informatives.

  • Optimisation des requêtes. Les deux types de clés étrangères peuvent être utilisés pour l'optimisation des requêtes. Lorsque l'optimiseur est autorisé à utiliser des clés étrangères d'information, les résultats de la requête peuvent ne pas refléter les données réelles si celles-ci ne correspondent pas aux relations de clés étrangères d'information (par exemple, si certaines clés contraintes n'ont pas de clés référencées correspondantes dans la table référencée).

Tableau des différences entre les clés étrangères

Le tableau suivant présente les différences détaillées entre les clés étrangères appliquées et informatives :

Clés étrangères appliquées Clés étrangères d'information
Mots clés ENFORCED NOT ENFORCED
Compatible avec GoogleSQL Oui. Les clés étrangères dans GoogleSQL sont appliquées par défaut. Oui.
Compatible avec PostgreSQL Oui. Les clés étrangères dans PostgreSQL ne peuvent être qu'appliquées. Non.
Stockage Les clés étrangères appliquées nécessitent un espace de stockage pour un maximum de deux index de soutien. Les clés étrangères informatives nécessitent un espace de stockage pour un index de soutien au maximum.
Crée des index de soutien sur les colonnes de table référencées si nécessaire. Oui. Oui.
Crée des index de soutien sur les colonnes de la table de référence si nécessaire. Oui. Non.
Compatibilité avec les actions de clé étrangère Oui. Non.
Valide et applique l'intégrité référentielle Oui. Non. L'absence de validation améliore les performances d'écriture, mais peut avoir un impact sur les résultats des requêtes lorsque des clés étrangères informatives sont utilisées pour l'optimisation des requêtes. Vous pouvez utiliser la validation côté client ou une clé étrangère forcée pour garantir l'intégrité référentielle.

Choisir le type de clé étrangère à utiliser

Vous pouvez utiliser les consignes suivantes pour décider du type de clé étrangère à utiliser :

Nous vous recommandons de commencer par les clés étrangères appliquées. Les clés étrangères appliquées assurent la cohérence des données et du modèle logique à tout moment. Les clés étrangères appliquées sont l'option recommandée, sauf si elles ne fonctionnent pas pour votre cas d'utilisation.

Nous vous recommandons d'envisager d'utiliser des clés étrangères informatives si chacune des conditions suivantes est remplie :

  • Vous souhaitez utiliser le modèle de données logique décrit par la clé étrangère d'information dans l'optimisation des requêtes.

  • Le maintien d'une intégrité référentielle stricte n'est pas pratique ou a un impact significatif sur les performances. Voici quelques exemples d'utilisation d'une clé étrangère d'information :

    • Votre source de données en amont suit un modèle de cohérence à terme. Dans ce cas, les mises à jour effectuées dans le système source peuvent ne pas être immédiatement répercutées dans Spanner. Étant donné que les mises à jour peuvent ne pas être immédiates, de brèves incohérences dans les relations de clés étrangères peuvent se produire.

    • Vos données contiennent des lignes référencées qui ont un grand nombre de relations de référencement. Les mises à jour de ces lignes peuvent consommer beaucoup de ressources, car Spanner doit valider ou, dans certains cas, supprimer toutes les lignes liées au maintien de l'intégrité référentielle. Dans ce scénario, les mises à jour peuvent avoir un impact sur les performances de Spanner et ralentir les transactions simultanées.

  • Votre application peut gérer les incohérences potentielles des données et leur impact sur les résultats des requêtes.

Utiliser des clés étrangères informatives

Les rubriques suivantes ne concernent que les clés étrangères informatives. Pour les sujets qui s'appliquent à la fois aux clés étrangères informatives et appliquées, consultez les ressources suivantes :

Créer une table avec une clé étrangère d'information

Vous pouvez créer et supprimer des clés étrangères d'information de votre base de données Spanner à l'aide d'instructions LDD. Vous pouvez ajouter des clés étrangères à une nouvelle table à l'aide de l'instruction CREATE TABLE. De même, vous pouvez ajouter ou supprimer des clés étrangères d'une table existante à l'aide de l'instruction ALTER TABLE.

L'exemple suivant crée une table avec une clé étrangère d'information à l'aide de GoogleSQL. Les clés étrangères d'information ne sont pas compatibles avec PostgreSQL.

GoogleSQL 

CREATE TABLE Customers (
  CustomerId INT64 NOT NULL,
  CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

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
 ) PRIMARY KEY (OrderId);

PostgreSQL 

Not Supported

Pour obtenir d'autres exemples de création et de gestion de clés étrangères, consultez Créer et gérer des relations de clés étrangères. Pour en savoir plus sur les instructions LDD, consultez la documentation de référence sur le LDD.

Utiliser des clés étrangères informatives pour l'optimisation des requêtes

L'optimiseur de requêtes peut utiliser à la fois des clés étrangères appliquées et des clés étrangères d'information pour améliorer les performances des requêtes. L'utilisation de clés étrangères informatives vous permet de profiter de plans de requête optimisés sans avoir à appliquer strictement l'intégrité référentielle.

Si vous activez l'optimiseur de requêtes pour utiliser les informations des clés étrangères informatives, il est important de comprendre que l'exactitude de l'optimisation dépend de la cohérence des données avec le modèle logique décrit par les clés étrangères informatives. En cas d'incohérences, les résultats de la requête peuvent ne pas refléter les données réelles. Par exemple, une incohérence se produit lorsqu'une valeur d'une colonne contrainte n'a pas de valeur correspondante dans une colonne référencée.

Par défaut, l'optimiseur de requêtes utilise les clés étrangères NOT ENFORCED. Pour modifier ce comportement, définissez l'option de base de données use_unenforced_foreign_key_for_query_optimization sur "false". Voici un exemple GoogleSQL qui illustre ce point (les clés étrangères informatives ne sont pas disponibles dans PostgreSQL) :

SET DATABASE OPTIONS (
    use_unenforced_foreign_key_for_query_optimization = false
);

L'indication d'instruction de requête booléenne @{use_unenforced_foreign_key} remplace l'option de base de données par requête qui contrôle si l'optimiseur utilise des clés étrangères NOT ENFORCED. La désactivation de cet indicateur ou de l'option de base de données peut être utile pour résoudre les problèmes liés à des résultats de requête inattendus. L'exemple suivant montre comment utiliser @{use_unenforced_foreign_key} :

@{use_unenforced_foreign_key=false} SELECT Orders.CustomerId
    FROM Orders
    INNER JOIN Customers ON Customers.CustomerId = Orders.CustomerId;

Utiliser des clés étrangères appliquées

Les rubriques suivantes ne concernent que les clés étrangères appliquées. Pour les sujets qui s'appliquent à la fois aux clés étrangères informatives et appliquées, consultez les ressources suivantes :

Créer une table avec une clé étrangère appliquée

Vous pouvez créer et supprimer des clés étrangères appliquées de votre base de données Spanner à l'aide du LDD. Vous ajoutez des clés étrangères à une nouvelle table à l'aide de l'instruction CREATE TABLE. De même, vous pouvez ajouter une clé étrangère à une table existante ou en supprimer une à l'aide de l'instruction ALTER TABLE.

Vous pouvez créer et supprimer des clés étrangères de votre base de données Spanner à l'aide du LDD. Vous pouvez ajouter des clés étrangères à une table avec l'instruction CREATE TABLE. De même, vous pouvez ajouter une clé étrangère à une table existante ou en supprimer une à l'aide de l'instruction ALTER TABLE.

L'exemple suivant montre comment créer une table avec une clé étrangère appliquée.

GoogleSQL 

CREATE TABLE Customers (
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

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) ENFORCED
) PRIMARY KEY (OrderId);

PostgreSQL 

CREATE TABLE Customers (
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CustomerId)
);

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

Pour obtenir d'autres exemples de création et de gestion de clés étrangères, consultez Créer et gérer des relations de clés étrangères.

Actions sur les clés étrangères

Les actions de clé étrangère ne peuvent être définies que sur les clés étrangères appliquées.

Les actions de clé étrangère contrôlent ce qui se passe pour la colonne contrainte lorsque la colonne à laquelle elle fait référence est supprimée ou mise à jour. Spanner accepte l'utilisation des actions ON DELETE CASCADE et ON DELETE SET NULL.

Avec l'action ON DELETE CASCADE, lorsque vous supprimez une ligne référencée par une clé étrangère, toutes les lignes qui font référence à cette clé sont également supprimées dans la même transaction.

Avec l'action ON DELETE SET NULL, lorsque vous supprimez une ligne référencée par une clé étrangère, les colonnes de référence de toutes les lignes qui font référence à cette clé sont définies sur NULL dans la même transaction.

Vous pouvez ajouter une clé étrangère avec une action lorsque vous créez votre base de données à l'aide du LDD. Utilisez l'instruction CREATE TABLE pour ajouter des clés étrangères avec une action à une nouvelle table. De même, vous pouvez utiliser l'instruction ALTER TABLE pour ajouter une action de clé étrangère à une table existante ou pour supprimer une action de clé étrangère. L'exemple suivant montre comment créer une table avec une action de clé étrangère.

GoogleSQL 

CREATE TABLE ShoppingCarts (
CartId INT64 NOT NULL,
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
CONSTRAINT FKShoppingCartsCustomers FOREIGN KEY(CustomerId, CustomerName)
  REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE,
) PRIMARY KEY(CartId);

PostgreSQL 

CREATE TABLE ShoppingCarts (
CartId bigint NOT NULL,
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CartId),
CONSTRAINT fkshoppingcartscustomers FOREIGN KEY (CustomerId, CustomerName)
  REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE
);

Voici une liste des caractéristiques des actions de clé étrangère dans Spanner.

  • Les actions de clé étrangère sont ON DELETE CASCADE, ON DELETE SET NULL ou ON DELETE NO ACTION.

  • Vous ne pouvez pas utiliser ON DELETE SET NULL sur des colonnes de référence qui font partie de la clé primaire de la table de référence, marquées comme NOT NULL ou générées.

  • Vous pouvez interroger INFORMATION_SCHEMA pour trouver les contraintes de clé étrangère qui ont une action.

  • L'ajout d'une action de clé étrangère sur une contrainte de clé étrangère existante n'est pas possible. Vous devez ajouter une nouvelle contrainte de clé étrangère avec une action.

Validation des contraintes

La validation des contraintes ne s'applique qu'aux clés étrangères appliquées.

Spanner valide les contraintes de clé étrangère appliquées lorsqu'une transaction est validée ou lorsque les effets des écritures sont visibles pour les opérations ultérieures de la transaction.

Une valeur insérée dans la colonne de référence est comparée aux valeurs de la table référencée et des colonnes référencées. Les lignes contenant des valeurs de référence NULL ne sont pas vérifiées, ce qui signifie que vous pouvez les ajouter à la table de référence.

Spanner valide toutes les contraintes référentielles de clé étrangère applicables lorsqu'il tente de mettre à jour des données à l'aide d'instructions LMD ou d'une API. Toutes les modifications en attente sont annulées si des contraintes ne sont pas valides.

La validation a lieu immédiatement après chaque instruction LMD. Par exemple, vous devez insérer la ligne référencée avant d'insérer les lignes qui la référencent. Lorsque vous utilisez une API de mutation, les mutations sont mises en mémoire tampon jusqu'au commit de la transaction. La validation forcée de la clé étrangère est différée jusqu'à ce que la transaction soit validée. Dans ce cas, il est possible d'insérer d'abord les lignes de référence.

Chaque transaction est évaluée pour les modifications qui affectent les contraintes de clé étrangère appliquées. Ces évaluations peuvent nécessiter des requêtes supplémentaires au serveur. Les index de soutien nécessitent également un temps de traitement supplémentaire pour évaluer les modifications de transaction et gérer les index. Un espace de stockage supplémentaire est également requis pour chaque index.

Action de suppression en cascade de longue durée

Lorsque vous supprimez une ligne d'une table référencée, Spanner doit supprimer toutes les lignes des tables de référence qui font référence à la ligne supprimée. Cela peut entraîner un effet en cascade, où une seule opération de suppression entraîne des milliers d'autres opérations de suppression. L'ajout d'une contrainte de clé étrangère avec une action de suppression en cascade à une table ou la création d'une table avec des contraintes de clé étrangère avec une action de suppression en cascade peut ralentir les opérations de suppression.

Action de suppression de longue durée avec définition de valeur nulle

Lorsque vous supprimez une ligne d'une table référencée, Spanner met à jour toutes les lignes des tables de référence qui ont une action ON DELETE SET NULL en définissant les colonnes de référence sur NULL. Cela peut entraîner un effet en cascade, où une seule opération de suppression entraîne des milliers d'opérations de mise à jour. L'ajout d'une contrainte de clé étrangère ON DELETE SET NULL à une table ou la création d'une table avec une telle contrainte peut ralentir les opérations de suppression.

Limite de mutations dépassée pour la suppression en cascade de clés étrangères

La suppression d'un grand nombre d'enregistrements à l'aide d'une suppression en cascade de clé étrangère peut avoir un impact sur les performances. En effet, chaque enregistrement supprimé entraîne la suppression de tous les enregistrements qui lui sont associés. Si vous devez supprimer un grand nombre d'enregistrements à l'aide d'une suppression en cascade de clé étrangère, supprimez explicitement les lignes des tables enfants avant de supprimer la ligne des tables parentes. Cela évite l'échec de la transaction en raison de la limite de mutations.

Limite de mutations dépassée pour la suppression de clé étrangère définie sur null

La suppression de lignes qui déclenchent un grand nombre de mises à jour à l'aide d'une action de clé étrangère ON DELETE SET NULL peut avoir un impact sur les performances. En effet, chaque ligne supprimée dans la table référencée déclenche la mise à jour de toutes les lignes qui la référencent dans la table de référence. Si vous devez supprimer une ligne qui déclenche de nombreuses mises à jour à l'aide de ON DELETE SET NULL, mettez explicitement à jour les lignes de référence sur NULL ou supprimez-les avant de supprimer la ligne référencée. Cette pratique empêche l'échec de la transaction en raison de la limite de mutation.

Utilisation des ressources pour les actions de suppression de clés étrangères

ON DELETE CASCADE et ON DELETE SET NULL ont des coûts de ressources et de mutation différents selon votre schéma :

  • Mutations de la table de base : ON DELETE SET NULL met à jour des colonnes individuelles. Par conséquent, chaque ligne de référence mise à jour est comptabilisée comme plusieurs mutations (pour la clé de ligne et chaque colonne définie sur NULL). ON DELETE CASCADE est comptabilisé comme une mutation par ligne supprimée, ou une mutation par plage de clés supprimée lorsque les colonnes de référence sont un préfixe de la clé primaire de la table de référence.
  • Mutations d'index : les deux actions utilisent une mutation de suppression par ligne sur l'index de soutien de la clé étrangère. Pour les autres index secondaires de la table de référence, ON DELETE SET NULL ne modifie que les index qui incluent les colonnes de référence, tandis que ON DELETE CASCADE supprime les entrées de chaque index secondaire de la table.
  • Lectures : ON DELETE SET NULL lit toujours l'index de soutien de la clé étrangère pour trouver les lignes correspondantes. ON DELETE CASCADE peut ignorer cette lecture et supprimer directement une plage de clés lorsque les colonnes de référence sont un préfixe de la clé primaire de la table de référence.
  • En cascade : ON DELETE SET NULL ne s'applique jamais en cascade aux tables enfants entrelacées, car les colonnes de clé primaire ne peuvent pas être définies sur NULL. Elle ne déclenche d'autres actions que si une autre clé étrangère fait référence à la colonne mise à jour. ON DELETE CASCADE supprime l'intégralité de la ligne de référence. Il est donc également appliqué en cascade à toutes les tables enfants entrelacées avec ON DELETE CASCADE et aux clés étrangères en aval.

Comparaison des clés étrangères appliquées et de l'entrelacement de tables

L'entrelacement de tables Spanner constitue un bon choix pour de nombreuses relations parent-enfant dans lesquelles la clé primaire de la table enfant inclut les colonnes de clé primaire de la table parent. La colocalisation des lignes enfant avec les lignes parent peut améliorer considérablement les performances.

Les clés étrangères constituent une solution parent-enfant plus générale et répondent à des cas d'utilisation supplémentaires. Elles ne sont pas limitées aux colonnes de clé primaire, et les tables peuvent posséder plusieurs relations de clés étrangères, à la fois en tant que parent dans certaines relations et en tant qu'enfant dans d'autres. Cependant, une relation de clé étrangère n'implique pas la colocalisation des tables dans la couche de stockage.

Prenons un exemple qui utilise une table Orders définie comme suit :

Schéma de base de données avec des clés étrangères

Figure 3. Diagramme du schéma de base de données avec des clés étrangères appliquées

La conception à la figure 3 présente certaines limites. Par exemple, chaque commande ne peut contenir qu'un seul article.

Imaginez que vos clients souhaitent pouvoir commander plusieurs produits par commande. Vous pouvez améliorer votre conception en introduisant une table OrderItems contenant une entrée pour chaque produit commandé par le client. Vous pouvez introduire une autre clé étrangère forcée pour représenter cette nouvelle relation un à plusieurs entre Orders et OrderItems. Cependant, vous savez également que vous souhaitez souvent exécuter des requêtes sur les commandes et leurs articles respectifs. Étant donné que la colocalisation de ces données améliore les performances, vous devez créer la relation parent-enfant à l'aide de la fonctionnalité d'entrelacement de tables de Spanner.

Voici comment définir la table OrderItems, entrelacée avec Orders.

GoogleSQL 

CREATE TABLE Products (
ProductId INT64 NOT NULL,
Name STRING(256) NOT NULL,
Price FLOAT64
) PRIMARY KEY(ProductId);

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;

PostgreSQL 

CREATE TABLE Products (
ProductId BIGINT NOT NULL,
Name varchar(256) NOT NULL,
Price float8,
PRIMARY KEY(ProductId)
);

CREATE TABLE OrderItems (
OrderId BIGINT NOT NULL,
ProductId BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
FOREIGN KEY (ProductId) REFERENCES Products (ProductId),
PRIMARY KEY (OrderId, ProductId)
) INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

La figure 4 est une représentation visuelle du schéma de base de données mis à jour suite à l'introduction de cette nouvelle table, OrderItems, entrelacée avec Orders. Vous pouvez également consulter la relation un-à-plusieurs entre ces deux tables.

Schéma de base de données montrant une relation de type un à plusieurs entre Orders et la nouvelle table OrderItems entrelacée

Figure 4. Ajout d'une table OrderItems entrelacée

Dans cette configuration, vous pouvez avoir plusieurs entrées OrderItems dans chaque commande, et les entrées OrderItems de chaque commande sont entrelacées et donc colocalisées avec les commandes. L'entrelacement physique de Orders et OrderItems de cette manière peut améliorer les performances, en pré-joignant efficacement les tables et en vous permettant d'accéder aux lignes associées tout en minimisant les accès au disque. Par exemple, Spanner peut effectuer des jointures par clé primaire localement, ce qui réduit l'accès au disque et le trafic réseau.

Si le nombre de mutations dans une transaction dépasse 80 000, la transaction échoue. Ces suppressions en cascade de grande envergure fonctionnent bien pour les tables ayant une relation "entrelacée dans le parent", mais pas pour les tables ayant une relation de clé étrangère. Si vous avez une relation de clé étrangère et que vous devez supprimer un grand nombre de lignes, vous devez d'abord supprimer explicitement les lignes des tables enfants.

Si vous disposez d'une table utilisateur avec une relation de clé étrangère vers une autre table, et que la suppression d'une ligne de la table référencée déclenche la suppression de millions de lignes, vous devez concevoir votre schéma avec une action de suppression en cascade avec "interleaved in parent".

Tableau de comparaison

Le tableau suivant compare les clés étrangères appliquées et l'entrelacement de tables. Vous pouvez utiliser ces informations pour déterminer ce qui convient à votre conception.

Type de relation parent-enfant Entrelacement de tables Clés étrangères appliquées
Peut utiliser des clés primaires Oui Oui
Peut utiliser des colonnes ne contenant pas de clé primaire No Oui
Nombre de parents acceptés 0 .. 1 0 .. N
Stocke les données parent et enfant ensemble Oui No
Accepte la suppression en cascade Oui Oui
Mode de correspondance des valeurs nulles Transmet si toutes les valeurs de référence ne sont pas distinctes des valeurs référencées.
Les valeurs nulles ne sont pas distinctes des valeurs nulles. Les valeurs nulles sont distinctes des valeurs non nulles.		 Transmet si une valeur de référence est nulle.
Transmet si toutes les valeurs de référence sont non nulles et que la table référencée comporte une ligne avec des valeurs égales aux valeurs de référence.
Échoue si aucune ligne correspondante n'a été trouvée.
Délai d'application Par opération lors de l'utilisation de l'API de mutation.
Par instruction lors de l'utilisation du LMD.		 Par transaction lorsque vous utilisez l'API de mutation.
Par instruction lors de l'utilisation du LMD.
Peut être supprimé Non. L'imbrication de tables ne peut pas être supprimée après sa création, sauf si vous supprimez l'intégralité de la table enfant. Oui

Index de soutien

Les clés étrangères n'utilisent pas d'index créés par l'utilisateur. À la place, ils créent leurs propres index de soutien. Les clés étrangères appliquées et informatives créent des index de soutien différemment dans Spanner :

  • Pour les clés étrangères appliquées, Spanner peut créer jusqu'à deux index de soutien secondaires pour chaque clé étrangère : un pour les colonnes de référence et un second pour les colonnes référencées.

  • Pour les clés étrangères informatives, Spanner peut créer jusqu'à un index de soutien si nécessaire pour les colonnes référencées. Les clés étrangères informatives ne créent pas d'index de soutien pour les colonnes de référence.

Pour les clés étrangères appliquées et informatives, une clé étrangère fait généralement référence aux clés primaires de la table référencée. Un index pour la table référencée n'est donc généralement pas nécessaire. Par conséquent, les clés étrangères informatives n'ont généralement aucun index de soutien. Si nécessaire, l'index de soutien créé pour la table référencée est un index UNIQUE NULL_FILTERED. La création de la clé étrangère échoue si des données existantes ne respectent pas la contrainte d'unicité de l'index.

Les clés étrangères informatives n'ont pas d'index de soutien pour la table de référence. Pour les clés étrangères appliquées, l'index de soutien de la table de référence est NULL_FILTERED.

Si deux clés étrangères ou plus nécessitent le même index de soutien, Spanner crée un seul index pour chacune d'elles. Les index de soutien sont supprimés lorsque les clés étrangères qui les utilisent sont supprimées. Vous ne pouvez pas modifier ni supprimer les index de soutien.

Spanner utilise le schéma d'informations de chaque base de données pour stocker les métadonnées sur les index de soutien. Les lignes de INFORMATION_SCHEMA.INDEXES dont la valeur SPANNER_IS_MANAGED est true décrivent les index de soutien.

En dehors des requêtes SQL qui appellent directement le schéma d'informations, la consoleGoogle Cloud n'affiche aucune information sur les index de soutien d'une base de données.

Modifications de schéma de longue durée

L'ajout d'une clé étrangère forcée à une table existante ou la création d'une table avec une clé étrangère peut entraîner des opérations de longue durée. Dans le cas d'une nouvelle table, celle-ci n'est pas accessible en écriture tant que l'opération de longue durée n'est pas terminée.

Le tableau suivant montre ce qui se passe dans Spanner lorsqu'une clé étrangère appliquée et une clé étrangère informative se trouvent dans une table nouvelle ou existante :

Type de table Clé étrangère appliquée Clé étrangère d'information
Nouveau Spanner remplit les index référencés selon les besoins de chaque clé étrangère. Spanner remplit les index référencés selon les besoins de chaque clé étrangère.
Existant Spanner remplit les index de référence et référencés si nécessaire. Spanner valide également les données existantes dans la table pour s'assurer qu'elles respectent la contrainte d'intégrité référentielle de la clé étrangère. La modification du schéma échoue si des données ne sont pas valides. Spanner remplit l'index référencé si nécessaire et ne valide pas les données existantes dans la table.

Les éléments suivants ne sont pas acceptés :

  • Ajouter une action de clé étrangère à une contrainte de clé étrangère appliquée existante.
  • Modifier l'application d'une clé étrangère existante.

Dans les deux cas, nous vous recommandons d'effectuer les opérations suivantes :

  1. Ajoutez une contrainte avec l'action ou l'application requise.
  2. Supprimez l'ancienne contrainte.

L'ajout d'une contrainte et la suppression de l'ancienne contrainte permettent d'éviter un problème d'opération de modification de contrainte de longue durée. Par exemple, supposons que vous souhaitiez ajouter une action DELETE CASCADE sur une clé étrangère existante. Après avoir créé la clé étrangère avec l'action ON DELETE CASCADE, l'effet des deux contraintes est une action DELETE CASCADE. Vous pouvez ensuite supprimer l'ancienne contrainte en toute sécurité.

La suppression d'une contrainte peut entraîner la suppression des index de soutien de la clé étrangère si les index ne sont pas utilisés par d'autres contraintes de clé étrangère. Par conséquent, si vous supprimez d'abord l'ancienne contrainte, l'ajout ultérieur de la même contrainte de clé étrangère avec une action peut entraîner des opérations de longue durée, telles que le remplissage des index, la validation des contraintes d'index uniques ou la validation des contraintes référentielles de clé étrangère.

Vous pouvez interroger INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.SPANNER_STATE pour vérifier l'état de création de la clé étrangère.

Étapes suivantes