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 assure que l'intégrité des données de ces relations est maintenue.
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 via des 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 dans
la table Orders dont l'élément CustomerID ou ProductID est introuvable dans
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 en savoir plus sur la façon dont
Spanner valide les contraintes. Pour en savoir plus, consultez la section Contrainte de transaction
validation 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 la supprimer de celle-ci avec 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.
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 en génère un automatiquement. La nom généré peut être interrogé à partir du schéma 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 le
PRIMARY KEYdu 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 Spanner ne peut pas utiliser la clé primaire de la table référencée, il crée unUNIQUE NULL_FILTERED INDEXsur les colonnes référencées.Spanner peut également utiliser la clé primaire du référencement , bien que cela soit moins courant. Si ce n'est pas le cas, Spanner crée un
NULL_FILTERED INDEXsur 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. Nom des index de sauvegarde peuvent être interrogés à partir de la vue INFORMATION_SCHEMA de Spanner. Pour plus d'informations, consultez la section Index de stockage.
Actions sur les clés étrangères
Les actions de clé étrangère vous permettent de contrôler ce qui se passe avec la colonne contrainte lorsque la colonne qu'elle référence est supprimée ou mise à jour. Spanner accepte l'utilisation de l'action ON DELETE CASCADE. Avec l'action ON DELETE CASCADE de la 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 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 en supprimer une. 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 de la 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 qui une action.
L'ajout d'une action de clé étrangère sur une contrainte de clé étrangère existante n'est pas compatibles. Vous devez ajouter une 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 selon les besoins 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. De plus, 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 sont incorrectes.
Il n'est pas possible d'ajouter une action de clé étrangère à une contrainte existante. Mer nous vous suggérons de procéder comme suit:
- Ajoutez une contrainte avec action.
- Supprimez l'ancienne contrainte sans action.
Cela permet d'éviter un problème 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. La suppression d'une contrainte peut entraîner la suppression des index de stockage de clé étrangère si les 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
des opérations de longue durée, comme le remplissage d'index,
la validation des contraintes d'index uniques et la validation du référentiel de clé étrangère.
de contraintes.
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 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. Lignes avec NULL faisant référence à des valeurs
ne sont pas cochées, c'est-à-dire que 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 essayez 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 une pour les colonnes de référence et une 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 l'ensemble 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.
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 avec une valeur SPANNER_IS_MANAGED de true décrivent les index de stockage.
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.
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 tableaux 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 peut entraîner des milliers d'autres opérations de suppression. L'ajout d'une contrainte de clé étrangère avec une action en cascade de suppression à une table ou la création d'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é déclenche la suppression de tous les enregistrements qui lui sont associés et qui utilisent des clés étrangères. Si le nombre de mutations dans une transaction dépasse 80 000 : la transaction échoue.
Si vous devez supprimer un grand nombre d'enregistrements à l'aide d'une clé étrangère, supprimez en cascade, vous devez supprimer explicitement les lignes des tables enfants avant en supprimant la ligne des tables parentes. Cela évite l'échec de la transaction en raison de la limite de mutation.
Comparaison entre clés étrangères et entrelacement des tables
L'entrelacement des tables Spanner convient à de nombreux où la clé primaire de la table enfant inclut la chaîne des colonnes de clé primaire. 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
Relation parent-enfant utilisant la fonctionnalité 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 localement des jointures par clé primaire, ce qui minimise les accès au disque et le trafic réseau.
Si le nombre de mutations dans 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 avez une table utilisateur avec une relation de clé étrangère avec un autre tableau et la suppression d'une ligne de la table référencée déclenche la suppression de millions de vous devez concevoir votre schéma avec une action de suppression en cascade "entrelacé dans le parent".
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 | 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é 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
Découvrez comment créer et gérer des relations de clé étrangère.
En savoir plus sur le schéma d'informations