Rodolphe vous lance un nouveau défi. Après l'intégration de nouvelles tables dans la base, il a remarqué des problèmes d'unicité qui peuvent provoquer des doublons et des incohérences dans les données. Ces erreurs pourraient affecter les analyses et les opérations de l’entreprise. Votre mission est de vous assurer que chaque donnée critique dans la base est unique et que les relations entre les tables sont correctement définies.
Prenez en main le concept de clé primaire et de clé étrangère
C’est quoi déjà une clé primaire ?
Commençons par un rappel dans ce cas. La clé primaire est une contrainte fondamentale qui garantit que chaque enregistrement d’une table est unique. Par exemple, dans la table customer de la base DVD Rental, la colonne customer_id est la clé primaire : chaque client a un identifiant unique qui permet de le distinguer de tous les autres.
Les propriétés de la clé primaire :
Elle est unique.
Elle ne peut pas contenir de valeur
NULL.Chaque table ne peut avoir qu’une seule clé primaire, mais celle-ci peut être constituée de plusieurs colonnes (clé primaire composite).
Et la clé étrangère du coup ?
La clé étrangère, quant à elle, établit une relation entre deux tables. Par exemple, la table rental (qui enregistre les locations) possède une colonne customer_id qui fait référence à la clé primaire customer_id dans la table customer.
Cela permet de relier chaque location à un client unique, garantissant ainsi la cohérence des données.
Les clés étrangères permettent de :
maintenir l'intégrité référentielle entre deux tables ;
garantir que les données référencées existent dans la table parente ;
synchroniser les opérations de mise à jour ou de suppression entre les tables liées.
Maintenant que vous vous rappelez ces différents concepts, il est temps de mettre en place des contraintes supplémentaires.
Appliquez des contraintes sur vos clés étrangères
Vous recevez un mail de Rodolphe avec une nouvelle mission à la clé :
Objet : Mise en place d’une contrainte d’unicité pour éviter les doublons dans les locations
Bonjour,
J'espère que tout se passe bien de ton côté !
Je reviens vers toi avec une nouvelle demande : nous venons de créer une table appelée customer_rentals pour gérer les locations de films. Cependant, il est impératif de s'assurer qu'un même client ne puisse pas louer plusieurs fois le même film à un moment donné.
Pour éviter tout doublon, pourrais-tu mettre en place une contrainte d'unicité sur cette table ? Cette contrainte devra s'appliquer sur les colonnes customer_id et film_id, afin de garantir qu'un client ne puisse louer chaque film qu'une seule fois.
Merci d'avance pour ton travail !
Rodolphe
DBA - DVD Rental
Appliquez des contraintes d’unicité lors de la création d'une table
C’est quoi une contrainte d’unicité ?
Une contrainte d'unicité (ou UNIQUE constraint) est une règle que l'on applique sur une ou plusieurs colonnes d'une table pour s'assurer que les valeurs de ces colonnes sont toujours uniques. Cela signifie qu'aucune donnée en double ne pourra être insérée dans la table pour ces colonnes.
Par exemple, si vous imposez une contrainte d'unicité sur la colonne email d'une table users, cela garantit que deux utilisateurs ne pourront jamais avoir la même adresse e-mail.
Reprenons la demande de Rodolphe, pour la tablecustomer_rentalschaque client ne peut louer qu'un seul exemplaire de chaque film à un moment donné.
Nous allons donc créer une table où chaque location est liée à un client et à un film, et nous allons ajouter une contrainte d’unicité sur la combinaison customer_id et film_id pour éviter que le même client ne loue le même film plusieurs fois en même temps. Une fois la location terminée, nous pourrons retirer la location de cette table. Pour cela nous allons utiliser le mot-cléUNIQUE:
CREATE TABLE customer_rentals (
rental_id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customer(customer_id),
film_id INT REFERENCES film(film_id),
rental_date DATE NOT NULL,
UNIQUE (customer_id, film_id)
);Dans cet exemple :
customer_id est une clé étrangère qui fait référence à la table customer.
film_id est une clé étrangère qui fait référence à la table film.
la contrainte
UNIQUE(customer_id, film_id) garantit qu'un même client ne peut pas louer plusieurs fois le même film au même moment.
Appliquez des contraintes d’unicité lors de la mise à jour d’une table
Comment je fais, si la table a déjà été créée, pour ajouter ma contrainte (ce qui est d’ailleurs le cas pour la demande de Rodolphe… ) ?
Si une table a déjà été créée, mais qu’elle ne comporte pas encore de contrainte d’unicité, vous pouvez ajouter cette contrainte a posteriori sans problème !
Gardons notre exemple sur la tablecustomer_rentalsmais cette fois-ci, la table a été créée sans garantir que chaque client ne peut louer qu’un exemplaire d’un film à la fois. Vous pouvez corriger cela en ajoutant une contrainte d’unicité à l’aide de la commandeALTER TABLE.
ALTER TABLE rental ADD UNIQUE (customer_id, film_id);Essayez désormais d’insérer 2 lignes consécutives avec les mêmes customer_id et film_id, vous devriez avoir l’erreur suivante :
Comprenez les options de contrainte
Allons un peu plus loin dans les contraintes.
Imaginez que l'équipe de DVD Rental décide de supprimer un film qui ne fait plus partie de leur catalogue. Que devrait-il se passer si des clients ont encore des locations actives pour ce film ? Ou si des commandes historiques contiennent des références à ce film supprimé ? Grâce aux contraintesON DELETEetON UPDATE, vous avez la possibilité de définir précisément ce genre de comportement, garantissant ainsi que la suppression ou la mise à jour des données dans une table parente n'entraîne pas de problèmes d'intégrité dans les tables enfants.
Lorsqu'une clé étrangère est définie, il est possible de spécifier ce qui doit se passer lorsqu’un enregistrement référencé est mis à jour ou supprimé dans la table parente.
Voici quelques options :
Option | Description |
ON DELETE NO ACTION (valeur par défaut) | Si vous essayez de supprimer un enregistrement parent qui a des références dans la table enfant, PostgreSQL refusera l'opération et retournera une erreur d'intégrité référentielle, à moins que vous ayez d'abord mis à jour ou supprimé manuellement les enregistrements dans la table enfant. |
ON DELETE CASCADE | Si un enregistrement de la table parente est supprimé, tous les enregistrements qui lui sont liés dans la table enfant seront également supprimés. |
ON DELETE SET NULL | Si un enregistrement parent est supprimé, les valeurs de la clé étrangère dans la table enfant seront définies à NULL. |
ON UPDATE CASCADE | Si un enregistrement parent est mis à jour, les valeurs correspondantes dans la table enfant seront automatiquement mises à jour. |
Ces options sont importantes pour maintenir l’intégrité des relations lors de la manipulation des données.
Appliquez des contraintes lors de la création ou de la mise à jour d’une table
Prenons un exemple avec la table rental, où chaque location est liée à un client et à un film. Vous souhaitez que la suppression d'un client entraîne la suppression de toutes ses locations, tandis que la suppression d'un film remplace sa référence parNULLdans les locations associées.
Grâce aux contraintesON DELETE etON UPDATE, vous pouvez mieux contrôler comment les modifications dans la table parente impactent les tables enfants, en garantissant que les données de la base DVD Rental restent intégrées et sans erreurs.
Nous allons pouvoir voir comment mettre cela en place ensemble, en vidéo :
Dans cette vidéo, vous avez vu :
Comment créer une table à partir d’une autre, tout en copiant ses données (pour éviter de casser les contraintes déjà en place sur dvdrental).
L’impact d’incohérences lors de suppressions de clés étrangères.
Comment appliquer des contraintes (
ON DELETE,ON UPDATE) lors de la création d’une table.Comment appliquer des contraintes (
ON DELETE,ON UPDATE) lors de la mise à jour d’une table.
En appliquant ces contraintes d’unicité sur vos clés étrangères, vous renforcez la qualité des relations entre les tables et garantissez que la base de données de DVD Rental reste cohérente et fiable.
Utilisez les contraintes pour valider vos données
Après vos récents succès sur la mise en place des contraintes, Rodolphe, le DBA de DVD Rental, vous a convié à une réunion pour discuter des prochaines étapes. Enthousiasmé par les résultats à date, il vous confie qu'il souhaite aller encore plus loin dans la sécurisation des données. Lors de votre échange, il souligne l'importance de valider les données avant même qu'elles n'entrent dans la base, afin d’éviter toute insertion de valeurs incorrectes ou incohérentes qui pourraient nuire aux analyses. Rodolphe vous demande donc d'ajouter des contraintes spécifiques selon les types de colonnes, pour garantir que chaque nouvelle donnée soit conforme aux exigences et aux règles strictes de l’entreprise.
Comprenez les options de contrainte selon le type de colonne
Chaque type de donnée dans PostgreSQL permet d’appliquer des règles de validation, appelées contraintes, pour s'assurer que les informations saisies respectent certains critères avant d’être ajoutées à la base de données. Regardons quelques exemples pratiques pour mieux comprendre :
VARCHAR(chaîne de caractères) : Si vous stockez une adresse e-mail ou un nom de client, vous pouvez limiter la longueur de la chaîne et imposer une contrainte d'unicité. Cela permet, par exemple, de garantir que deux clients ne puissent pas utiliser la même adresse e-mail, évitant ainsi des doublons.
Exemple : Assurez-vous que chaque e-mail est unique et ne dépasse pas 255 caractères.email VARCHAR(255) UNIQUEINTEGER(numérique) : Lorsque vous enregistrez des valeurs numériques comme le nombre de films loués ou le montant dépensé par un client, vous pouvez ajouter des contraintes pour garantir que ces valeurs sont positives ou dans un certain intervalle. Cela évite d’avoir des valeurs erronées ou impossibles comme un montant négatif.Exemple : S'assurer que le stock d’un film soit toujours supérieur ou égal à zéro.
stock INT CHECK (stock >= 0)DATE: Lorsqu’on travaille avec des dates, il est important de vérifier leur validité. Par exemple, pour une location de film, il faut s’assurer que la date de retour soit toujours postérieure à la date de location. Sans cette contrainte, il serait possible d’avoir une date de retour antérieure, ce qui n’aurait pas de sens.Exemple : S'assurer qu'une carte de fidélité ne soit jamais expirée avant d’être délivrée.
expiration_date DATE CHECK (expiration_date > CURRENT_DATE)
Appliquez des contraintes lors de la création d’une table
Voyons un exemple plus concret. Supposons que nous devions créer à nouveau la table qui enregistre les films loués par les clients,customer_rentals. Dans cette table, nous allons ajouter des contraintes pour nous assurer que les données sont toujours cohérentes.
CREATE TABLE customer_rentals (
rental_id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customer(customer_id),
film_id INT REFERENCES film(film_id),
-- la date ne peut pas avoir comme valeur NULL
rental_date DATE NOT NULL,
-- la date return_date doit être postérieur à la date d’emprunt (rental_date)
return_date DATE CHECK (return_date > rental_date)
);Ajoutez des contraintes sur une table déjà créée
Une fois encore, si la table est déjà créée, est-ce que je vais pouvoir utiliser la commandeALTER?
Exactement ! Prenons l'exemple de la tablepaymentoù nous voulons nous assurer que le prix d'une location soit toujours supérieur à -1. Cela permet d’éviter des erreurs de saisie qui pourraient indiquer qu’un film a été loué à un prix négatif.
ALTER TABLE payment ADD CONSTRAINT price_check CHECK (amount > -1);À vous de jouer
Contexte
Après une analyse approfondie des données clients, Rodolphe a constaté quelques incohérences dans la base de données. Il vous demande de renforcer la qualité des informations en ajoutant des contraintes sur la table customer pour valider les données avant leur insertion. Cela permettra de garantir que les informations client sont toujours valides et uniques.
Consignes
Rodolphe souhaite s'assurer que les numéros de téléphone des clients contiennent entre 0 et 12 chiffres. Ajoutez une contrainte
CHECKsur la colonne phone pour garantir cette règle.Ajoutez une contrainte d'unicité : Pour éviter les doublons, ajoutez une contrainte d'unicité sur la colonne email, afin que chaque client ait une adresse email unique dans la base de données.
En résumé
Vous avez appris à renforcer l'intégrité des données en ajoutant des contraintes sur les tables pour garantir la cohérence et la qualité des informations.
Vous avez vu que les contraintes comme
CHECKetUNIQUEpermettent de valider les données avant leur insertion et de prévenir les erreurs ou doublons.Vous avez également vu comment utiliser des contraintes sur les clés étrangères avec les options
ON DELETEetON UPDATEpour maintenir la cohérence entre les tables liées.
Maintenant que vous êtes au courant des bases de la fiabilité des données, passons à la partie 2, où vous allez apprendre à résoudre des requêtes complexes, optimiser vos résultats avec des vues, et explorer des concepts avancés comme les procédures stockées et les fonctions fenêtres. Mais avant cela, testez vos connaissances dans le quiz clôturant cette partie.
