Guide des relations en base de données

Une relation en base de données relie les lignes d’un tableau à celles d’un autre grâce à une clé étrangère : une colonne qui contient la clé primaire de la ligne liée. Il existe trois types de relations, un-à-un, un-à-plusieurs et plusieurs-à-plusieurs, et toutes reposent sur des clés étrangères. Les exemples fonctionnent avec PostgreSQL, MySQL, SQLite et SQL Server.

Comment créer des relations en base de données

Ajoute au tableau enfant une colonne pour l’id du parent, et déclare-la comme clé étrangère pour que la base refuse les valeurs qui ne correspondent à aucune vraie ligne parente :

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Fais une jointure sur la clé étrangère pour réunir les lignes liées. La contrainte bloque aussi les lignes qui ne pointeraient vers rien :

-- Input: customers
-- id | name
-- ---+-----------
-- 1  | Ana Lima
-- 2  | Ben Okafor

-- Input: orders
-- id | customer_id | total
-- ---+-------------+------
-- 10 | 1           | 25.00
-- 11 | 2           | 15.50
-- 12 | 1           | 40.00

SELECT c.name, o.id AS order_id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;

-- Output:
-- name       | order_id | total
-- -----------+----------+------
-- Ana Lima   | 10       | 25.00
-- Ben Okafor | 11       | 15.50
-- Ana Lima   | 12       | 40.00

INSERT INTO orders (id, customer_id, total) VALUES (13, 99, 10.00);
-- ERROR: insert or update on table "orders" violates foreign key constraint
-- "orders_customer_id_fkey"

DELETE FROM customers WHERE id = 1;
-- ERROR: update or delete on table "customers" violates foreign key constraint
-- "orders_customer_id_fkey" on table "orders"

Les règles à connaître

• SQLite ignore les clés étrangères tant que tu ne les actives pas. Exécute PRAGMA foreign_keys = ON; à chaque connexion, sinon le mauvais INSERT ci-dessus passe.
• Écris la clé étrangère sur sa propre ligne FOREIGN KEY (...) REFERENCES ..., comme ci-dessus. MySQL 8.0 ignore sans prévenir un REFERENCES écrit directement sur la colonne (customer_id INTEGER REFERENCES customers(id)), et les mauvaises lignes passent directement.
• Décide de ce qui se passe quand on supprime un parent. Par défaut, la base refuse, comme ci-dessus. Ajoute ON DELETE CASCADE à la clé étrangère pour supprimer aussi les lignes enfants, ou ON DELETE SET NULL pour les garder sans parent.
• Indexe la colonne de clé étrangère. Tu vas faire des jointures et des filtres dessus en permanence, et seul MySQL l’indexe pour toi : CREATE INDEX idx_orders_customer_id ON orders (customer_id);
• Donne-lui le même type que la clé qu’elle référence, par exemple INTEGER pour un id INTEGER.
• Ne stocke jamais une liste d’ids dans une seule colonne, comme '1,3,5'. La base ne peut ni la vérifier ni faire de jointure dessus. C’est une relation plusieurs-à-plusieurs, et il lui faut une table de jonction (voir plus bas).

Syntaxe alternative

Nomme la contrainte pour que ses erreurs soient plus lisibles et qu’elle soit plus facile à supprimer plus tard :

CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id)

Pour ajouter une clé étrangère à un tableau existant, utilise ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id);. SQLite ne sait pas le faire : crée un nouveau tableau avec la clé étrangère, copie les lignes et échange les tableaux.

Pour des ids générés par la base elle-même, utilise GENERATED ALWAYS AS IDENTITY (ou SERIAL) avec PostgreSQL, AUTO_INCREMENT avec MySQL, IDENTITY(1,1) avec SQL Server et INTEGER PRIMARY KEY avec SQLite.

Quand utiliser des relations en base de données ?

Utilise une relation dès que deux types d’éléments sont liés. Le type de relation dépend du nombre de lignes possibles de chaque côté.

Diviser un tableau en deux : un-à-un

Chaque ligne correspond au plus à une ligne de l’autre tableau. Ajoute une clé étrangère UNIQUE au second tableau. Sers-t’en pour sortir des colonnes facultatives ou peu utilisées d’un tableau trop large, ou pour placer des données sensibles (utilisateurs et leurs profils, employés et leurs fiches de salaire) sous des permissions distinctes.

Donner plusieurs enfants à une ligne : un-à-plusieurs

La relation la plus courante : un client a plusieurs commandes, un auteur plusieurs articles. Place une simple clé étrangère du côté « plusieurs ». Le parent peut même être le même tableau, comme quand employees.manager_id pointe vers un autre employé.

Lier plusieurs lignes à plusieurs autres : plusieurs-à-plusieurs

Les étudiants suivent plusieurs cours et les cours ont plusieurs étudiants, donc aucun des deux tableaux ne peut contenir l’id de l’autre. Ajoute une table de jonction avec une clé étrangère vers chaque côté et une clé primaire sur la paire, pour que le même lien ne puisse pas être enregistré deux fois. Les informations propres au lien, comme une note, vont dans la table de jonction.

Trouver les lignes sans correspondance

Une relation montre aussi ce qui manque. Un LEFT JOIN sur la clé étrangère garde toutes les lignes du premier tableau et met des NULL là où rien ne correspond : en filtrant sur NULL, tu trouves les clients sans commande ou les cours que personne n’a suivis.

Exemples concrets de relations en base de données

Les utilisateurs et leurs profils

profiles.user_id est UNIQUE, donc un utilisateur ne peut pas avoir de second profil. Le LEFT JOIN garde les utilisateurs qui n’en ont pas encore rempli :

-- profiles.user_id is declared as: user_id INTEGER UNIQUE NOT NULL

-- Input: users
-- id | username
-- ---+---------
-- 1  | mia
-- 2  | raj
-- 3  | zoe

-- Input: profiles
-- id | user_id | bio
-- ---+---------+---------------------
-- 1  | 1       | Software developer
-- 2  | 2       | Marketing specialist

SELECT u.username, p.bio
FROM users u
LEFT JOIN profiles p ON p.user_id = u.id
ORDER BY u.id;

-- Output:
-- username | bio
-- ---------+---------------------
-- mia      | Software developer
-- raj      | Marketing specialist
-- zoe      | NULL

INSERT INTO profiles (id, user_id, bio) VALUES (3, 1, 'Second profile');
-- ERROR: duplicate key value violates unique constraint "profiles_user_id_key"

Les employés et leurs managers

Une relation un-à-plusieurs au sein d’un même tableau : manager_id est une clé étrangère vers employees.id. Fais une jointure du tableau sur lui-même pour afficher chaque employé à côté de son manager :

-- Input: employees
-- id | name      | manager_id
-- ---+-----------+-----------
-- 1  | Maya Chen | NULL
-- 2  | Sam Ortiz | 1
-- 3  | Jo Kim    | 2
-- 4  | Lee Ross  | 2

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id;

-- Output:
-- employee  | manager
-- ----------+----------
-- Maya Chen | NULL
-- Sam Ortiz | Maya Chen
-- Jo Kim    | Sam Ortiz
-- Lee Ross  | Sam Ortiz

Les étudiants et leurs cours

La table de jonction enrollments a une clé étrangère vers chaque côté et PRIMARY KEY (student_id, course_id). Passe par elle dans ta jointure pour savoir qui suit quoi :

-- Input: students
-- id | name
-- ---+------
-- 1  | Priya
-- 2  | Tomas
-- 3  | Leah

-- Input: courses
-- id | title
-- ---+----------------
-- 1  | Database Design
-- 2  | Web Development
-- 3  | Statistics

-- Input: enrollments
-- student_id | course_id | grade
-- -----------+-----------+------
-- 1          | 1         | A
-- 1          | 2         | B+
-- 2          | 1         | A-
-- 3          | 2         | A

SELECT s.name AS student, c.title AS course, e.grade
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
ORDER BY s.name, c.title;

-- Output:
-- student | course          | grade
-- --------+-----------------+------
-- Leah    | Web Development | A
-- Priya   | Database Design | A
-- Priya   | Web Development | B+
-- Tomas   | Database Design | A-

INSERT INTO enrollments VALUES (1, 1, 'B');
-- ERROR: duplicate key value violates unique constraint "enrollments_pkey"

Les cours sans aucun inscrit

NOT EXISTS est une autre façon de trouver les lignes sans correspondance. Il garde chaque cours qui n’a aucune ligne dans la table de jonction :

-- Input: the courses and enrollments tables above

SELECT c.title
FROM courses c
WHERE NOT EXISTS (
  SELECT 1 FROM enrollments e WHERE e.course_id = c.id
);

-- Output:
-- title
-- ----------
-- Statistics

Pour aller plus loin sur les relations en base de données

Sur ce blog :

• Guide des relations un-à-un : le un-à-un en détail
• Guide des relations un-à-plusieurs : jointures, comptage et agrégation des lignes enfants
• Guide des relations plusieurs-à-plusieurs : les tables de jonction en détail
• Guide illustré des JOIN SQL : tous les types de jointure utiles sur une relation
• Utiliser les clés étrangères dans PostgreSQL : les options de clé étrangère dans PostgreSQL
• ON DELETE CASCADE dans PostgreSQL : supprimer les lignes enfants avec leur parent
• Clés primaires composites en SQL : la clé sur deux colonnes d’une table de jonction
• Guide SQL CREATE TABLE : clés et contraintes à la création d’un tableau

Documentation officielle :

• PostgreSQL : clés étrangères
• MySQL : contraintes FOREIGN KEY
• SQLite : prise en charge des clés étrangères
• SQL Server : contraintes de clé primaire et étrangère