Cet article commence une série présentant des fonctionnalités du langage SQL disponibles dans PostgreSQL. Vous pouvez retrouver une comparaison de la disponibilité des fonctionnalités ici: http://www.sql-workbench.eu/dbms_comparison.html. Ces fonctionnalités ne sont pas forcément présentes dans la norme SQL.

Ce premier article présente une fonctionnalité très utile lorsqu’on souhaite manipuler des données dont le type est plus complexe qu’un simple scalaire : la contrainte d’exclusion.

Le but de cette fonctionnalité est de vérifier que les données qu’on insère dans une table ne sont pas redondantes, qu’il n’y a pas de doublons.

La contrainte d’unicité

Pour ceci, il existe une fonctionnalité très courante lorsqu’on utilise une base de données relationnelle : la contrainte d’unicité.

Une contrainte d’unicité garantit l’unicité des données contenues dans une colonne ou un groupe de colonnes par rapport à toutes les lignes de la table. Elle n’autorise donc pas l’insertion de doublon dans une table, ce qui permet de s’assurer de la qualité des données.

Par exemple, la table suivante permet de connaitre l’ensemble des plages d’adresses IPs utiles :

CREATE TABLE reseau
(
  id int GENERATED BY DEFAULT AS IDENTITY primary key
  , adresses ip4r
  , commentaire text
);

L’attribut adresses contient donc les adresses des réseaux, avec le type de données ip4r fourni par l’extension du même nom : https://github.com/RhodiumToad/ip4r.

Si on veut éviter d’enregistrer des doublons, il est alors nécessaire d’ajouter une contrainte d’unicité :

ALTER TABLE reseau ADD UNIQUE ( adresses ) ;

Cet ordre crée un index btree d’unicité (on parle aussi d’index unique, mais ce n’est pas l’index qui est unique), mettant en œuvre la contrainte. Il n’est alors plus possible d’ajouter de plage d’adresse IP en doublon :

INSERT INTO reseau (adresses)
  VALUES ('192.168.122.0/24')
       , ('192.168.89.0/24');
INSERT 0 2

INSERT INTO reseau (adresses)
  VALUES ('192.168.122.0/24');
ERROR:  duplicate key value violates unique constraint "reseau_adresses_key"
DETAIL:  Key (adresses)=(192.168.122.0/24) already exists.

Mais si on ajoute un réseau qui interfère avec une entrée existante, sans être strictement égal, que se passe-t-il ?

INSERT INTO reseau (adresses)
  VALUES ('192.168.122.0/28');
INSERT 0 1

On constate que l’entrée est valide, alors qu’il y a potentiellement un problème d’intégrité de nos données : la plage d’adresses IP 192.168.122.0/28 est en réalité comprise dans la plage 192.168.122.0/24, et il y a donc un problème fonctionnel.

La raison est simple : la contrainte d’unicité ne sait utiliser que l’opérateur d'égalité (=). Pour constater le conflit d’adresses réseau, il nous faut utiliser l’opérateur de chevauchement : &&.

La contrainte d’exclusion

On pourrait simplement résumer la contrainte d’exclusion comme étant une extension de la contrainte d’unicité, avec un opérateur de comparaison différent de l'égalité.

L’opérateur de comparaison doit être commutatif, indexable (btree ou gist) et renvoyer un booléen (le type d’index gin n’est pas supporté).

Dans le cas de notre exemple, l’opérateur de comparaison && fourni par l’extension ip4r remplit les conditions, grâce à la classe d’opérateurs gist_ip4r_ops autorisant le parcours d’un index gist.

La création de la table est alors :

CREATE TABLE reseau
(
  id int GENERATED BY DEFAULT AS IDENTITY primary key
  , adresses ip4r
  , commentaire text
);

Et la création de la contrainte d’exclusion est :

ALTER TABLE reseau
  ADD CONSTRAINT reseau_adresses_excl EXCLUDE
    USING gist
    ( adresses WITH && );

Comme pour la contrainte d’unicité, la contrainte crée implicitement un index, ici de type gist, précisé avec le mot-clé USING.

Le mot-clé EXCLUDE permet donc de préciser le type de contrainte, et le mot-clé WITH indique l’opérateur utilisé.

Ensuite, l’insertion de données permet de mettre en évidence le contrôle d’intégrité :

INSERT INTO reseau (adresses)
  VALUES ('192.168.122.0/24'), ('192.168.89.0/24');
INSERT 0 2

INSERT INTO reseau (adresses)
  VALUES ('192.168.122.0/24');
ERROR:  conflicting key value violates
        exclusion constraint "reseau_adresses_excl"
DETAIL:  Key (adresses)=(192.168.122.0/24) conflicts with
         existing key (adresses)=(192.168.122.0/24).

INSERT INTO reseau (adresses)
  VALUES ('192.168.122.0/28');
ERROR:  conflicting key value violates
        exclusion constraint "reseau_adresses_excl"
DETAIL:  Key (adresses)=(192.168.122.0/28) conflicts with
         existing key (adresses)=(192.168.122.0/24).

On comprend aisément en lisant les messages d’erreurs que la contrainte d’exclusion permet de couvrir les fonctionnalités de la contrainte d’unicité, et de mettre un œuvre une contrainte d’intégrité liée à la nature de la donnée (non-scalaire), implémentée par un opérateur spécifique (&&).

Conclusion

La notion de contrainte d’exclusion est un outil puissant pour assurer l’intégrité de données : une donnée non scalaire avec un opérateur autre que la simple égalité est alors stockée en toute sérénité.

Le prochain article de la série présentera une famille de type de données ressemblant à ip4r : les plages de valeurs. Et les contraintes d’exclusion seront à l'œuvre !