De l'invariance des clés primaires
Par fsmrel

Le , par fsmrel, Expert éminent sénior
_____________________

Rappel :

D’un point de vue purement pragmatique et informel, une clé est un moyen permettant à l’utilisateur d’accéder à l’information contenue dans une ligne spécifique d’une table donnée R.

Considérée sous un autre angle et de façon plus formelle : une clé candidate (ou plus simplement clé, quand il n’y a pas de confusion possible) est un sous-ensemble de colonnes K de l’en-tête d’une table R, respectant les deux contraintes suivantes :

Unicité. Deux lignes distinctes de R ne peuvent avoir même valeur de K.

Irréductibilité (ou minimalité). Il n’existe pas de sous-ensemble strict de K garantissant la règle d’unicité.

N.B. L’en-tête de la table R est lui-même un ensemble, l’ensemble des colonnes de la forme <A, T> où A est un nom de colonne et T le nom du type de la colonne A). A noter qu’en SQL l’en-tête d’une table n’est pas nécessairement un ensemble (noms de colonnes en double ou pouvant être omis ).

A quoi sert une clé ? Indépendamment de ce qui vient d’être dit quant à l’aspect pragmatique des choses, et pour aller dans le sens formel, une clé sert à garantir que le corps de la table (l’ensemble de ses lignes) soit un ensemble, donc que ce corps ne comporte pas de doublons, sinon ça ne serait pas un ensemble, mais un sac (tuple bag). Pourquoi ce corps doit-il être un ensemble ? Parce qu’on manipule les tables au moyen de l’algèbre relationnelle, laquelle est conforme à la théorie des ensembles et à la logique du 1er ordre.

Qu’est-ce qu’une clé primaire ?

Plusieurs sous-ensembles de colonnes de la table R peuvent respecter les deux contraintes d’unicité et d’irréductibilité et donc faire l’objet de clés candidates. Or, en SQL, il est exigé qu’une clé soit « plus » candidate que les autres, et on l’on pourrait l’appeler Miss clé primaire. Les autres clés seront ses dauphines et qualifiées du doux nom de clés alternatives.

Quelles sont les vertus dont Miss clé primaire doit être parée ?

Elle doit être invariante. Pourquoi ? Parlons de l’intégrité des données. On sait que les clés primaires peuvent servir de référence pour des clés étrangères. Que se passe-t-il concrètement si une clé primaire est référencée par des clés étrangères présentes dans 20 tables (R pouvant elle-même faire l’objet d’une auto-référence) et si on change la valeur de cette clé primaire ? Des mises à jour en masse, des phénomènes de verrouillage, des écritures dans les logs à n’en plus finir, bref on patine pour rien, voyez ci-dessous l’histoire vécue.

En corollaire, une clé primaire ne doit pas être significative, porteuse de sens, bref, l’utilisateur ne doit avoir aucun pouvoir sur elle, et en poussant la logique jusqu’au bout, il ne doit même pas y avoir accès, ni même en connaître l’existence. Je cite Yves Tabourier qui écrit à la page 80 de son remarquable ouvrage (De l’autre côté de MERISE, Les Éditions d’organisation, 1986), ce qui constitue une règle d’or valant pour les identifiants des entités-types florissant dans les MCD merisiens (règle d’or trop souvent méconnue, hélas ! Et comme disent Goethe et Cie, « ceux qui ont oublié le passé sont condamnés à le revivre... ») :

« ... la fonction d’une propriété est de décrire les objets (et les rencontres), alors que l’identifiant ne décrit rien. Son rôle fondamental est d’être sûr de distinguer deux jumeaux parfaits, malgré des descriptions identiques.
L’expérience montre d’ailleurs que l’usage des “identifiants significatifs” (ou “codes significatifs”) a pu provoquer des dégâts tellement coûteux que la sagesse est d’éviter avec le plus grand soin de construire des identifiants décrivant les objets ou, pis encore, leurs liens avec d’autres objets... »

Parce qu’il est au niveau conceptuel Merise, Tabourier parle d’identifiants, et ceux-ci ne doivent pas être porteurs d’information mais il suffit ensuite, au niveau SQL, de transcrire identifiant par clé, la règle reste valable, sinon plus ! En passant, on notera qu’une clé primaire n’accepte pas d’être polluée par le bonhomme Null, pour des raisons évidentes.

Histoire vécue

Les concepteurs d’un projet particulièrement sensible d’une grande banque avaient retenu le numéro Siren des entreprises pour identifier celles-ci (attribut NoSiren de l’entité-type ENTREPRISE dans le MCD). Au niveau SQL, par le jeu des liens inter-tables (clé primaire - clé étrangère), le numéro Siren se propageait dans de nombreuses tables. Or, ce numéro est fourni par l’INSEE, lequel envoyait tous les mois les correctifs modifiant le Siren des entreprises venant de naître (10% d’entre elles à peu près). Les concepteurs en avaient tenu compte et me montrèrent le modèle correspondant à la mise à jour des tables impliquées : une usine à gaz ! J’avais fait observer que, vu le nombre de tables touchées et leur volumétrie (plusieurs millions de lignes chacune), cela pouvait faire exploser la production informatique (batchs de nuit), du fait d’une activité de mise à jour excessive et en plus, délicate à ordonnancer. Après leur avoir parlé de la règle d’or de Tabourier, sans que j’ai eu à le leur demander, les concepteurs définirent dans le MCD un nouvel attribut, non porteur d’information, artificiel et invariant, destinée à devenir au stade SQL la colonne composant la clé primaire de la table Entreprise, propagé en conséquence dans les autres tables, en lieu et place de la colonne NoSiren. A partir de là, modifier un numéro de Siren n’impactait plus que la seule table ENTREPRISE, les utilisateurs ayant bien évidemment toujours accès au contenu de la colonne NoSiren (et à elle seule du reste), devenue clé alternative (et n’ayant donc pas perdu ses propriétés d’unicité et d’irréductibilité).

_____________________________________________


Vous avez aimé cette actualité ? Alors partagez-la avec vos amis en cliquant sur les boutons ci-dessous :


 Poster un commentaire

Avatar de fsmrel fsmrel - Expert éminent sénior https://www.developpez.com
le 28/06/2015 à 17:40
Citation Envoyé par ALWEBER Voir le message
Le SIRET est à la base un clé primaire fournie par l'organisme générateur de ce numéro.

Que l’INSEE définisse le numéro SIRET comme étant un identifiant d'établissement, d’accord, mais cet organisme n’est évidemment pas habilité à donner la définition de la clé primaire, concept relevant de la théorie relationnelle (qui est une branche des mathématiques appliquées). C’est Ted Codd qui a défini pour la première fois ce concept en 1970, dans A Relational Model of Data for Large Shared Data Banks, avant d'en faire évoluer la définition.

Pour l’utilisateur de la base de données, le SIRET est un point d’entrée dans le système pour accéder aux données d’un établissement, avec la garantie que deux établissements n’auront pas le même SIRET.

Que le SIRET fasse l’objet d’une clé alternative, rien de plus légitime, et les administrateurs des données de la banque dont j’ai fait mention (sans la nommer, mais il ne s'agit pas de la moindre !) l’ont bien compris, en mettant à la poubelle un paquet de routines et de tables devenues inessentielles, après avoir déchu le SIREN de son rang initial de clé primaire, concept stratégique dans le contexte de l’intégrité référentielle, et l'avoir ravalé au rang de dauphine, de clé alternative.

Le système d’information doit avoir la maîtrise complète de l’affectation des clés primaires, lesquelles n’ont à dépendre d’un système externe, aussi respectable soit-il, ni des lubies de l’utilisateur.

Citation Envoyé par ALWEBER Voir le message
Si tu veux travailler correctement tu dois gérer des ensembles de SIRET et gérer la manière dont ils sont assemblés.


En l’occurrence on se situe plutôt au niveau du COMMENT, mais la base de données n’est concernée que par ce qui est essentiel, à savoir le QUOI : le COMMENT (la manière d’assembler) aura toujours sa solution, quitte à développer des contraintes ad-hoc, directement attachées à la base de données quand c'est possible (cf. l’instruction CREATE ASSERTION de la norme SQL ou l'instruction CREATE TRIGGER).
Avatar de fsmrel fsmrel - Expert éminent sénior https://www.developpez.com
le 02/03/2016 à 3:32
Merci Zizoua !
Contacter le responsable de la rubrique SGBD & SQL