Il est de bon ton de se souhaiter la bonne année. Mais vous, et vous seul, pourrez faire en sorte que cette année soit bonne, meilleure que celle qui vient de s'écouler. Apprenez à ne compter que sur vous, car personne n'est plus qualifié que vous-même pour bâtir, réparer ou améliorer votre propre vie. Personne ne fera les choses à votre place. D'ailleurs, tout ce que les autres peuvent faire, c'est souhaiter que vous le fassiez. Et ne croyez pas que tout ceux qui vous entourent vous apporteront des solutions : certains font juste partie de vos problèmes. Transformez vos résolutions en actes, et dans douze mois, retournez-vous et souriez-vous fièrement : C'était long. C'était difficile. Mais ça y est : 2017 était une bonne année, merci Moi.

Leçon 16
 
Relations

Autant vous prévenir, cette leçon est assez longue et ardue, mais je vais essayer de vous la rendre la plus digeste possible !

Access est un Système de Gestion de Base de données Relationnelles. Nous allons donc étudier ces fameuses relations qui n'ont aucun équivalent dans Excel.

Nous allons relier les tables entre elles de manière à ce que les données contenues dans une table soient cohérentes, correspondent avec les données d'une autre table.

Nous avons vu les listes déroulantes, qui permettent de sélectionner des données dans une autre table : nous allons "officialiser" cette "union".

Nous verrons aussi comment on peut modifier les données d'une table pour que la modification se reporte sur la table liée, et même comment effacer un enregistrement dans une table tout en effaçant les enregistrements correspondants dans une autre table.

Cette notion de relation est très importante : c'est un peu la police de votre base de données... Imaginez une ville sans police... Elle va fonctionner, mais il y aura très rapidement des abus et des malversations, de plus en plus jusqu'à la ruine de la ville... Une base de données sans relations serait un peu dans le même cas.

 
Sommaire

Regardons attentivement la table T_Client et T_Pays. J'aimerais les voir l'une à côté de l'autre, mais le mieux que je puisse faire est de les ouvrir conjointement dans deux onglets côte à côte :

Affichage de plusieurs objets simultanément

Nous avons la possibilité d'afficher réellement les deux tables côte à côte :

  1. Allez dans le menu Fichier/Options
  2. Dans Base de données active, cliquez sur Fenêtres superposées, et OK :
  3. Il vous dit de ferner et de rouvrir la base de données :
  4. Faites-le : -
  5. Dans le menu Accueil, vous pouvez afficher automatiquement tous les objets ouverts (qu'il s'agisse de tables, de requêtes, de relations, etc.) les uns à côté des autres, comme ceci :

  6. Rouvrez T_Client et T_Pays. Maintenant, vous pouvez redimensionner, déplacer les tables comme des fenêtres Windows normales... je suppose que vous savez faire :

Dans T_Client, en Mode création, déplacez PaysOrigine à droite de Prenom, et ensuite mettez les deux tables côte à côte pour que nous puissions les analyser :

On constate qu'il n'y a pas grand chose en commun dans ces deux listes ! En fait, seule France subsiste dans les deux tables !

Qu'il existe des pays dans la table T_Pays qui ne soit attribué à aucun client (Espagne, Italie, U.S.A), passe encore, mais que des clients soient originaires de pays non-présents dans T_Pays (Switzerland, Suisse, Belgique), c'est vraiment gênant ! ...  En plus, Switzerland et Suisse, c'est le même pays, avec deux orthographes différentes...

Nous allons "faire la police": c'est à dire que nous allons faire en sorte qu'il ne soit pas possible qu'un client provienne d'un pays qui n'existe pas dans T_Pays.

Fermez les deux tables.

Allez dans les relations (Outils de base de données / Relations) :

Tables système

Il est possible que vous voyez quelques tables qui ne vous rappellent rien : ce sont des tables nécessaire au bon fonctionnement interne d'Access, mais elles n'ont aucun intérêt à être sous vos yeux ! Elles ne sont même pas visibles dans la liste des tables à gauche !

Vous n'allez pas les supprimer, mais simplement les masquer de la fenêtre de relations. Pour les masquer, cliquez sur chacune d'entre elles et appuyez sur la touche (Si, si !). (Bouton droit de la souris, et Masquer la table, ça marche aussi).

Si vous désirez (re)voir toutes les tables système, allez dans le menu Fichier / Options :

Voici alors ces fameuses tables :

 

 

Mais je vous déconseille fortement de les afficher ! Elles ne vous serviront à rien.

 

 

Affichage des relations

Cliquez sur Afficher toutes les relations.

Si vous avez bien laissé les tables masquées, comme je viens de vous le conseiller, il ne se passe rien, la fenêtre des relations reste désespérément vide.

Mais maintenant, nous allons faire une petite expérience : Fermez les relations.

Mise en forme esthétique des relations

Access vous demande d'enregistrer la mise en forme des relations :

Ce ne sont pas véritablement les relations, mais la disposition esthétique des relations sur l'écran.. En l'occurrence, il nous demande d'enregistrer le fait que nous avons masqué les tables.

Allez dans T_Client en mode Création, allez dans le champ PaysOrigine, et refaites complètement la liste déroulante : c'est à dire que vous rechoisissez comme type de données : Assistant liste de choix, et re-créez la liste déroulante sur la table T_Pays, exactement comme nous n'avons fait ici, dans la leçon 14.

Mais, au dernier moment, cette fois-ci, à la question

Répondez Oui.

Lancez la table en mode saisie de données, et vérifiez le bon fonctionnement de votre liste : .

Ok, elle fonctionne donc tout aussi bien. Fermez la table.

Inutilité des relations sans intégrité

Allez dans les relations. Cliquez sur Afficher toutes les relations.

Cette fois, vous voyez la relation qui vient d'être créée :

C'est une relation qui n'a aucune utilité particulière. Elle sert juste à préciser que le champ Pays et PaysOrigine ont un vague rapport entre eux, c'est tout ! D'ailleurs, que la relation existe ou pas, la liste déroulante fonctionne pareillement.

Nous allons d'ailleurs tester. Avant de tester, fermez les relations : vous avez donc bien le même message que tout à l'heure : Répondez oui, et rouvrez immédiatement les relations.

Suppression de relations

Maintenant, vous allez effacer la relation qui existe entre T_Pays et T_Client. Pour ce faire, vous allez cliquer une fois sur la ligne qui représente la relation. Ca a pour effet d'épaissir un peu cette ligne. Vos appuyez alors sur , et vous répondez Oui au message qui apparaît, comme ceci :

Différence entre masquage et suppression

Donc, quand on clique sur une table, et qu'on appuie sur , ça la masque, mais quand on fait la même chose sur une relation, ça l'efface vraiment.

Fermez les relations, et constatez que vous n'avez plus le message

 

 

C'est bien la preuve que la mise en forme esthétique des tables (Déplacement, redimensionnement, ou masquage) est traité de manière différente que les relations en elles-mêmes

Revenez dans les relations.

Création manuelle d'une relation

Pour créer une relation, il suffit de tirer n'importe quel champ de n'importe quelle table jusque n'importe quel champ de n'importe quelle autre table.

Par exemple, tirez Remarque de T_Client vers Pays de T_Pays (Cliquez sur Remarque, laissez le doigt sur la souris, et glissez ainsi la souris sur Pays, puis lâchez le bouton gauche) :

Cette boîte de dialogue apparaît : . Cliquez sur Créer.

Dans les relations, vous pouvez cliquer avec le bouton droit de la souris sur une des tables, et choisir Mode création pour aller directement dedans. On regrettera qu'il n'y ait pas la possibilité de Bouton droit/Mode saisie de données

Vous venez de créer une relation entre Remarque et pays.

C'est une relation qui n'a aucune utilité, aucun sens, et aucune influence sur le bon fonctionnement de vos tables.

 

Vous pouvez vérifier : aucune liste déroulante n'a été créée sur Remarque, la liste déroulante de choix de pays fonctionne aussi bien qu'avant. On se demande bien à quoi ça sert, finalement, ces relations!

Erreurs courantes

Nous allons la voir maintenant, l'utilité !

Allez d'abord dans la table T_Pays, et retirez la clé primaire (Vous vous rappelez ?).

Une fois cette clé retirée, faites exprès de créer un doublon dans Pays :

Voilà ! Comme ça, c'est la totale : il y a des doublons dans la table T_Pays, et les PaysOrigine des clients sont presque tous différents des pays présents dans T_Pays !

Nous allons remettre de l'ordre dans tout ça avec les relations. Ah elles ne servent à rien ? C'est ce qu'on va voir !

Laissez la table T_Pays Ouverte, et allez dans les relations.

Effacez la relation entre Pays de T_Pays et Remarque de T_Client. :

Appliquer l'intégrité référentielle

Créez une relation entre Pays et PaysOrigine (Peu importe que vous glissiez la souris de T_Pays à T_Client ou l'inverse)

Une fois dans cette boîte de dialogue, tout l'intérêt réside dans la coche de la case Appliquer l'intégrité référentielle

Intégrité Référentielle ! En voilà un mot barbare ! C'est un peu comme le mariage, ou chacun des époux se jure loyauté et fidélité : Ils doivent être intègres. Eh bien, ici, c'est chacune des deux tables qui se jure loyauté et fidélité. En bref, T_Client jure à T_Pays qu'elle ne contiendra jamais de PaysOrigine absent de T_Pays. Par exemple, si Argentine n'existe pas dans T_Pays, T_Client s'engage à ne jamais avoir de client dont PaysOrigine est Argentine.

Evidemment, dans le cas d'un vrai mariage, ça ferait mauvais genre de se jurer de s'appliquer l'intégrité référentielle jusqu'à ce que la mort nous sépare .

Inutile de dire qu'on n'est pas sorti de l'auberge avec nos pays qui ne correspondent pas entre les deux tables (à part France)

Faites-le, et cliquez sur Créer. Vous êtes gratifié d'un message d'erreur aussi abscons que d'habitude !

Ca veut simplement dire que la relation ne peut pas être créée si votre table est ouverte. Cliquez sur OK, puis sur Annuler :

Fermez T_Pays, et réessayez de créer la même relation de PaysOrigine à Pays. re-cochez Appliquer l'intégrité référentielle, et cliquez à nouveau sur OK :

Clé primaire obligatoire pour l'intégrité référentielle

Ah ! Voici un autre message d'erreur :

Il nous informe qu'il n'est pas possible de créer l'intégrité référentielle à cause d'un "index unique introuvable". En fait, c'est à cause de la clé primaire que vous avez enlevée dans Pays de T_Pays. Pour appliquer l'intégrité référentielle, cette clé primaire est indispensable, et en fait c'est logique !

Vous vous rappelez qu'une clé primaire empêche les doublons : du coup, si nous avons un client dont nous avons choisi France comme PaysOrigine, comment Access pourrait-il deviner si vous parlez du premier France, ou du 2ème France que vous avez entré tout à l'heure ? Il doit savoir !

Du coup, vous devez remettre la clé primaire sur Pays de T_Pays, mais vous ne pourrez pas le faire tant que vous n'aurez pas supprimé le 2ème France ! Voyez comme l'intégrité référentielle vous "oblige" à faire le ménage ? Faites-le : supprimez le 2ème France.

Une fois que vous avez mis la clé primaire sur Pays de T_Pays et que vous avez refermé T_Pays, revenez dans les relations. Constatez qu'une petite clé est apparue à côté du champ :  , et, encore une fois (oui, c'est lassant, mais je veux vous montrer toutes les erreurs possibles), vous créez la relation entre Pays de T_Pays et PaysOrigine de T_Client, avec l'intégrité référentielle.

Erreur de non correspondance

 

Et voici la dernière erreur, qui a sans doute la palme du message d'erreur le plus long ! N'essayez pas de le comprendre, ou alors, prenez une aspirine avant :

Même si nous avions un million de clients, dont 999'999 avaient un PaysOrigine correspondant dans T_Pays, sauf un seul client qui aurait une toute petite faute d'orthographe dans son PaysOrigine, le message d'erreur ci-dessus serait exactement le même.

En fait, c'est tout bête : ça veut dire que vous avez au moins un client dont PaysOrigine n'a pas de correspondance parfaite dans T_Pays. Bon, dans notre cas, ce n'est pas un, c'est presque tous (on ne compte pas les PaysOrigine vides - ceux-là ne sont pas gênants).

Pas le choix, donc, il faut à nouveau annuler la relation.

Pour faire le ménage de manière efficace, je vous propose de fermer les relations, et de mettre la table T_Pays et  T_Client côte à côte, comme au début de la leçon.

Voici les choses à faire pour que tout correspondent

  1. Steven Spielberg : Choisir Suisse à la place de Switzerland. Mais pour ce faire, il faut d'abord ajouter Suisse à T_Pays. Et quand vous aurez ajouté Suisse à T_Pays, vous ne pourrez pas encore le choisir dans T_Client, parce que la table ne s'est pas rafraîchie (Vous vous rappelez  dans la leçon 14 ?). Je vous laisse vous débrouiller pour arriver à vos fins .
  2. Ajouter Belgique à la table T_Pays (C'est plus facile, il n'y a rien d'autre à faire !)

Le fait qu'il y ait plein de clients qui n'aient pas de PaysOrigine ne gêne pas (heureusement), et le fait que certains pays (Espagne, Italie, U.S.A) ne soient liés à aucun client ne gêne pas non plus (heureusement aussi).

Deux tables correctement liées

Voici à quoi devraient ressembler vos 2 tables après nettoyage.

Vous constatez donc bien que, à part les vides, tous les PaysOrigine des clients possèdent leur correspondance dans T_Pays, mais tous les Pays de T_Pays n'ont pas forcément de correspondance vers T_Client.

Les conditions pour que l'intégrité référentielle soit possible sont remplies !.

Fermez les tables, retournez dans les relations, recréez encore la liaison entre Pays de T_Pays et PaysOrigine de T_Client, cochez encore la case Appliquer l'intégrité référentielle et cliquez sur Créer.

O joie ! O bonheur ! Il accepte ! Il n'y a plus de message d'erreur ! Champagne !

Regardez attentivement la différence entre une relation sans intégrité référentielle et avec :

Il y a à un 1 du côté de la table avec la clé primaire, et un 8 couché (Qui représente l'infini en mathématique) du côté de l'autre table.

Ca veut dire que 1 pays dans T_Pays peut être représenté autant de fois qu'on veut dans T_Client (Actuellement, on a deux clients Suisse, mais on pourrait en avoir zéro, 10 ou trois millions).

Limiter à liste VS intégrité référentielle

Maintenant, fini de rire ! Plus question d'écrire n'importe quoi n'importe comment !

Allez dans la table T_Client, et assurez-vous que dans le champ PaysOrigine, la propriété Limiter à liste soit bien définie sur Non. Vous vous rappelez ? C'est pour qu'on puisse écrire autre chose que ce qui est proposé dans la liste.

Comme vous allez constater que nous n'allons justement pas pouvoir entrer autre chose que ce qui est proposé dans la liste (à cause de l'intégrité rérérentielle), je ne voulais pas que vous imaginiez que c'est à cause de cette propriété.

Lancez la table en mode saisie de données, Et écrivez Allemagne comme PaysOrigine de Chaplin (Un élément hors-liste, donc).

Tant que vous n'enregistrez pas, vous pouvez cliquer dans les différents champs de Chaplin, Access ne bronche pas (Alors que si vous aviez indiqué Limiter à liste : oui, il aurait déjà affiché une erreur).

Mais à l'instant de l'enregistrement, vous avez alors ce message d'erreur (facile à comprendre, pour une fois ) :

Il vous est donc impossible d'enregistrer.

Ajoutez Allemagne (Vous vous rappelez de la petite icone ?), dans le formulaire F_Pays.

Fermez ensuite F_Pays.

Choisissez cette fois Allemagne dans Chaplin, enregistrez. Voilà ! Cette fois ça maaaaaaaaaarche !

Maintenant, fermez la table T_Client, et ouvrez T_Pays. Supprimez Italie.

Pas de souci ! Comme aucun client ne vient d'Italie, la suppression se fait sans problème.

 

Par contre, essayez maintenant de supprimer Suisse. Là, ce n'est plus possible, simplement parce qu'au moins un client est originaire de Suisse (2 clients dans notre cas) :

Nous verrons un peu plus bas dans cette leçon comment arriver à nos fins.

Modification de deux tables simultanées avec "+"

Constatez une chose sympathique dans T_Pays : il y a maintenant un petit + à gauche.

Si vous cliquez dessus, vous voyez tous les clients associés. Cliquez sur le petit + de Suisse, et celui de France :

Over méga trop cool, non ?

Mieux encore : vous avez la possibilité d'ajouter ou de modifier des clients directement ici ! Essayez : Ecrivez Grande chanteuse comme remarque d'Edith Piaf, et ajoutez Roger Federer comme Suisse :

 

Fermez T_Pays, et ouvrez T_Client. Constatez que les changements ont été bien pris en compte :

Ces petits + apparaissent dès qu'un champ en clé primaire est relié (avec ou sans intégrité référentielle) avec un champ d'une autre table.

 

Ils n'apparaissent pas lors de la création de la liste déroulante : la preuve, c'est que nous avons un champ VilleHabitation dans T_Client, qui est une liste déroulante basée sur T_Ville (Vous vous rappelez ?).

Eh bien, si vous allez dans T_Ville, vous ne verrez pas ces petits +.

 

Ben tiens ! Voilà un excellent exercice !!!

Allez dans T_Client en mode création, et commencez par glisser le champ VilleHabitation juste en dessous de Prenom, pour vous faciliter la vie.

Votre exercice consiste à créer une relation avec intégrité référentielle entre T_Ville et T_Client. Pour ce faire, vous allez évidemment rencontrer quelques problèmes, sinon ce ne serait pas drôle ! Pas d'inquiétude : ce ne sont que des problèmes du même genre que ceux que nous avons eu avec les pays.

Afin de créer la relation entre les pays et les clients, nous avons refait, un peu plus haut dans cette leçon, la liste déroulante, et au dernier moment, nous avons répondu Oui à la question "La table doit être enregistrée avant que les relations ne puissent être créées".

Ne refaites pas ça : allez simplement dans les relations, et cliquez sur Afficher la table, et choisissez T_Ville :

Et voià ! A vous ! A vous de déterminer pourquoi l'intégrité référentielle ne fonctionne pas, ajouter une clé primaire et compléter T_Ville si besoin !

 

A la fin de l'exercice, vous devez avoir la relation avec intégrité référentielle, ainsi que les petits + dans T_Client :

Si vous n'y arrivez pas, vous pourrez de toute façon télécharger la base de données à la fin de la leçon, qui contient la relation correcte.

Rapport entre Relations et Listes déroulantes

On pourrait s'imaginer qu'il existe un rapport entre une liste déroulante et une relation.

En fait non : on peut très bien avoir une liste déroulante basée sur une autre table, sans qu'il y ait de relation entre elles.

A contrario, on peut aussi avoir deux tables liées entre elles (avec même l'intégrité référentiellesans qu'il y ait de liste déroulante.

Suppression d'une liste déroulante

Faisons un  test : détruisez la liste déroulante que vous avez faite sur PaysOrigine. C'est facile, il suffit de choisir Contrôle de l'affichage / Zone de texte dans ses propriétés :


Vous constaterez que toutes les autres propriétés de l'onglet Liste de choix ont disparu :

Lancez la table en mode saisie de données.

Essayez maintenant d'écrire, par exemple, Argentine comme PaysOrigine de Patrick Bruel : à l'instant de l'enregistrement, vous aurez le message d'erreur que vous connaissez.

Vous devrez donc connaître les pays de T_Pays par coeur, pour pouvoir les inscrire ainsi sans aucune erreur !

En résumé : les listes déroulantes n'ont rien à voir avec les relations avec intégrité référentielle, mais il faut admettre que ces deux concepts fonctionnent sacrément bien ensemble.

Comme l'amour et le sexe, finalement .

Du coup, remettez la propriété Contrôle de l'affichage de PaysOrigine à Zone de liste déroulante :  

Mise à jour en cascade

Maintenant, il y a quelques contraintes liées à cette intégrité référentielle ! Essayez ceci :

  1. Dans la table T_Client, Refaites glisser le champ PaysOrigine juste sous Prenom, et fermez T_Client
  2. Allez dans T_Pays, et ajoutez le nouveau pays Portugale (le e à la fin est une faute, mais faisons-là exprès)
  3. Fermez T_Pays, et retournez dans T_Client. Dites que Coluche et Di Caprio viennent du Portugale

Nous sommes donc sous le coup d'un pays mal orthographié dans T_Pays, et par conséquent, également dans T_Client !

Que faire ?

Nous savons que dans T_Client, si nous essayons d'enlever le e de Portugale, nous aurons un message d'erreur, puisque l'intégrité référentielle nous empêche d'avoir la moindre différence entre les deux tables !

Et si nous nous rendions dans T_Pays pour retirer ce e à Portugale ? ... Vous pouvez essayer, mais vous aurez le même message d'erreur : comme des clients viennent de Portugale, pas question de modifier l'orthographe dans la table source non plus !

Que faire, que faire ???

On pourrait ajouter le nouveau pays Portugal (sans e) dans T_Client, et remplacer tous les Portugale par Portugal dans T_Client...

On pourrait ! ... Mais si nous avions 2'000 clients portuguais, ce sera pénible

On pourrait aussi supprimer l'intégrité référentielle, ce qui nous permet denlever le e de Portugal dans T_Pays sans qu'Access ne dise rien, puis remplacer aussi les Portugale par Portugal dans les PaysOrigine de T_Client...

On pourrait ! ... Mais ça revient un peu à retirer la police le temps de commettre une malversation et la remettre juste après... C'est très moyen !

Il existe une solution vraiment plus efficace !

Fermez toutes les tables éventuellement ouvertes et allez dans les relations. Double-cliquez sur la relation T_Pays - T_Client.

 

Ca affiche les propriétés de la relation.

 

Cochez Mettre à jour en cascade les champs correspondants, et OK.

Fermez les relations.

Allez dans T_Pays, et supprimez le e de Portugale. Fermez la table : il se laisse faire !

En fait, il vient de corriger l'orthographe de manière discrète et transparente dans nos 2 clients portuguais également ! Consultez T_Client pour vérifier si je dis vrai !

C'est donc une option bien utile !

Ca, c'était pour autoriser les modifications orthographiques.

Suppressions d'enregistrements en cascade

Mais que se passe-t-il si vous désirez carrément effacer Portugal de T_Pays ? Essayez ! Vous êtes gratifié de ce message d'erreur que vous connaissez.

En effet, c'est une autre histoire ! Corriger l'orthographe, c'est une chose ! Mais que faire lorsque l'enregistrement de référence n'existe carrément plus ?

Vous pourriez vous dire qu'Access n'a qu'à simplement effacer Portugal de Coluche et de Di Caprio, et le tour est joué !

Oui, vous avez raison, mais non, Access ne fait pas ça.

Fermez vos tables et allez dans les relations.

Double-cliquez à nouveau sur la relation T_Pays - T_Client, et, cette fois-ci, toujours dans la même boîte de dialogue, cochez Effacer en cascade les champs correspondants, et OK.

Fermez les relations.

Dans T_Client, nous avons actuellement : 11 clients, dont deux portuguais.

 

Fermez T_Client si vous l'aviez ouverte, et ouvrez T_Pays.

Supprimez Portugal.

Et voilà ! Access vient d'effacer Portugal dans T_Pays, mais également nos deux clients portuguais !

 

Fermez T_Pays, et ouvrez T_Client pour vous en convaincre : T_Client n'a plus maintenant que 9 clients au lieu de 11 :

Dangers de la suppression en cascade

pas question d'annuler cette suppression ! Si vous aviez eu 23'000 clients portuguais, ils se seraient volatilisés sans autre forme de procès ! Inutile de dire que cette case à cocher est à utiliser avec précaution !

En résumé, vous pouvez avoir deux tables reliées entre elles

  1. avec intégrité référentielle
    ou
  2. sans intégrité référentielle

S'il y a une intégrité référentielle, on dispose alors de deux autres possibilités :

  1. Mettre à jour en cascade les champs correspondants
    et/ou
  2. Effacer en cascade les enregistrements correspondants.

Choisir la bonne option de relation

Cas Explication

Imaginons que nous avons hérité de ces deux tables T_Pays et T_Client, et que l'intégrité référentielle soit impossible à créer pour le moment. Il faudra prendre le temps de vérifier les pays et les clients associés. En attendant, établissons une relation sans intégité référentielle, juste pour info.

Ou alors...

Il s'agit d'une table T_Pays, avec seulement une dizaine de pays, les plus courants, mais rien n'empêche d'en ajouter d'autres à la main dans la table T_Client. L'idée étant ici de pouvoir écrire plein de pays exotiques pour certains clients sans pour autant remplir T_Pays de pays rarement utilisés.

Ce cas est le plus strict : Il ne peut pas y avoir de client originaire d'un pays absent de T_Pays (intégrité référentielle), mais, en plus, nous estimons que T_Pays est impeccable, et qu'il n'y a aucune raison d'en changer l'orthographe (Mettre à jour en cascade non cochée). Et, pour finir, il est hors de question d'effacer un pays dont un ou plusieurs clients serait originaire, puisque ça effacerait les clients en question (Effacer en cascade non cochée) !

L'intégrité référentielle est forcément cochée pour nous permettre de cocher la case Mettre à jour en cascade.

Ici, nous avons créé T_Client, mais il nous reste quelques doutes sur l'orthographe des pays ! USA ou Etats-Unis ? Confédération Helvétique ou Suisse ? ... Lorsque nous finirons par être certains de nos orthographes, nous décocherons cette case.

Dans ce cas, l'intégrité référentielle est aussi obligatoire pour cocher la case Effacer en cascade.

Imaginons que nous devons abandonner les clients en provenance de tel ou tel pays (pour des raisons légales par exemple), ce peut être pratique de pouvoir ainsi supprimer directement les pays de T_Pays pour que les clients correspondants disparaissent également (Je vous avoue que cet exemple est un peu tiré par les cheveux... Vous rencontrerez certainement des cas plus pertinents pour lesquels cette case à cocher s'avérera plus utile que dangereuse)

En très gros, toutes les tables d'une base de données Access devraient théoriquement être reliées entre elles avec une relation pourvue d'une intégrité référentielle. Si vous êtes confronté à une base de données dans laquelle les tables ne sont pas toutes reliées ainsi, ça veut dire que :

OU

Juste avant de terminer, réaffichez les objets en mode "onglets" (Fichier / Options / Base de données active / Documents à onglets.

Dans cette leçon, nous avons vu comment et pourquoi relier deux tables entre elles.

Nous avons constaté qu'il y a des relations avec et sans intégrité référentielle.

Sans intégrité, les relations perdent toute utilité. Les champs reliés avec intégrité doivent être de même type (on ne relie pas du texte avec du chiffre).

Pour obtenir l'intégrité, il faut qu'un des deux champs soit défini en clé primaire. Les champs doivent correspondre entre les deux tables, à la lettre près.

Nous avons ensuite vu qu'il est possible de supprimer un enregistrement dans une table, et de supprimer en cascade les enregistrements correspondants dans la table liée.

Nous constatons que pour rendre la chose plus aisiée, mieux vaut créer les relations avant même de commencer à saisir des données.

Exercice

L'exercice consiste à créer une nouvelle base de données que vous nommerez ExerciceLecon016.accdb.

Il s'agit d'une base de données qui contient une table T_Voiture qui contient des voitures d'occasion à vendre. Chaque voiture est identifiée par sa clé primaire NoChassis, et pour chaque voiture, on doit connaître :

  • Sa Couleur (Liste déroulante basée sur une table T_Couleur)
  • Ses options (Liste déroulante avec plusieurs valeurs possibles, basée sur une table T_Option)
  • Sa marque (Liste déroulante basée sur une table T_Marque. Dans T_Marque, il y a également une liste déroulante pour choisir le Pays constructeur de la Marque)

Dans la base de données d'exercice, vous verrez déjà des relations... Mais alors, c'est du grand n'importe quoi ! Des tables mal reliées (ou pas reliées du tout), pas d'intégrité réfétentielle ! Ca ne va pas du tout !

Les tables sont déjà pleines de données, et les listes déroulantes sont déjà créées, mais alors ! Quel désordre !

Que d'erreurs, que d'erreurs !!!

Je vous les ai surligné en rouge pour vous aider :

Par exemple :

  • Jantes Allu avec 2 L, c'est une faute !
  • Allemagne et Deutschland dans les Pays, c'est idiot !
  • Deux fois Nacre dans la liste des couleurs, quelle idée ?
  • Un enregistrement vide dans les Pays, on se demande pourquoi ?
  • Une Toyota à vendre alors que cette marque n'existe pas dans la liste des marques !

Mais quelle horreur !

Demandez l'affichage en fenêtres superposées pour visualiser toutes les tables d'un coup, ça vous aidera grandement pour le nettoyage !

Votre travail consiste à faire un grand nettoyage de tout ça. Ca veut donc dire :

  • Rectifier les erreurs
  • Mettre les clés primaires là où il faut
  • Lier toutes les tables avec intégrité référentielle

Remarque pour les options : dans T_Voiture, vous voyez . C'est parce qu'il s'agit d'une liste déroulante multi-choix. En gros, pour l'exemple de la BMW, Option contient le texte suivant :

  • GPS; Jantes Alu; Tatouage vitres

Tandis que Option contient

  1. GPS
  2. Jantes Alu
  3. Tatouage Vitres

C'est donc Option.Value, et pas Option qu'il faudra lier à T_Option. C'est un peu bizarre, mais ne vous inquiétez pas, on y reviendra.

Si vous avez des problèmes, vous pouvez cliquer ici pour visualiser la solution de l'exercice comme une simple image, et comme toujours, la solution complète de l'exercice se trouve ici :

Quizz
1. Effacer une table dans le tableau d'affichage des relations équivaut à :
L'effacer de la base de données
La masquer de la base de données
La masquer du tableau d'affichage des relations
2. Est-il possible de relier deux tables sans intégrité référentielle si les deux champs reliés sont de type différent ?
Oui
Non
Oui, si dans les options d'Access, on a coché l'option Base de données active / Intégrité permissive
3. Lorsque dans une table T_1, on crée une liste déroulante basée sur la table T_2
T_1 et T_2 doivent ensuite être reliées
T_1 et T_2 doivent ensuite être reliées avec intégrité référentielle
T_1 et T_2 doivent ensuite être reliées avec intégrité référentielle, et avoir l'option Mettre à jour en cascade les champs correspondants
T_1 et T_2 ne doivent pas obligatoirement être reliées par la suite
4. Je relie deux tables, mais aucun des deux champs de liaison n'est défini en clé primaire :
Je ne peux donc pas créer l'intégrité référentielle
La coche intégrité référentielle est cochée par défaut, et je ne peux pas la retirer
A l'instant de la création de la relation, Access me propose de créer lui-même la clé primaire dans chacune des tables
Je ne peux carrément pas créer de relation, même sans intégrité référentielle

5. J'ai une table T_1, avec un champ Truc (pas clé primaire) qui contient :

  • FrigZ
  • frigz

J'ai aussi une table T_2, avec un champ Machin (en clé primaire) qui contient :

  • FRIGZ
  • GROUM

Je veux relier Truc à Machin avec intégrité référentielle. Est-ce possible ?

Oui
Non
Oui, si dans les options d'Access, on a coché l'option Base de données active / Intégrité permissive
La question ne se pose pas, car la table T_1 ne peut pas contenir deux fois la même chose, même si les majuscules sont disposées différemment
6. J'ai deux tables reliées entre elles avec intégrité référentielle, et je veux supprimer la relation :
Oui, pas de problème
Oui, seulement s'il n'existe aucune donnée dans aucune des deux tables
Oui, seulement si la table dans laquelle il y a la clé primaire ne contient aucune donnée
Non. On ne peut pas. La seule solution est de détruire l'une des deux tables et la recréer
7.J'ai une table T_1, avec un champ Truc (pas clé primaire) qui contient :
  • FRIGZ
  • Tutut
  • FRIGZ

J'ai aussi une table T_2, avec un champ Machin (en clé primaire) qui contient :

  • FRIGZ
  • TUTUT

Ces deux tables sont reliées avec l'option Effacer en cascade les champs correspondants. J'efface FRIGZ dans T_1. Combien me restera-t-il d'enregistrements dans T_2

1
2
La question ne se pose pas, car l'intégrité référentielle nous interdit d'effacer des enregistrements dans T_1. L'option Effacer en cascade ne nous permet d'effacer des enregistrements que dans T_2
7.J'ai une table T_1, avec un champ Truc (pas clé primaire) qui contient :
  • elephant
  • éléphant
  • élephant

J'ai aussi une table T_2, avec un champ Machin (en clé primaire) qui contient :

  • éléphant
  • girafe

Ces deux tables sont reliées avec l'option Effacer en cascade les champs correspondants. J'efface éléphant dans T_2. Combien me restera-t-il d'enregistrements dans T_1

0
1
2
3
Ca va dépendre de ce qu'on répondra a la question Prendre en compte les caractères accentués lors de la suppression.