Clés étrangères

Cet article décrit les clés étrangères dans Cloud Spanner et explique comment les utiliser pour appliquer l'intégrité référentielle dans votre solution de base de données.

Présentation

Les clés étrangères vous permettent de définir des relations entre les tables. Cloud Spanner garantit l'intégrité des données de ces relations.

Imaginons que vous soyez le 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 concernant chaque commande, chaque client et chaque 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 client, une table Orders pour effectuer le suivi de toutes les commandes passées et une table Products pour stocker des informations sur chaque produit que les clients peuvent commander.

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

  • Une commande a été passée par un client.

  • Une commande a été passée pour un produit.

Vous décidez que votre base de données doit appliquer les règles suivantes pour vous assurer que les commandes de notre 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 nous appliquons ces règles, ou contraintes, nous affirmons que nous maintenons l'intégrité référentielle de nos 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. Cloud Spanner renforce l'intégrité référentielle par le biais de clés étrangères.

Appliquer l'intégrité du référentiel avec des clés étrangères

Examinons à nouveau notre exemple de traitement des commandes, avec plus de détails ajoutés à la conception, comme illustré à la figure 2.

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

Figure 2. Schéma de notre base de données avec des clés étrangère

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 garantit que toutes les lignes de Orders ont un champ CustomerID valide. La clé étrangère FK_ProductOrder garantit que toutes les valeurs ProductID de la table Orders sont valides. Le tableau suivant fait correspondre ces contraintes aux règles réelles que nous voulons appliquer.

Nom de la clé étrangère Contrainte Description réaliste
FK_CustomerOrder Garantit que toutes les lignes de Orders ont un CustomerID valide Une commande a été passée par un client valide.
FK_ProductOrder Garantit que toutes les lignes de Orders ont un ProductID valide Une commande a été passée pour un produit valide.

Cloud 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. Elle échoue également 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 plus d'informations sur la façon dont Cloud Spanner valide les contraintes, reportez-vous à la section Validation des contraintes de transaction ci-dessous.

Définir des clés étrangères

Les clés étrangères sont créées et supprimées de votre base de données Cloud Spanner à l'aide du LDD. Les clés étrangères sont ajoutées à une nouvelle table avec l'instruction CREATE TABLE. De même, une clé étrangère peut être ajoutée à une table existante ou supprimée de celle-ci avec l'instruction ALTER TABLE. Voici un exemple de création d'une table avec une clé étrangère.

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

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.

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

  • La table qui définit la clé étrangère correspond à la table refering, et les colonnes de clé étrangère sont les colonnes referenceing.

  • 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 ci-dessus, vous pouvez nommer chaque contrainte de clé étrangère. Si vous n'en spécifiez aucun, Cloud Spanner génère un nom automatiquement. Le nom généré peut être interrogé à partir de la colonne INFORMATION_SCHEMA de Cloud 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 de classement est important. La première colonne de référence correspond à la première colonne référencée, la deuxième à la deuxième, etc.

  • Une colonne de référence et sa contrepartie référencée doivent être du même type. Les colonnes doivent également être indexables.

  • Il est impossible de créer des clés étrangères sur des colonnes comportant 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é externe "auto-référencée"). Par exemple, une table "Employé" 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. Par conséquent, 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. Cloud Spanner utilise le PRIMARY KEY du tableau référencé si les colonnes référencées de la clé étrangère correspondent aux colonnes de clé primaire de la table référencée. Si Cloud Spanner ne peut pas utiliser la clé primaire de la table référencée, il crée une UNIQUE NULL_FILTERED INDEX sur les colonnes référencées.

  • Cloud Spanner peut également utiliser la clé primaire de la table de référence, bien que ce paramètre soit moins courant. Si ce n'est pas le cas, Cloud Spanner crée un NULL_FILTERED INDEX sur les colonnes de référence.

  • Les clés étrangères n'utilisent pas les index secondaires que vous avez créés, mais créent leurs propres index de stockage. Ces index de stockage sont utilisables dans les évaluations de requête, y compris dans les directives explicites force_index. Le nom des index de stockage peut être interrogé à partir de INFORMATION_SCHEMA de Cloud Spanner. Pour en savoir plus, consultez la page Index de sauvegarde.

Modifications de schéma de longue durée

L'ajout d'une clé étrangère à 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.

Lors de la création d'une table avec une clé étrangère, Cloud Spanner doit remplir les index référencés selon les besoins pour chaque clé étrangère.

Lorsque vous ajoutez une clé étrangère à une table existante, Cloud Spanner doit remplir les index de référence et référencés si nécessaire. En outre, Cloud Spanner valide 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 échouera si des données ne sont pas valides.

Les modifications de schéma ci-dessus peuvent échouer si l'index référencé ne peut pas être créé en raison d'une violation de contrainte UNIQUE.

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

Validation des contraintes pour une transaction

Cloud Spanner valide les contraintes de clé étrangère 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 ou les colonnes 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, c'est-à-dire qu'elles peuvent être ajoutées à la table de référence.

Cloud Spanner valide toutes les contraintes référentielles pour les clés étrangères applicables lors de la tentative de mise à jour de données via des instructions LMD ou 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. Vous devez, par exemple, 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 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. 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.

Index de stockage

Les clés étrangères n'utilisent pas les index créés par l'utilisateur. Elles créent leurs propres index de stockage.

Cloud 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. Cependant, 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, le deuxième index de la table référencée n'est généralement pas nécessaire.

L'index de stockage de 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. 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, Cloud 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. Les utilisateurs ne peuvent pas modifier ni supprimer les index de stockage.

Les index de stockage de clé étrangère peuvent être utilisés dans les évaluations de requêtes. Leurs noms peuvent être interrogés à partir de INFORMATION_SCHEMA de Cloud Spanner.

Comparaison entre clés étrangères et entrelacement des tables

L'entrelacement des tables de Cloud Spanner est un bon choix pour les relations parents-enfants dans lesquelles la clé primaire de la table enfant inclut les colonnes de clé primaire de la table parente. La colocation des lignes enfants avec leurs lignes parentes 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.

Examinons un exemple utilisant le schéma de traitement des commandes dont nous avons parlé plus tôt dans cet article. Rappel : notre table Orders a été définie comme suit :

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

Figure 3. Schéma de notre base de données avec des clés étrangère

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

Imaginons que nos clients nous indiquent qu'ils souhaitent pouvoir commander plusieurs produits par commande. Nous pouvons améliorer notre conception en introduisant une table OrderItems contenant une entrée pour chaque produit commandé par le client. Nous pouvons introduire une autre clé étrangère pour représenter cette nouvelle relation un à plusieurs entre Orders et OrderItems. Cependant, nous savons également que nous devons fréquemment exécuter des requêtes sur les commandes et leurs articles respectifs. La colocalisation de ces données permettrait d'améliorer les performances. Nous allons donc créer la relation parent-enfant à l'aide de la fonctionnalité d'entrelacement des tables de Cloud Spanner.

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

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;

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 un-à-plusieurs entre les commandes et la nouvelle table OrderItems entrelacée

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

Dans cette configuration, nous pouvons 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 de OrderItems de cette manière peut améliorer les performances. Il permet de joindre les tables et d'accéder aux lignes associées simultanément tout en limitant les accès au disque. Par exemple, Cloud Spanner peut effectuer des jointures par clé primaire localement, réduisant ainsi l'accès au disque et le trafic réseau.

Résumé

Le tableau suivant récapitule les différences entre les clés étrangères 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
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 No
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.
Réussit si les valeurs référencées sont nulles.
Réussit si toutes les valeurs référencées ne sont pas nulles et que la table référencée comporte une ligne avec des valeurs égales aux valeurs de référence.
Échec 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é facilement 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

Étape suivante