Quel est l'avantage des clés étrangères en SQL ?

Qu'est-ce qu'une clé étrangère et pourquoi est-elle si importante dans les bases de données relationnelles ? Découvrez tous les détails sur les clés étrangères dans cet article.

L'une des principales caractéristiques des bases de données relationnelles est la possibilité de lier des données stockées dans différentes tables. Ces liens, appelés références, agissent essentiellement comme des connexions entre les tables. Ils sont créés à l'aide de la contrainte FOREIGN KEY sur les colonnes de la table.

Dans cet article, nous allons apprendre ce que fait la contrainte FOREIGN KEY en SQL. Je vous expliquerai comment la définir à l'aide de l'instruction CREATE TABLE et je vous présenterai quelques exemples. Ensuite, nous parlerons de ses avantages et de ses caractéristiques. Plus précisément :

• Nous apprendrons la relation entre la table primaire (qui fournit ses valeurs de colonne(s) de clé primaire à la table étrangère) et la table étrangère (qui utilise la ou les colonnes fournies par la table primaire comme clé étrangère).
• Nous parlerons de ce qui se passe lorsque les valeurs des colonnes de la table primaire sont supprimées ou modifiées. La contrainte FOREIGN KEY offre plusieurs options dans de telles situations. Je les clarifierai en incluant quelques exemples.
• À la fin, nous discuterons des options de cardinalité qui peuvent être mises en œuvre avec la contrainte . FOREIGN KEY

Commençons.

Qu'est-ce qu'une contrainte de clé étrangère en SQL ?

Pour comprendre le concept de la contrainte FOREIGN KEY en SQL, vous pouvez la considérer comme un lien de référence entre des tables dites primaires (ou parent) et étrangères (ou enfant). La table étrangère fait référence à une ou plusieurs colonnes (la clé primaire, qui peut être une ou plusieurs colonnes) de la table primaire ; c'est ainsi que le lien est créé. Si vous avez besoin de rafraîchir vos connaissances sur les clés primaires en SQL, je vous recommande de lire l'article What Is a Primary Key ?

Un exemple en images

Je pense que certaines aides visuelles pourraient être utiles ici. Regardons les images ci-dessous.

Ici, nous avons la table Airplane (la table primaire) et la table Flight (la table étrangère). La colonne AirplaneId, qui est une colonne clé primaire pour la table Airplane, est utilisée comme colonne clé étrangère dans la table Flight table. C'est ainsi que la relation est créée entre ces tables - la colonne AirplaneId de la table Flight définit quel avion est utilisé pour chaque vol.

Veuillez noter que la colonne AirplaneId de la table étrangère Flight ne doit pas nécessairement contenir toutes les valeurs stockées dans la colonne Airplane.AirplaneId. Elle peut contenir un sous-ensemble de ces valeurs :

Les valeurs contenues dans la colonne Flight.AirplaneId font directement référence aux valeurs contenues dans la colonne Airplane.AirplaneId. Par conséquent, toute action de mise à jour ou de suppression sur les lignes de la table primaire Airplane doit être reflétée en conséquence par la table étrangère Flight. La contrainte FOREIGN KEY offre plusieurs options pour mettre en œuvre ces mises à jour ou suppressions, qui seront abordées plus loin.

Encore une chose importante. L'exemple ci-dessus montre une relation entre les tables Airplane et Flight en utilisant une seule colonne. Il est également possible d'utiliser plusieurs colonnes pour créer de telles relations entre les tables.

Ici, au lieu d'utiliser la colonne AirplaneId pour la contrainte FOREIGN KEY, nous avons décidé d'utiliser les colonnes AirplaneBrand et AirplaneModel, car elles identifient également de manière unique chaque ligne de la table Airplane (en supposant que notre compagnie aérienne ne possède qu'un seul avion de chaque paire marque-modèle répertoriée dans la table).

Vous pouvez remarquer que nous pourrions tout aussi bien placer toutes les données dans une seule table appelée AirplaneFlight. C'est vrai, mais nous ne serions pas en mesure de séparer les données en différentes catégories au sein d'une même table. C'est la raison pour laquelle les clés étrangères sont un élément crucial de la conception des bases de données. Elles nous permettent de placer des données connexes dans plusieurs tables, puis de les relier entre elles pour préserver leur intégrité.

Comment définir la contrainte de clé étrangère en SQL

Nous voulons maintenant intégrer notre conception dans la base de données. Pour ce faire, nous pouvons utiliser l'instruction CREATE TABLE et y définir la contrainte FOREIGN KEY.

Voyons à quoi ressemble l'instruction CREATE TABLE si nous définissons la contrainte FOREIGN KEY sur la base de l'exemple ci-dessus, qui utilise la colonne AirplaneId.

Tout d'abord, nous devons créer la table primaire et définir sa colonne de clé primaire.

CREATE TABLE Airplane (
AirplaneId VARCHAR(10) NOT NULL,
AirplaneBrand VARCHAR(30) NOT NULL,
AirplaneModel VARCHAR(30) NOT NULL,
CONSTRAINT PK_AirplaneId PRIMARY KEY (AirplaneId)
);

Pour en savoir plus sur la contrainte PRIMARY KEY, consultez la rubrique What is a Primary Key in SQL ?

Ensuite, nous créons la table étrangère :

CREATE TABLE Flight (
FlightId VARCHAR(10) NOT NULL,
AirplaneId VARCHAR(10) NOT NULL,
PilotId INTEGER NOT NULL,
CONSTRAINT PK_FlightId PRIMARY KEY (FlightId),
CONSTRAINT FK_AirplaneId FOREIGN KEY (AirplaneId)
REFERENCES Airplane(AirplaneId)
);

Ici, outre la contrainte PRIMARY KEY, nous avons défini la contrainte FOREIGN KEY. Nous lui avons donné le nom FK_AirplaneId. Entre parenthèses, nous avons défini la colonne de la table Flight qui met en œuvre la contrainte FOREIGN KEY. Après le mot-clé REFERENCES, le nom de la table primaire et une colonne suivent.

C'est ainsi que nous pouvons transformer notre concept de conception de base de données en une base de données exploitable.

Il peut arriver que l'on souhaite ajouter la contrainte FOREIGN KEY à la table après sa création. Flight après que la table ait été créée. Pour ce faire, nous utilisons l'instruction ALTER TABLE:

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId);

Nous pouvons également vouloir supprimer une contrainte FOREIGN KEY. Supposons que nous voulions changer la contrainte FOREIGN KEY définie ci-dessus en sa version multicolonne. Nous devons d'abord la supprimer, puis créer la nouvelle clé :

ALTER TABLE Flight DROP FOREIGN KEY FK_AirplaneId;
ALTER TABLE Flight ADD FOREIGN KEY FK_Airplane (AirplaneBrand, AirplaneModel)
REFERENCES Airplane(AirplaneBrand, AirplaneModel);

Désormais, la table<strong>Flight utilise la contrainte multicolonne FOREIGN KEY .

Si vous pensez avoir besoin de plus de pratique en matière de création de tables de base de données, consultez notre cours sur les bases de la création de tables en SQL.

Quels sont les avantages des clés étrangères ?

Maintenant que nous avons appris toutes les bases de la contrainte FOREIGN KEY, ses caractéristiques et les possibilités qu'elle offre pour la fonctionnalité des bases de données, nous pouvons nous plonger plus profondément dans ses avantages.

Comme je l'ai déjà mentionné, lors de la mise en œuvre d'une contrainte FOREIGN KEY, nous pouvons faire la distinction entre la table primaire et la table étrangère.

La table primaire fournit une colonne, ou un ensemble de colonnes, qui est utilisée par la table étrangère. En d'autres termes, la colonne (ou l'ensemble de colonnes) de la table étrangère fait référence à une colonne (ou un ensemble de colonnes) de la table primaire.

Ainsi, le premier avantage de la contrainte FOREIGN KEY est qu'elle garantit l'existence de la ligne référencée dans la table primaire. Si la ligne référencée n'est pas dans la table primaire, elle ne peut pas être présente dans la table étrangère.

Un autre avantage de la contrainte FOREIGN KEY (FK) est qu'elle garantit l'exactitude de la référence même lorsque les valeurs de la table primaire sont modifiées ou supprimées. Il existe une variété d'actions qui indiquent ce qu'il faut faire lors de la mise à jour ou de la suppression d'une ligne de clé primaire (PK). Voyons quelles sont ces actions.

Quand une ligne de clé primaire est supprimée

La contrainte FOREIGN KEY offre un certain nombre d'options concernant l'action à effectuer lors de la suppression d'une ou de plusieurs lignes de clé primaire de la table primaire.

Par défaut, lorsqu'aucune action n'est spécifiée pour la contrainte FOREIGN KEY, vous ne pouvez pas supprimer la ligne concernée dans la table primaire (parent) s'il existe des lignes qui y font référence dans la table étrangère (enfant).

Vous pouvez spécifier l'action à entreprendre lors de la suppression de la ou des lignes PK de la table primaire en choisissant l'une des options suivantes :

• ON DELETE CASCADE
• ON DELETE RESTRICT
• ON DELETE NO ACTION
• ON DELETE SET NULL
• ON DELETE SET DEFAULT

Passons en revue chacune d'entre elles. Tout d'abord, nous allons examiner la syntaxe que toutes ces options partagent.

Syntaxe ON DELETE

La déclaration de l'option ON DELETE suit directement la déclaration de la contrainte FOREIGN KEY.

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON DELETE ;

Examinons maintenant toutes les options.

ON DELETE CASCADE

L'option ON DELETE CASCADE garantit que lorsqu'une ligne est supprimée du PK de la table primaire, la ligne de la table étrangère qui la référence est également supprimée.

Voyons-la en action à l'aide de nos Airplane et Flight en action. Tout d'abord, nous avons les deux tables liées :

Nous supprimons une ligne de la table primaire :

Et maintenant, la ligne qui fait référence à la ligne supprimée est supprimée de la table étrangère.

Pour mettre en œuvre l'option ON DELETE CASCADE, ajoutez-la après la déclaration de la contrainte Y de FOREIGN KE:

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON DELETE CASCADE;

Vous devez penser à mettre en œuvre l'option ON DELETE CASCADE avec la contrainte FOREIGN KEY lorsque vous le souhaitez :

• La possibilité de supprimer des lignes de la table primaire sans aucune restriction.
• La suppression automatique des lignes de la table étrangère lorsque la ligne de référence de la table primaire est supprimée.

SUR LA RESTRICTION DE SUPPRESSION

L'option ON DELETE RESTRICT ne permet pas la suppression des lignes de la clé primaire dans la table primaire. Lorsqu'une telle action est tentée, une erreur est renvoyée.

Cette option est utile pour éviter toute modification indésirable des tables primaires et étrangères.

ON DELETE NO ACTION

L'option ON DELETE NO ACTION est similaire à l'option ON DELETE RESTRICT; elle empêche également la suppression des lignes de la table primaire et renvoie une erreur si une telle action est tentée.

La différence entre ON DELETE NO ACTION et ON DELETE RESTRICT est que, dans certaines bases de données, ON DELETE NO ACTION peut être différé, c'est-à-dire qu'il peut être reporté jusqu'à la fin de la transaction de sorte que l'erreur éventuelle soit lancée après la fin de la transaction - c'est-à-dire qu'au cours de la transaction, la valeur de la clé étrangère peut être fixée, sinon l'erreur est lancée.

ON DELETE SET NULL

L'option ON DELETE SET NULL fait exactement ce qu'elle dit. Lorsqu'une ligne est supprimée de la table primaire, les valeurs des colonnes qui y font référence dans la table étrangère sont définies sur NULL.

Prenons un exemple ci-dessous. Voici à nouveau les deux tables.

Nous supprimons une ligne de la table primaire.

Et maintenant, la valeur de la colonne AirplaneId de la ligne qui fait référence à la ligne supprimée est définie sur NULL dans la table étrangère.

L'option ON DELETE SET NULL peut être utilisée lorsque vous souhaitez autoriser la suppression d'une ligne dans la table primaire mais que vous ne voulez pas qu'elle soit propagée dans la table étrangère. Il existe une empreinte de la suppression dans la table étrangère (la valeur marquée NULL).

ON DELETE SET DEFAULT

L'option ON DELETE SET DEFAULT est quelque peu similaire à l'option ON DELETE SET NULL. Ici, après la suppression d'une ligne dans la table primaire, nous définissons la valeur de la colonne FK de la table étrangère à sa valeur par défaut au lieu de NULL.

Veuillez noter qu'une valeur par défaut doit être spécifiée pour la ou les colonnes de clé étrangère.

Spécifions une valeur par défaut pour la colonne AirplaneId de la table Flight de la table

ALTER TABLE Flight
ALTER AirplaneId
SET DEFAULT 'ABA340';

Nous pouvons maintenant utiliser l'option ON DELETE SET DEFAULT. Après la suppression de la ligne de la table primaire, il se passe ce qui suit :

Vous devez utiliser l'option ON DELETE SET DEFAULT lorsque vous souhaitez autoriser la suppression de lignes dans la table primaire. Le degré de contrôle ici est que vous pouvez choisir quelle valeur est insérée dans la table étrangère lors de la suppression d'une ligne dans la table primaire.

Mise à jour d'une ligne de clé primaire

Les actions offertes par la contrainte FOREIGN KEY sur la mise à jour effectuée dans la table primaire sont similaires aux actions sur la suppression. Il en est de même pour la syntaxe.

Syntaxe de l'option ON UPDATE

La déclaration de l'option ON UPDATE suit directement la déclaration de la contrainte FOREIGN KEY.

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON UPDATE ;

Par défaut, lorsqu'aucune action n'est spécifiée pour la contrainte FOREIGN KEY, il n'est pas possible de modifier les lignes de la table primaire (parent) s'il existe des lignes qui y font référence dans la table étrangère (enfant).

Vous pouvez définir ce qui se passe lors de la mise à jour de la ou des lignes de la table primaire en choisissant l'une des options suivantes :

• ON UPDATE CASCADE
• ON UPDATE RESTRICT
• ON UPDATE NO ACTION
• ON UPDATE SET NULL
• ON UPDATE SET DEFAULT

Passons en revue chacune d'entre elles.

SUR LA MISE À JOUR EN CASCADE

L'option ON UPDATE CASCADE vous permet de modifier les valeurs de la ou des colonnes PK de la table primaire. Ces modifications sont ensuite propagées à la table étrangère.

Voyons cela en action en utilisant nos Airplane et Flight et :

Nous modifions les valeurs de la dernière ligne de la table primaire.

Et maintenant, la ligne qui y fait référence est également mise à jour.

Pour mettre en œuvre l'option ON UPDATE CASCADE, ajoutez-la après la déclaration de la contrainte FOREIGN KEY:

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON UPDATE CASCADE;

Cette option permet de modifier la ou les colonnes de la clé primaire de la table primaire. Toute modification est ensuite propagée à la ou aux clés étrangères.

RESTRICTION LORS DE LA MISE À JOUR

Si vous voulez empêcher toute modification de la clé primaire de la table primaire, utilisez l'option ON UPDATE RESTRICT.

En cas de tentative de modification des valeurs de la clé primaire de la table, une erreur est renvoyée.

ON UPDATE NO ACTION

L'option ON UPDATE NO ACTION est similaire à l'option ON UPDATE RESTRICT. Aucune modification de la valeur PK de la table primaire n'est autorisée.

La différence est que certaines bases de données permettent de reporter les contrôles d'actions jusqu'à la fin de la transaction avec ON UPDATE NO ACTION. Si les valeurs PK de la table primaire sont modifiées lorsque la transaction est terminée, une erreur est renvoyée.

ON UPDATE SET NULL

L'option ON UPDATE SET NULL permet de modifier le PK de la table primaire. Lors de telles mises à jour, les valeurs du FK de la table étrangère sont définies sur NULL.

Prenons un exemple ci-dessous, en commençant par les deux tables :

Nous modifions les valeurs de la dernière ligne de la table primaire.

Et maintenant, la ligne qui y fait référence est définie sur NULL.

ON UPDATE SET DEFAULT

L'option ON UPDATE SET DEFAULT permet de modifier la PK de la table primaire. En conséquence, les valeurs de la clé étrangère sont définies sur leur valeur par défaut. Vous ne pouvez utiliser cette option que si une valeur par défaut est déclarée pour la ou les colonnes de la clé étrangère.

Spécifions la valeur par défaut de la colonne AirplaneId de la table Flight table :

ALTER TABLE Flight
ALTER AirplaneId
SET DEFAULT 'ABA340';

Maintenant, nous pouvons utiliser l'option ON UPDATE SET DEFAULT. Après la mise à jour de la ligne de la table primaire, la situation suivante se produira :

Cardinalité de la contrainte de clé étrangère

Une autre fonctionnalité offerte par la contrainte FOREIGN KEY est sa cardinalité. En définissant la cardinalité, nous pouvons décider de la manière dont la ou les colonnes de la clé étrangère sont liées à la ou aux colonnes de la clé primaire. Les relations peuvent être :

• Une à une (une valeur de ligne PK peut apparaître dans exactement une ligne FK).
• Many-to-one (une valeur de ligne PK peut apparaître dans une ou plusieurs lignes FK).
• Many-to-many (de nombreuses valeurs de lignes PK peuvent apparaître dans de nombreuses lignes FK).

Pour mettre en œuvre la relation biunivoque, nous devons déclarer la ou les colonnes de clé étrangère comme UNIQUE. Dans ce cas, la relation est en fait (un ou zéro)-to-un, mais elle garantit qu'il y a au maximum une ligne de la table étrangère qui se rapporte à une ligne particulière de la table primaire.

En revanche, si la ou les colonnes de la clé étrangère de la table étrangère ne sont pas déclarées comme UNIQUE, la relation entre la ou les colonnes de la clé étrangère et la ou les colonnes de la clé primaire est de plusieurs à un - de nombreuses lignes de la table étrangère peuvent être liées à une seule ligne de la table primaire.

La relation plusieurs-à-plusieurs est mise en œuvre en utilisant une table séparée qui stocke chaque paire de lignes liées.

Remarquez que lorsque nous avons créé cette relation many-to-many, le lien direct entre les tables Airplane et Flight se transforme en une table séparée qui stocke toutes les relations dans les paires AirplaneId-FlightId.

La table AirplaneFlight a une clé étrangère (AirplaneId) qui fait référence à la colonne AirplaneId de la table. Airplane de la table Elle possède également une clé étrangère (FlightId) qui fait référence à la colonne FlightId de la table Flight table. Les clés étrangères Airplane et Flight sont maintenant indirectement liées l'une à l'autre.

Vous voulez en savoir plus sur les clés étrangères en SQL ?

La contrainte FOREIGN KEY est cruciale pour la conception des bases de données relationnelles. Elle nous permet de lier les données en fonction de nos besoins. Comme elle crée certaines dépendances entre les colonnes des tables primaires et étrangères, elle nous permet également de décider de la suite à donner aux actions ON UPDATE et ON DELETE effectuées sur les lignes de la table primaire.

En utilisant la contrainte FOREIGN KEY, nous garantissons l'intégrité, l'exactitude et la compacité des données. Les valeurs utilisées par la table étrangère doivent exister dans la table primaire. Nous pouvons également imposer des règles sur le type de relation, c'est-à-dire une à une, plusieurs à une ou plusieurs à plusieurs.

Si vous souhaitez en savoir plus sur les clés étrangères, consultez notre article intitulé " Qu'est-ce qu'une clé étrangère en SQL ?

Pour en savoir plus sur la structure et la conception d'une base de données, je vous recommande de consulter notre piste sur la création d'une structure de base de données, qui comprend tous les éléments essentiels. Vous pouvez vous renseigner sur cette piste ici avant de vous y plonger.