Clés étrangères

Cet article décrit les clés étrangères dans 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. Spanner garantit que l'intégrité des données de ces relations est préservée.

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 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 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. Spanner applique l'intégrité référentielle à l'aide de clés étrangères.

Appliquer l'intégrité référentielle 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.

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

Spanner échoue toute transaction qui tente d'insérer ou de mettre à jour une ligne de la table Orders dont l'élément CustomerID ou ProductID est 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 de la table Orders. Pour plus d'informations sur la manière dont Spanner valide les contraintes, consultez la section Validation de la contrainte 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 Spanner à l'aide du LDD. Les clés étrangères sont ajoutées à une nouvelle 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. Voici un exemple de création d'une table avec une clé étrangère.

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

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.

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 ci-dessus, vous pouvez nommer chaque contrainte de clé étrangère. Si vous ne spécifiez pas de nom, Spanner génère un nom automatiquement. Le nom généré peut être interrogé à partir de la vue INFORMATION_SCHEMA de Spanner. Les noms de contrainte sont limités au schéma, ainsi que les noms des tables et des index, par exemple, 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.

• Les clés étrangères ne peuvent pas être créées 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é 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. Spanner utilise la valeur PRIMARY KEY de la table référencée 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 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.

• Spanner peut également utiliser la clé primaire de la table de référence, bien que cela soit moins courant. Si ce n'est pas le cas, Spanner crée un objet 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, 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 sauvegarde peut être interrogé à partir de la vue INFORMATION_SCHEMA de Spanner. Pour plus d'informations, consultez la section Index de stockage.

Actions clés étrangères

Les actions de clé étrangère vous permettent de contrôler ce qu'il advient de la colonne contrainte lorsque la colonne référencée est supprimée ou mise à jour. Spanner permet l'utilisation de l'action ON DELETE CASCADE. Avec l'action de clé étrangère ON DELETE CASCADE, 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 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. Voici un exemple de création d'une table avec une action de clé étrangère.

GoogleSQL

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

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 Customers (
  CustomerId bigint NOT NULL,
  CustomerName character varying(1024) NOT NULL,
  PRIMARY KEY(CustomerId)
);

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 sur les clés étrangères sont ON DELETE CASCADE ou ON DELETE NO ACTION.

• Vous pouvez interroger les vues INFORMATION_SCHEMA pour rechercher les contraintes de clé étrangère ayant une action.

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

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.

Pour une nouvelle table avec une clé étrangère, Spanner doit remplir les index référencés si nécessaire pour chaque clé étrangère.

Pour une table existante avec une clé étrangère, Spanner doit remplir les index de référence et référencés si nécessaire. En outre, Spanner valide 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.

Il n'est pas possible d'ajouter une action de clé étrangère à une contrainte existante. Nous vous suggérons d'effectuer les opérations suivantes:

1. Ajout d'une nouvelle contrainte avec action.
2. Supprimez l'ancienne contrainte sans action.

Cela permet d'éviter un problème de Long-running Alter Constraint Operation. Une fois la nouvelle clé étrangère créée avec l'action ON DELETE CASCADE, l'effet net des deux contraintes est DELETE CASCADE. L'abandon d'une contrainte peut entraîner la suppression des index de sauvegarde de clé étrangère si ces index ne sont pas utilisés dans d'autres contraintes de clé étrangère. Par la suite, si l'utilisateur ajoute la même contrainte de clé étrangère avec action, des opérations de longue durée peuvent être nécessaires, telles que le remplissage d'index, la validation des contraintes d'index uniques et la validation des contraintes référentielles de clé étrangère.

Remarque :Si vous ne supprimez pas l'ancienne contrainte de clé étrangère, la validation des contraintes de clé étrangère supplémentaire entraîne des frais.

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

Spanner valide les contraintes de clé étrangère lorsqu'une transaction est validée ou lorsque les effets des écritures sont rendus visibles pour les opérations ultérieures 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. En d'autres termes, vous pouvez les ajouter à la table de référence.

Spanner valide toutes les contraintes référentielles de clé étrangère applicables lorsque vous tentez de mettre à jour des données via des instructions LMD ou 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. 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.

Spanner peut créer jusqu'à deux index de sauvegarde secondaires pour chaque clé étrangère, l'un pour les colonnes de référence et l'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 nécessitent le même index de soutien, Spanner crée un index unique pour toutes. 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.

Spanner utilise le schéma d'informations de chaque base de données pour stocker des métadonnées sur les index de sauvegarde. Les lignes dans INFORMATION_SCHEMA.INDEXES avec une valeur SPANNER_IS_MANAGED de true décrivent des 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 sauvegarde d'une base de données.

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 référencées qui référencent la ligne supprimée. Cela peut entraîner un effet en cascade : une seule opération de suppression peut entraîner 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 peuvent ralentir les opérations de suppression.

Limite de mutation dépassée pour la cascade de suppressions de 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é déclenche la suppression de tous les enregistrements associés qui utilisent des clés étrangères. Si le nombre de mutations d'une transaction dépasse 80 000, la transaction échoue.

Si vous devez supprimer un grand nombre d'enregistrements à l'aide d'une cascade de suppression de clé étrangère, vous devez supprimer explicitement les lignes des tables enfants avant de les supprimer des tables parentes. Cela permet d'éviter l'échec de la transaction en raison de la limite de mutations.

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

L'entrelacement de 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 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 :

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 ajouter 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 améliorerait les performances. Nous allons donc créer la relation parent-enfant à l'aide de la capacité d'entrelacement de tables de Spanner.

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

GoogleSQL

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 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, 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. 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 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 d'une transaction dépasse 80 000, la transaction échoue. Les suppressions en cascade aussi volumineuses fonctionnent bien pour les tables dont la relation est "entrelacée dans le parent", mais pas pour celles 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 avez 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ée 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 des deux est redondante et peut consommer davantage de stockage et de ressources de calcul pour vérifier les contraintes et gérer des index de sauvegarde.

Tableau de comparaison

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	Non	Oui
Nombre de parents acceptés	0 .. 1	0 .. N
Stocke les données parent et enfant ensemble	Oui	Non
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é 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

Étapes suivantes

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

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