Bloc 2 — TD/TP PostgreSQL : Contraintes

Objectifs d’apprentissage

• Poser et migrer des contraintes de colonne : NOT NULL, CHECK, types adaptés.

• Définir des clés et références : PRIMARY KEY, UNIQUE, FOREIGN KEY avec stratégies ON DELETE/UPDATE.

• Utiliser des contraintes avancées :

  • Exclusion (EXCLUDE USING gist) avec daterange pour interdire les chevauchements de séjours par client.
  • DEFERRABLE / INITIALLY DEFERRED pour des mises à jour atomiques.
  • NOT VALID / VALIDATE CONSTRAINT pour migrer sans downtime.

• Gérer l’ordre des opérations : détection des données sales, correction, ajout/validation des contraintes.

• Écrire des scénarios de test qui prouvent que vos contraintes acceptent le bon et rejettent le mauvais.

Pré-requis / Setup

• Réutilisez vos tables du Bloc 1 (mêmes noms/colonnes). Si besoin, relancez ce DDL minimal (sans contraintes) :

drop table if exists sejour;
drop table if exists village;
drop table if exists client;

create table client (
  idc  bigint generated always as identity,
  nom  text,
  age  int
);

create table village (
  idv      bigint generated always as identity,
  ville    text,
  activite text,
  prix     numeric,   -- €/jour
  capacite int
);

create table sejour (
  ids   bigint generated always as identity,
  idc   bigint,
  idv   bigint,
  debut date,
  fin   date
);

Gardez quelques lignes incohérentes volontairement (ex. age NULL, prix négatif, fin < debut) pour pratiquer la migration sous contrainte.

Parcours « Core » (à faire en priorité)

A) Contraintes de colonne (qualité des données)

1. NOT NULL progressif

   • Objectif : rendre client.nom, client.age, village.ville, village.activite, village.prix, village.capacite, sejour.idc, sejour.idv, sejour.debut, sejour.fin NOT NULL.

   • Étapes :

     1. Diagnostiquer : select * from … where col is null;
     2. Corriger (au cas par cas ou via valeurs de remplacement raisonnables).
     3. Poser la contrainte : alter table … alter column … set not null;

   • Attendu : script “diagnostic → correction → contrainte” reproductible.

2. CHECK métier simple

   • Poser :

     • client.age between 0 and 120
     • village.prix >= 0
     • village.capacite >= 0
     • sejour.fin >= sejour.debut

   • Travail demandé :

     • Écrire les alter table … add constraint … check (…) not valid;
     • Corriger les lignes invalides
     • Puis validate constraint …;

   • Justifiez vos bornes (ex. age).

3. Types plus précis (option design)

   • Proposez (et justifiez) un passage vers :

     • numeric(10,2) pour prix
     • éventuellement un domaine euro numeric(10,2) check (value >= 0) et age_int int check (value between 0 and 120)

   • Montrez la migration : create domain …; alter table … alter column … type euro using (prix::numeric);

B) Identité, clés et références

4. Clés primaires

   • Ajoutez primary key sur client(idc), village(idv), sejour(ids).
   • Si des doublons existent (rare), montrez comment les résoudre.

5. Clés étrangères + politiques d’effacement

   • sejour.idc → client.idc avec on delete cascade (efface les séjours si un client est supprimé).

   • sejour.idv → village.idv avec on delete restrict (empêcher de supprimer un village qui a des séjours).

   • Travail demandé :

     • Justifier vos choix cascade/restrict (et proposer une alternative).
     • Prouver par deux tests (un delete client, un delete village).

6. UNIQUE utile

   • Proposez au moins un UNIQUE pertinent (ex. si vous décidez que (ville, activite, prix) caractérise un “produit” village à un instant T — ou plus raisonnable : (village.ville, village.activite, idv) n’a pas de sens; souvent il n’y a pas de clé naturelle stricte → expliquez pourquoi vous n’ajoutez pas de UNIQUE ici).
   • Alternatif réaliste : interdire deux séjours du même client commençant le même jour → unique(idc, debut) (utile même si on interdira les chevauchements plus loin).

C) Pas de chevauchement de séjours (client)

Spécification : Un client ne peut pas avoir deux séjours qui se chevauchent.

7. Préparer le type “période” et l’extension

   • Activez l’extension : create extension if not exists btree_gist;

   • Ajoutez une colonne générée (ou une vue) pour la période :

     alter table sejour
       add column periode daterange
       generated always as (daterange(debut, fin, '[]')) stored;
     

     Copy

     On reste en [] (inclusif) pour coller à vos données Bloc 1.

8. EXCLUDE USING gist (avancé sans trigger)

   • Ajoutez la contrainte d’exclusion déferrable :

     alter table sejour add constraint sejour_no_overlap_per_client
       exclude using gist (
         idc with =,
         periode with &&
       )
       deferrable initially immediate;
     

     Copy

   • Tests à fournir :

     • Insertion de deux séjours qui ne se chevauchent pas → OK.
     • Insertion de deux séjours qui se chevauchent pour le même client → rejet.
     • Deux séjours qui se chevauchent mais clients différents → OK.

9. DEFERRABLE / INITIALLY DEFERRED (transaction)

   • Montrez qu’une mise à jour “swap” qui temporairement crée un chevauchement échoue en mode immediate mais passe en :

     begin;
     set constraints sejour_no_overlap_per_client deferred;
     -- updates ici
     commit;
     

     Copy

   • Expliquez quand employer deferred (rares opérations de maintenance).

D) Gestion et migration des contraintes

10. NOT VALID → VALIDATE

    • Répétez la séquence complète pour au moins une contrainte : add … not valid → diagnostic → correction → validate constraint.
    • Montrez l’intérêt : ajout de contraintes sans bloquer la production (concept).

11. Renommage et documentation

    • Renommez toutes vos contraintes avec un conventionnement lisible :

      • ck_client_age, ck_village_prix_nonneg, pk_sejour, fk_sejour_idv, etc.

    • Montrez comment on constraint … is '…'; pour documenter.

Parcours « Bonus » (si temps / devoir maison)

E) Exclusions alternatives et colonnes calculées

12. Période canonique “nuitée”

    • Variante plus réaliste : en hôtellerie, on réserve [debut, fin) (fin exclusive). Créez une autre colonne générée periode_excl = daterange(debut, fin, '[)'). Adaptez et testez l’exclusion sur periode_excl.

13. Générées utiles

    • Ajoutez nb_nuits int generated always as (greatest((fin - debut), 0)) stored;
    • Ajoutez un CHECK nb_nuits >= 1 si vous voulez interdire les séjours vides.

F) Index partiels & unicités conditionnelles

14. Unicité conditionnelle (exemple pédagogique)

    • Ajoutez une colonne annule boolean not null default false.

    • Empêchez deux séjours actifs commençant le même jour pour un client :

      create unique index uniq_client_debut_non_annule
        on sejour(idc, debut)
        where annule = false;
      

      Copy

    • Montrez qu’un séjour annulé ne bloque pas une nouvelle réservation.

G) Stratégies ON DELETE/UPDATE (comparatif)

15. Scénario comparatif

    • Basculer fk sejour→village en on delete cascade dans une branche de test.
    • Montrez les effets de bord (perte de l’historique des séjours).
    • Concluez sur le rôle de l’archivage (qui arrivera au Bloc 5 via trigger).

H) Contraintes sur valeurs de référence

16. LISTE contrôlée d’activités

    • Montrez deux variantes :

      • check (activite in ('surf','voile','kitesurf','plongée','rando','yoga'))
      • ou create type activite_t as enum (…) puis migration vers ce type.

    • Discutez avantages/inconvénients (enum vs check vs table référentielle).