Partie 2 · SQL de base

Clés, contraintes et index

Le carnet d'adresses du cours (table Personne) a besoin d'un identifiant numérique unique par enregistrement. On crée une colonne entière non signée, assez grande pour tous les enregistrements (un décompte : il commence à 1) ; SMALLINT UNSIGNED va de 0 à 65 535, ce qui suffit pour quelques centaines de personnes. Elle doit être :

NOT NULL : jamais vide ;
UNIQUE : pas de doublon ;
AUTO_INCREMENT : incrémentée automatiquement.

PRIMARY KEY regroupe NOT NULL et UNIQUE. Cet identifiant s'appelle la clé primaire, numérotée à partir de 1 et non de 0.

CREATE TABLE Personne (
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(40), prénom VARCHAR(40), adresse TINYTEXT, téléphone DECIMAL(10,0)
)

À toi de coder

Clé primaire auto-incrémentée

On veut un identifiant numérique unique, jamais vide, qui s'incrémente tout seul à chaque insertion : c'est la clé primaire (PRIMARY KEY regroupe NOT NULL et UNIQUE, et AUTO_INCREMENT fournit la numérotation, qui commence à 1).

Complète le CREATE TABLE. (Hors cours : en SQLite l'option s'écrit AUTOINCREMENT, sans tiret bas, placée après PRIMARY KEY.)

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("""CREATE TABLE Personne (
    id INTEGER PRIMARY KEY ___,
    nom VARCHAR(40),
    prenom VARCHAR(40)
)""")
con.execute("INSERT INTO Personne(nom, prenom) VALUES ('Martin', 'Marc')")
con.execute("INSERT INTO Personne(nom, prenom) VALUES ('Dupond', 'Anne')")
ids = [r[0] for r in con.execute("SELECT id FROM Personne ORDER BY id")]
suivi = con.execute("SELECT name FROM sqlite_sequence").fetchall()

Ctrl/⌘+Entrée

Clé primaire sur plusieurs colonnes

Pour interdire les doublons sur le couple (nom, prénom) tout en les rendant obligatoires, on crée une clé primaire sur ce couple. Mauvaise syntaxe : mettre PRIMARY KEY sur chacune des deux colonnes. Bonne syntaxe : une déclaration à part, PRIMARY KEY (nom, prénom).

Vérifie que tu as compris

Comment déclarer une clé primaire sur le couple (nom, prénom) ?

NOT NULL, DEFAULT, UNIQUE

Option	Effet
`NOT NULL`	la colonne doit avoir une valeur. Si rien n'est fourni, la valeur par défaut (si déclarée) est utilisée ; sinon MySQL met `''` pour une chaîne, `0` pour un nombre, `0000-00-00` / `00:00:00` pour une date/heure. `NULL` autorise l'absence de valeur.
`DEFAULT valeur`	valeur utilisée si aucune n'est donnée à l'insertion. Impossible pour les types TEXT et BLOB.
`UNIQUE(colonnes)`	interdit les doublons : `UNIQUE(nom)`, ou `UNIQUE(nom)` + `UNIQUE(prénom)` (deux colonnes indépendantes), ou `UNIQUE(nom, prénom)` (interdit le couple en double)

Vérifie que tu as compris

Avec UNIQUE(nom) et UNIQUE(prénom) séparés, que se passe-t-il pour ('Martin','Marc') puis ('Dupond','Marc') ?

Les index

Sans index, MySQL parcourt la table dans n'importe quel ordre : sur de nombreuses lignes, la recherche est très longue. Un index est une liste ordonnée des valeurs d'un ou plusieurs attributs avec l'adresse de la ligne associée : on cherche et on trie sur un ensemble déjà ordonné, bien plus vite. En contrepartie, les mises à jour (insertion, suppression, modification de clé) sont ralenties.

À indexer : les colonnes les plus sollicitées par les recherches ou utilisées comme critère de jointure.
À épargner : celles qui ont peu de valeurs différentes, et celles dont les valeurs changent très souvent.

Syntaxe : INDEX idx_nom_prenom (nom, prénom) ; on peut n'indexer que les M premiers caractères d'une colonne texte : INDEX idx_nom (nom(3)).

Vérifie que tu as compris

Sur quelle colonne un index est-il le moins rentable ?