Création de foreign keys lors de la création des tables

Bienvenue dans l'une des leçons clés de notre cours ! Aujourd'hui, on va parler de comment créer des foreign keys dans PostgreSQL. Ce sujet est super important dans la conception de bases de données, parce que ce sont justement les foreign keys qui permettent de faire des liens entre les tables. Si tu sens que tu vas bientôt te perdre dans ta future "ville SQL", imagine les foreign keys comme des ponts qui relient différents quartiers.

Pour faire simple, une foreign key, c'est une colonne (ou un groupe de colonnes) dans une table qui fait référence à une colonne (généralement la primary key) d'une autre table.

Par exemple, si tu as deux tables — students (étudiants) et courses (cours), alors une foreign key dans la table courses peut "indiquer" à quel étudiant le cours est attribué. Comme ça, tu crées une relation entre ces tables.

Pourquoi c'est important ?

1. La foreign key aide à garantir l'intégrité des données : tu ne peux pas écrire quelque chose dans une table si ça n'existe pas dans l'autre.
2. Elles simplifient la gestion des données. Par exemple, quand tu supprimes une ligne dans une table, tu peux configurer la suppression automatique des lignes liées dans l'autre table.

Syntaxe pour créer une foreign key

Créer une foreign key dans PostgreSQL, c'est facile — il suffit d'un peu de magie SQL. Voilà la syntaxe de base :

CREATE TABLE table_dependante (
    colonne_foreign_id DATA_TYPE REFERENCES table_parente(colonne_id)
);

Plongeons un peu plus dans les détails avec quelques exemples.

Exemple 1 : Tables students et courses

Imagine qu'on veut créer un lien entre les étudiants et les cours. Chaque cours doit être lié à un étudiant. Pour ça, il suffit de lancer la requête suivante :

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    student_id INT REFERENCES students(student_id)
);

Ici :

• Dans la table students, on a créé une primary key PRIMARY KEY pour identifier chaque étudiant.
• Dans la table courses, la colonne student_id est une foreign key FOREIGN KEY qui fait référence à la colonne student_id de la table students.

Table students

Table courses

Remarque importante

Quand tu ajoutes une foreign key, PostgreSQL crée automatiquement une règle qui vérifie que les valeurs de la foreign key correspondent à des valeurs existantes dans la table indiquée. Si tu essaies d'insérer une valeur incorrecte, la base de données va te sortir une erreur.

Utilisation pratique : modèle students, courses et enrollments

Regardons un exemple un peu plus costaud avec une relation "many-to-many". Les mêmes étudiants peuvent s'inscrire à plusieurs cours, et un cours peut avoir plein d'étudiants. Pour faire ce lien, il nous faut une table intermédiaire.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id), -- foreign key
    course_id INT REFERENCES courses(course_id)		-- foreign key
);

Ici, la table enrollments fait le lien entre les tables students et courses en utilisant les foreign keys student_id et course_id.

Insertion de données

-- On ajoute des étudiants
INSERT INTO students (name) VALUES ('Ivan Ivanov'), ('Maria Smirnova');

-- On ajoute des cours
INSERT INTO courses (title) VALUES ('Mathematiques'), ('Physique');

-- On inscrit les étudiants aux cours
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1), (1, 2), (2, 1);

Sélection de données

Maintenant, on peut facilement faire des requêtes pour savoir à quels cours un étudiant participe ou quels étudiants sont inscrits à un cours précis :

-- Les cours auxquels Ivan Ivanov est inscrit
SELECT c.title
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
WHERE e.student_id = 1;

-- Les étudiants inscrits au cours "Mathematiques"
SELECT s.name
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
WHERE e.course_id = 1;

Fonctionnalités avancées : ON DELETE et ON UPDATE

La foreign key doit aussi gérer le comportement de la table quand on modifie ou supprime des lignes dans la table parente. Pour ça, on utilise les modificateurs ON DELETE et ON UPDATE. Voici les options principales :

• CASCADE : les modifications ou suppressions dans la table parente sont automatiquement appliquées aux lignes enfants.
• SET NULL : les valeurs de la foreign key dans la table enfant sont mises à NULL.
• RESTRICT : interdit la suppression ou la modification des données si elles sont déjà utilisées dans la table enfant dépendante.
• NO ACTION : en gros, c'est comme RESTRICT, mais la vérification se fait plus tard.

Exemple 2 : Utilisation de ON DELETE CASCADE

Imagine qu'on veut que la suppression d'un étudiant dans la table students supprime automatiquement tous ses cours dans la table courses. Voilà comment faire :

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE
);

Maintenant, si tu supprimes un étudiant de la table students, toutes les lignes liées à cet étudiant dans la table courses seront aussi supprimées. Par exemple :

INSERT INTO students (name) VALUES ('Ivan Ivanov');
INSERT INTO courses (title, student_id) VALUES ('Mathematiques', 1), ('Physique', 1);

-- Suppression de l'étudiant Ivanov
DELETE FROM students WHERE student_id = 1;

-- La table courses sera maintenant vide, car tous les cours liés à Ivanov sont supprimés

On va revoir ce sujet encore plus en détail dans la prochaine leçon.

Processus de validation des données avec les foreign keys

Quand tu crées une foreign key, PostgreSQL joue le rôle du gardien strict et vérifie chaque nouvelle ligne. Par exemple :

• Si tu essaies d'insérer une ligne avec une valeur de foreign key qui n'existe pas, tu auras une erreur.
• Si tu supprimes une ligne dans la table parente qui est liée à d'autres tables (sans ON DELETE CASCADE), ça va casser l'intégrité des données.

Exemple : tentative d'insertion de données incorrectes

-- Tentative d'inscrire un cours pour un étudiant qui n'existe pas
INSERT INTO enrollments (student_id, course_id) VALUES (3, 1);
-- Erreur : violation de la contrainte de foreign key

Erreurs classiques lors de la création de foreign keys

1. Pas d'index sur la foreign key. PostgreSQL crée automatiquement un index pour la primary key, mais pas pour la foreign key. Si tu utilises souvent la foreign key dans les clauses WHERE, pense à créer l'index à la main.
2. Mauvais ordre de création des tables. Tu ne peux pas créer une foreign key qui pointe vers une table qui n'existe pas encore.
3. Oubli du modificateur ON DELETE ou ON UPDATE. Ça peut donner des comportements bizarres quand tu modifies les données.

Maintenant que tu sais comment créer des foreign keys, tu as un outil puissant pour construire des bases de données structurées et cohérentes. Dans la prochaine leçon, on va creuser encore plus les actions ON DELETE CASCADE et ON UPDATE RESTRICT pour gérer les données liées.