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 sujets suivants vous aident à découvrir les clés étrangères et à les utiliser:

• Présentation des clés étrangères dans Spanner
• Types de clés étrangères
• Comparaison des types de clés étrangères
• Choisir le type de clé étrangère à utiliser
• Utiliser des clés étrangères appliquées
• Utiliser des clés étrangères informatives
• Index de stockage
• Modifications de schéma de longue durée

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 maintenue.

Imaginons que vous soyez le développeur principal d'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.

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

Vous définissez une table Customers pour stocker les informations client, une table Orders pour suivre toutes les commandes et une table Products pour stocker des 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 conserve l'intégrité référentielle, toutes les tentatives d'ajout de données incorrectes, qui entraîneraient l'utilisation de liens ou de références non valides entre les données, échoueront. 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é à la figure 2.

Figure 2. Schéma d'une 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 fait correspondre ces contraintes aux règles réelles que vous souhaitez appliquer.

Nom de la clé étrangère	Contrainte	Description réaliste
FK_CustomerOrder	Attend que toutes les lignes de Orders aient un CustomerId valide	Un client valide passe commande
FK_ProductOrder	Attend 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 échoue toutes les transactions qui tentent d'insérer ou de mettre à jour une ligne de la table Orders contenant une table CustomerId ou ProductId introuvable dans les tables Customers et Products. Il échoue également les transactions qui tentent de mettre à jour ou de supprimer des lignes dans les tables Customers et Products qui invalideraient les identifiants dans 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 informative dans ce scénario, une transaction qui tente d'insérer ou de mettre à jour une ligne dans la table Orders contenant une table CustomerId ou ProductId introuvable dans les tables Customers et Products n'est pas validée et la transaction ne échoue pas. 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 référence les 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 INFORMATION_SCHEMA de Spanner. Les noms de contrainte sont limités au schéma, ainsi qu'aux 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 à 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 auto-ré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 font référence, directement ou indirectement. La table référencée doit exister avant de créer une clé étrangère. Cela signifie qu'au moins l'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 le PRIMARY KEY de la table référencée si les colonnes référencées d'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. Elles créent plutôt leurs propres index de stockage. Les index de stockage sont utilisables dans les évaluations de requête, y compris dans les directives force_index explicites. Vous pouvez interroger les noms des index de stockage à partir de INFORMATION_SCHEMA de Spanner. Pour en savoir plus, consultez la section Index de stockage.

Types de clés étrangères

Il existe deux types de clés étrangères : les clés forcées et les clés 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 informatives 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 à des fins d'optimisation des requêtes. Pour en savoir plus, consultez les sections suivantes sur les clés étrangères obligatoires 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 forcé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, les tentatives suivantes échouent:

• L'ajout d'une ligne à une table référençante 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 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 informatives

Les clés étrangères informatives permettent de 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 informatives, l'intégrité référentielle n'est pas appliquée. Si vous souhaitez valider sélectivement l'intégrité référentielle lorsque vous utilisez des clés étrangères informatives, vous devez gérer la logique de validation côté client. Pour en savoir plus, consultez la section 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 informatives.

Comparaison des types de clés étrangères

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

Différences entre les clés étrangères de haut niveau

Voici quelques-unes des différences entre les clés étrangères forcé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 ni ne garantissent 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 secours de 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 informatives, les résultats de la requête peuvent ne pas refléter les données réelles si elles ne correspondent pas aux relations de clé étrangère informative (par exemple, si certaines clés contraintes ne comportent 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 informatives
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 appliquées que.	Non.
Stockage	Les clés étrangères appliquées nécessitent un espace de stockage pouvant aller jusqu'à deux index de stockage.	Les clés étrangères informatives nécessitent un espace de stockage pouvant aller jusqu'à un index de sauvegarde.
Crée des index de sauvegarde sur les colonnes de table référencées si nécessaire.	Oui.	Oui.
Crée des index de stockage sur les colonnes de 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 de requête 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 appliquée pour garantir l'intégrité référentielle.

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

Vous pouvez utiliser les consignes suivantes pour choisir le 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 maintiennent 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'utiliser des clés étrangères informatives si toutes les conditions suivantes sont remplies:

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

• Maintenir une intégrité référentielle stricte est peu pratique ou a un impact significatif sur les performances. Voici des exemples de cas où vous pouvez envisager d'utiliser une clé étrangère informative:

  • Votre source de données en amont suit un modèle de cohérence finale. 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 peuvent se produire dans les relations de clé étrangère.

  • Vos données contiennent des lignes référencées qui présentent un grand nombre de relations de référence. Les mises à jour de ces lignes peuvent utiliser 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 sujets suivants ne concernent que les clés étrangères à titre d'information. Pour les sujets qui s'appliquent à la fois aux clés étrangères informatives et appliquées, consultez les articles suivants:

• Modifications de schéma de longue durée
• Index de stockage

Créer une table avec une clé étrangère informative

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

L'exemple suivant crée une table avec une clé étrangère informative à l'aide de GoogleSQL. PostgreSQL n'est pas compatible avec les clés étrangères informatives.

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 plus d'exemples sur la manière de créer et de gérer des clés étrangères, consultez la section Créer et gérer des relations de clé étrangère. Pour en savoir plus sur les instructions LDD, consultez la documentation de référence sur le langage de définition de données (LDD).

Remarque :Vous pouvez également ajouter et supprimer des clés étrangères à l'aide de la console Google Cloud. Sélectionnez Modifier en tant que texte dans l'interface de création de table, puis saisissez le LDD pour gérer les clés étrangères de votre table.

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

L'optimiseur de requêtes peut utiliser à la fois les clés étrangères appliquées et les clés étrangères informatives pour améliorer les performances des requêtes. L'utilisation de clés étrangères informatives vous permet de profiter des plans de requête optimisés sans les frais généraux liés à l'application stricte de l'intégrité référentielle.

Si vous autorisez l'optimiseur de requêtes à utiliser des informations sur les clés étrangères informatives, il est important de comprendre que l'exactitude de l'optimisation dépend de la disponibilité de données cohérentes 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 risquent de ne pas refléter les données réelles. Un exemple d'incohérence est une valeur dans une colonne contrainte qui ne correspond pas à une valeur dans une colonne référencée.

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

SET DATABASE OPTIONS (
    use_unenforced_foreign_key_for_query_optimization = false
);

L'indice 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. Désactiver cette indication ou 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. Voici 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 sujets suivants ne concernent que les clés étrangères appliquées. Pour les sujets qui s'appliquent aux clés étrangères informatives et appliquées, consultez les articles suivants:

• Modifications de schéma de longue durée
• Index de stockage

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

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

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

Voici un exemple de création d'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 plus d'exemples sur la manière de créer et de gérer des clés étrangères, consultez la section Créer et gérer des relations de clé étrangère.

Remarque :Vous pouvez également ajouter et supprimer des clés étrangères à l'aide de la console Google Cloud. Sélectionnez Modifier en tant que texte dans l'interface de création de table, puis saisissez le LDD pour gérer les clés étrangères de votre table.

Actions sur les clés étrangères

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

Les actions de clé étrangère contrôlent ce qui se passe avec la colonne contrainte lorsque la colonne qu'elle référence est supprimée ou modifiée. Spanner accepte l'utilisation de l'action ON DELETE CASCADE. Avec l'action ON DELETE CASCADE de clé étrangère, lorsque vous supprimez une ligne contenant une clé étrangère référencée, toutes les lignes qui font référence à cette clé sont également supprimées 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 de DDL. 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. Voici un exemple de création d'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 ou ON DELETE NO ACTION.

• Vous pouvez interroger INFORMATION_SCHEMA pour rechercher les contraintes de clé étrangère associées à 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 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 lors du commit d'une transaction ou lorsque les effets des écritures sont rendus visibles pour les opérations suivantes de la transaction.

Une valeur insérée dans la colonne de référence est mise en correspondance avec les 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 appliquées applicables lorsque vous essayez de mettre à jour des données à l'aide d'instructions DML ou d'une API. Toutes les modifications en attente sont annulées si des contraintes sont incorrectes.

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 ses lignes de référence. Lorsque vous utilisez une API de mutation, les mutations sont mises en mémoire tampon jusqu'au commit de la transaction. La validation de la clé étrangère appliquée est différée jusqu'au commit de la transaction. Dans ce cas, il est possible d'insérer d'abord les lignes de référence.

Chaque transaction est évaluée pour détecter les modifications affectant les contraintes de clé étrangère appliquées. Ces évaluations peuvent nécessiter l'envoi de requêtes supplémentaires au serveur. Les index de stockage 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 nécessaire 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. Ajouter une contrainte de clé étrangère avec une action en cascade de suppression à une table ou créer une table avec des contraintes de clé étrangère avec une action en cascade de suppression peut ralentir les opérations de suppression.

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

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

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

L'entrelacement des tables de Spanner est 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 parente. La colocalisation des lignes enfant avec leurs 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 en faisant office de tables parentes dans certaines relations et de tables enfants dans d'autres. Cependant, une relation de clé étrangère n'implique pas la colocalisation des tables dans la couche de stockage.

Prenons l'exemple d'une table Orders définie comme suit:

Figure 3. Schéma de la 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.

Imaginons 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 ajouter une autre clé étrangère appliquée pour représenter cette nouvelle relation un à plusieurs entre Orders et OrderItems. Cependant, vous savez également que vous devez fréquemment 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 des 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.

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. En entrelaçant physiquement Orders et OrderItems de cette manière, vous pouvez améliorer les performances, réaliser efficacement une pré-jointure des tables et accéder aux lignes associées tout en minimisant les accès au disque. Par exemple, Spanner peut effectuer localement des jointures par clé primaire, ce qui minimise les accès au disque et le trafic réseau.

Si le nombre de mutations d'une transaction dépasse 80 000, la transaction échoue. De telles suppressions en cascade fonctionnent bien pour les tables avec une relation "entrelacée dans le parent", mais pas pour les tables avec 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 avec 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 "entrelacé dans le parent".

Remarque : Google recommande de représenter les relations parents-enfants soit comme des tables entrelacées, soit comme des clés étrangères, mais pas les deux. L'utilisation de ces deux fonctionnalités est redondante et peut consommer des ressources de stockage et de calcul supplémentaires afin de vérifier les contraintes et de maintenir les index de sauvegarde.

Tableau de comparaison

Le tableau suivant récapitule les différences entre les clés étrangères appliquées et l'entrelacement des 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'entrelacement de table ne peut pas être supprimé après sa création, sauf si vous supprimez la table enfant en entier.	Oui

Index de stockage

Les clés étrangères n'utilisent pas les index créés par l'utilisateur. Elles créent plutôt leurs propres index de stockage. Les clés étrangères forcées et informatives créent des index de sauvegarde différemment dans Spanner:

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

• Pour les clés étrangères à des fins d'information, Spanner peut créer jusqu'à un index de stockage si nécessaire pour les colonnes référencées. Les clés étrangères informatives ne créent pas d'index de stockage 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. Par conséquent, un indice pour la table référencée n'est généralement pas nécessaire. Par conséquent, les clés étrangères informatives n'ont généralement aucun index de sauvegarde. Si nécessaire, l'index de stockage 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 enfreignent la contrainte d'unicité de l'index.

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

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

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

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

Modifications de schéma de longue durée

L'ajout d'une clé étrangère appliqué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, la table n'est pas accessible en écriture tant que l'opération de longue durée n'est pas terminée.

Le tableau suivant indique 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 informative
Nouveau	Spanner remplit les index référencés selon les besoins pour chaque clé étrangère.	Spanner remplit les index référencés selon les besoins pour 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 sont conformes à la contrainte d'intégrité référentielle de la clé étrangère. La modification du schéma échoue si des données sont incorrectes.	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 :

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

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

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

Ajouter une nouvelle contrainte et supprimer l'ancienne empêche 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 à une clé étrangère existante. Une fois la nouvelle clé étrangère créée 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é.

Supprimer une contrainte peut entraîner la suppression des index de stockage de 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, ajouter la même contrainte de clé étrangère avec une action ultérieure 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.

Remarque :Si vous ne supprimez pas l'ancienne contrainte de clé étrangère, les contraintes de clé étrangère supplémentaires doivent être validées, ce qui entraîne des frais généraux inutiles. De plus, si une violation de contrainte UNIQUE empêche la création de l'index référencé, l'ajout de la nouvelle contrainte peut échouer.

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

Étape suivante

• Découvrez comment créer et gérer des relations de clé étrangère.

• En savoir plus sur le schéma d'informations