Voici un exemple d'une petite base de données d'un site web de ventes ou échanges d'un produit. L'utilisateur s'inscrit et à partir de ce moment, il peut acheter ou vendre des produits ou une combinaison des deux.
Création de la base de données
Voici les deux contraintes spéciales à retenir avant de créer la base de données. La première est que chaque colonne TEXT ne doit pas permettre la chaîne de caractères vide. La deuxième est que le vendeur doit être différent de l'acheteur d'un produit.
Voici la création des deux premières tables Categories et Utilisateurs :
CREATE TABLE Categories( CategorieId INTEGER NOT NULL PRIMARY KEY, Nom TEXT NOT NULL CHECK (Nom <> ''));CREATE TABLE Utilisateurs( UtilisateurId INTEGER NOT NULL PRIMARY KEY, Prenom TEXT NOT NULL CHECK (Prenom <> ''), Nom TEXT NOT NULL CHECK (Nom <> ''), Courriel TEXT NOT NULL UNIQUE CHECK (Courriel <> ''), Telephone TEXT CHECK (Telephone <> ''));
Les deux tables sont relativement simples, car ils n'ont pas de clé étrangère. Seulement Courriel doit être unique et Telephone est la seule colonne NULL.
Ça se corse pour la table Produits :
CREATE TABLE Produits( ProduitId INTEGER NOT NULL PRIMARY KEY, Titre TEXT NOT NULL CHECK (Titre <> ''), Prix REAL CHECK (Prix >= 0), Description TEXT NOT NULL CHECK (Description <> ''), CategorieId INTEGER NOT NULL, VendeurId INTEGER NOT NULL, AcheteurId INTEGER CHECK (AcheteurId <> VendeurId), FOREIGN KEY (CategorieId) REFERENCES Categories(CategorieId) ON DELETE RESTRICT ON UPDATE CASCADE, FOREIGN KEY (VendeurId) REFERENCES Utilisateurs(UtilisateurId) ON DELETE RESTRICT ON UPDATE CASCADE, FOREIGN KEY (AcheteurId) REFERENCES Utilisateurs(UtilisateurId) ON DELETE RESTRICT ON UPDATE CASCADE);
Les premières colonnes sont similaires à ce qu'on a déjà fait.
Pour gérer la contrainte de ne pas avoir le même utilisateur qui est acheteur et vendeur, on peut ajouter une comparaison entre les deux colonnes ->
AcheteurId INTEGER CHECK (AcheteurId <> VendeurId). Cela empêche l'ajout d'un produit ayant la même clé d'utilisateur comme acheteur et vendeur :
INSERT INTO Categories (Nom)VALUES ('Jeux video');INSERT INTO Utilisateurs (Prenom, Nom, Courriel)VALUES ('Bob', 'Gratton', 'bobgratton@cegeptest.org');--Cette requête va échouer!INSERT INTO Produits (Titre, Prix, Description, CategorieId, VendeurId, AcheteurId)VALUES ('Problème contrainte', 9.99, 'Ça ne passera pas!', 1, 1, 1);
Pour ce qui est de la clé étrangère dans ce cas, le vendeur et l'acheteur sont des utilisateurs les deux. C'est légitime d'avoir deux clés étrangères qui pointent sur la même table. L'intérêt c'est que la colonne représente un concept différent soit l'acheteur et le vendeur. Dans une autre base de données, l'acheteur pourrait avoir ça propre table Acheteurs et pour le vendeur Vendeurs. Cela dépend des informations que l'on veut avoir.
FOREIGN KEY (VendeurId) REFERENCES Utilisateurs(UtilisateurId) ON DELETE RESTRICT ON UPDATE CASCADE,FOREIGN KEY (AcheteurId) REFERENCES Utilisateurs(UtilisateurId) ON DELETE RESTRICT ON UPDATE CASCADE
On remarque que la colonne dans la table est différente VendeurId et AcheteurId, mais la référence de la table parente est la même : REFERENCES Utilisateurs(UtilisateurId).
Quelques requêtes
Une fois la base de données créée avec les tables, on peut ajouter des données :
INSERT INTO Categories (Nom)VALUES ('Jeux video'), ('Meuble'), ('Voiture'), ('Autres');INSERT INTO Utilisateurs (Prenom, Nom, Courriel, Telephone)VALUES ('Bob', 'Gratton', 'bobgratton@cegeptest.org', '8885554321'), ('Gérard', 'D. Laflaque', 'gerardlaflaque@cegeptest.org', '8885551234'), ('Alain', 'Alain', 'alain_alain@cegeptest.org', NULL), ('Maryse', 'Boucher', 'mboucher@cegeptest.org', NULL), ('Sonia', 'Champoux', 'schampoux@cegeptest.org', NULL);INSERT INTO Produits (Titre, Prix, Description, CategorieId, VendeurId, AcheteurId)VALUES ('Zelda NES très propre!', 59.99, 'C''est vraiment le jeu de l''année... 1987', 1, 1, 2), ('Cyberpunk 2077', NULL, 'Je le passe au prochain!', 1, 1, NULL), ('Mon vieux divan', 100, 'Il a vécu beaucoup d''action, j''en ai acheté un nouveau', 2, 2, NULL), ('Willi Waller 2006', 19.99, 'Toujours dans la boite À ne pas manquer :p', 4, 2, 5), ('Honda Civic 99', 799.99, 'Pas trop bauché dans le temps. À qui la chance!', 3, 4, 2);
Cela permet de faire quelques requêtes.
Obtenir tous les produits d'une catégorie
Voici la requête pour obtenir tous les produits d'une catégorie précise exemple la catégorie Jeux video:
SELECT Produits.Titre, Produits.PrixFROM ProduitsJOIN Categories ON Produits.CategorieId = Categories.CategorieIdWHERE Categories.Nom = 'Jeux video'
Titre Prix ---------- ---------- Zelda NES très propre! 59.99 Cyberpunk 2077 NULL
Obtenir tous les produits vendus par
Voici la requête pour obtenir tous les produits vendus par 'Gérard D. Laflaque':
SELECT Produits.Titre, Produits.PrixFROM ProduitsJOIN Utilisateurs ON Produits.VendeurId = Utilisateurs.UtilisateurIdWHERE Utilisateurs.Prenom = 'Gérard' AND Utilisateurs.Nom = 'D. Laflaque'
Titre Prix ---------- ---------- Mon vieux divan 100 Willi Waller 2006 19.99
Obtenir tous les produits achetés par
Voici la requête pour obtenir tous les produits achetés par 'Gérard D. Laflaque':
SELECT Produits.Titre, Produits.PrixFROM ProduitsJOIN Utilisateurs ON Produits.AcheteurId = Utilisateurs.UtilisateurIdWHERE Utilisateurs.Prenom = 'Gérard' AND Utilisateurs.Nom = 'D. Laflaque'
Titre Prix ---------- ---------- Zelda NES très propre! 59.99 Honda Civic 99 799.99
Obtenir tous les produits achetés ou vendus par
C'est là que ça se complique. Comment on fait notre jointure pour obtenir à la fois le vendeur ou l'acheteur...
SELECT Produits.Titre, Produits.PrixFROM ProduitsLEFT OUTER JOIN Utilisateurs u1 ON Produits.AcheteurId = u1.UtilisateurIdLEFT OUTER JOIN Utilisateurs u2 ON Produits.VendeurId = u2.UtilisateurIdWHERE u1.Prenom = 'Gérard' AND u1.Nom = 'D. Laflaque' AND u2.Prenom = 'Gérard' AND u2.Nom = 'D. Laflaque'
Si je fais cela, ça ne fonctionnera pas! Car il n'y a aucune combinaison qui fait que l'utilisateur vendeur et l'utilisateur acheteur soit le même. Pour cela, il faut utiliser la flexibilité de la condition après le ON. En effet, on peut mettre n'importe quelle condition pour le ON. Habituellement, on limite à l'égalité entre deux colonnes identifiants, mais il est possible de mettre plus d'une condition :
SELECT Produits.Titre, Produits.PrixFROM ProduitsINNER JOIN Utilisateurs ON Produits.AcheteurId = Utilisateurs.UtilisateurId OR Produits.VendeurId = Utilisateurs.UtilisateurIdWHERE Utilisateurs.Prenom = 'Gérard' AND Utilisateurs.Nom = 'D. Laflaque'
Titre Prix ---------- ---------- Zelda NES très propre! 59.99 Mon vieux divan 100 Willi Waller 2006 19.99 Honda Civic 99 799.99
ON Produits.AcheteurId = Utilisateurs.UtilisateurId OR Produits.VendeurId = Utilisateurs.UtilisateurId
Ici on précise que je veux obtenir l'utilisateur qui est soit le vendeur ou l'acheteur. S'il était possible d'avoir le même vendeur et acheteur,
ON Produits.AcheteurId = Utilisateurs.UtilisateurId AND Produits.VendeurId = Utilisateurs.UtilisateurId
l'utilisation de AND au lieu de OR aurait permis d'avoir tous les produits qui ont le même acheteur que vendeur.
En résumé, Même si on utilise la clé étrangère vers la clé primaire comme comparaison dans le ON, il y a des cas particuliers où l'on peut avoir plus d'une condition dans notre ON. Ce cas particulier s'applique bien pour multiples clés étrangères qui référencent la même table parent.
Version originale par Pierre-Luc Boulanger