Clé étrangère SQL : comprendre son rôle et la créer dans une base de données

Imaginez un fichier de clients et un fichier de commandes. Chaque commande doit être rattachée au bon client. Mais que se passe-t-il si une commande indique un numéro de client qui n’existe pas ? Ou si vous supprimez un client encore associé à plusieurs commandes ? C’est précisément le genre de problème qu’une clé étrangère permet d’éviter.

Dans une base de données, cette contrainte aide à garder des informations cohérentes et bien reliées. Voyons à quoi elle sert, comment la créer et quelles précautions prendre pour l’utiliser sans mauvaise surprise.

Qu’est-ce qu’une clé étrangère ?

Une clé étrangère est une colonne, ou un groupe de colonnes, qui fait référence à une clé d’une autre table. Elle représente un lien entre les deux tables et indique à la base de données comment leurs lignes sont associées.

La table qui contient la clé étrangère est généralement appelée la table enfant. La table dont la clé est référencée est la table parent. Par exemple, une table commandes peut contenir une colonne client_id qui renvoie à l’identifiant du client dans la table clients.

La clé étrangère ne sert donc pas seulement à documenter les liens entre les données. Elle demande au système de vérifier que ces liens sont valides. Si une commande désigne un client inexistant, la base peut refuser son enregistrement.

À quoi sert-elle dans une base de données ?

Son rôle principal est de préserver l’intégrité référentielle, autrement dit la cohérence des relations entre les tables. La base empêche ainsi certaines erreurs avant qu’elles ne se transforment en casse-tête dans un rapport ou une application.

• Éviter les références inexistantes : une commande ne peut pas être rattachée à un identifiant client absent de la table des clients.

• Encadrer les suppressions : la base peut empêcher la suppression d’un client qui possède encore des commandes, ou appliquer une règle prévue à l’avance.

• Clarifier la structure des données : le lien entre deux tables est explicite, ce qui facilite la lecture du modèle et le travail des personnes qui le maintiennent.

• Limiter les incohérences : les règles sont vérifiées directement par la base, et pas uniquement par le code de chaque application qui la consulte.

Sans cette contrainte, rien n’empêche nécessairement d’ajouter une commande pour le client numéro 8472, même si aucun client ne porte cet identifiant. La ligne existe alors, mais son rattachement est fantôme. Pratique pour un roman de science-fiction ; beaucoup moins pour une gestion commerciale.

Clé étrangère et clé primaire : quelle différence ?

Ces deux notions se complètent, mais elles n’ont pas le même rôle. Une clé primaire identifie de manière unique chaque ligne d’une table. Une clé étrangère fait référence à une clé d’une autre table pour établir une relation.

Dans la table clients, la colonne id peut être la clé primaire. Chaque client possède alors un identifiant distinct. Dans la table commandes, la colonne client_id peut être une clé étrangère vers clients.id. Plusieurs commandes peuvent ainsi désigner le même client.

Dans une relation classique, la clé étrangère pointe vers une clé primaire ou une autre colonne assortie d’une contrainte d’unicité. Les colonnes reliées doivent également avoir des types compatibles. Une colonne numérique ne devrait pas être mise en relation avec une colonne de texte sous prétexte qu’elles contiennent parfois les mêmes caractères.

Créer une clé étrangère lors de la création des tables

Prenons un exemple de boutique. Chaque commande appartient à un client, tandis qu’un client peut passer plusieurs commandes. On peut créer les tables ainsi :

CREATE TABLE clients (    id INT PRIMARY KEY,    nom VARCHAR(100) NOT NULL);CREATE TABLE commandes (    id INT PRIMARY KEY,    date_commande DATE NOT NULL,    client_id INT NOT NULL,    CONSTRAINT fk_commandes_clients        FOREIGN KEY (client_id)        REFERENCES clients(id));

La contrainte nommée fk_commandes_clients indique que la valeur de client_id dans commandes doit correspondre à une valeur existante de id dans clients. La colonne client_id est aussi déclarée NOT NULL : chaque commande doit donc avoir un client.

Le nom de la contrainte est choisi par la personne qui conçoit la base. Un nom descriptif permet de la retrouver plus facilement lors d’une modification. Les règles de nommage précises peuvent varier selon le système utilisé, mais une forme comme fk_table_enfant_table_parent reste facile à comprendre.

Essayez maintenant d’insérer une commande avec client_id égal à 42 alors qu’aucun client ne possède l’identifiant 42. Le système refusera normalement l’opération. En revanche, si le client 42 existe, la commande pourra être ajoutée.

Ajouter une clé étrangère à une table existante

Il n’est pas nécessaire de prévoir toutes les contraintes dès le premier jour. Si les tables existent déjà, vous pouvez ajouter une clé étrangère avec une commande d’altération :

ALTER TABLE commandesADD CONSTRAINT fk_commandes_clientsFOREIGN KEY (client_id)REFERENCES clients(id);

Avant d’ajouter la contrainte, vérifiez toutefois les données déjà présentes. Si une commande contient un identifiant absent de la table des clients, la base peut refuser la modification. Il faudra alors corriger ces lignes, leur attribuer un client valide ou définir une stratégie adaptée à votre situation.

Cette vérification préalable est particulièrement importante sur une base déjà utilisée par une application. Une contrainte nouvelle peut révéler des erreurs anciennes que personne n’avait encore remarquées. Ce n’est pas la base qui crée le désordre : elle cesse simplement de le laisser passer.

Que se passe-t-il lorsqu’une ligne est modifiée ou supprimée ?

La création d’une clé étrangère établit le lien, mais il faut aussi décider ce qui doit arriver si la ligne parent change ou disparaît. SQL permet de préciser ce comportement avec des règles telles que ON DELETE et ON UPDATE. Les possibilités exactes dépendent du système de base de données.

• RESTRICT ou NO ACTION : la suppression ou la modification est refusée si des lignes enfant dépendent encore de la ligne parent. C’est souvent le choix le plus prudent.

• CASCADE : l’action est répercutée sur les lignes liées. Si un client est supprimé, ses commandes peuvent l’être également. Puissant, mais à manier avec précaution.

• SET NULL : la clé étrangère des lignes enfant est remplacée par une valeur vide. Cette règle suppose que la colonne accepte les valeurs nulles.

• SET DEFAULT : la colonne prend sa valeur par défaut, si le système et la définition de la table permettent ce comportement.

Par exemple, pour effacer automatiquement les commandes d’un client supprimé, on pourrait écrire :

CREATE TABLE commandes (    id INT PRIMARY KEY,    date_commande DATE NOT NULL,    client_id INT NOT NULL,    CONSTRAINT fk_commandes_clients        FOREIGN KEY (client_id)        REFERENCES clients(id)        ON DELETE CASCADE);

Attention : CASCADE peut supprimer beaucoup plus de données qu’on ne l’imagine. Dans une application réelle, effacer un client peut entraîner l’effacement de centaines de lignes reliées. Si ces commandes doivent être conservées pour des raisons comptables ou historiques, mieux vaut empêcher la suppression ou utiliser un mécanisme d’archivage.

Clé étrangère facultative ou obligatoire

Une clé étrangère peut être obligatoire ou facultative. Avec la contrainte NOT NULL, chaque ligne enfant doit référencer une ligne parent. Sans cette contrainte, la colonne peut contenir une valeur nulle, ce qui signifie qu’aucune ligne parent n’est associée.

Une valeur nulle n’est pas la même chose qu’un identifiant qui n’existe pas. Si client_id vaut NULL, aucune relation n’est indiquée. Si client_id vaut 42, la base doit vérifier que le client 42 existe.

Dans notre boutique, un brouillon de commande pourrait ne pas encore être associé à un client : une valeur nulle aurait alors un sens. Pour une commande déjà validée, le rattachement peut au contraire être obligatoire. Le choix dépend de la règle métier, pas seulement d’une préférence technique.

Relier plusieurs colonnes

Une clé étrangère peut aussi être composée de plusieurs colonnes. C’est utile lorsque l’identification d’une ligne repose elle-même sur plusieurs valeurs. Les colonnes de référence doivent être indiquées dans le même ordre que celles de la clé correspondante dans la table parent.

Par exemple, si une table inscriptions est identifiée par un couple formé de etudiant_id et de cours_id, une autre table peut faire référence à ce couple. Ce cas est plus avancé, mais le principe reste le même : la combinaison des valeurs doit correspondre à une ligne existante.

Erreurs fréquentes à éviter

• Des types incompatibles : vérifiez que la colonne enfant et la colonne référencée ont des types adaptés, notamment pour les nombres et les textes.

• Des lignes orphelines avant l’ajout : repérez les valeurs présentes dans la table enfant qui ne correspondent à aucune ligne parent.

• Une suppression en cascade mal évaluée : imaginez les conséquences sur toutes les tables liées avant d’activer cette option.

• Une colonne de référence non indexée : certains systèmes créent ou recommandent un index pour accélérer les recherches et les opérations liées. Vérifiez le comportement de votre système.

• Une contrainte absente de certaines tables : si plusieurs applications écrivent dans la même base, une vérification faite seulement dans le code peut être contournée par une autre application.

Vérifier et choisir la bonne règle

Avant de créer votre contrainte, posez-vous quelques questions simples : quelle colonne identifie la ligne parent ? Une relation est-elle obligatoire ? Que faut-il faire si la ligne parent est supprimée ? Existe-t-il déjà des données qui ne respectent pas la règle ?

Vous pouvez ensuite tester les cas importants dans un environnement de développement : ajouter une ligne valide, tenter une référence inexistante, supprimer une ligne parent utilisée et modifier une valeur référencée. Ces essais permettent de vérifier que le comportement correspond bien aux besoins de l’application.

Les détails de syntaxe et les comportements disponibles varient entre MySQL, PostgreSQL, SQL Server et d’autres systèmes. Consultez la documentation du système utilisé, en particulier avant de déployer une nouvelle contrainte sur une base en production.

Une clé étrangère est donc bien plus qu’un lien décoratif entre deux tables : c’est une règle appliquée par la base pour protéger la cohérence des données. Bien choisie, elle évite les références fantômes, rend la structure plus lisible et épargne quelques recherches pénibles dans des fichiers ou des rapports. Et comme souvent avec les outils de données, une règle claire au départ vaut mieux qu’une réparation compliquée plus tard.