Sql Mysql Foreign Keys

Résoudre le problème des Clés Étrangères Multiples dans MySQL : Un guide étape par étape

Lorsque vous travaillez avec des bases de données, en particulier dans MySQL, vous pourriez rencontrer des scénarios où vous devez mettre en œuvre plusieurs clés étrangères référant à la même table parent. Cela peut être particulièrement difficile, comme le montrent les problèmes rencontrés par de nombreux utilisateurs, tels que le fait d’essayer d’associer différents fournisseurs dans une table de détails d’expédition. Dans cet article, nous allons explorer une question courante concernant les clés étrangères multiples, fournir une solution structurée englobant les meilleures pratiques, et vous guider à travers le processus de dépannage.

Le Problème

Imaginez que vous avez une table SHIPPING_DETAILS qui est responsable du suivi à la fois des fournisseurs de transport (comme FedEx ou UPS) et des fournisseurs de manipulation de produit (pensez à Dunder Mifflin). Dans cette situation, plusieurs colonnes au sein des détails d’expédition doivent référencer la même table parent, VENDOR. Cependant, des problèmes peuvent survenir lorsque vous essayez de créer des contraintes de clé étrangère pour ces colonnes, comme l’incapacité à définir plusieurs clés étrangères.

Les utilisateurs rencontrent souvent des erreurs comme celle-ci :

Impossible de créer la table './<DB_NAME>/SHIPPING_GRID.frm' (errno: 150)

Cette erreur signale généralement un problème avec la définition de la clé étrangère ou les types de données impliqués.

Aperçu de la Solution

Pour résoudre le problème de définition de plusieurs clés étrangères dans MySQL, suivez ces étapes :

  1. Définir correctement la structure de la table : Assurez-vous que votre table VENDOR a une clé primaire unique compatible avec les colonnes de clé étrangère de votre table SHIPPING_DETAILS.

  2. Corriger le type de table : Assurez-vous que les deux tables (VENDOR et SHIPPING_GRID) sont de type InnoDB. Cela est crucial car les contraintes de clé étrangère ne sont pas prises en charge dans d’autres moteurs de stockage comme MyISAM.

  3. Assurer la compatibilité des types de données : Vérifiez que les types de données des clés étrangères correspondent au type de données de la clé primaire qu’elles référencent. Par exemple, si VENDOR.no est défini comme INT(6), toutes les références de clé étrangère doivent également être du même type.

  4. Éviter les définitions de clé primaire en double : Assurez-vous que votre instruction SQL n’inclut pas involontairement plusieurs définitions de la clé primaire. Cela peut entraîner des erreurs lors de la création de la table.

Exemple de Code

Voici un exemple corrigé de la manière dont votre table SHIPPING_GRID peut être définie correctement après avoir pris en compte les points ci-dessus :

CREATE TABLE SHIPPING_GRID(  
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'ID unique pour chaque ligne',
    shipping_vendor_no INT(6) NOT NULL COMMENT 'Clé étrangère vers VENDOR.no pour le fournisseur de transport (vendors_type doit être 3)',
    start_vendor_no INT(6) NOT NULL COMMENT 'Clé étrangère vers VENDOR.no pour le fournisseur d'où l'on expédie',
    end_vendor_no INT(6) NOT NULL COMMENT 'Clé étrangère vers VENDOR.no pour le fournisseur vers lequel on expédie',
    shipment_duration INT(1) DEFAULT 1 COMMENT 'Durée en jours entiers que l'expédition prendra',
    price FLOAT(5,5) NOT NULL COMMENT 'Prix en dollars américains par livre d'expédition (jusqu'à 5 décimales)',
    is_flat_rate TINYINT(1) DEFAULT 0 COMMENT '1 si c’est un tarif fixe quel que soit le poids, 0 si le prix est par livre',
    INDEX (shipping_vendor_no),
    INDEX (start_vendor_no),
    INDEX (end_vendor_no),
    FOREIGN KEY (shipping_vendor_no) REFERENCES VENDOR (no),
    FOREIGN KEY (start_vendor_no) REFERENCES VENDOR (no),
    FOREIGN KEY (end_vendor_no) REFERENCES VENDOR (no)  
) ENGINE=InnoDB;

Points Clés

  • Les Types de Données Comptent : Assurez-vous toujours que les colonnes de clé étrangère correspondent au type de données de la clé primaire référencée.
  • Utilisez InnoDB : Si vous mettez en place des clés étrangères entre les tables, l’utilisation du moteur InnoDB est essentielle.
  • Évitez les Doublons : Si vous constatez que votre SQL génère des erreurs, vérifiez les définitions en double ou les problèmes de syntaxe.

En suivant ces directives, vous pouvez créer efficacement votre table avec plusieurs clés étrangères et éviter les pièges courants associés aux contraintes de clé étrangère. Bonne chance avec vos projets MySQL et bonne programmation !