S'il y a bien quelque chose que peu de monde connait en SQL c'est bien les clés composites. Moi même je ne savais pas ce que c'était avant d'en avoir besoin. Et pourtant c'est très utile et je vous conseille de vous y intéresser ! En voici une petite explication. Une clé composite est une clé composée de plusieurs champs. (Tout simplement)
Une clé primaire composite est une clé primaire composée de plusieurs champs. (Une clé primaire n'a jamais été cantonnée à un seul champ, tout comme les clés uniques et les index)
Pour que ça soit plus parlant prenons un exemple :
On souhaite stocker des documents disponibles en plusieurs langues. Simplement on pourrait faire :
CREATE TABLE documents (
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
lang CHAR(2) NOT NULL,
title VARCHAR(255) NOT NULL,
author VARCHAR(255) NOT NULL
)
Ainsi, pour insérer des documents :
INSERT INTO documents (lang, title, author)
VALUES ('fr', 'Rapport', 'Nicolas Le Gall');
INSERT INTO documents (lang, title, author)
VALUES ('en', 'Report', 'Jens Meiert');
L'inconvénient c'est que l'on obtient 2 id différents, et qu'il est donc quasiment impossible d'identifier un lien entre ces deux documents. Pourtant il s'agit du même document, mis à part quils ne sont pas dans la même langue.
Une solution est de gérer l'id manuellement (en enlevant l'option AUTO_INCREMENT), mais il est impossible d'avoir 2 id identiques (puisqu'il s'agit d'une clé primaire, ce champ est par définition unique).
Passer le champ en unique ne changera rien, et le passer en index résultera à une perte de cardinalité (ce qui n'est pas bon, ou très déconseillé, pour un id).
Créer une table pour chaque langue n'est pas non plus la bonne solution, ça surchargera le nombre de table, rajoutera des jointures et ce n'est pas très maintenable (imaginez que vous trouvez un contributeur japonais, vous n'avez plus qu'à créer une nouvelle table...)
La solution réside dans une clé composite (vous ne vous y attendiez pas, hein ;-) ?).
Si nous créons notre table comme cela :
CREATE TABLE documents (
id TINYINT UNSIGNED NOT NULL,
lang VARCHAR(2) NOT NULL,
title VARCHAR(255) NOT NULL,
author VARCHAR(255) NOT NULL,
PRIMARY KEY (id, lang)
)
Cette fois pour insérer les données on doit préciser l'id :
INSERT INTO documents (id, lang, title, author)
VALUES ('1', 'fr', 'Rapport', 'Nicolas Le Gall');
INSERT INTO documents (id, lang, title, author)
VALUES ('1', 'en', 'Report', 'Jens Meiert');
INSERT INTO documents (id, lang, title, author)
VALUES ('1', 'de', 'Bericht', 'Jens Meiert');
INSERT INTO documents (id, lang, title, author)
VALUES ('2', 'fr', 'Tutoriel', 'Eric Daspet');
INSERT INTO documents (id, lang, title, author)
VALUES ('2', 'en', 'Tutorial', 'Christian Heilman');
INSERT INTO documents (id, lang, title, author)
VALUES ('3', 'fr', 'La pierre et le sabre', 'Karl Dubost');
INSERT INTO documents (id, lang, title, author)
VALUES ('3', 'jp', '宮本武蔵', 'Yoshikawa Eiji');
(oui, je me suis lâché)
Maintenant on peut parfaitement sélectionner un document :
SELECT * FROM documents WHERE id = 1
Et on obtient le document et toutes ses traductions. En examinant d'un peu plus prêt (avec un EXPLAIN) on peut voir que le SGBD (MySQL dans mon cas) utilise l'index de la table, donc ne la parcourt pas (ainsi sur une très grande table vous avez de très très bonne performances).
Vous me direz que ça ne change pas d'avant, mis à part le fait que l'on peut avoir des id identiques ? Et bien essayons d'insérer une langue qui existe pour le document 1 :
INSERT INTO documents (id, lang, title, author) VALUES ('1', 'de', 'Bericht', 'Jens Meiert');
Le SGBD va vous spécifier que la clé "1-de" existe déjà. Nous venons donc de modifier le comportement de notre clé primaire (qui était 'id' auparavant, maintenant la clé est le couple 'id-lang') pour y ajouter une contrainte supplémentaire.
Il y a une étrangeté tout de même; si vous sélectionnez une langue :
SELECT * FROM documents WHERE lang = 'fr'
Vous obtenez bien vos documents, mais EXPLAIN nous indique que le SGBD n'utilise pas l'index. Si vous savez pourquoi merci de m'éclairer ;-) .
Si vous avez une table vraiment conséquente vous pouvez quand même rajouter le champ lang en index :
ALTER TABLE documents ADD INDEX (lang)
Si vous sélectionnez l'id ET la langue vous n'aurez pas ce "problème".
Astuce :
Voici deux requêtes vous permettant de trouver le prochain id disponible (puisqu'on ne peut plus utiliser d'AUTO_INCREMENT sur notre clé primaire) :
SELECT id AS last FROM documents ORDER BY id DESC LIMIT 1
Celle ci vous permet de récupérer le dernier id attribué.
SELECT MAX(id)+1 AS next FROM documents
Et cette dernière vous donne directement l'id à utiliser (mais a l'inconvénient de ne pas utiliser l'index).
]]>
