BD VII: gestion avancée des contraintes

Définition de données: Contraintes

Équipe BD

LPSM Université Paris-Cité

2025-10-24

Revisiter le schéma world

Schema de world

Modèle relationnel du schéma World

Conraintes sur la table `country`

Un pays est identifié par son countrycode,
Deux pays ne devraient pas avoir le même nom,
Une région ne devrait appartenir qu’à un seul continent,
La capitale d’un pays devrait être une ville connue dans city,
La capitale d’un pays devrait être une ville de ce pays,
Les capitales de deux pays différents devraient être des villes différentes,
…

Ce ne sont pas les seules contraintes possibles

Mise en œuvre des contraintes

Un pays est identifié par son countrycode : PRIMARY KEY
Deux pays ne devraient pas avoir le même nom : UNIQUE
Une région ne devrait appartenir qu’à un seul continent : ??? -> EXCLUDE
La capitale d’un pays devrait être une ville connue dans city : REFERENCES
La capitale d’un pays devrait être une ville de ce pays : ??? -> trigger
Les capitales de deux pays différents devraient être des villes différentes : UNIQUE
Deux pays différents ne peuvent avoir le même code2 : UNIQUE

Examen du schema de `country`

+--------------------+---------------+-----------+
| Column             | Type          | Modifiers | 
|--------------------+---------------+-----------+-
| countrycode        | character(3)  |  not null | 
| name_country       | text          |  not null | 
| continent          | text          |  not null | 
| region             | text          |  not null |
| surfacearea        | real          |  not null |
| indepyear          | smallint      |           | 
| population_country | integer       |  not null | 
| lifeexpectancy     | real          |           | 
| gnp                | numeric(10,2) |           | 
| gnpold             | numeric(10,2) |           | 
| localname          | text          |  not null | 
| governmentform     | text          |  not null | 
| headofstate        | text          |           | 
| capital            | integer       |           | 
| code2              | character(2)  |  not null | 
+--------------------+---------------+-----------+

Un schéma de table ne se réduit pas à la données des noms et domaines des colonnes.

suite

Indexes:
    "country_pkey" PRIMARY KEY, btree (countrycode)
Check constraints:
    "country_continent_check" CHECK (continent = 'Asia'::text OR 
                                     continent = 'Europe'::text OR 
                                     continent = 'North America'::text OR 
                                     continent = 'Africa'::text OR
                                     continent = 'Oceania'::text OR
                                     continent = 'Antarctica'::text OR
                                     continent = 'South America'::text),
Foreign-key constraints:
    "country_capital_fkey" FOREIGN KEY (capital) REFERENCES city(id)

DDL pour `country`

CREATE TABLE world.country (
    countrycode bpchar(3) NOT NULL,
    name_country text NOT NULL,
    continent text NOT NULL,
    region text NOT NULL,
    surfacearea float4 NOT NULL,
    indepyear int2 NULL,
    population_country int4 NOT NULL,
    lifeexpectancy float4 NULL,
    gnp numeric(10, 2) NULL,
    gnpold numeric(10, 2) NULL,
    localname text NOT NULL,
    governmentform text NOT NULL,
    headofstate text NULL,
    capital int4 NULL,
    code2 bpchar(2) NOT NULL,
    CONSTRAINT country_continent_check CHECK (((continent = 'Asia'::text) 
    OR (continent = 'Europe'::text) 
    OR (continent = 'North America'::text) 
    OR (continent = 'Africa'::text) 
    OR (continent = 'Oceania'::text)
    OR (continent = 'Antarctica'::text) 
    OR (continent = 'South America'::text))),
    CONSTRAINT country_pkey PRIMARY KEY (countrycode)
);

Ajout de contraintes référentielles

Une contrainte d’intégrité référentielle impose l’inclusion de l’ensemble des valeurs d’une colonne (la colone qui référence) à l’ensemble des valeurs d’une autre colonne (la colonne référencée).

La colonne référencée est quasiment toujours la clé primaire d’une autre table. On peut alors définir la contrainte en n’indiquant que la table référencée (car la clé primaire est unique) :

ALTER TABLE world.country ADD CONSTRAINT 
  country_capital_fkey FOREIGN KEY (capital) REFERENCES world.city;

Si la colonne référencée n’est pas une clé primaire, elle doit vérifier une contrainte d’unicité. Il faut alors préciser le nom de la table et le nom de la colonne référencée avec cette syntaxe : REFERENCES nom_table(nom_colonne)

Gestion de la mise à jour ou de la suppression d’un attribut référencé

Important

Après la définition de la contrainte country_capital_fkey, la mise à jour ou la suppression d’une valeur de l’attribut city.id référencée dans l’attribut country.capital est bloquée par le SGBD.

On peut définir différentes actions pour les rendre possibles :

CASCADE : supprime ou met à jour en cascade les lignes qui référencent la valeur supprimée ou mise à jour.

SET NULL : met à NULL la ou les colonnes des lignes qui référencent la valeur supprimée ou mise à jour.

SET DEFAULT : met à leur valeur par défaut la ou les colonnes des lignes qui référencent la valeur supprimée ou mise à jour.

Utilisation de l’action `CASCADE`

ALTER TABLE world.city 
  ADD CONSTRAINT city_countrycode_fkey 
    FOREIGN KEY (countrycode) 
    REFERENCES world.country
    ON DELETE CASCADE
    ON UPDATE CASCADE;

Si un countrycode est suprrimé dans la table country, toutes les lignes de la table city où ce code est présent seront supprimées, et si ces lignes sont référencées dans un autre table, les lignes qui les référencent seront elles-mêmes supprimées et ainsi de suite “en cascade”.

Si un countrycode est mis à jour dans la table country, toutes les lignes de la table city où ce code est présent seront mises à jour, et si ces mises à jour sont référencées dans un autre table, les lignes qui les référencent seront elles-mêmes mises à jour et ainsi de suite “en cascade”.

Utilisation de l’action `SET NULL`

ALTER TABLE world.city 
  ADD CONSTRAINT country_capital_fkey 
    FOREIGN KEY (capital) 
    REFERENCES world.city(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

Si un id est suprrimé dans la table city, les lignes de la table country où ce code est présent ne seront pas supprimées, la colonne capital sera mise à NULL. Cette action est possible car la valeur NULL est autorisée pour la colonne capital.

Contraintes d’exclusion

Une région n’appartient qu’à un seul continent

Cette contrainte signifie que le résultat de la requête suivante est vide :

SELECT *
FROM 
  world.country c1 
JOIN 
  world.country c2 ON (c1.continent<> c2.continent AND c1.region=c2.region);

On exclut l’existence de deux lignes qui coïncideraient sur region mais pas sur continent

Nous postulons l’existence d’une dépendance fonctionnelle

\[\texttt{region} \longrightarrow \texttt{continent}\]

Deux attitudes

Dans sa forme actuelle, la table country de world contient des informations redondantes: on répète plusieurs fois que la région Western Europe est située dans le continent Europe.

Ces redondances représentent un gaspillage de mémoire (très modeste ici) et un risque pour le maintien de la cohérence de la base : chaque insertion, mise-à-jour est susceptible de mettre en défaut la dépendance fonctionnelle.

On peut considérer qu’il s’agit d’une anomalie de schéma et normaliser le schéma.

La normalisation suppose une révision du schéma. Si le schéma est muni de vues et de fonctions, il faudra éventuellement réécrire ces vues et ces fonctions.

Autre possibilité avec une contrainte d’exclusion

Avec une contrainte d’exclusion (EXCLUDE), on ne touchera pas au schéma, les vues et les fonctions resteront utilisables.

La contrainte d’exclusion permettra de maintenir la dépendance fonctionnelle.

Les contraintes d’exclusion

Objectifs

Mise en place de dépendances fonctionnelles,
Mise en place de contraintes de non-recouvrement (données spatiales ou temporelles),
Mise en place de contraintes très diverses.

Clause EXCLUDE

Une clause EXCLUDE empêche que deux lignes vérifient toutes les comparaisons sur les colonnes spécifiées en utilisant les opérateurs indiqués par le mot clé WITH.

ALTER TABLE country
   ADD CONSTRAINT xcl_country_region_continent 
      EXCLUDE USING GIST
      (
         region WITH =, 
         continent WITH <>
      );

Cette contrainte d’exclusion empèche que deux lignes de la table country aient la colonne region égale et la colonne continent différente.

Note

La clause USING définit le type d’index utilisé pour vérifier la contrainte d’exclusion.

En pratique, le type d’index sera toujours GiST ou SP-GiST (GIST est l’acronyme de Generalized Search Tree).

BD VII: gestion avancée des contraintes

Revisiter le schéma world

Schema de world

Conraintes sur la table country

Mise en œuvre des contraintes

Examen du schema de country

suite

DDL pour country

Ajout de contraintes référentielles

Gestion de la mise à jour ou de la suppression d’un attribut référencé

Utilisation de l’action CASCADE

Utilisation de l’action SET NULL

Contraintes d’exclusion

Une région n’appartient qu’à un seul continent

Deux attitudes

Autre possibilité avec une contrainte d’exclusion

Les contraintes d’exclusion

Clause EXCLUDE

Fin

Conraintes sur la table `country`

Examen du schema de `country`

DDL pour `country`

Utilisation de l’action `CASCADE`

Utilisation de l’action `SET NULL`