Identifiants

Même avec un schéma bien normalisé, il peut y avoir des scenarii dans lesquels il est impossible de choisir la clé primaire qui est un super-key.

Nous avons déjà vu l'exemple :

etudiant_id	cours_id
1	SGBDR
1	Backend
2	Angular
3	Backend
3	Angular

cours_id	prix
SGBDR	1000
Backend	1500
Angular	2000

Dans l’exemple, les cours sont identifiés par leur nom en français, mais si on devait utiliser un système localisé (traduit), le nom du cours n’est plus une super-key.

Un autre exemple, dans le cas d’un étudiant, il n’y a pas de set d’attributs qui identifie chaque étudiant, puisqu’il y a toujours la possibilité que 2 étudiants partagent les mêmes attributs (nom de famille, prénom, date de naissance, etc).

Nous désignons alors un identifiant. Mais lequel ?

Une chose est certaine : le ID doit être une super-key et donc avoir des mêmes caractéristiques :

• être unique sur toutes les lignes de la table

• identifier une seule ligne de la table

ID type auto-increment

Une option fournie par beaucoup de SGBDR est l'identifiant de type entier qui s'incrémente automatiquement avec chaque insert into. Le SGBDR prend note de la progression de ce compteur, assurant alors son unicité.

On utilise le mot auto_increment :

create table etudiant (
  id int not null auto_increment,
  nom varchar(255),
  prenom varchar(255),
  primary key (id)
);

Ce type de clé a plusieurs avantages :

• Facile à lire

• Un étudiant n’a qu’à retenir son numéro pour accéder aux services

• Optimal pour la construction des indexes

En revanche, il y a aussi plusieurs désavantages :

• Facile à espionner : Si on connait le numéro d’un étudiant, par exemple, 55, on saurait immédiatement qu’il y a un étudiant 54 et 56

• On peut déterminer la quantité (minimale) d’entités dans la table juste par son ID. Ex. nombre de commandes passés

• Risque d’erreurs en insertion en parallèle. Imaginons que 2 processus essaie d’insérer une ligne en même temps. Comment la valeur s’incrémente ?

• Difficile à gérer les doubles dans les systèmes distribués : s'il a plusieurs data-centers, chacun gère son identifiant, le risque de doublon est élevé !

Exercice

Insérer quelques lignes à la table dessus.

• Qu'est-ce que vous observez ?

• Supprimer la dernière ligne, et en ajoutez une nouvelle. Qu'est-ce que vous observez ?

ID type UUID

Un UUID (Universal Unique Identifier) est un numéro aléatoire généré (par un algorithme) selon un set de variables qui sont plus ou moins garanties d’être uniques :

• La date, heure, secondes, millisecondes

• L’adresse mac de l’ordinateur

• La température de l’ordinateur au moment de la génération

• ... et un ensemble de facteurs similaires

L'objectif ultime est de pouvoir, à tout moment, générer une valeur qui est garantie (ou au moins aura une très très haute probabilité) d'être unique au monde. Bien évidemment, ceci est une tâche presque impossible. Mais, ce qui suffit pour notre plateforme, est que la valeur soit unique, juste dans le domaine de notre table, qui est beaucoup plus faisable !

Dans notre SGBDR, on peut récupérer une valeur type UUID avec une fonction :

select uuid();

On peut appeler cette fonction comme default pour notre identifiant :

create table customer (
  id varchar(36) not null default uuid(),
  name varchar(256),
  primary key (id)
);

Les UUIDS ont des avantages et désavantages aussi.

Avantages :

• unicité et (presque) garanti

• difficile à espionner

• facilite des SGBDR distribués

Désavantages :

• difficile à lire (donc pas approprié pour donner aux utilisateurs, comme numéro d'étudiant par exemple)

• plus de place nécessaire pour le stockage

• plus lent pour les recherches

Triggers

Un déclencheur est une sorte de fonction qui est appelée systématiquement avant ou après une action (insert, update, delete) sur une ligne.

Ils peuvent être donc très utiles pour faire un prétraitement de nos données, ou bien des post-traitements, pour, par exemple, déclencher des mises à jour secondaires.

Par exemple, dans le cas de génération de UUIDon pourra intercepter la requête insert, et en modifiant les valeurs :

create trigger before_insert_customer
before insert on customer
for each row
set new.id = uuid();

Ici, on précise before insert on customer : avant d'insérer une nouvelle ligne à la table customer. On procède à générer un uuid pour la nouvelle ligne.

C'est ainsi qu'on assure de toujours générer en interne nos UUID.

Je vous laisse comme exercice une exploration plus profonde : https://www.mariadbtutorial.com/mariadb-triggers/mariadb-create-trigger/