Cours MariaDB / Identifier les lignes avec PRIMARY KEY, AUTO_INCREMENT et UNIQUE
Dans MariaDB, PRIMARY KEY identifie chaque ligne, UNIQUE empêche la répétition d’une valeur ou d’une combinaison de valeurs, et AUTO_INCREMENT produit des identifiants numériques. Ces trois mécanismes se complètent : un numéro de client peut servir de clé primaire tandis qu’une contrainte distincte interdit deux adresses électroniques identiques. Un numéro généré ne garantit toutefois pas une suite sans trous.
Séparer l’identité d’une ligne de l’unicité métier
Deux clients peuvent porter le même nom. Une adresse électronique peut changer, et certains clients peuvent ne pas en avoir fourni. Utiliser directement l’un de ces renseignements comme identifiant rend donc les références aux clients plus difficiles à maintenir. Une colonne stable, comme customer_id, permet d’identifier la ligne indépendamment de ces informations.
| Mécanisme | Rôle | Point à retenir |
|---|---|---|
PRIMARY KEY |
Identifier une ligne sans ambiguïté. | Une seule clé primaire par table, éventuellement composée de plusieurs colonnes ; aucune de ses colonnes ne peut être NULL. |
UNIQUE |
Imposer une règle d’unicité supplémentaire. | Plusieurs contraintes sont possibles. Une colonne nullable peut contenir plusieurs NULL malgré UNIQUE. |
AUTO_INCREMENT |
Générer une valeur entière lorsque l’application ne la fournit pas. | Une seule colonne par table ; elle doit être indexée, mais n’est pas obligatoirement la clé primaire. La génération ne remplace pas une contrainte d’unicité. |
Dans l’exemple, customer_id sera la clé primaire générée et email portera une contrainte UNIQUE séparée. Le nom restera une information descriptive. La documentation des clés primaires de MariaDB explique également leur rôle dans l’organisation des tables InnoDB.
Exécuter un exemple complet avec trois tables de laboratoire
Utilisez le client MariaDB et le compte de formation, puis la base shopcourse créée pour le cours. Le compte doit pouvoir créer, remplir, consulter et supprimer les tables de cette base. Vérifiez la version du serveur et la base sélectionnée ; le premier appel à DATABASE() peut renvoyer NULL si aucune base n’a encore été choisie.
SELECT VERSION() AS server_version, DATABASE() AS selected_database;
USE shopcourse;
SELECT DATABASE() AS selected_database;
Enregistrez le script suivant dans un fichier UTF-8 nommé lesson12-keys.sql. Exécutez-le dans le client connecté avec SOURCE suivi du chemin complet du fichier et d’un point-virgule. Les tables customers_key_lab, product_codes_key_lab et auto_gap_lab doivent être absentes au départ. Si vous avez déjà fait l’exercice, utilisez la suppression ciblée de la dernière section avant de recommencer. La table products des exercices précédents n’est pas modifiée.
USE shopcourse;
CREATE TABLE customers_key_lab (
customer_id INT NOT NULL AUTO_INCREMENT,
customer_name VARCHAR(100) NOT NULL,
email VARCHAR(254) NULL,
PRIMARY KEY (customer_id),
CONSTRAINT uq_customers_key_email UNIQUE (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO customers_key_lab (customer_name, email) VALUES
('Alex Chen', 'alex@example.com'),
('Sam Patel', NULL),
('Jordan Lee', NULL);
SELECT customer_id, customer_name, email FROM customers_key_lab ORDER BY customer_id;
CREATE TABLE product_codes_key_lab (
supplier_id INT NOT NULL,
supplier_code VARCHAR(30) NOT NULL,
label VARCHAR(100) NOT NULL,
PRIMARY KEY (supplier_id, supplier_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO product_codes_key_lab VALUES
(1, 'N001', 'Notebook'),
(2, 'N001', 'Notebook from supplier two');
SELECT supplier_id, supplier_code, label FROM product_codes_key_lab ORDER BY supplier_id;
CREATE TABLE auto_gap_lab (
item_id INT PRIMARY KEY AUTO_INCREMENT,
label VARCHAR(30) NOT NULL
) ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO auto_gap_lab (label) VALUES ('Rolled-back row');
ROLLBACK;
INSERT INTO auto_gap_lab (label) VALUES ('Saved row');
SELECT item_id, label FROM auto_gap_lab;
Avec une configuration neuve où l’incrément et le décalage de génération valent 1, la première requête SELECT renvoie les lignes suivantes. Les noms et libellés anglais sont des données de test : leur traduction ne change pas les règles SQL.
| customer_id | customer_name | |
|---|---|---|
| 1 | Alex Chen | alex@example.com |
| 2 | Sam Patel | NULL |
| 3 | Jordan Lee | NULL |
L’application a omis customer_id dans INSERT : MariaDB a généré les numéros. Les deux NULL sont acceptés dans email, car NULL représente ici une adresse non renseignée, et non une adresse concrète répétée. Pour les règles de présence et les valeurs manquantes, voyez les différences entre NULL, DEFAULT, NOT NULL et CHECK. Si l’adresse doit toujours être fournie, UNIQUE seul ne suffit pas : la colonne doit également être NOT NULL, et la validité du format demande une validation adaptée.
Observer les rejets de doublons sans les masquer
Après le script complet, exécutez cette instruction seule dans le client. Elle tente d’ajouter un client avec l’adresse déjà attribuée à Alex.
INSERT INTO customers_key_lab (customer_name, email) VALUES ('Another Alex', 'alex@example.com');
MariaDB renvoie une erreur 1062 de doublon ; le message indique la clé unique uq_customers_key_email. La ligne n’est pas ajoutée. Contrôlez le résultat en relançant le SELECT des clients du script : il reste trois lignes. Le nom de la contrainte permet de distinguer ce rejet d’un problème de clé primaire.
Essayez ensuite, séparément, un identifiant déjà présent :
INSERT INTO customers_key_lab (customer_id, customer_name) VALUES (1, 'Duplicate ID');
Cette fois, l’erreur 1062 concerne PRIMARY : le numéro 1 identifie déjà une ligne. L’adresse omise aurait pu être NULL ; c’est bien la répétition de la clé primaire qui interdit l’insertion. Le tableau des clients contient toujours trois lignes. N’utilisez ni INSERT IGNORE ni REPLACE pour cet exercice : ces instructions changeraient le traitement des conflits et masqueraient le rejet que vous cherchez à observer.
La comparaison des chaînes dépend de la collation. Avec utf8mb4_unicode_ci utilisée ici, alex@example.com et ALEX@example.com sont également considérées comme identiques pour cette contrainte. UNIQUE ne définit donc pas à lui seul une politique universelle de comparaison des adresses. Choisissez la normalisation et la collation selon les règles de votre application ; les types de chaînes et leurs collations expliquent cette distinction.
Comprendre une clé primaire composée
Dans product_codes_key_lab, l’identité d’une ligne est le couple (supplier_id, supplier_code). Un fournisseur peut utiliser plusieurs codes, et deux fournisseurs peuvent employer le même code. Ce qui doit rester unique est la combinaison complète, pas chacune de ses composantes prise séparément.
| supplier_id | supplier_code | Interprétation |
|---|---|---|
| 1 | N001 | Code N001 du fournisseur 1 : présent. |
| 2 | N001 | Même code, autre fournisseur : accepté. |
| 1 | N002 | Même fournisseur, autre code : combinaison autorisée, non insérée par le script. |
| 1 | N001 | Répétition exacte de la première combinaison : refusée. |
Vérifiez le dernier cas avec cette instruction isolée :
INSERT INTO product_codes_key_lab VALUES (1, 'N001', 'Duplicate code');
Vous obtenez une erreur 1062 sur PRIMARY. Le SELECT des codes fournisseurs du script renvoie toujours les deux lignes initiales. Une clé composée est une seule définition de clé primaire comportant plusieurs colonnes ; elle ne revient pas à déclarer deux clés primaires indépendantes.
Si le code fournisseur doit pouvoir changer fréquemment ou si d’autres tables doivent référencer cette ligne, un identifiant stable distinct peut simplifier les relations. Il reste alors nécessaire de conserver UNIQUE sur le couple fournisseur/code si cette règle métier doit être garantie. Le choix de la clé dépend à la fois de sa stabilité et des règles que la base doit faire respecter.
Accepter les trous dans AUTO_INCREMENT
La dernière partie du script commence une transaction, insère une ligne puis l’annule avec ROLLBACK. InnoDB supprime les effets de cette insertion sur les données, mais ne remet pas le numéro réservé à disposition comme si l’insertion n’avait jamais eu lieu. Sur la table fraîche de l’exemple, le SELECT final montre donc item_id = 2 et label = Saved row. La ligne portant le numéro 1 n’existe pas.
Une suppression, certaines insertions en échec ou des opérations concurrentes peuvent également laisser des trous. Après les essais de doublons, ne déduisez pas le numéro de la prochaine ligne du seul contenu visible de la table. La documentation de AUTO_INCREMENT décrit ses règles et ses restrictions. Ce mécanisme convient à la création d’identifiants techniques, pas à une numérotation devant être juridiquement ou fonctionnellement sans interruption.
Pour compter les lignes, utilisez COUNT(*), et non la plus grande valeur de l’identifiant. Pour récupérer un identifiant généré, l’application peut appeler LAST_INSERT_ID() dans la même connexion, après une insertion réussie. Pour un INSERT qui génère plusieurs identifiants, cette fonction renvoie le premier identifiant généré par cette instruction, pas le dernier numéro du lot. Une connexion différente, un échec ou une annulation demandent de traiter précisément l’état de l’opération ; ne réutilisez pas aveuglément une ancienne valeur.
Évitez de calculer vous-même le prochain numéro avec MAX(id) + 1 : deux connexions pourraient lire la même valeur et tenter la même insertion. Confiez la génération au serveur et gérez séparément la réussite de l’insertion et la récupération de son résultat.
Vérifier les clés et remettre l’exercice à zéro
Ces commandes affichent les définitions réellement enregistrées et les index qui soutiennent les contraintes :
SHOW CREATE TABLE customers_key_lab;
SHOW INDEX FROM customers_key_lab;
SHOW CREATE TABLE product_codes_key_lab;
Dans SHOW CREATE TABLE, recherchez la clé primaire de customer_id, son attribut AUTO_INCREMENT et la clé unique nommée uq_customers_key_email. SHOW INDEX indique notamment Key_name, Non_unique et les colonnes indexées : la valeur 0 de Non_unique signale un index qui impose l’unicité. Pour la table des codes, SHOW CREATE TABLE doit afficher les deux colonnes dans une même définition PRIMARY KEY.
DESCRIBE est pratique pour un aperçu des colonnes, mais ses indicateurs abrégés ne remplacent pas cette lecture de la définition complète, notamment pour les clés composées. Les commandes de consultation de la structure permettent de comparer ce qui était prévu à ce qui existe réellement.
Pour refaire l’exercice avec des tables neuves, vérifiez que ces trois tables ne contiennent que vos données de laboratoire, puis supprimez-les explicitement :
DROP TABLE shopcourse.customers_key_lab, shopcourse.product_codes_key_lab, shopcourse.auto_gap_lab;
DROP TABLE supprime les définitions et les données ; cette opération n’est pas annulable par un simple ROLLBACK. Elle ne vise ici que les trois tables de l’exercice et laisse shopcourse.products intacte. Relancez ensuite le fichier complet pour retrouver les lignes et les numéros initiaux, sous les mêmes réglages de génération.




