IPV4 to IPV6: Base de données

Le piège du null

Tukiyooo 0 Base de données, PostgreSQL, SQL 05:33

Plantons le décor: <div>Une table "dépenses" créée avec l'instruction:</div><div style="background-color: #ffffcc;">CREATE TABLE dépenses (référence CHAR(9) PRIMARY KEY,  date_exec DATE,  montant NUMERIC(13,2),  détails VARCHAR(50),  code_u CHAR(2)) ;</div>et dans laquelle quelques données ont été placées. un code_u dont la signification est:  code_u | signification      --------+----------------  01     | Médecin  02     | Grande surface  03     | Carburant  04     | Restaurant Voilà, le décor est planté. Examinons le contenu de la table "dépenses", par exemple depuis un terminal psql si nous travaillons avec PostgreSQL: <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg72qfGSsx8XaL2HXwMyzB9eW9DlSJ1Q2DO6BPtlecHuPRr-akO3YISm98TeAEzUj-4n9HEofd4cLp4T4dj0qYN9JyggYNUTyPrMAyhpBbXHkY8v3NuO0XR7wlqnYxWluaPs-qeelrpmvE/s1600/Capture+du+2013-06-02+11:54:20.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg72qfGSsx8XaL2HXwMyzB9eW9DlSJ1Q2DO6BPtlecHuPRr-akO3YISm98TeAEzUj-4n9HEofd4cLp4T4dj0qYN9JyggYNUTyPrMAyhpBbXHkY8v3NuO0XR7wlqnYxWluaPs-qeelrpmvE/s1600/Capture+du+2013-06-02+11:54:20.jpg" /></a></div> Nous désirons mettre à jour code_u avec cette instruction: <div style="background-color: #ffffcc;">UPDATE dépenses SET code_u = '02' WHERE (détails = 'Courrefar' OR détails = 'Caro' OR détails = 'Aupré' ) AND code_u <> '02' ;</div>La ligne AND est là pour éviter les mises à jour inutiles (mise à jour des rangées avec code_u valant déjà '02'). En principe 3 rangées devraient être impactées: celles dont la référence se termine par 17, 21 et 22. Vérifions s'il en sera bien ainsi: <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg7nxeWDCUuBLG6PduzkiWQ5wkYz8PWU208W_7kErX_f3bcLVdmiZ2Xk884R3cypBvNjgL_sCqIoCH35OStb1kygO6qFVQpln8LQDLE4txk966BwjT_m5P_W1ojmQLuRg4xh5ps3SlT0-Y/s1600/Capture+du+2013-06-02+16:04:50.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg7nxeWDCUuBLG6PduzkiWQ5wkYz8PWU208W_7kErX_f3bcLVdmiZ2Xk884R3cypBvNjgL_sCqIoCH35OStb1kygO6qFVQpln8LQDLE4txk966BwjT_m5P_W1ojmQLuRg4xh5ps3SlT0-Y/s1600/Capture+du+2013-06-02+16:04:50.jpg" /></a></div> Il manque la rangée 2013-0022. Pourtant rien ne semble la distinguer (par rapport aux critères de sélection) de la rangée 2013-0021. Poussons plus loin  les investigations. L'utilisation de la méta-commande \pset null nous permet de choisir ce qui sera imprimé dans le cas de la valeur NULL. Par défaut rien n'est imprimé <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4BKK-Zo-TwIyMP2iNUDCzTCtZac2qusuvps1u9EPlRksx-rJqWYxEnB50A2hG37E4stGsAs3IBzDZCHxAeQKtVd5T15qWEuDCIGYUQqqM5WbFmSWcOaH0z0jYClOQEGjDZ8dDYSoDHBk/s1600/Capture+du+2013-06-02+16:07:28.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4BKK-Zo-TwIyMP2iNUDCzTCtZac2qusuvps1u9EPlRksx-rJqWYxEnB50A2hG37E4stGsAs3IBzDZCHxAeQKtVd5T15qWEuDCIGYUQqqM5WbFmSWcOaH0z0jYClOQEGjDZ8dDYSoDHBk/s1600/Capture+du+2013-06-02+16:07:28.jpg" /></a></div> Nous voyons maintenant apparaître une différence essentielle entre les rangées 2013-0021 et 2013-0022. Pour la rangée 2013-0021, code_u est caractérisé par une chaîne de caractère vide, ce qui permet de dire que code_u n'est pas égal à 01, ni à 02, 03, 04.(ou sinon la chaîne ne serait pas vide). Par contre dans le cas de la rangée 2013-0022, l'indication NULL signifie que la base de données ne contient aucune information concernant code_u. Pour cette rangée, code_u est inconnu: il peut très bien valoir '02', où pas. Il est donc impossible de mettre cette rangée dans l'ensemble de celles dont le code_u vaut '02' ou dans l'ensemble de celles dont le code_u est différent de '02'. L'union des 2 ensembles nous fournit 7 rangées (au lieu de 9): <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdJnPjoo2FuxfLMnyNwwTqMJyh81_nkZMrl839Rpj3-DaZ_UWEntHEkcpWYtnmsRrOda10h8yIXX8oofSkWsJ6MrdaRM3YeU6Z9R14lzRIVfoI-HlvjeQ4TqKKmac05p7KqiIWdTQkHmE/s1600/Capture+du+2013-06-02+17:02:56.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdJnPjoo2FuxfLMnyNwwTqMJyh81_nkZMrl839Rpj3-DaZ_UWEntHEkcpWYtnmsRrOda10h8yIXX8oofSkWsJ6MrdaRM3YeU6Z9R14lzRIVfoI-HlvjeQ4TqKKmac05p7KqiIWdTQkHmE/s1600/Capture+du+2013-06-02+17:02:56.jpg" /></a></div> Si le critère de sélection est code_u = '02', les rangées avec code_u NULL se sont pas reprises. Mais elles ne le sont pas non plus si le critère de sélection est code_u différent de '02'. Et voilà le piège, le piège du NULL dans lequel il ne faut pas tomber! Y tomber, c'est être nul! L'instruction à utiliser pour la mise à jour devrait être: <div style="background-color: #ffffcc;">UPDATE dépenses SET code_u = '02' WHERE (détails = 'Courrefar' OR détails = 'Caro' OR détails = 'Aupré' ) AND (code_u <> '02' OR code_u IS NULL ) ;</div> Dans le cas d'une base de données avancée comme PostgreSQL, qui gère l'intégrité référentielle, nous aurions pu après avoir créé une table destinée à contenir les significations de code_u: <div style="background-color: #ffffcc;">CREATE TABLE utilisations (code_u CHAR(2) PRIMARY KEY,  signification VARCHAR(20)) ;</div>créer la table "dépenses" comme suit: <div style="background-color: #ffffcc;">CREATE TABLE dépenses (référence CHAR(9) PRIMARY KEY,  date_exec DATE,  montant NUMERIC(13,2),  détails VARCHAR(50),  code_u CHAR(2) REFERENCES utilisations) ;</div>Dans ce cas les seules valeurs autorisées pour code_u auraient été celles qui figurent dans la table "utilisations". Une rangée comme la 2013-0021 n'aurait pas pu exister, mais bien des rangées avec code_u à la valeur NULL. La notion d'intégrité référentielle a été étudiée <a href="http://ipv4-ipv6.blogspot.com.blogspot.be/2011/11/integrite-referentielle.html">dans ce billet</a>.

Le piège du null

Plantons le décor: Une table "dépenses" créée avec l'instruction: CREATE TABLE dépenses (référence CHAR(9) PRIMARY KEY, date_...

Jointures et autres requêtes sql

Tukiyooo 0 Base de données, PostgreSQL, SQL 04:20

Dans notre base de données PostgreSQL de test, nous créons deux tables à l'aide des instructions SQL suivantes: <div style="background-color: #cfe2f3;">CREATE TABLE opération (jour CHAR(8),  seq SMALLINT,  montant NUMERIC(13,2),  contrepartie CHAR(5),  PRIMARY KEY (jour, seq)) ; CREATE TABLE contrepartie (contrepartie CHAR(5) PRIMARY KEY,  libellé VARCHAR(50)) ;</div>et nous y plaçons quelques données, données que nous pouvons facilement retrouver grâce à des requêtes SQL élémentaires, exécutées par exemple dans un terminal psql: <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhefBfZJ7OBC_xt7Denb0ixUSP62-oh3c8CIEcvGiuUoq5pGslZDqcA73JAgDjSA1x_H5hdCC-NX-9FTB78pj0rfoqmIDYRiRfrckYYlyiP7GoIpqztPKeBCUvREiZHlRR7a916DJuxcVA/s1600/join01.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhefBfZJ7OBC_xt7Denb0ixUSP62-oh3c8CIEcvGiuUoq5pGslZDqcA73JAgDjSA1x_H5hdCC-NX-9FTB78pj0rfoqmIDYRiRfrckYYlyiP7GoIpqztPKeBCUvREiZHlRR7a916DJuxcVA/s1600/join01.jpeg" /></a></div> Nous aimerions maintenant faire apparaître à la suite de chaque opération le libellé de la contrepartie. Pour ce faire nous devons réaliser une jointure entre les deux tables. Diverses possibilités s'offrent à nous. Jointure intérieure (INNER JOIN): <div style="background-color: #e6e6ff;">bdtest=> SELECT a.jour, a.seq, a.montant, b.libellé bdtest-> FROM opération a INNER JOIN contrepartie b bdtest-> ON a.contrepartie = b.contrepartie bdtest-> ORDER BY a.jour, a.seq bdtest-> ;    jour   | seq | montant |    libellé      ----------+-----+---------+----------------  2011-255 |   2 | -400.00 | Epargne #1  2011-255 |   3 | -123.29 | Grande Surface  2011-255 |   4 | -150.00 | Alphonse  2011-256 |   2 |   57.49 | Epargne #2 (4 lignes)</div>Le mot INNER est facultatif car il s'agit du type de jointure par défaut. Les deux colonnes qui sont jointes portent le même nom. Dans ce cas, nous aurions pu arriver au même résultat avec le query suivant: <div style="background-color: #cfe2f3;">SELECT a.jour, a.seq, a.montant, b.libellé FROM opération a NATURAL JOIN contrepartie b ORDER BY a.jour ;</div>Un problème se pose: toutes les opérations ne sont pas reprises. Nous devons donc utiliser un autre type de jointure: Jointure extérieure gauche (LEFT OUTER JOIN): <div style="background-color: #e6e6ff;">bdtest=> SELECT a.jour, a.seq, a.montant, b.libellé bdtest-> FROM opération a NATURAL LEFT OUTER JOIN contrepartie b bdtest-> ORDER BY a.jour, a.seq bdtest-> ;    jour   | seq | montant |    libellé      ----------+-----+---------+----------------  2011-255 |   1 | 1460.45 |   2011-255 |   2 | -400.00 | Epargne #1  2011-255 |   3 | -123.29 | Grande Surface  2011-255 |   4 | -150.00 | Alphonse  2011-256 |   1 |  -48.00 |   2011-256 |   2 |   57.49 | Epargne #2  2011-257 |   1 |  -28.27 |  (7 lignes)</div>Lorsque la colonne a.contrepartie contient une valeur qui n'existe pas dans la table contrepartie, le système joint à la rangée concernée de la table opération une rangée (fictive) de la table contrepartie dont tous les champs sont à la valeur NULL. En ajoutant une clause WHERE au query précédent (et en modifiant aussi la clause SELECT), nous pouvons établir une liste des contreparties dont le libellé manque: <div style="background-color: #e6e6ff;">bdtest=> SELECT DISTINCT a.contrepartie bdtest-> FROM opération a NATURAL LEFT OUTER JOIN contrepartie b bdtest-> WHERE b.contrepartie IS NULL bdtest-> order by a.contrepartie bdtest-> ;  contrepartie  --------------  autre  EL     RN    (3 lignes)</div>D'autres queries conduisent au même résultat: <div style="background-color: #cfe2f3;">SELECT DISTINCT a.contrepartie FROM opération a WHERE NOT EXISTS (SELECT *  FROM contrepartie b  WHERE b.contrepartie = a.contrepartie) ORDER by a.contrepartie ;</div>ou encore: <div style="background-color: #cfe2f3;">SELECT DISTINCT contrepartie FROM opération  WHERE contrepartie NOT IN (SELECT contrepartie  FROM contrepartie ) ORDER by contrepartie ;</div>Nous n'avons pas épuisé toutes les possibilités en ce qui concerne les types de jointures: Jointure externe droite (RIGHT OUTER JOIN) <div style="background-color: #e6e6ff;">bdtest=> SELECT a.jour, a.seq, a.montant, b.libellé bdtest-> FROM opération a NATURAL RIGHT OUTER JOIN contrepartie b bdtest-> ORDER BY a.jour bdtest-> ;    jour   | seq | montant |    libellé      ----------+-----+---------+----------------  2011-255 |   2 | -400.00 | Epargne #1  2011-255 |   3 | -123.29 | Grande Surface  2011-255 |   4 | -150.00 | Alphonse  2011-256 |   2 |   57.49 | Epargne #2           |     |         | Magasin (5 lignes)</div>Cette fois tous les libellés de la table de droite font partie du résultat, qu'il y ait ou non des opérations correspondantes. Et enfin... Jointure  complète (FULL JOIN) <div style="background-color: #e6e6ff;">bdtest=> SELECT a.jour, a.seq, a.montant, b.libellé bdtest-> FROM opération a  NATURAL FULL JOIN contrepartie b bdtest-> ORDER BY a.jour bdtest-> ;    jour   | seq | montant |    libellé      ----------+-----+---------+----------------  2011-255 |   1 | 1460.45 |   2011-255 |   2 | -400.00 | Epargne #1  2011-255 |   3 | -123.29 | Grande Surface  2011-255 |   4 | -150.00 | Alphonse  2011-256 |   1 |  -48.00 |   2011-256 |   2 |   57.49 | Epargne #2  2011-257 |   1 |  -28.27 |            |     |         | Magasin (8 lignes)</div>Répartition sur deux colonnes des crédits et des débits Dans la table opération tous les montants sont dans une seule colonne. Il est possible de les afficher sur deux colonnes, une réservée aux crédits et une autre pour les débits: <div style="background-color: #e6e6ff;">bdtest=> SELECT jour, seq, libellé, bdtest-> CASE   bdtest->  WHEN montant > 0 THEN bdtest->  montant  bdtest-> ELSE 0 bdtest-> END AS credit, bdtest-> CASE   bdtest->  WHEN montant < 0 THEN bdtest->  -montant  bdtest-> ELSE 0 bdtest-> END AS debit bdtest-> FROM opération NATURAL LEFT OUTER JOIN contrepartie bdtest-> ORDER BY jour;    jour   | seq |    libellé     | credit  | debit   ----------+-----+----------------+---------+--------  2011-255 |   1 |                | 1460.45 |      0  2011-255 |   2 | Epargne #1     |       0 | 400.00  2011-255 |   3 | Grande Surface |       0 | 123.29  2011-255 |   4 | Alphonse       |       0 | 150.00  2011-256 |   1 |                |       0 |  48.00  2011-256 |   2 | Epargne #2     |   57.49 |      0  2011-257 |   1 |                |       0 |  28.27 (7 lignes) <div> </div>bdtest=> \w opération.sql</div>Après exécution de la requête, nous avons écrit celle-ci dans le fichier opération.sql en vue d'un usage ultérieur. Exportation vers un tableur L'output de la requête ci-dessus peut-être redirigé vers un fichier et formaté de manière telle que les données puissent être facilement exportées vers un tableur. Pour ce faire, il suffit d'utiliser dans notre terminal psql les meta commandes suivantes: \pset numericlocale (pour avoir par exemple 1.460,45 au lieu de 1460.45) \a pour passer au mode de sortie non aligné \t pour afficher uniquement les rangées (sans les en-têtes) \f ';' pour initialiser le séparateur de champ à ';' \o [FICHIER] pour envoyer le résultat de la requête vers le fichier FICHIER \i  [FICHIER] pour exécuter les commande du fichier FICHIER (ce n'est évidemment pas le même) <div style="background-color: #e6e6ff;">bdtest=> \pset numericlocale Affichage de la sortie numérique adaptée à la locale. <div>bdtest=> \a</div>Le format de sortie est unaligned. bdtest=> \t Affichage des tuples seuls. bdtest=> \f ';' Le séparateur de champs est « ; ». bdtest=> \o bilan.csv bdtest=> \i opération.sql bdtest=> </div>Ouvrons bilan.csv dans un tableur. Rapidement nous arrivons à ceci: <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhc-woDdDPtDTtup-6SWLxBLxbohnpXETaD_OKs-RuLG1g0_tw-j7pJNnG2K0LeK_t0-5P3kSZNMvB0vV93qOrc_J7I_4RaSxZnD8llKx1dHOTVtuutVLUZf40qV2vC-5NGfO7UL_kFyEc/s1600/join05.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhc-woDdDPtDTtup-6SWLxBLxbohnpXETaD_OKs-RuLG1g0_tw-j7pJNnG2K0LeK_t0-5P3kSZNMvB0vV93qOrc_J7I_4RaSxZnD8llKx1dHOTVtuutVLUZf40qV2vC-5NGfO7UL_kFyEc/s1600/join05.jpeg" /></a></div> <div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: left;">Bilans comme résultat de requêtes</div><div class="separator" style="clear: both; text-align: left;">L'idéal pour obtenir un bilan est d'utiliser un tableur. Il est également possible d'établir un rapport depuis un outil comme libreoffice base.</div><div class="separator" style="clear: both; text-align: left;">Cependant de simples requêtes permettent d'arriver à de bons résultats.</div><div class="separator" style="clear: both; text-align: left;">Ainsi:</div><div style="background-color: #cfe2f3;">SELECT jour, seq, libellé, CASE    WHEN montant > 0 THEN  montant   ELSE 0 END AS credit, CASE    WHEN montant < 0 THEN  -montant   ELSE 0 END AS debit, montant AS solde FROM opération NATURAL LEFT OUTER JOIN contrepartie UNION SELECT 'Total' AS jour, 0 AS seq, '' AS libellé, sum(credit) AS credit, sum(debit) AS debit, sum(solde) AS solde FROM (SELECT CASE    WHEN montant > 0 THEN  montant   ELSE 0 END AS credit, CASE    WHEN montant < 0 THEN  -montant   ELSE 0 END AS debit, montant AS solde FROM opération NATURAL LEFT OUTER JOIN contrepartie ) a ORDER BY jour, seq ;</div>nous donne (on a laissé numericlocale activé): <div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYZ0_VqF6_8olTgcfw7uZnqhCOfcnW7iF9HmJBzoS1hjvTo31tePlB3NfwDOhO8jN5J9e6uinJoeXtcB52Be8d3fbreSvYUPdm37kOhyHQbd_EyXFvRkiJBgw3tE-kbN8MqdeFEKnKSOw/s1600/join08.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYZ0_VqF6_8olTgcfw7uZnqhCOfcnW7iF9HmJBzoS1hjvTo31tePlB3NfwDOhO8jN5J9e6uinJoeXtcB52Be8d3fbreSvYUPdm37kOhyHQbd_EyXFvRkiJBgw3tE-kbN8MqdeFEKnKSOw/s1600/join08.jpeg" /></a></div> <div class="separator" style="clear: both; text-align: center;"></div>Ce query: <div style="background-color: #cfe2f3;">SELECT  jour,  sum(credit) AS credit, sum(debit) AS debit, sum(solde) as solde FROM (SELECT jour, CASE    WHEN montant > 0 THEN  montant  END AS credit, CASE    WHEN montant < 0 THEN  -montant  END AS debit, montant AS solde FROM opération) a GROUP BY jour UNION SELECT  'Total' AS jour, sum(credit) AS credit, sum(debit) AS debit, sum(solde) AS solde FROM (SELECT CASE    WHEN montant > 0 THEN  montant  END AS credit, CASE    WHEN montant < 0 THEN  -montant  END AS debit, montant AS solde FROM opération) a ORDER BY jour ;</div>conduit au résultat: <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgIHpYHSBsoDQFPXy3uj3_eOEhiR66rDINXKJxdICnmEGdWJQkC4qsfhf-Jhp9NlR4xIWR28DuHLZL-KK-lobIIpd5XnUjbsIecJ19JTZ4jdVDtfz-l65FNA5sO3q7XZkNLB5qz4PI6ZLs/s1600/join07.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgIHpYHSBsoDQFPXy3uj3_eOEhiR66rDINXKJxdICnmEGdWJQkC4qsfhf-Jhp9NlR4xIWR28DuHLZL-KK-lobIIpd5XnUjbsIecJ19JTZ4jdVDtfz-l65FNA5sO3q7XZkNLB5qz4PI6ZLs/s1600/join07.jpeg" /></a></div> <div class="separator" style="clear: both; text-align: center;"></div>

Jointures et autres requêtes sql

Dans notre base de données PostgreSQL de test, nous créons deux tables à l'aide des instructions SQL suivantes: CREATE TABLE opération (...

Intégrité référentielle

Tukiyooo 0 Base de données, SQL 03:37

PostgreSQL est un SGBD moderne et puissant qui gère la notion d'intégrité référentielle des données. Cette notion a déjà été évoquée plusieurs fois dans ce blog et notamment dans le billet intitulé <a href="http://ipv4-ipv6.blogspot.com/2010/09/postgresql-initiation.html">PostgreSQL (initiation)</a>. Nous allons y revenir un peu plus en détails. Considérons la table des employés d'une entreprise. Cette table contient une colonne "service" où est indiqué sous forme d'un code le service auquel appartient l'employé. D'autre part, nous avons une table "services", donnant la signification de ce code, créée avec cette instruction: <div style="background-color: #cfe2f3;">CREATE TABLE services (service_id CHAR(2) PRIMARY KEY,  dénomination CHAR(25)) ;</div>Cette table contient par exemple les données: <div style="background-color: #cfe2f3;"> service_id |       dénomination         ------------+---------------------------  00         | Direction                  01         | Secrétariat                02         | Gestion du personnel       03         | Relations publiques        04         | Production                 05         | Recherches                 06         | Ventes         </div>PostgreSQL peut garantir que le code service de chaque employé se trouve dans la table "services", en clair chaque employé doit être affecté dans un service. Pour cela, il suffit que la table des employés ait été créée postérieurement à la table "services" suivant par exemple cette instruction: <div style="background-color: #cfe2f3;">CREATE TABLE employés (matricule SERIAL PRIMARY KEY,  nom CHAR(50),  adresse CHAR(50),  code_postal CHAR(5),  cp_seq SMALLINT,  naissance DATE,  sexe CHAR(1) CHECK (sexe IN ('M','F')),  service CHAR(2) REFERENCES services) ;</div>qui définit (en rouge) une contrainte d'intégrité référentielle. La colonne service de la table "employés" constitue une clef étrangère (foreign key) qui pointe vers la clef primaire de la table services. Ici le champ "service" peut prendre la valeur NULL, ce qui correspondrait au cas où le service de l'employé n'est pas connu. Mais la table "employé" peut-être définie de manière à exclure cette possibilité. Que se passe-t-il si nous essayons de supprimer un service contenant des employés? Essayons de procéder dans un terminal psql. La suppression est refusée: <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhPxPfXxlZCHByWHX6Af1iLZF9pj8buPWA08YllNZiOtEhZneKpUaQZsQ_nwJu0WcUcx1UIQE4c2lHopqul_kqb_0Iz_aynzREFg2_MryNREKxwlQvPQaoI8ekVCpqZ_1ocZVD9tBqsgcI/s1600/fk01.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="113" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhPxPfXxlZCHByWHX6Af1iLZF9pj8buPWA08YllNZiOtEhZneKpUaQZsQ_nwJu0WcUcx1UIQE4c2lHopqul_kqb_0Iz_aynzREFg2_MryNREKxwlQvPQaoI8ekVCpqZ_1ocZVD9tBqsgcI/s640/fk01.jpeg" width="640" /></a></div> C'est le comportement par défaut. Mais d'autres possibilités sont offertes par PostgreSQL. Le code service de tous les employés  appartenant au service supprimé peut être mis à NULL . Ce sera le cas si l'instruction de création de la table "employés" contient : <div style="background-color: #cfe2f3;">service CHAR(2) REFERENCES services ON DELETE SET NULL</div>Cette table peut également être définie de telle sorte que la suppression d'un service entraîne la suppression de tous les employés. Ce sera le cas si le "CREATE" contient: <div style="background-color: #cfe2f3;">service CHAR(2) REFERENCES services ON DELETE CASCADE</div>Examinons d'un peu plus près cette possibilité afin d'avoir une idée  de ce qu'implique exactement le mot CASCADE. Dans notre base de données de test nous avons également défini les tables paie et codepaie: <div style="background-color: #cfe2f3;">CREATE TABLE codepaie (code CHAR(2) PRIMARY KEY,  signification CHAR(20)) ; CREATE TABLE paie (matricule INTEGER REFERENCES employés,  mois CHAR(6),  seq SMALLINT,  code CHAR(2) REFERENCES codepaie,  montant NUMERIC(13,2),  PRIMARY KEY (matricule,mois,seq)) ;</div>La table paie se rapportant au paiement du salaire des employés, chaque opération de paiement doit faire référence à un employé donné. La table codepaie donne la signification des codes utilisés et du fait de la relation d'intégrité référentielle, elle définit également l'ensemble des codes autorisés. Schématiquement, nous avons cette situation: <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjiDk7R1LqQ6iXEL0z7yPbFY44mPVXeR_jso47nNz2P3YTdwCgjj5C_AUY-xgY-UHNzwXeQ2h7yz5WYkOW-7zftm23Xvz1BiQW55A5NAjLl6N6rxAmjgVXBAAfyhvSeQG_cXomrIYR7QXA/s1600/bdtest.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="210" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjiDk7R1LqQ6iXEL0z7yPbFY44mPVXeR_jso47nNz2P3YTdwCgjj5C_AUY-xgY-UHNzwXeQ2h7yz5WYkOW-7zftm23Xvz1BiQW55A5NAjLl6N6rxAmjgVXBAAfyhvSeQG_cXomrIYR7QXA/s640/bdtest.jpeg" width="640" /></a></div> <div class="separator" style="clear: both; text-align: center;"></div>Les relations d'intégrité référentielle sont matérialisées par des flèches issues des FK (foreign keys) et pointant vers les PK (primary keys). En rouge, nous avons une relation définie avec 'ON DELETE CASCADE'. De ce fait, un "delete" au niveau de la table "services" va se propager vers la droite, telle une vague destructrice remontant un courant dont le sens est indiqué par les flèches. La table codepaie est à l'abri (la flèche est à l'envers). Mais qu'en est-il de la table paie? Logiquement elle devrait être protégée car la contrainte "paie => employés" n'a pas été définie de manière particulière (elle n'est pas représentée par une flèche rouge). Vérifions tout cela dans notre terminal psql. Procédons avec un utilisateur ordinaire (titine) auquel nous donnons tous les droits sur la table services: <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg696JV5rNf-wOjmE1zj-awQY8DqKoHRzA5IzoSeg2n3wE90ceehLr33X-YVPlm4Ree5uliCPavn_sFdZfbCKCN6NvJNRz7zjzuugGoyX2uL4Oujs3I8C-1j_e1M9V4ieFSc2fV2OM88UE/s1600/fk02.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg696JV5rNf-wOjmE1zj-awQY8DqKoHRzA5IzoSeg2n3wE90ceehLr33X-YVPlm4Ree5uliCPavn_sFdZfbCKCN6NvJNRz7zjzuugGoyX2uL4Oujs3I8C-1j_e1M9V4ieFSc2fV2OM88UE/s1600/fk02.jpeg" /></a></div> Pour les autres tables de bdtest, titine a le droit "select": <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjctvFoalkV6gVNx5yjpwuu7hU9kFmgb06ZNMGH8K4tJKOZT9Z6iJEf-Msv038RTeTHeW8mphtRzC_GnKjJsKeWJv_KpOrSNp3QfVgf-j-fQi6ce_ewlDyKAug2ejHSfCc-Xawxq5thJ8U/s1600/fk10.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjctvFoalkV6gVNx5yjpwuu7hU9kFmgb06ZNMGH8K4tJKOZT9Z6iJEf-Msv038RTeTHeW8mphtRzC_GnKjJsKeWJv_KpOrSNp3QfVgf-j-fQi6ce_ewlDyKAug2ejHSfCc-Xawxq5thJ8U/s1600/fk10.jpeg" /></a></div> <div class="separator" style="clear: both; text-align: center;"></div>mais pas d'autres droits: <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdWbiXR6BUiBILvt9Cu3Wk1yRdwMtd7Y5zj52jS9kp7HHWC0eKitHDr2Aw_jTpPq9htNptX8Ffoat5QfDgn0EXh8jY_x7O72MaBOfrgDY39k240RUZOeEh7Wgrsj2DoO_Wnol07w4NwIk/s1600/fk11.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdWbiXR6BUiBILvt9Cu3Wk1yRdwMtd7Y5zj52jS9kp7HHWC0eKitHDr2Aw_jTpPq9htNptX8Ffoat5QfDgn0EXh8jY_x7O72MaBOfrgDY39k240RUZOeEh7Wgrsj2DoO_Wnol07w4NwIk/s1600/fk11.jpeg" /></a></div> Recherchons tout d'abord quel est dans chaque service le nombre d'employés référencés par la table paie: <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjTAWqwd3WYsbhw1fcBr0xOTwMY3TTDlchQzh4ROBxFPWYxWEU94LOhsTxlY4Z2ZEfa5b4PrXJmJTiwO4wptvbXh9rb-jGHnG97SLzcxyZySiD4hRwI4wGupdc7ZX3uNU0dbFclkKtGHf0/s1600/fk04.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjTAWqwd3WYsbhw1fcBr0xOTwMY3TTDlchQzh4ROBxFPWYxWEU94LOhsTxlY4Z2ZEfa5b4PrXJmJTiwO4wptvbXh9rb-jGHnG97SLzcxyZySiD4hRwI4wGupdc7ZX3uNU0dbFclkKtGHf0/s1600/fk04.jpeg" /></a></div> En fait, deux employés seulement sont concernés: un dans le service '00' et un autre dans le service '01'. Essayons dans un premier temps de supprimer le service '02': <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-Lm5_PIVQoqJ6rzu5-5eYo8Lv0-NfZR7rLHLlMMemxrlgDh8wzJQ8aTs7zNrmZ41SqRwwKQMb7nEKG4w-8hXP5la7n8-qzEehFSIbbs21dX_R56ZeLfoFTNp7COKPqcQi2rWhUvxLqdg/s1600/fk05.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-Lm5_PIVQoqJ6rzu5-5eYo8Lv0-NfZR7rLHLlMMemxrlgDh8wzJQ8aTs7zNrmZ41SqRwwKQMb7nEKG4w-8hXP5la7n8-qzEehFSIbbs21dX_R56ZeLfoFTNp7COKPqcQi2rWhUvxLqdg/s1600/fk05.jpeg" /></a></div> Opération couronnée de succès: une seule rangée supprimée dans la table "services". Par contre dans la table "employés" <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipf8k-I6eDX1zDZnRV2raI1hleVIg190olr08e4OrD9JnCbQRSZYRrI-czM9Ar_7HcPu10fYxtJ21jgyhlv-xCjFGg9DiPRfg8WujbW2lNwaR5na8yGxkf3dOaV7Lk3SKgD-d-xAKfhuk/s1600/fk06.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipf8k-I6eDX1zDZnRV2raI1hleVIg190olr08e4OrD9JnCbQRSZYRrI-czM9Ar_7HcPu10fYxtJ21jgyhlv-xCjFGg9DiPRfg8WujbW2lNwaR5na8yGxkf3dOaV7Lk3SKgD-d-xAKfhuk/s1600/fk06.jpeg" /></a></div> quatre rangées ont été supprimées alors que titine n'a pas le droit "delete" sur cette table! Attaquons-nous maintenant au service '01': <div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgFGd5wumzBoD6Bv1D3PIsJ9Ma3b2AI7CotkuIeIoHx8iov3QyAMfCwaajl6iCYPVa4SyApoYhAjfwU7wHWVCBjouIMRcEYizqnzT8niFbjOa9DkyIvBbU6Djcv5X3scfnR95d4cCxZmU8/s1600/fk07.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="130" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgFGd5wumzBoD6Bv1D3PIsJ9Ma3b2AI7CotkuIeIoHx8iov3QyAMfCwaajl6iCYPVa4SyApoYhAjfwU7wHWVCBjouIMRcEYizqnzT8niFbjOa9DkyIvBbU6Djcv5X3scfnR95d4cCxZmU8/s640/fk07.jpeg" width="640" /></a></div> La suppression d'un des 4 employés du groupe a échoué: celui référencé depuis la table paie. De ce fait malgré l'option CASCADE, la suppression du service devient impossible. Les autres employés du groupe, qui eux auraient pu être supprimés, sont sauvés: <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjv9WHZkCXi0pCBp2FHp-DH-OHHTDaO7lajvlTVwKihunATbcQNcJwjZvSXDKDcb3kcPfeMfnwvS8-yT-UAN-6H2DtthxBgY6OBPc6BOvRtgCxVG0whzDiRG2BPJsTRhs6W_6zxnm6xzNM/s1600/fk09.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjv9WHZkCXi0pCBp2FHp-DH-OHHTDaO7lajvlTVwKihunATbcQNcJwjZvSXDKDcb3kcPfeMfnwvS8-yT-UAN-6H2DtthxBgY6OBPc6BOvRtgCxVG0whzDiRG2BPJsTRhs6W_6zxnm6xzNM/s1600/fk09.jpeg" /></a></div>

Intégrité référentielle

PostgreSQL est un SGBD moderne et puissant qui gère la notion d'intégrité référentielle des données. Cette notion a déjà été évoquée plu...

Le piège du null

Jointures et autres requêtes sql

Intégrité référentielle

ADV

Populars

Arsip Blog