Vérification d’intégrité des données pour la table protection_espece de l’INPN

L’INPN fournie une liste d’espèces réglementées s’appuyant sur son référentiel TAXREF. Réglementations et protections aux niveaux international, communautaire, national, régional ou départemental sont déclinées aux niveaux des taxons terminaux (mais pas seulement car j’ai listé des taxons pour les rangs PH, CL, OR, FM, GN, ES, SSES, VAR, FO, CAR)

Vous pouvez la télécharger ici

Dans le cadre de l’intégration du référentiel TAXREFv60 j’ai voulu faire des tests sur l’intégrité des données. En effet, vu les résultats observés sur la table TAXREF j’ai préféré vérifier avant tout l’existence des clés de liaisons dans la table parente.
En effet, la contrainte de clé étrangère ne fonctionne pas.

Vérification d’intégrité

Pour cela j’ai vérifié si le cd_nom existe dans la table TAXREF

Requête SQL à exécuter

/************************************************************************
 
– Description : Tester l'intégrité sur le cd_nom avec la table taxref
 
– Auteur : Jérôme Seigneuret
 
- Date : 2013-09-13
 
*************************************************************************/
SELECT
    p.cd_nom, cd_nom_cite, nom_cite, syn_cite, nom_francais_cite, precisions
FROM
    tmp.inpn_taxref AS t
RIGHT JOIN inpn_protection_espece AS p
    ON t.cd_nom = p.cd_nom
WHERE
    t.cd_nom IS NULL;

Télécharger la requête

La requête retourne 7 résulat.

J’ai également vérifié si le cd_nom_cite existe dans la table TAXREF

Requête SQL à exécuter

/************************************************************************ 
 
– Description : Tester l'intégrité sur le cd_nom_cite avec la table taxref 
 
– Auteur : Jérôme Seigneuret 
 
- Date : 2013-09-13 
 
*************************************************************************/ 
SELECT
    DISTINCT cd_nom_cite, nom_cite, syn_cite, nom_francais_cite, precisions 
FROM
    inpn_taxref AS t 
RIGHT JOIN inpn_protection_espece AS p  
    ON t.cd_nom = p.cd_nom_cite 
WHERE
    t.cd_nom IS NULL;

Télécharger la requête

La requête retourne 15 codes à revoir.

Vous pouvez visualiser le résultat dans ce tableau.

J’ai envoyé ces résultats au référent INPN TAXREF dans l’attente d’avoir un retour.

Pour l’instant j’ai supprimé les entrées dont le cd_nom n’existe pas. Mais le problème reste présent avec le cd_nom_cite.

DELETE FROM inpn_protection_espece WHERE cd_nom IN (SELECT
    p.cd_nom
FROM
    tmp.inpn_taxref AS t 
RIGHT JOIN inpn_protection_espece AS p  
    ON t.cd_nom = p.cd_nom 
WHERE
    t.cd_nom IS NULL);

Il est probable que cela soit du au script d’export des données de la base TAXREF. Si l’on se réfère à la documentation et au contenu de la base de données, les sous-ordre ne sont pas livrés ce qui pose du coup problème pour l’intégrité des données.
Cas pratique:
En effet, je prend l’exemple de « Ophidia », qui dans l’arbre taxonomique est un sous-ordre des « Squamata », on peut observer son absence de la base de données mais il est mentionné dans la réglementation.

SELECT * FROM tmp.inpn_taxref WHERE cd_nom =186288

Du coup la famille « Colubridae » fait référence directement au taxon supérieur correspond à l’ordre « Squamata ». On voit bien que dans le traitement de hiérarchisation des taxons, les sous-ordre sont ignorés. Le seul moyen est d’ajouter les rangs intermédiaires et de mettre à jour la hiérarchisation.

Je ferai peut-être un patch en ce sens (A moins que l’INPN se décide à le faire lui même). La suite au prochain épisode.

Le code du taxon fait-il référence au taxon valide?

pour des raisons de simplicité dans les requêtes et pour éviter de chercher le taxon valide j’ai vérifié que le cd_nom correspondait bien à un taxon valide dans la table inpn_taxref

Requête SQL :

WITH t 
     AS (SELECT cd_nom 
         FROM   tmp.inpn_taxref 
         WHERE  cd_nom = cd_ref) 
SELECT p.cd_nom 
FROM   inpn_protection_espece AS p 
       LEFT JOIN t 
              ON t.cd_nom = p.cd_nom 
WHERE  t.cd_nom IS NULL

Le résultat est concluant. Nous n’avons pas à faire une recherche de taxon valide car le cd_nom est bien le même que le cd_ref

Ajouter la contrainte de clé étrangère

Pour l’instant j’ai mis « la clé étrangère » car il est impossible de la mettre sur le code du nom cité pour les raison précisé au préalable dans l’article.
J’ai donc ajouté ma contrainte sur le cd_nom uniquement.

Requête SQL :

ALTER TABLE inpn_protection_espece 
  ADD CONSTRAINT cd_nom_est_inpn_taxref_cd_nom_fk FOREIGN KEY( cd_nom) 
  REFERENCES tmp.inpn_taxref ( cd_nom) ON DELETE CASCADE ON UPDATE CASCADE;

Cette table étant de type descriptive par rapport à TAXREF, l’intérêt de conserver des informations sur une espèce n’existant pas dans la table parente n’aurait aucun sens et donc à continuer d’exister dans la table. C’est pourquoi, j’ai choisi d’ajouter la suppression et la mise à jour en cascade. Le jour où TAXREF évolue, je n’aurais pas à lancer les scripts de mise à jour et de suppression sur cette table.
Au pire je pourrais faire une table de log avec un TRIGGER pour connaitre son évolution