Clé étrangère SQL : Veuillez vérifier vos relations de base de données

Clés étrangères composites et autoréférentielles

Une clé composite est constituée d'au moins deux colonnes qui identifient de manière unique un enregistrement et doivent être référencées ensemble. Ce modèle est utilisé lorsque la clé naturelle couvre plusieurs champs ou si vous souhaitez éviter les relations dupliquées. Par exemple, vous pouvez disposer d'une offre de cours identifiée par (course_id, semester) ou d'une table de jonction avec student_id et course_id comme clé primaire composite.

-- Defines courses table
CREATE TABLE course_offerings (
    course_id INT,
    semester VARCHAR(10),
    
    -- Composite primary key ensures a course can only be offered once per semester
    PRIMARY KEY (course_id, semester) 
);

-- Create enrollment table
CREATE TABLE enrollment (
    student_id INT,
    course_id INT,
    semester VARCHAR(10),

    -- This foreign key links enrollment records to a valid course offering
    FOREIGN KEY (course_id, semester)
        REFERENCES course_offerings(course_id, semester)
);

Une clé étrangère autoréférencée ou récursive est une clé étrangère qui fait référence à la clé primaire au sein de la même table. Ce modèle est courant dans les données hiérarchiques telles que Employé → Responsable. Cela permet de représenter des relations récursives sans créer de tableaux supplémentaires.

Dans la requête ci-dessous, l'manager_id e fait référence à employee_id dans le même tableau.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),

    -- manager_id references employee_id in the same table
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

Travailler avec des données et garantir leur intégrité

Nous comprenons désormais que les clés étrangères sont utilisées pour garantir l'intégrité des données dans les bases de données. Dans cette section, nous examinerons les considérations pratiques relatives à l'utilisation des clés étrangères pour les opérations SQL, les défis courants et la manière de gérer les contraintes.

Opérations INSERT, UPDATE et DELETE

Examinons comment ces opérations interagissent avec les clés étrangères.

Opérations d'insertion avec clés étrangères

Pour insérer un enregistrement enfant, la valeur parent référencée doit exister. Si ce n'est pas le cas, l'insertion échoue. Par exemple, la requête ci-dessous insérera l'enregistrement dans le tableau orders si le tableau users contient un enregistrement où user_id est 1.

-- users table contains user_id = 1
INSERT INTO orders (order_id, user_id, order_date)
VALUES (100, 1, '2024-02-01');

Si vous essayez d'insérer un enregistrement pour un utilisateur qui n'existe pas dans la table parent, vous obtiendrez une erreur indiquant une violation de la contrainte de clé étrangère.

-- user_id = 999 does NOT exist in users table
INSERT INTO orders (order_id, user_id, order_date)
VALUES (101, 999, '2024-02-01');

ERROR: insert or update on table "orders" violates foreign key constraint "fk_orders_users"

Mise à jour des opérations et comportement en cascade

Les mises à jour peuvent affecter soit la clé étrangère, soit la clé primaire. Par exemple, si vous mettez à jour la clé étrangère dans la table des commandes, cela ne réussira que si la clé primaire existe dans le tableau parente. Par exemple, la mise à jour ci-dessous ne réussira que si users.user_id = 5 exists.

-- updating foreign key value
UPDATE orders
SET user_id = 5
WHERE order_id = 100;

Dans l'exemple ci-dessus, il n'est pas possible de mettre à jour la clé primaire sans l'ON UPDATE CASCADE, car les lignes enfants font toujours référence à 1. Pour éviter cela, nous supprimons d'abord la contrainte de clé étrangère existante, puis la recréons avec l'option « ON UPDATE CASCADE » activée. Grâce à cette méthode, les valeurs d'orders.user_id s associées sont mises à jour automatiquement.

-- Drop existing foreign key constraint
ALTER TABLE orders
DROP CONSTRAINT fk_orders_users;

-- Recreate FK with ON UPDATE CASCADE enabled
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id)
    REFERENCES users(user_id)
    ON UPDATE CASCADE;   -- Automatically update child rows when parent key changes

UPDATE users
SET user_id = 500
WHERE user_id = 1;

Supprimer les opérations et les cascades

Si vous essayez de supprimer une ligne parent sans qu'aucune règle en cascade ne soit en place, la suppression échouera et vous obtiendrez une erreur. Lorsque vous ajoutez une clause « ON DELETE CASCADE », cela indique à la base de données de supprimer automatiquement toutes les lignes enfants associées lorsque l'enregistrement parent est supprimé, afin de garantir la cohérence de l'ensemble.

Dans l'exemple ci-dessous, nous créons une clé étrangère sur orders.user_id qui fait référence à users.user_id. Avec ON DELETE CASCADE, si un utilisateur est supprimé de la table des utilisateurs, toutes les commandes associées seront automatiquement supprimées.

-- Add a new foreign key constraint to the orders table
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id)
    REFERENCES users(user_id)
    ON DELETE CASCADE;             -- Delete orders automatically when user is deleted

Il peut arriver que vous souhaitiez que la base de données gère automatiquement les suppressions. Vous pouvez appliquer cette règle à l'aide de la clause ` ON DELETE SET NULL ` lors de la création du tableau. Cela permettra de définir les colonnes de clé étrangère dans le tableau enfant sur « NULL » si l'enregistrement parent est modifié ou supprimé.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,               -- FK referencing users.user_id
    order_date DATE,
    FOREIGN KEY (user_id)
        REFERENCES users(user_id)
        ON DELETE SET NULL     -- If a user is deleted, set user_id in orders to NULL
);

Gérer les défis courants

Lorsque vous concevez des bases de données complexes, vous êtes susceptible de rencontrer des difficultés avec les clés étrangères. Voici les problèmes courants que j'ai rencontrés avec les clés étrangères et les solutions pratiques qui s'y rapportent.

• Types de données incompatibles: Si votre clé étrangère et les colonnes de clé primaire référencées ont des types de données incompatibles, la contrainte ne peut pas être créée. Pour éviter cela, veuillez toujours normaliser les types dans les tableaux associés.

• Dépendances circulaires: Se produit lorsque deux tables se référencent mutuellement, ce qui peut entraîner des blocages d'insertion. Pour résoudre ce problème, il est recommandé de réévaluer la conception du schéma ou de créer d'abord le tableau sans clés étrangères, de charger les données de base, puis d'utiliser ALTER TABLE pour ajouter les contraintes.

• Clés étrangères flottantes: Ce problème survient lorsqu'une clé étrangère d'un enfant pointe vers une table parent inexistante. Pour résoudre ce problème, veuillez utiliser des actions en cascade afin de valider les données lors des opérations de suppression et de mise à jour.

Gestion des modifications et suppression des contraintes

Il peut arriver que vous souhaitiez modifier les contraintes de clé étrangère lorsque vous redéfinissez le schéma de votre base de données. Voici différentes méthodes pour supprimer la clé étrangère, avec de légères variations syntaxiques selon les différents dialectes SQL.

Dans les bases de données PostgreSQL, SQL Server et Oracle, nous utilisons l'instruction « DROP CONSTRAINT ».

-- Drop foreign key
ALTER TABLE orders
DROP CONSTRAINT fk_orders_users;

Cependant, dans MySQL, nous utilisons l'instruction ` DROP FOREIGN KEY ` pour supprimer la clé étrangère.

-- Drop foreign key
ALTER TABLE orders
DROP FOREIGN KEY fk_orders_users;

Veuillez noter que la suppression d'une contrainte de clé étrangère supprime l'application de l'intégrité référentielle par la base de données. Après la suppression, il est possible d'insérer ou de mettre à jour des données qui enfreignent la relation, ce qui peut entraîner des incohérences dans les données.

Performance et optimisation

Les exemples ci-dessus nous ont permis de constater comment les clés étrangères garantissent une conception efficace des données en préservant leur intégrité. Cependant, il est important de comprendre comment optimiser les clés étrangères afin d'améliorer les performances de vos bases de données. Voici quelques conseils que j'ai trouvés utiles lors de l'utilisation de clés étrangères :

Performances d'indexation et de requête

Lorsque vous indexez une clé étrangère, la base de données peut rapidement valider les modifications lors des opérations d'insertion, de mise à jour ou de suppression en localisant les clés correspondantes dans la table parente. De même, les index accélèrent les opérations de jointure qui relient les tableaux enfants et tableaux parents en permettant au moteur de base de données de faire correspondre les lignes en fonction des valeurs des clés étrangères.

En tant que bonne pratique, je recommande d'indexer explicitement les colonnes de clés étrangères si le SGBD ne le fait pas automatiquement.

Techniques d'optimisation des requêtes

Certains moteurs SQL utilisent l'existence de clés étrangères pour appliquer l'élimination des jointures, une technique qui identifie et supprime les jointures inutiles du plan d'exécution de la requête. Par exemple, si une requête n'accède qu'aux colonnes de la table enfant mais inclut une jointure sur une table parent pour le filtrage garanti par la contrainte de clé étrangère, l'opération de jointure peut être optimisée.

Vous pouvez toujours consulter les plans d'exécution pour observer comment l'optimiseur de requêtes gère les clés étrangères et les jointures. Ces optimisations et leur efficacité varient selon les moteurs SQL, tels que PostgreSQL, MySQL et SQL Server. Comprendre le comportement de l'optimiseur de votre base de données spécifique vous aidera à rédiger des requêtes performantes qui tirent pleinement parti des contraintes de clés étrangères.

Par exemple, vous pouvez utiliser la requête suivante pour afficher le plan d'exécution dans PostgreSQL :

-- View execution plan
EXPLAIN ANALYZE
SELECT o.order_id, u.email
FROM orders o
JOIN users u ON o.user_id = u.user_id;

Ce tableau résume comment afficher le plan d'exécution dans différentes bases de données :

Jevous recommande de suivre notre cours Joindre des données dans SQL pour découvrir les différents types de jointures en SQL et apprendre à travailler avec différentes tables liées dans la base de données.

Cas d'utilisation avancés et meilleures pratiques

Au-delà de la mise en œuvre de base, il est possible d'utiliser des clés étrangères pour créer une architecture de base de données robuste, évolutive et facile à maintenir. Dans cette section, je vais vous présenter les scénarios spécifiques et les principes de conception généraux à prendre en compte lors de l'utilisation de clés étrangères.

Relations entre bases de données ou entre schémas

Alors que les clés étrangères sont conçues pour garantir l'intégrité au sein d'une seule base de données ou d'un seul schéma, les environnements d'entreprise plus complexes nécessitent souvent des relations au-delà de ces limites. Le tableau ci-dessous résume la prise en charge inter-schémas et inter-bases de données pour différents dialectes SQL.

Maintenance et audit

Au fur et à mesure que vous créez des clés étrangères, vous remarquerez qu'elles changent à chaque fois que votre logique métier évolue. Veuillez envisager les méthodes suivantes pour auditer et maintenir les clés étrangères afin de garantir leur mise à jour :

• Examiner les contraintes : Vérifiez régulièrement les contraintes de clés étrangères afin de vous assurer qu'elles restent conformes à l'évolution des règles métier et aux modifications apportées au schéma.
• Audit régulier de l'index : Veuillez toujours vérifier que des index existent sur toutes les colonnes de clés étrangères afin de maintenir des performances optimales en matière de jointure et de validation, en particulier après des importations de données volumineuses ou des mises à niveau du système.
• Documentation et visualisation : Veuillez utiliser des diagrammes entité-relation (ER) ou des outils de visualisation de schémas pour documenter les relations. Ces supports visuels permettront à tous les développeurs et analystes de bien comprendre les relations et les règles d'intégrité appliquées par vos clés étrangères.

Meilleures pratiques pour une conception de base de données robuste

Afin de tirer le meilleur parti du fonctionnement des clés étrangères, veuillez respecter les directives suivantes :

• Alignez vos clés sur une logique métier réelle : Veuillez vous assurer que les clés primaires et étrangères représentent effectivement des entités réelles et les relations entre elles, plutôt que de s'appuyer sur des identifiants artificiels ou instables.

• Veuillez utiliser des noms clairs et cohérents : Veuillez choisir des noms de contraintes descriptifs, tels que « fk_orders_user_id », afin que votre schéma soit plus facile à maintenir, à déboguer et à utiliser avec d'autres personnes.

• Équilibrer rigueur et flexibilité : Les contraintes doivent garantir la qualité des données, mais votre conception doit néanmoins permettre de traiter les cas particuliers ou les validations dans la couche application.

• Évitez les relations circulaires : Structurez votre schéma de manière à ce que les dépendances des clés étrangères ne se référencent pas mutuellement, car cela rend les insertions, les mises à jour et les suppressions inutilement complexes.

• Optimiser les performances : Indexez correctement vos clés étrangères et appliquez les actions en cascade avec précaution afin de préserver l'intégrité sans ralentir votre système.

Conclusion

Comme pour toute compétence, la maîtrise des clés étrangères s'acquiert par la pratique. Je vous encourage à expérimenter la modélisation de schémas, à tester les actions en cascade et à comprendre comment les contraintes se comportent dans des scénarios réels. Au fil du temps, vous apprendrez à trouver le juste équilibre entre des règles strictes et la flexibilité requise par vos applications, en créant des bases de données à la fois résilientes et adaptables à mesure que les exigences évoluent.

Maintenant que vous comprenez comment utiliser les clés étrangères dans la conception de bases de données, je vous recommande d'essayer notre cursus professionnel d'ingénieur de données associé en SQL pour acquérir les bases de l'ingénierie des données et du stockage de données. Enfin, si vous souhaitez approfondir vos compétences en gestion de bases de données pour le big data, je vous recommande de suivre notre cours Introduction à la modélisation des données dans Snowflake afin d'en savoir plus sur la modélisation dimensionnelle.