Contexte et importance des contraintes d'intégrité

• Les contraintes d'intégrité sont des règles imposées sur les données pour garantir leur validité.
• Elles assurent que les données sont conformes aux règles métiers (par exemple, un âge ne peut pas être négatif, un email doit être unique).
• Elles empêchent l'insertion, la modification ou la suppression de données incorrectes.
• Les contraintes sont définies dans le SGBD pour s'assurer que toutes les opérations sur les données respectent ces règles.
• Lorsqu'une contrainte est violée, l'opération est annulée et un message d'erreur est retourné.
• Cela garantit que les erreurs humaines ou les bugs dans les programmes ne corrompent pas la base de données.

Types de contraintes dans SQL

• NOT NULL : Empêche l'enregistrement de valeurs nulles dans une colonne.
  Exemple : un champ "nom" ne peut pas être vide dans une table "Personne".
• UNIQUE : Assure que toutes les valeurs dans une colonne sont uniques.
  Exemple : aucune adresse email ne doit être dupliquée dans une table "Utilisateurs".
• PRIMARY KEY : Définit une ou plusieurs colonnes comme clé primaire, garantissant l'unicité et servant d'identifiant pour chaque ligne.
  Exemple : une colonne "id" dans une table "Personne".
• FOREIGN KEY : Garantit la validité des relations entre plusieurs tables en établissant un lien entre une clé primaire et une clé étrangère.
  Exemple : la colonne "id_personne" dans une table "Réservations" référant à la clé primaire "id" dans une table "Personne".
• CHECK : Vérifie qu'une condition logique est respectée.
  Exemple : un champ "âge" doit toujours être supérieur ou égal à zéro.

Contrainte multi-colonnes

• Les contraintes peuvent impliquer plusieurs colonnes, comme les clés uniques composées.
• Exemple : Supposons que nous souhaitions garantir qu'une combinaison de deux colonnes soit unique.

CREATE TABLE IF NOT EXISTS Employe (
  id BIGSERIAL PRIMARY KEY,
  prenom VARCHAR(50) NOT NULL,
  nom VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  CONSTRAINT uq_prenom_nom UNIQUE (prenom, nom)
);

Cet exemple impose qu'une combinaison de "prenom" et "nom" soit unique dans la table "Employe". Cela peut être utilisé pour éviter des doublons où des personnes auraient le même prénom et nom.

Mécanismes internes des contraintes

• Les contraintes telles que les clés primaires et les clés uniques reposent souvent sur des index sous-jacents dans le SGBD.
• Ces index permettent de garantir l'unicité de manière rapide et efficace, mais peuvent aussi impacter les performances sur des tables de grande taille.
• Les contraintes comme CHECK et FOREIGN KEY sont des contraintes natives (implémentées par le SGBD).

Bien que ces contraintes améliorent la sécurité et la fiabilité des données, il est important de prendre en compte leur impact sur les performances, surtout pour les opérations massives d'insertion ou de modification.

Gestion des violations de contraintes

• Lorsqu'une contrainte est violée, une exception est levée et l'opération échoue.
• Les SGBD permettent de gérer ces erreurs avec des transactions qui peuvent être annulées en cas de violation.
• Exemple : Utilisation d'une transaction pour vérifier des conditions avant insertion.

BEGIN;

INSERT INTO Personne (nom, age, email)
  VALUES ('Alice', -25, 'alice@example.com');  -- échoue

ROLLBACK; -- annule l'opération

Dans cet exemple, la tentative d'insertion échoue en raison de la contrainte CHECK sur l'âge. L'utilisation d'une transaction permet d'annuler automatiquement toute modification de la base de données.

Contraintes côté application vs côté SGBD

• Les contraintes peuvent également être implémentées côté application, par exemple en Java, pour garantir la cohérence des données avant qu'elles ne soient envoyées au SGBD.
• Cependant, les contraintes dans le SGBD offrent une garantie supplémentaire, car elles sont exécutées au niveau de la base et ne dépendent pas de la qualité du code de l'application.

Exemple : Validation de la syntaxe d'une adresse email dans une application Java avant de l'insérer dans une base de données. Cette validation est utile, mais elle ne remplace pas une contrainte UNIQUE dans la base.

Exemple : Clé étrangère avec suppression en cascade

CREATE TABLE IF NOT EXISTS Commande (
  id_commande BIGSERIAL CONSTRAINT pk_commande PRIMARY KEY,
  id_client INTEGER NOT NULL,
  CONSTRAINT fk_commande_client FOREIGN KEY (id_client) 
      REFERENCES Client(id) ON DELETE CASCADE
);

DELETE FROM Client WHERE id = 1; -- supprime également les commandes liées

Ici, la suppression d'un client dans la table "Client" entraîne la suppression automatique des commandes associées dans la table "Commande" grâce à l'option on delete cascade.

Exemple : Cascade sur mise à jour

CREATE TABLE IF NOT EXISTS Commande (
  id_commande BIGSERIAL CONSTRAINT pk_commande PRIMARY KEY,
  id_client INTEGER NOT NULL,
  CONSTRAINT fk_commande_client FOREIGN KEY (id_client) 
      REFERENCES Client(id) ON UPDATE CASCADE ON DELETE CASCADE
);

UPDATE Client 
  SET id = 2 WHERE id = 1; -- met également à jour l'id dans la table "Commande"

Ici, toute mise à jour de la colonne "id" dans la table "Client" sera répercutée automatiquement dans la table "Commande" grâce à l'option on update cascade.

Principe des séquences

• Les séquences sont utilisées pour générer automatiquement des identifiants uniques dans une base de données.
• Elles garantissent que chaque nouvel enregistrement obtient une valeur unique, ce qui est essentiel pour les clés primaires.
• Dans les systèmes multi-utilisateurs, elles permettent d'éviter les conflits d'identifiants lors de l'insertion simultanée de données.
• Les séquences sont créées avec une requête SQL spécifique et peuvent être utilisées dans des commandes d'insertion pour générer automatiquement de nouveaux identifiants.

Paramètres avancés des séquences

• Les séquences peuvent être paramétrées avec différents attributs pour répondre aux besoins spécifiques.
• Par exemple, les valeurs générées peuvent avoir un intervalle défini, un cycle ou une limite maximale.

CREATE SEQUENCE IF NOT EXISTS seq_personne
START WITH 100
INCREMENT BY 10
MAXVALUE 1000
CYCLE;

Cette séquence commence à 100, incrémente de 10 à chaque appel, et reprend à 100 lorsque la valeur maximale de 1000 est atteinte (grâce à l'option cycle).

Exemples de requêtes SQL pour les séquences

CREATE SEQUENCE seq_personne;

SELECT nextval('seq_personne');  -- génère la prochaine valeur
SELECT currval('seq_personne');  -- renvoie la dernière valeur générée

L'ordre nextval génère la prochaine valeur dans la séquence, tandis que currval renvoie la dernière valeur générée par nextval. Ces commandes sont essentielles pour l'insertion d'enregistrements avec des identifiants uniques dans des systèmes multi-utilisateurs.

IDENTITY — c’est quoi ?

• Colonne auto-numérotée standard SQL : la valeur est générée par le SGBD via une séquence interne liée à la colonne.
• Remplace les usages historiques de SERIAL/BIGSERIAL ; plus explicite et mieux contrôlable.
• Deux modes :
  • GENERATED ALWAYS AS IDENTITY : l’appli ne peut pas fournir de valeur (sauf OVERRIDING SYSTEM VALUE).
  • GENERATED BY DEFAULT AS IDENTITY : l’appli peut fournir une valeur (sinon génération automatique).

-- Création (recommandé)
create table personne (
  id  bigint generated always as identity,
  nom text not null,
  age int  not null
);

-- Insérer et récupérer l'identifiant créé (idiome PG)
insert into personne(nom, age)
values ('Alice', 30)
returning id;

-- Personnaliser l'IDENTITY (pas, départ, cycle…)
alter table personne
  alter column id
  add generated by default as identity
  (start with 100, increment by 10, minvalue 100, cache 20);

IDENTITY vs PRIMARY KEY — différences & complémentarité

• IDENTITY = mécanisme de génération de valeurs (par défaut) pour une colonne.
• PRIMARY KEY = contrainte d’unicité + not null qui identifie de façon unique chaque ligne.
• Une colonne IDENTITY n’est pas automatiquement clé primaire : il faut poser la contrainte PK.

-- Bon pattern : IDENTITY + contrainte de clé primaire
create table personne (
  id  bigint generated always as identity primary key,
  nom text not null,
  age int  not null
);

-- Alternative si vous séparez :
create table personne (
  id  bigint generated by default as identity,
  nom text not null,
  age int  not null,
  constraint personne_pk primary key (id)
);

Contraintes EXCLUDE USING (PostgreSQL)

• Les contraintes EXCLUDE permettent d'empêcher les chevauchements ou conflits complexes entre enregistrements.
• Contrairement à UNIQUE, elles utilisent des opérateurs de comparaison personnalisés (=, &&, overlaps, etc.).
• Nécessitent l'extension btree_gist pour fonctionner avec des types comme les dates.

CREATE EXTENSION IF NOT EXISTS btree_gist;

-- Empêcher les chevauchements de séjours pour un même client
ALTER TABLE sejour ADD CONSTRAINT sejour_no_overlap_per_client
  EXCLUDE USING gist (
    idc WITH =,           -- même client
    daterange(debut, fin, '[]') WITH &&  -- périodes qui se chevauchent
  );

Cette contrainte empêche un client d'avoir deux séjours dont les dates se chevauchent, mais autorise des clients différents à avoir des séjours aux mêmes dates.

Contraintes DEFERRABLE (Validation différée)

• Les contraintes DEFERRABLE permettent de reporter la vérification à la fin de la transaction.
• Utile pour les mises à jour complexes qui violent temporairement les contraintes durant la transaction.
• Deux modes : INITIALLY IMMEDIATE (par défaut) ou INITIALLY DEFERRED.

-- Création d'une contrainte deferrable
ALTER TABLE sejour ADD CONSTRAINT sejour_no_overlap_per_client
  EXCLUDE USING gist (idc WITH =, periode WITH &&)
  DEFERRABLE INITIALLY IMMEDIATE;

-- Utilisation dans une transaction
BEGIN;
  SET CONSTRAINTS sejour_no_overlap_per_client DEFERRED;
  
  -- Mises à jour qui violent temporairement la contrainte
  UPDATE sejour SET debut = '2025-01-15' WHERE ids = 1;
  UPDATE sejour SET debut = '2025-01-10' WHERE ids = 2;
  
COMMIT; -- Vérification des contraintes ici

Les contraintes différées sont essentielles pour les opérations de maintenance ou les "swaps" de données qui nécessitent plusieurs étapes.

Migration progressive des contraintes (NOT VALID → VALIDATE)

• Permet d'ajouter des contraintes sur des tables en production sans downtime.
• Étape 1 : Ajouter la contrainte avec NOT VALID (pas de vérification des données existantes).
• Étape 2 : Corriger les données invalides.
• Étape 3 : Valider la contrainte avec VALIDATE CONSTRAINT.

-- Étape 1 : Ajout sans validation (rapide, pas de lock)
ALTER TABLE client 
  ADD CONSTRAINT chk_age_valid 
  CHECK (age BETWEEN 0 AND 120) NOT VALID;

-- Étape 2 : Diagnostic et correction
SELECT * FROM client WHERE age < 0 OR age > 120;
UPDATE client SET age = 0 WHERE age < 0;

-- Étape 3 : Validation (vérifie toutes les données)
ALTER TABLE client VALIDATE CONSTRAINT chk_age_valid;

Cette approche permet d'éviter les locks prolongés sur les grandes tables en production. Les nouvelles données respectent immédiatement la contrainte.

Types de données avancés et personnalisés

• DOMAIN : Type personnalisé avec contraintes intégrées pour la réutilisabilité.
• ENUM : Type énuméré pour limiter les valeurs possibles.
• Colonnes générées : Calculs automatiques basés sur d'autres colonnes.

-- Domaine avec contrainte réutilisable
CREATE DOMAIN euro AS NUMERIC(10,2) 
  CHECK (VALUE >= 0);

-- Type énuméré
CREATE TYPE activite_t AS ENUM (
  'surf', 'voile', 'kitesurf', 'plongée', 'rando', 'yoga'
);

-- Colonnes générées
ALTER TABLE sejour
  ADD COLUMN periode daterange
  GENERATED ALWAYS AS (daterange(debut, fin, '[]')) STORED,
  ADD COLUMN nb_nuits int
  GENERATED ALWAYS AS (fin - debut) STORED;

-- Utilisation
ALTER TABLE village ALTER COLUMN prix TYPE euro;
ALTER TABLE village ALTER COLUMN activite TYPE activite_t;

Index partiels et contraintes conditionnelles

• Les index partiels appliquent des contraintes uniquement sur un sous-ensemble de données.
• Permettent des règles métier complexes comme "unicité seulement si actif".
• Plus performants que les index complets car ils sont plus petits.

-- Ajout d'une colonne de statut
ALTER TABLE sejour ADD COLUMN annule BOOLEAN NOT NULL DEFAULT FALSE;

-- Index partiel : unicité conditionnelle
CREATE UNIQUE INDEX uniq_client_debut_non_annule
  ON sejour(idc, debut)
  WHERE annule = FALSE;

-- Test : deux séjours à la même date
INSERT INTO sejour (idc, idv, debut, fin, annule) 
VALUES (1, 1, '2025-01-01', '2025-01-05', TRUE);   -- OK (annulé)

INSERT INTO sejour (idc, idv, debut, fin, annule) 
VALUES (1, 1, '2025-01-01', '2025-01-05', FALSE);  -- OK (différent statut)

INSERT INTO sejour (idc, idv, debut, fin, annule) 
VALUES (1, 1, '2025-01-01', '2025-01-05', FALSE);  -- ERREUR (doublon actif)

Cet index permet d'avoir plusieurs séjours annulés à la même date, mais un seul séjour actif par client et par date de début.

Bonnes pratiques : Nommage et documentation

• Convention de nommage claire et cohérente pour toutes les contraintes.
• Documentation explicite avec COMMENT pour faciliter la maintenance.
• Renommage des contraintes auto-générées pour plus de lisibilité.

-- Renommage des contraintes avec convention
ALTER TABLE client RENAME CONSTRAINT client_pkey 
  TO pk_client_idc;

-- Ajout avec nommage explicite
ALTER TABLE client ADD CONSTRAINT ck_client_age_valid 
  CHECK (age BETWEEN 0 AND 120);

-- Documentation
COMMENT ON CONSTRAINT ck_client_age_valid ON client 
  IS 'Âge doit être compris entre 0 et 120 ans';

COMMENT ON CONSTRAINT fk_sejour_client ON sejour 
  IS 'Référence vers la table client avec suppression en cascade';

-- Vérification des contraintes
SELECT 
  conname AS nom_contrainte,
  contype AS type_contrainte,
  obj_description(oid) AS description
FROM pg_constraint 
WHERE conrelid = 'client'::regclass;