Relations et jointures PostgreSQL

Introduction

Dans ce labo, vous explorerez les relations et les jointures (joins) dans PostgreSQL. Vous apprendrez à créer des tables avec des contraintes de clé étrangère (foreign key constraints) pour garantir l'intégrité des données.

Vous commencerez par créer deux tables, customers et orders, et établirez une relation de clé étrangère entre elles. Ensuite, vous insérerez des données dans ces tables, en vous assurant que les données respectent la relation définie. Enfin, vous apprendrez à récupérer des données à l'aide de INNER JOIN et à comparer les résultats des opérations LEFT, RIGHT et FULL OUTER JOIN pour comprendre comment elles gèrent différentes relations de données.

Skills Graph

Créer des tables avec des contraintes de clé étrangère (Foreign Key Constraints)

Dans cette étape, vous allez créer deux tables, customers et orders, et établir une contrainte de clé étrangère entre elles. Cette contrainte garantit que la relation entre les tables est maintenue, empêchant ainsi la saisie de données non valides.

Comprendre les clés étrangères (Foreign Keys)

Une clé étrangère est une colonne dans une table qui fait référence à la clé primaire (primary key) d'une autre table. Elle établit un lien entre les deux tables. La table contenant la clé étrangère est appelée la table "enfant" (child table), et la table contenant la clé primaire est appelée la table "parent" (parent table).

Étape 1 : Se connecter à PostgreSQL

Ouvrez un terminal dans votre VM LabEx. Connectez-vous à la base de données PostgreSQL en utilisant la commande psql :

sudo -u postgres psql

Vous devriez maintenant voir l'invite PostgreSQL (postgres=#).

Étape 2 : Créer la table customers

Créez la table customers avec les colonnes suivantes :

• customer_id : Un identifiant unique pour chaque client (clé primaire).
• first_name : Le prénom du client.
• last_name : Le nom de famille du client.
• email : L'adresse e-mail du client (doit être unique).

Exécutez la commande SQL suivante dans le shell psql :

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

Cette commande crée la table customers. Le mot-clé SERIAL génère automatiquement une séquence de nombres pour le customer_id, le rendant auto-incrémenté. PRIMARY KEY désigne customer_id comme clé primaire. NOT NULL garantit que les colonnes first_name et last_name ne peuvent pas être vides, et UNIQUE garantit que chaque adresse e-mail est unique.

Étape 3 : Créer la table orders avec une clé étrangère (Foreign Key)

Créez la table orders avec les colonnes suivantes :

• order_id : Un identifiant unique pour chaque commande (clé primaire).
• customer_id : L'ID du client qui a passé la commande (clé étrangère faisant référence à customers).
• order_date : La date à laquelle la commande a été passée.
• total_amount : Le montant total de la commande.

Exécutez la commande SQL suivante dans le shell psql :

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

Cette commande crée la table orders. La colonne customer_id est définie comme une clé étrangère en utilisant le mot-clé REFERENCES. REFERENCES customers(customer_id) spécifie que la colonne customer_id dans la table orders fait référence à la colonne customer_id dans la table customers. Cela établit la relation de clé étrangère.

Étape 4 : Vérifier la création de la table

Vérifiez que les tables ont été créées avec succès en listant les tables dans la base de données. Exécutez la commande suivante dans le shell psql :

\dt

Vous devriez voir les tables customers et orders listées.

Étape 5 : Décrire les tables

Pour voir la structure des tables, utilisez la commande \d suivie du nom de la table. Par exemple, pour décrire la table customers, exécutez :

\d customers

Cela vous montrera les colonnes, les types de données et les contraintes définies pour la table customers. De même, vous pouvez décrire la table orders :

\d orders

Cela vous montrera la contrainte de clé étrangère sur la colonne customer_id.

Vous avez maintenant créé avec succès deux tables avec une contrainte de clé étrangère.

Insérer des données et appliquer l'intégrité référentielle (Referential Integrity)

Dans cette étape, vous allez insérer des données dans les tables customers et orders, en vous assurant que l'intégrité référentielle est maintenue. Cela signifie que vous ne pouvez pas ajouter une commande pour un client qui n'existe pas dans la table customers.

Comprendre l'intégrité référentielle (Referential Integrity)

L'intégrité référentielle garantit que les relations entre les tables restent cohérentes. Dans notre cas, cela signifie que le customer_id dans la table orders doit exister dans la table customers.

Étape 1 : Insérer des données dans la table customers

Insérez des données dans la table customers en utilisant la commande SQL suivante dans le shell psql :

INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', '[email protected]'),
('Jane', 'Smith', '[email protected]'),
('David', 'Lee', '[email protected]');

Cette commande ajoute trois clients à la table customers. Le customer_id est généré automatiquement.

Étape 2 : Vérifier l'insertion des données dans la table customers

Vérifiez que les données ont été insérées correctement en interrogeant la table customers :

SELECT * FROM customers;

Vous devriez voir les trois clients que vous venez d'insérer, ainsi que leurs valeurs customer_id générées automatiquement. La sortie devrait ressembler à ceci :

 customer_id | first_name | last_name |         email
-------------+------------+-----------+------------------------
           1 | John       | Doe       | [email protected]
           2 | Jane       | Smith     | [email protected]
           3 | David      | Lee       | [email protected]
(3 rows)

Étape 3 : Insérer des données dans la table orders

Insérez des données dans la table orders, en référençant les valeurs customer_id de la table customers :

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1, '2023-11-01', 100.00),
(2, '2023-11-05', 250.50),
(1, '2023-11-10', 75.25),
(3, '2023-11-15', 120.00);

Cette commande ajoute quatre commandes à la table orders. Chaque commande est associée à un customer_id de la table customers.

Étape 4 : Vérifier l'insertion des données dans la table orders

Vérifiez que les données ont été insérées correctement en interrogeant la table orders :

SELECT * FROM orders;

Vous devriez voir les quatre commandes que vous venez d'insérer. La sortie devrait ressembler à ceci :

 order_id | customer_id | order_date | total_amount
----------+-------------+------------+--------------
        1 |           1 | 2023-11-01 |       100.00
        2 |           2 | 2023-11-05 |       250.50
        3 |           1 | 2023-11-10 |        75.25
        4 |           3 | 2023-11-15 |       120.00
(4 rows)

Étape 5 : Tenter d'insérer des données non valides (Démonstration de l'intégrité référentielle)

Pour démontrer la contrainte d'intégrité référentielle, essayez d'insérer une commande avec un customer_id qui n'existe pas dans la table customers :

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(4, '2023-11-20', 50.00);

Vous devriez voir un message d'erreur similaire à celui-ci :

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(4) is not present in table "customers".

Ce message d'erreur confirme que la contrainte de clé étrangère fonctionne. La base de données empêche l'insertion de la commande car le customer_id 4 n'existe pas dans la table customers.

Vous avez maintenant inséré avec succès des données dans les tables customers et orders, en assurant l'intégrité référentielle.

Interroger des données en utilisant INNER JOIN

Dans cette étape, vous apprendrez à récupérer des données à partir de plusieurs tables en utilisant la clause INNER JOIN dans PostgreSQL. INNER JOIN combine les lignes de deux tables ou plus en fonction d'une colonne liée.

Comprendre INNER JOIN

Un INNER JOIN renvoie uniquement les lignes où il existe une correspondance dans les deux tables jointes. S'il n'y a pas de correspondance, la ligne est exclue du résultat.

Étape 1 : Se connecter à PostgreSQL

Assurez-vous que vous êtes connecté à la base de données PostgreSQL en utilisant la commande psql :

sudo -u postgres psql

Étape 2 : Exécuter la requête INNER JOIN

Exécutez la requête SQL suivante dans le shell psql :

SELECT orders.order_id, customers.first_name, orders.order_date, orders.total_amount
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

Décomposons cette requête :

• SELECT orders.order_id, customers.first_name, orders.order_date, orders.total_amount : Ceci spécifie les colonnes que vous souhaitez récupérer à partir des tables orders et customers. L'utilisation du nom de la table comme préfixe (par exemple, orders.order_id) clarifie de quelle table provient chaque colonne.
• FROM orders : Ceci spécifie la première table à partir de laquelle vous interrogez.
• INNER JOIN customers ON orders.customer_id = customers.customer_id : Ceci spécifie la deuxième table avec laquelle vous effectuez la jointure (customers) et la condition de jointure (orders.customer_id = customers.customer_id). La clause ON spécifie que le customer_id dans la table orders doit correspondre au customer_id dans la table customers pour que les lignes soient incluses.

Étape 3 : Analyser les résultats

La requête renverra un ensemble de résultats contenant l'ID de la commande, le prénom du client, la date de la commande et le montant total pour chaque commande. La sortie devrait ressembler à ceci :

 order_id | first_name | order_date | total_amount
----------+------------+------------+--------------
        1 | John       | 2023-11-01 |       100.00
        3 | John       | 2023-11-10 |        75.25
        2 | Jane       | 2023-11-05 |       250.50
        4 | David      | 2023-11-15 |       120.00
(4 rows)

La requête a joint avec succès les tables orders et customers en fonction du customer_id et a récupéré les informations demandées. Seules les commandes avec un client correspondant dans la table customers sont incluses.

Étape 4 : Utilisation d'alias (facultatif)

Pour les requêtes plus complexes, vous pouvez utiliser des alias pour rendre la requête plus lisible. La requête précédente peut être réécrite en utilisant des alias :

SELECT o.order_id, c.first_name, o.order_date, o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

Dans cette requête, o est un alias pour orders et c est un alias pour customers. Le résultat sera le même, mais la requête est plus concise.

Vous avez maintenant interrogé avec succès des données à partir de plusieurs tables en utilisant la clause INNER JOIN.

Comparer les résultats de LEFT, RIGHT et FULL OUTER JOIN

Dans cette étape, vous explorerez et comparerez les résultats de LEFT OUTER JOIN (ou LEFT JOIN), RIGHT OUTER JOIN (ou RIGHT JOIN) et FULL OUTER JOIN (ou FULL JOIN) dans PostgreSQL. Ces jointures récupèrent toutes les lignes d'une ou des deux tables, même s'il n'y a pas de valeurs correspondantes dans l'autre table.

Comprendre les OUTER JOIN

• LEFT OUTER JOIN (ou LEFT JOIN) : Renvoie toutes les lignes de la table de gauche et les lignes correspondantes de la table de droite. S'il n'y a pas de correspondance dans la table de droite, les valeurs NULL sont renvoyées pour les colonnes de la table de droite.
• RIGHT OUTER JOIN (ou RIGHT JOIN) : Renvoie toutes les lignes de la table de droite et les lignes correspondantes de la table de gauche. S'il n'y a pas de correspondance dans la table de gauche, les valeurs NULL sont renvoyées pour les colonnes de la table de gauche.
• FULL OUTER JOIN (ou FULL JOIN) : Renvoie toutes les lignes des deux tables. S'il n'y a pas de correspondance dans une table, les valeurs NULL sont renvoyées pour les colonnes de l'autre table.

Étape 1 : Se connecter à PostgreSQL

Assurez-vous que vous êtes connecté à la base de données PostgreSQL en utilisant la commande psql :

sudo -u postgres psql

Étape 2 : Insérer un nouveau client sans commandes

Insérez un nouveau client dans la table customers qui n'a passé aucune commande :

INSERT INTO customers (first_name, last_name, email) VALUES
('Alice', 'Brown', '[email protected]');

Étape 3 : Vérifier le nouveau client

Vérifiez que le nouveau client a été ajouté à la table customers :

SELECT * FROM customers;

Vous devriez voir Alice Brown listée dans les résultats, avec un nouvel customer_id (probablement 4).

Étape 4 : Effectuer un LEFT OUTER JOIN

Exécutez la requête SQL suivante pour effectuer un LEFT OUTER JOIN entre les tables customers et orders :

SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
LEFT OUTER JOIN orders ON customers.customer_id = orders.customer_id;

Cette requête renvoie tous les clients, ainsi que toutes les commandes qu'ils ont passées. Si un client n'a passé aucune commande, les colonnes order_id et order_date contiendront des valeurs NULL. La sortie devrait ressembler à ceci :

 first_name | order_id | order_date
------------+----------+------------
 John       |        1 | 2023-11-01
 John       |        3 | 2023-11-10
 Jane       |        2 | 2023-11-05
 David      |        4 | 2023-11-15
 Alice      |          |
(5 rows)

Notez qu'Alice Brown est incluse, même si elle n'a passé aucune commande. Les colonnes order_id et order_date sont NULL pour elle.

Étape 5 : Effectuer un RIGHT OUTER JOIN

Exécutez la requête SQL suivante pour effectuer un RIGHT OUTER JOIN entre les tables customers et orders :

SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
RIGHT OUTER JOIN orders ON customers.customer_id = orders.customer_id;

Cette requête renvoie toutes les commandes, ainsi que le prénom du client qui a passé chaque commande. Étant donné que chaque commande a un client correspondant, le résultat sera le même qu'un INNER JOIN dans ce cas. La sortie devrait ressembler à ceci :

 first_name | order_id | order_date
------------+----------+------------
 John       |        1 | 2023-11-01
 Jane       |        2 | 2023-11-05
 John       |        3 | 2023-11-10
 David      |        4 | 2023-11-15
(4 rows)

Étape 6 : Effectuer un FULL OUTER JOIN

Exécutez la requête SQL suivante pour effectuer un FULL OUTER JOIN entre les tables customers et orders :

SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;

Cette requête renvoie tous les clients et toutes les commandes. Si un client n'a passé aucune commande, les colonnes order_id et order_date contiendront des valeurs NULL. Si une commande n'a pas de client correspondant (ce qui n'est pas possible dans notre configuration actuelle en raison de la contrainte de clé étrangère), la colonne first_name contiendrait des valeurs NULL. La sortie devrait ressembler à ceci :

 first_name | order_id | order_date
------------+----------+------------
 John       |        1 | 2023-11-01
 John       |        3 | 2023-11-10
 Jane       |        2 | 2023-11-05
 David      |        4 | 2023-11-15
 Alice      |          |
(5 rows)

Notez qu'Alice Brown est incluse avec des valeurs NULL pour order_id et order_date.

Étape 7 : Comprendre les différences

• LEFT OUTER JOIN inclut toutes les lignes de la table customers, même s'il n'y a pas de commandes correspondantes.
• RIGHT OUTER JOIN inclut toutes les lignes de la table orders. Dans notre cas, il se comporte comme un INNER JOIN car toutes les commandes ont un client correspondant.
• FULL OUTER JOIN inclut toutes les lignes des deux tables.

Vous avez maintenant exploré et comparé les résultats de LEFT OUTER JOIN, RIGHT OUTER JOIN et FULL OUTER JOIN dans PostgreSQL.

Résumé

Dans ce labo, vous avez appris à créer des tables avec des contraintes de clé étrangère (foreign key constraints) dans PostgreSQL pour maintenir l'intégrité des données. Vous avez créé les tables customers et orders et établi une relation entre elles en utilisant une clé étrangère. Vous avez ensuite inséré des données dans ces tables, en vous assurant que la contrainte de clé étrangère était appliquée. Enfin, vous avez exploré différents types d'opérations JOIN (INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN et FULL OUTER JOIN) pour récupérer des données à partir de tables liées et comprendre comment elles gèrent différentes relations de données.