Gestion des contraintes de clé étrangère

Vidéo non disponible ! Réessayez plus tard
Cette vidéo fait partie de la formation
Apprendre MySQL 8.0 par la pratique
Revoir le teaser Je m'abonne
Transcription

Cette leçon fait partie de la formation
89,00€ Je commande

Formation incluse dans l'abonnement Elephorm Je m'abonne à Elephorm

DescriptionProgrammeAvis

Cette leçon fait partie de la formation
89,00€ Je commande

Formation incluse dans l'abonnement Elephorm Je m'abonne à Elephorm

Être capable de créer et exploiter une base de données MySQL au quotidien de manière autonome.

Cette formation a été conçue à destination d'un public sans connaissances préalables sur les bases de données, avec l'ambition de transmettre les notions fondamentales permettant d'entrer sereinement et efficacement dans l'univers des bases de données, et d'exploiter MySQL au quotidien de manière autonome.

Elle permet de tirer parti de logiciels de qualité conçus spécialement pour travailler avec les bases de données : MySQL Workbench et Datagrip.

Elle met l'accent sur la pratique, et comprend de nombreuses démonstrations, mises en situations, exercices corrigés et commentés.

Elle met à disposition en ressources téléchargeables 3 bases de données portant sur des thèmes variés (médicaments vétérinaires, sécurité routière, prénoms), les scripts SQL des démonstrations les plus longues, et intègre un code promotionnel vous permettant de bénéficier d'une prolongation à 4 mois de la période d'essai gratuite de Datagrip.

Voir plus
Dans cette vidéo, on va s'intéresser aux clés étrangères et à l'intégrité référentielle. Et donc je prends un exemple pour montrer comment est-ce qu'on met ça en oeuvre. On peut supposer qu'un médicament puisse exister en différents lots successifs. Donc on va créer une table MEDOC2 qui va contenir des informations de médicaments et une table LOT qui va contenir les informations relatives à un lot pour un médicament donné. Donc un médicament peut exister dans plusieurs lots mais un lot ne correspond qu'à un seul médicament. Créons donc la table MEDOC2 avec une clé primaire qui pour le coup n'est pas auto-incrémentée ici. Je vais avoir besoin de la modifier ensuite donc je choisis de faire en sorte qu'elle ne soit pas auto-incrémentée. Puis j'insère dans cette table 20 enregistrements issus de la table MEDOC. Je prends l'ID et le nom de ces médocs et j'insère donc ces enregistrements dans la table MEDOC2. Je peux m'assurer que j'ai bien effectivement mes 20 médicaments dans la table MEDOC. Maintenant on va s'intéresser à la table LOT. Dans cette table il y a donc trois colonnes, un ID, un MEDOCID et un nom. L'ID est la clé primaire donc un int not null auto-increment primary key. Et j'ai ensuite un MEDOCID qui va me servir de clé étrangère. A ce titre elle va donc stocker des informations en relation avec la colonne ID de la table MEDOC2. Donc il faut qu'elle ait impérativement le même type. Ici c'est un int, ici c'est un int également. Si j'avais mis un tiny int ça n'aurait pas pu correspondre puisque la clé étrangère n'aurait pas pu stocker toutes les valeurs de l'ID. Donc il faut faire attention à ce que ce soit bien le même type et même le même signe. C'est-à-dire que si la clé primaire est unsigned alors il faut que la clé étrangère soit unsigned également. Je choisis de rendre cette clé étrangère non nullable pour la bonne et simple raison qu'on peut penser qu'un lot ne peut pas exister sans être associé à un médicament. Tout dépend du contexte. Ici c'est pertinent de faire cette clé étrangère non nulle mais dans d'autres contextes on peut accepter d'avoir une clé étrangère nulle. Puis je stocke un nom de lot sous forme d'un VARCHARD 50 NOTE NULL. Jusqu'ici j'ai uniquement déclaré des colonnes mais je n'ai pas fourni d'informations sur réellement une contrainte de clé étrangère. Alors ça peut se faire de deux manières. Soit en utilisant cette ligne là qui va déclarer une contrainte de type clé étrangère sur ma colonne MEDOCAIDI soit en le faisant a posteriori après la création de la table. Alors l'instruction est globalement assez proche dans les deux cas. Donc je vais d'abord créer ma table. Constatez que la table a bien été créée et regardez un petit peu sa structure. Le script qui a été exécuté par ma SQL est le suivant. J'ai bien ma clé primaire par contre je n'ai aucune information qui concerne une clé étrangère et c'est normal. Je vais la créer ici avec ce script. ALTER TABLE, le nom de la table. Donc ici LAW, ADD CONSTRAINT, le nom de la contrainte. Donc ici FK pour FOREIGN KEY, clé étrangère en anglais. Le nom de la table suivi le nom de la colonne qui est concernée par cette clé étrangère. Le type de contrainte, ici c'est une contrainte de type clé étrangère FOREIGN KEY. Entre parenthèses, là où les colonnes concernées par cette clé étrangère dans la table LAW. Ici j'ai qu'une colonne qui est concernée par la clé étrangère, c'est la colonne MEDOCAIDI. REFERENCES suivi du nom de la table qui contient la clé primaire, donc MEDOC2. Et entre parenthèses, la colonne qui fait office de clé primaire. J'exécute cette instruction et je peux rafraîchir la requête que j'avais exécutée avant pour voir que désormais j'ai effectivement une contrainte de type clé étrangère qui a été prise en compte. J'ai aussi automatiquement un index qui a été créé par MySQL et qui porte le même nom que ma contrainte et qui concerne la colonne MEDOCAIDI. Les clés étrangères doivent être indexées. Donc si on ne le fait pas explicitement, MySQL se charge de le faire lui-même. Et d'ailleurs si on inspecte l'arborescence au niveau de la table LAW, je vois que j'ai effectivement ma clé étrangère, mais je vois que j'ai deux index. L'index associé à la clé primaire qui s'appelle PRIMARY et l'index associé à la clé étrangère. Cette table est créée, donc on va pouvoir maintenant insérer un certain nombre d'enregistrements dedans. Je choisis d'aller piocher dans la table MEDOC des ID de médicaments pour créer deux enregistrements pour lesquels le numéro de LAW est généré aléatoirement. Donc j'exécute ces trois instructions et j'ai ici, après insertion de ces enregistrements, le contenu de ma table LAW. Je vois que j'ai donc deux LAW qui ont été créées. Un associé aux médicaments d'ID 5100, un associé aux médicaments d'ID 5270. Si j'avais voulu insérer dans la table LAW un LAW associé à un médicament dont l'ID est inconnu dans la table MEDOC2, j'aurais eu une erreur. C'est ce que je fais ici, en tout cas c'est ce que je tente de faire. Je récupère l'ID maximal de MEDOC dans la table MEDOC2. Donc c'est 61909 et j'essaye d'insérer dans ma table LAW un LAW pour lequel la valeur de MEDOCID c'est cette valeur plus 1. C'est-à-dire qu'elle ne correspond à aucun médicament dans ma table MEDOC2. Si j'essaye d'insérer cet enregistrement, c'est refusé. C'est une des manifestations de l'intégrité référentielle. Il y en a d'autres. Je ne peux pas créer non plus un enregistrement orphelin dans ma table LAW. Ça c'est la conséquence de la contrainte note nulle que j'ai appliquée sur ma clé étrangère. Un enregistrement orphelin, ce serait un enregistrement, un LAW pour lequel on ne connaîtrait pas le médicament associé. Donc la valeur de la colonne MEDOCID dans ce cas serait à nulle. C'est ce qu'on essaye de faire ici. Je ne peux pas. Si j'essaye de le faire, ça va me dire que la colonne MEDOCID ne peut pas être nulle. L'intégrité référentielle impose d'autres contraintes notamment sur la suppression et la mise à jour des médicaments. Je ne peux pas supprimer ni modifier un enregistrement, notamment sa clé primaire, s'il est associé à un LAW dans la table LAW. Ici, par exemple, j'essaye de supprimer de ma table MEDOC2 un médicament qui est associé à un LAW. Par exemple, je vais essayer de supprimer dans la table MEDOC le médicament d'ID 9270. Et ça me dit que c'est refusé pour des raisons de liaison clé primaire-clé étrangère. C'est l'intégrité référentielle aussi qui est à l'œuvre. À ce stade, dans ma table MEDOC, j'ai bien toujours 20 médicaments. Rien ne m'empêche néanmoins d'en supprimer un si ce médicament n'est pas associé à un LAW. C'est ce que je fais ici. Une fois la suppression faite, je peux calculer le nombre de médicaments et je vois cette fois-ci qu'il y en a bien un qui a été supprimé. Si j'essaye de mettre à jour un ID de médicament dans la table MEDOC et que ce médicament est associé à un LAW, ça va être interdit par l'intégrité référentielle. Ici par exemple, je veux mettre à jour la valeur de l'ID du médicament d'ID 9270 pour que ça passe à 9271. Si j'essaye de le faire, MySQL va s'y opposer parce que j'ai une intégrité référentielle à l'œuvre. Ça ne m'empêche pas cependant de mettre à jour l'ID d'autres médicaments dans la table MEDOC, notamment ceux qui ne sont pas associés à un LAW. Par exemple, ici j'ai mis à jour le 1,1 médicament non associé à un LAW. Je peux par contre supprimer un certain nombre de LAW. Je peux même vider complètement ma table comme je le ferais si j'exécutais cette instruction ici. Par exemple, supprimons tous les enregistrements de la table. Je vois que ça fonctionne et je peux m'en assurer en consultant le contenu de cette table. Je vois que je n'ai plus aucun LAW. L'intégrité référentielle a aussi des conséquences sur les suppressions de tables. Par exemple, je ne peux pas supprimer la table MEDOC2 tant qu'elle est liée à la table LAW. Il faut soit d'abord supprimer la table LAW, soit d'abord supprimer la contrainte d'intégrité référentielle entre les deux, la contrainte de clé étrangère. Par exemple, pour supprimer cette contrainte, il faudrait que j'exécute l'instruction ALTER TABLE LAW DROP FOREIGN KEY avec le nom de la clé étrangère. Si on ne se souvient pas du nom de cette contrainte, on peut exécuter l'instruction SHOW CREATE TABLE et regarder ici quel est le nom qui a été affecté à la contrainte. À ce moment-là, on le recopie ici. Quand on exécute cette instruction, on supprime la contrainte de clé étrangère. Par exemple, si je rafraîchis cette instruction, je vois que désormais le script de création de la table LAW ne mentionne pas la présence d'une clé étrangère. Et si je regarde ici au niveau de l'arborescence, je vois que je n'ai plus d'informations concernant la clé étrangère. En revanche, je vois que j'ai toujours l'index associé à la clé étrangère. Donc, j'ai supprimé la clé étrangère, mais je n'ai pas supprimé l'index que MySQL avait créé tout seul. Cependant, je peux, maintenant que la contrainte n'est plus présente, supprimer la table MEDOC. Je peux aussi supprimer la table LAW. Alors, revenons au départ, parce qu'il y a un certain nombre de commentaires que j'aimerais faire. On va recréer la table MEDOC2, et insérer à nouveau les enregistrements dedans. Je peux vérifier que ça s'est passé comme je l'attendais. Et je vais recréer ma table LAW, cette fois-ci, en acceptant des valeurs nulles pour la colonne MEDOCID. Je vais, par la même occasion, modifier le script que j'ai exécuté pour créer la contrainte de clé étrangère. En fait, implicitement, lorsqu'on exécute le script, Implicitement, lorsqu'on exécute le script ALTER TABLE ADD CONSTRAINT FOR INKY REFERENCES, implicitement, on a cette deuxième partie qui se rajoute. J'explique à quoi elle sert. ON DELETE définit le comportement de MySQL lorsque l'on va supprimer un médicament. ON UPDATE, lorsqu'on va mettre à jour la clé primaire d'un enregistrement d'un médicament en particulier. Ici, il est précisé NO ACTION pour les deux. Ça veut dire qu'en fait, MySQL va interdire la suppression d'un médicament ou la mise à jour de sa clé primaire tant que ce médicament est associé à un lot. C'est le comportement par défaut. NO ACTION, c'est l'équivalent de RESTRICT pour MySQL. Il existe d'autres possibilités. En particulier, SET NULL. On va utiliser cette option-là et voir quelles conséquences ça peut avoir en termes d'intégrité référentielle. Je peux m'assurer que cette modification a bien été prise en compte. Ici, j'ai bien un ON DELETE SET NULL, ON UPDATE SET NULL. Et je vais insérer à nouveau des enregistrements dans ma table lot. J'ai exactement le même jeu d'enregistrement que dans la situation précédente. Je ne peux toujours pas créer des lots qui soient associés à un médicament d'ID inconnu. Par contre, je peux, cette fois-ci, créer un lot orphelin, c'est-à-dire un lot associé à aucun médicament. C'est possible parce que ma clé étrangère MEDOCID est maintenant nullable. Ça n'a rien à voir avec l'option ON DELETE SET NULL et ON UPDATE SET NULL. Là, c'est vraiment lié au fait que ma clé étrangère MEDOCID est nullable. Si j'essaie de le faire, je vois que désormais, j'ai un lot qui est associé à aucun médicament. C'est ce qu'on appelle un orphelin. Maintenant, que se passe-t-il si j'essaie de supprimer un médicament associé à un lot ? Par exemple, essayons de supprimer celui-ci. L'option ON DELETE SET NULL va entrer en action et va, dans la table lot, passer à nulle la valeur de MEDOCID pour ce lot. Voyons voir. Je supprime donc mon médicament dans la table MEDOC2 ayant l'ID 9270. Si je rafraîchis ensuite, je vois que désormais, j'ai créé un deuxième orphelin. Je peux toujours supprimer un médicament non associé à un lot. Maintenant, voyons voir ce qui se passe si je mets à jour l'ID du médicament d'ID 5100 pour le passer à 5101. Encore une fois, on va avoir le contre-coup du passage de NO ACTION à ON UPDATE SET NULL. Ici, au lieu d'avoir 5100, après la mise à jour, j'aurai nulle, donc je vais avoir créé un troisième orphelin. On peut s'en assurer. C'est effectivement le cas. Je peux bien sûr toujours modifier un enregistrement dans la table MEDOC si ce MEDOC n'est associé à aucun lot. Cela n'aura pas de répercussion sur la table lot dans ce cas. Je vais supprimer la table lot puis la table MEDOC pour les recréer avec la dernière option que j'aimerais vous montrer. C'est l'option ON DELETE CASCADE et ON UPDATE CASCADE. On va recréer notre table MEDOC2 avec son contenu. On voit que cela a bien fonctionné. On va remettre notre colonne MEDOCID à notre nulle. Je recrée cette table lot. Au lieu d'avoir un 7 0, je vais mettre un cascade et ON UPDATE CASCADE. C'est quelque chose d'intéressant Qu'est-ce que cela va avoir comme conséquence ? Quand je vais supprimer un médicament dans la table MEDOC2 et que ce médicament est associé à un lot dans la table lot, le médicament va être supprimé mais le lot qui est associé à ce médicament va être supprimé. S'il y a plusieurs lots, ils seront supprimés également. C'est important d'avoir en tête les conséquences que cela peut avoir. On peut avoir des enregistrements qui vont être supprimés de manière massive. Il faut faire attention avec cette option. Cela peut être pratique mais on peut être amené à détruire beaucoup de données. MySQL ne proposera pas de mise en garde. Le ON UPDATE CASCADE signifie que quand on met à jour la clé primaire d'un médicament, la valeur correspondante dans la clé étrangère sera mise à jour de la même manière. On a créé notre contrainte de type clé étrangère. On peut s'assurer qu'elle a été prise en compte comme on l'espérait. On a effectivement un ON DELETE CASCADE et un ON UPDATE CASCADE. On va bien sûr rajouter de nouveau des enregistrements dans notre table lot. On voit qu'on a le même jeu que la dernière fois. Les remarques concernant la création d'un lot associé à un médicament inconnu sont toujours valables. On ne peut toujours pas créer un orphelin puisque ma clé étrangère est non nullable. Que se passe-t-il si je supprime un médicament associé à un lot ? Je vais supprimer dans la table MEDOC le MEDOC d'ID 9270. Je m'attends à ce que le lot associé à ce médicament soit supprimé. Voyons voir. Je supprime donc mon médicament dans la table MEDOC2. Si je rafraîchis ensuite cette requête, je vois que le lot a été supprimé comme je l'attendais. Je peux bien sûr toujours supprimer un médicament qui n'est associé à aucun lot. Ça n'aura pas de répercussion sur la table lot. Et si je mets à jour l'ID, la clé primaire d'un médicament et que ce médicament est associé à un lot, la modification sera propagée dans la table MEDOC. Je peux faire l'exemple ici en essayant de mettre à jour l'ID du médicament 5100 pour le passer à 5101. Si je rafraîchis cette requête, je vois que j'ai bien eu la mise à jour qui a été propagée à la table lot. Je peux toujours modifier un médicament non associé à un lot. Modifier sa clé primaire, ça n'aura pas de répercussion sur la table lot. Les remarques concernant la suppression de lot et l'ordre dans lequel on doit supprimer les tables si on veut le faire sont toujours valables et ne sont pas affectées par le choix de l'option ON DELETE NO ACTION, ON DELETE SET DEFAULT ou ON DELETE CASCADE. On a une dernière option qui est ON DELETE SET DEFAULT qui n'est pas encore implémentée par MySQL et qui prévoit que lorsque l'on supprime un médicament, la valeur du MedocID passe à la valeur attribuée par défaut à cette colonne. Encore faut-il qu'elle ait une valeur par défaut. Actuellement, ce n'est pas encore implémenté par MySQL.

Programme détaillé

Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Unions 06:39
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Je commande Je m'abonne
Nos dernières formations Code & Data
  • Nouveau
    Webflow - Créer la landing Page d'OpenAI
    Découvrir
    À l'issue de cette formation, les participants seront capables de créer de manière autonome une landing page sophistiquée, similaire à celle d'OpenAI, en utilisant les fonctionnalités avancées de Webflow pour le design responsive et les interactions.
    3h48 19 leçons
  • Apprendre Svelte 3 et SvelteKit 1.0
    Découvrir
    Créez des applications web modernes et ultra-rapides avec notre formation "Apprendre Svelte 3 et SvelteKit 1.0". Rejoignez-nous et devenez un expert du développement JavaScript ! 🚀 #Svelte #SvelteKit #DéveloppementWeb
    7h42 77 leçons
  • Apprendre Vue JS 3
    Découvrir
    Apprenez à créer des applications web modernes avec notre formation "Apprendre Vue JS 3". Rejoignez-nous et maîtrisez ce puissant framework JavaScript ! 🚀 #VueJS #FormationWeb #Développement
    11h47 91 leçons
  • Gratuit
    Apprendre ChatGPT
    Découvrir
    Ce cours vise à fournir une compréhension approfondie de l'intelligence artificielle, en se concentrant sur l'utilisation pratique de ChatGPT et DALL-E. Les participants découvriront comment l'IA peut transformer des domaines variés tels que le marketing, le développement web, et le design graphique. Le contenu couvre les bases de l'IA, l'art de créer des prompts efficaces, l'utilisation de plugins avancés, et le codage assisté par IA. Des projets pratiques permettront d'appliquer ces connaissances dans des scénarios réels. Destiné aux professionnels de la technologie, aux marketeurs, aux développeurs, et à toute personne intéressée par l'IA, ce cours est une opportunité d'acquérir des compétences innovantes et applicables dans divers contextes professionnels et créatifs. Les prérequis incluent une compréhension basique de l'informatique et un intérêt pour l'IA. Le cours promet une aventure d'apprentissage enrichissante, ouvrant de nouvelles perspectives dans l'utilisation de l'intelligence artificielle.
    3h12 30 leçons
  • Créer sa boutique de e-commerce avec Shopify
    Découvrir
    À la fin de cette formation, vous aurez les compétences nécessaires pour créer et gérer votre propre boutique de e-commerce prospère avec Shopify. Transformez vos idées en succès commercial en ligne ! 
    4h10 39 leçons

éditeur de vidéos pédagogiques

Des supports pédagogiques en vidéo, produits avec les meilleurs experts. Dans nos studios à Paris, Lyon ou Montpellier. Vous souhaitez travailler avec nous ?
image-micro