PostgreSQL : bien démarrer

Ce que vous saurez faire

Modéliser une relation entre auteurs et cours, lire une jointure et choisir une frontière de transaction. Utilisez une base d’exercice vide, jamais votre base de production.

1. Modéliser ce qui doit rester vrai

Un cours a un auteur ; un auteur peut écrire plusieurs cours. Conserver les informations de l’auteur dans sa propre table évite de recopier son nom dans chaque cours. Une clé étrangère empêche de référencer un auteur inexistant.

CREATE TABLE authors (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);
CREATE TABLE courses (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  author_id integer NOT NULL REFERENCES authors(id)
);
INSERT INTO authors (name) VALUES ('Camille');
INSERT INTO courses (title, author_id)
SELECT 'Comprendre les jointures', id FROM authors;

SELECT c.title, a.name
FROM courses AS c
JOIN authors AS a ON a.id = c.author_id;

Dans cette base vide, la requête renvoie le cours avec Camille comme auteur. Les alias c et a rendent l’origine des colonnes explicite. Une jointure relie des lignes selon une condition ; ce n’est pas seulement un collage de tables.

Un LEFT JOIN partant des auteurs conserve aussi les auteurs sans cours : les colonnes du cours valent alors NULL. Placez soigneusement les filtres : exiger ensuite une valeur non nulle côté cours dans WHERE peut éliminer ces lignes.

Référence : jointures PostgreSQL.

2. Une transaction protège une intention complète

Supposons qu’une publication exige deux écritures. Si seule la première réussit, la base peut contenir un état incomplet. Une transaction permet de valider l’ensemble ou de l’abandonner.

BEGIN;
UPDATE courses SET title = 'Les jointures, pas à pas'
WHERE title = 'Comprendre les jointures';
SELECT title FROM courses;
ROLLBACK;
SELECT title FROM courses;

Le premier SELECT voit le nouveau titre dans la transaction. Après ROLLBACK, le titre initial revient. COMMIT aurait conservé le changement. Les autres sessions ne voient pas ces modifications non validées.

Gardez les transactions courtes : ne les laissez pas attendre pendant qu’un utilisateur réfléchit ou qu’un service réseau répond. Une transaction SQL n’annule pas un e-mail déjà envoyé.

Référence : transactions PostgreSQL.

À vous de jouer

Ajoutez un second auteur sans cours. Quelle jointure permet de l’afficher dans la liste des auteurs et de leurs cours ?

Correction commentée

Partez de authors et utilisez LEFT JOIN courses ON courses.author_id = authors.id. L’auteur sans cours apparaît avec un titre NULL. Un JOIN ordinaire ne conserverait que les auteurs possédant une correspondance.