Cette page explique comment gérer les relations de clé étrangère dans votre base de données.
Une clé étrangère est une colonne partagée entre les tables pour établir une un lien entre des données connexes. Lorsque vous utilisez une clé étrangère, Spanner s'assure que cette relation soit maintenue.
Le schéma suivant illustre un schéma de base de données simple dans lequel les données d'une table ont un aux données d'un autre tableau.
Figure 1 : Schéma d'une base de données de traitement des commandes
Le schéma de la figure 1 comprend trois tables:
- La table
Customers
enregistre les noms de chaque client. - Les tables
Orders
permettent d'effectuer le suivi de toutes les commandes passées. - La table
Products
stocke les informations sur chaque produit.
Il existe deux relations de clé étrangère entre ces tables :
Une relation clé étrangère est définie entre la table
Orders
et la tableCustomers
pour garantir qu'une commande ne puisse pas être créée, à moins qu'il n'y ait un client correspondant.Une relation de clé étrangère entre les tables
Orders
etProducts
garantit qu'il n'est pas possible de créer une commande pour un produit qui n'existe pas.
En utilisant le schéma précédent comme exemple, cette rubrique présente le schéma
Des instructions CONSTRAINT
DDL (langage de définition) permettant de gérer
les relations entre les tableaux
d'une base de données.
Ajouter une clé étrangère lors de la création d'une table
Supposons que vous avez créé une table Customers
dans votre produit simple
de commande. Vous avez maintenant besoin d'une table Orders
pour stocker les informations sur le
commandes passées par les clients. Pour garantir la validité de toutes les commandes,
laissez le système insérer des lignes dans la table Orders
, sauf s'il existe également
l'entrée correspondante dans la table Customers
. Par conséquent, vous avez besoin
d'une clé étrangère pour
établir une relation
entre les deux tableaux. Vous pouvez ajouter
CustomerID
à la nouvelle table et l'utiliser comme clé étrangère pour créer une
avec la colonne CustomerID
de la table Customers
.
Lorsque vous créez une table avec une clé étrangère, vous utilisez REFERENCE
.
pour établir une relation
avec un autre tableau. La table
qui contient l'instruction REFERENCE
est la table de référence. La
nommée dans l'instruction REFERENCE
est la table référencée. La colonne
nommée dans l'instruction REFERENCE
est appelée colonne de référence.
L'exemple suivant montre comment utiliser l'instruction LDD CREATE TABLE
pour
créez la table Orders
avec une contrainte de clé étrangère qui référence
CustomerID
dans la table Customers
.
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)
);
L'instruction précédente contient une clause CONSTRAINT
comportant les éléments suivants :
caractéristiques:
Utilisation de la syntaxe
CONSTRAINT
pour nommer une contrainte, ce qui en fait plus facile de supprimer le tableau en utilisant le nom que vous avez choisi.La contrainte est nommée
FK_CustomerOrder
. Les noms de contraintes sont limités à le schéma et doit être unique dans le schéma.La table
Orders
, sur laquelle vous définissez la contrainte, correspond au de référence. La tableCustomers
est la table de référence.La colonne de référence dans le tableau de référence est
CustomerID
. Il référence le champCustomerID
de la tableCustomers
. Si quelqu'un essaie pour insérer une ligne dansOrders
avec un élémentCustomerID
qui n'existe pas dansCustomers
, l'insertion échoue.
L'exemple suivant montre une autre instruction de création de table. Ici, la contrainte de clé étrangère est définie sans nom. Lorsque vous utilisez cette syntaxe, Spanner génère un nom pour vous. Pour découvrir les noms de toutes les clés étrangères, reportez-vous à la section Afficher les propriétés d'une relation de clé étrangère.
GoogleSQL
CREATE TABLE Orders (
OrderID INT64 NOT NULL,
CustomerID INT64 NOT NULL,
ProductID INT64 NOT NULL,
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,
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
PRIMARY KEY (OrderID)
);
Ajouter une clé étrangère à une table existante
Vous voulez également vous assurer que les clients
ne peuvent commander que les produits
existent. Tout d'abord, vous devez supprimer la contrainte existante. Ensuite, vous pouvez utiliser
ALTER TABLE
pour ajouter une autre contrainte de clé étrangère à la table Orders
, comme
comme indiqué ci-dessous:
ALTER TABLE Orders
ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID);
La colonne de référence dans Orders
est ProductID
et fait référence à la colonne ProductID
dans la table Products. Encore une fois, si Spanner vous convient
contraintes, utilisez la syntaxe suivante:
ALTER TABLE Orders
ADD FOREIGN KEY (ProductID) REFERENCES Products (ProductID);
Ajouter une clé étrangère avec une action de suppression lors de la création d'une table
Souvenez-vous de l'exemple précédent, où vous avez une table Customers
dans un produit.
de commande qui nécessite une table Orders
. Vous souhaitez ajouter une clé étrangère
qui référence la table Customers
. Cependant, vous devez vous assurer
que lorsque vous supprimez
un enregistrement client, Spanner
supprime également toutes les commandes de ce client. Dans ce cas, il est préférable d'utiliser
Action ON DELETE CASCADE
avec la contrainte de clé étrangère.
L'instruction LDD CREATE TABLE
suivante pour la table Orders
inclut l'élément
contrainte de clé étrangère qui référence la table Customers
avec une action ON DELETE
CASCADE
.
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) ON DELETE CASCADE
) PRIMARY KEY (OrderID);
PostgreSQL
CREATE TABLE Orders (
OrderID BIGINT NOT NULL,
CustomerID BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
ProductID BIGINT NOT NULL,
FOREIGN KEY (CustomerID)
REFERENCES Customers (CustomerID) ON DELETE CASCADE,
PRIMARY KEY (OrderID)
);
L'instruction précédente contient une contrainte de clé étrangère avec une clause
ON DELETE CASCADE
. La colonne CustomerID
est une clé étrangère qui
référence le champ CustomerID
de la table Customers
. Cela signifie que chaque
La valeur CustomerID
de la table Orders
doit également exister dans le Customers
tableau. Si quelqu'un tente de supprimer une ligne de la table Customers
, toutes les
les lignes de la table Orders
qui font référence à la valeur CustomerID
supprimée sont
également supprimé
dans la même transaction.
Ajouter une clé étrangère avec une action de suppression à une table existante
Vous voulez aussi vous assurer que les commandes
ne sont créées que pour les produits
existent. Vous pouvez utiliser ALTER TABLE
pour ajouter une autre contrainte de clé étrangère avec
ON DELETE CASCADE
à la table des commandes, comme suit:
ALTER TABLE Orders
ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID)
REFERENCES Products (ProductID) ON DELETE CASCADE;
La suppression d'une ligne de la table Products
entraîne la suppression de toutes les lignes de la
Table Orders
qui fait référence à la valeur ProductID
supprimée.
Interroger des données sur les relations de clé étrangère
SELECT * FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Products ON Orders.ProductsID = Products.ProductID;
Exemples de la façon dont Spanner conserve l'intégrité référentielle
L'ajout de relations de clés étrangères permet principalement à Spanner de gérer intégrité référentielle, vos données. Si vous modifiez des données d’une manière qui rompt une contrainte de clé étrangère, la mise à jour échoue et une erreur s'affiche.
Examinez les données de la figure 2. Certains clients ont commandé des produits, comme indiqué dans la table des commandes. Grâce aux clés étrangères en place, vous
peut garantir que les données insérées dans la table Orders
ont bien
et l'intégrité référentielle.
Figure 2. Exemples de données dans l'ordre de bases de données.
Les exemples suivants montrent ce qui se passe lorsque vous essayez de modifier les données d'une qui briserait l'intégrité référentielle.
Ajoutez à la table
Orders
une ligne dont la valeurCustomerID
n'est pas existe dansCustomers
Que se passe-t-il si vous essayez la modification suivante à partir de l'échantillon de données du diagramme précédent ?
INSERT INTO Orders (OrderID, ProductID, Quantity, CustomerID) VALUES (19, 337876, 4, 447);
Dans ce cas, le système tentera d'insérer une ligne dans
Orders
avec unCustomerID
(447) qui n'existe pas dans la tableCustomers
. Si le système cela aurait entraîné une commande non valide dans votre système. Toutefois, grâce au contrainte de clé étrangère que vous avez ajoutée à votre tableOrders
, votre table est protégées.INSERT
échoue avec le message suivant, en supposant que est appeléeFK_CustomerOrder
.Foreign key constraint `FK_CustomerOrder` is violated on table `Orders`. Cannot find referenced values in Customers(CustomerID).
Tentative de suppression d'une ligne de la table
Customers
lorsque le client référencées dans une contrainte de clé étrangère.Imaginons qu'un client se désabonne de votre boutique en ligne. Vous souhaitez supprimer le client de votre backend. Vous essayez donc d'effectuer les opérations suivantes : opération.
DELETE FROM Customers WHERE CustomerID = 721;
Dans cet exemple, Spanner détecte via la contrainte de clé étrangère que la table
Orders
contient toujours des enregistrements faisant référence au client ligne que vous essayez de supprimer. Dans ce cas, l'erreur suivante s'affiche.Foreign key constraint violation when deleting or updating referenced row(s): referencing row(s) found in table `Orders`.
Pour résoudre ce problème, commencez par supprimer toutes les entrées faisant référence à
Orders
. Toi vous pouvez également définir la clé étrangère avec l'actionON DELETE CASCADE
pour indiquer Spanner gère la suppression des entrées de référencement.
Afficher les propriétés d'une relation de clé étrangère
Le schéma INFORMATION_SCHEMA de Spanner contient des informations sur les clés étrangères et et leurs index de sauvegarde. Voici quelques exemples de questions auxquelles vous pouvez répondre en interrogeant le SCHÉMA D'INFORMATION.
Pour plus d'informations sur les index de sauvegarde, consultez la section Index de sauvegarde de clés étrangères.
Quelles sont les contraintes définies dans ma base de données ?
SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY';
Quelles clés étrangères sont définies dans ma base de données ?
SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.SPANNER_STATE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;
Quels index sont des index secondaires pour les clés étrangères, également appelés index de sauvegarde ?
Les index de sauvegarde de clés étrangères sont gérés par Spanner. Par conséquent , l'interrogation
SPANNER_IS_MANAGED
sur la vue INDEXES
renvoie tous les index de sauvegarde.
SELECT i.TABLE_NAME, i.INDEX_NAME, i.INDEX_TYPE, i.INDEX_STATE,
i.IS_UNIQUE, i.IS_NULL_FILTERED, i.SPANNER_IS_MANAGED
FROM INFORMATION_SCHEMA.INDEXES as i
WHERE SPANNER_IS_MANAGED = 'YES';
Quelle est l'action référentielle définie avec la contrainte de clé étrangère ?
SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.DELETE_RULE,
rc.UPDATE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;
Pour en savoir plus, consultez la page Schéma d'informations.
Supprimer une relation de clé étrangère
Le LDD suivant supprime une contrainte de clé étrangère dans la table Orders
.
ALTER TABLE Orders
DROP CONSTRAINT FK_CustomerOrder;
Les index de stockage de clé étrangère sont supprimés automatiquement lorsque la contrainte elle-même est supprimée.
Compatibilité avec les relations de clé étrangère plus complexes
Plusieurs colonnes
Les clés étrangères peuvent faire référence à plusieurs colonnes. La liste des colonnes forme une clé correspondant à la clé primaire d'une table ou à un index de stockage. La table référençante contient les clés étrangères de la clé de table référencée.
Dans l'exemple suivant, les définitions de clé étrangère indiquent à Spanner
que chaque valeur SongName
dans la table TopHits doit avoir une valeur correspondante dans
le tableau des chansons ; et chaque paire de valeurs SingerFirstName
et SingerLastName
doit avoir une paire de valeurs FirstName
et LastName
correspondante dans la table Singers
tableau.
GoogleSQL
CREATE TABLE TopHits (
Rank INT64 NOT NULL,
SongName STRING(MAX),
SingerFirstName STRING(MAX),
SingerLastName STRING(MAX),
-- Song names must either be NULL or have matching values in Songs.
FOREIGN KEY (SongName) REFERENCES Songs (SongName),
-- Singer names must either be NULL or have matching values in Singers.
FOREIGN KEY (SingerFirstName, SingerLastName)
REFERENCES Singers (FirstName, LastName)
) PRIMARY KEY (Rank);
PostgreSQL
CREATE TABLE TopHits (
Rank BIGINT NOT NULL,
SongName VARCHAR,
SingerFirstName VARCHAR,
SingerLastName VARCHAR,
-- Song names must either be NULL or have matching values in Songs.
FOREIGN KEY (SongName) REFERENCES Songs (SongName),
-- Singer names must either be NULL or have matching values in Singers.
FOREIGN KEY (SingerFirstName, SingerLastName)
REFERENCES Singers (FirstName, LastName),
PRIMARY KEY (Rank)
);
Références circulaires
Les tables peuvent parfois présenter des dépendances circulaires, par exemple pour des raisons d'héritage de versions anciennes ou de dénormalisation. Les clés étrangères Spanner autorisent les références circulaires.
Étant donné qu'une table référencée doit préalablement exister pour qu'une clé étrangère puisse la référencer, l'une des clés étrangères doit être ajoutée à l'aide d'une instruction ALTER TABLE
. Exemple :
- Créer une table TableA, sans clé étrangère
- Créez une table TableB avec une contrainte de clé étrangère sur la table TableA.
- Utilisez
ALTER TABLE
sur TableA pour créer une référence de clé étrangère à TableB.
Tables d'auto-référencement
Un type spécial de référence circulaire existe. Il consiste en une table qui définit une clé étrangère faisant référence à la même table. Par exemple, l'extrait suivant montre l'utilisation d'une clé étrangère de sorte que le ManagerId d'un employé soit également un employé.
GoogleSQL
CREATE TABLE Employees (
EmployeeId INT64 NOT NULL,
EmployeeName STRING(MAX) NOT NULL,
ManagerId INT64,
FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId)
) PRIMARY KEY (EmployeeId);
PostgreSQL
CREATE TABLE Employees (
EmployeeId BIGINT NOT NULL,
EmployeeName VARCHAR NOT NULL,
ManagerId BIGINT,
FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId),
PRIMARY KEY (EmployeeId)
);
Étape suivante
En savoir plus sur la compatibilité des clés étrangères dans Spanner.
Apprenez-en plus sur le schéma d'information de Spanner.