Contraintes et expressions régulières avec PostgreSQL

Les contraintes dans les bases de données sont importantes pour l'intégrité des données. Elles sont les derniers remparts pour n'avoir que des données fiables, cohérente et pertinente dans sa base de données. Il existe une manière assez efficace mais souvent oubliée pour contraindre le système : Les expressions régulières ou regex.

Syntaxe

Une que l'on connaît bien est la contrainte NOT NULL mais il existe d'autres contraintes.

Si vous ne connaissez pas la syntaxe des contraintes avec PostgreSQL, elle est assez directe:

CREATE TABLE produit (
    id integer NOT NULL,
    nom text NOT NULL,
    prix numeric,
    CHECK (prix > 0),
    prix_reduit numeric,
    CHECK (prix_reduit > 0),
    CHECK (prix > prix_reduit)
);

On voit ici que le prix réduit ne doit pas dépasser le prix normal et que le prix. De plus, le prix et le prix réduit doivent être supérieur à zéro.

On peut ajouter CONSTRAINT qui ajoutera un libellé au message d'erreur plus explicite.

CREATE TABLE produit (
    id integer,
    nom text,
    prix numeric CONSTRAINT prix_positif CHECK (prix > 0),
    prix_reduit numeric CONSTRAINT prix_reduit_positif CHECK (prix_reduit > 0),
    CONSTRAINT prix_reduit_valide CHECK (prix > prix_reduit)
);

L'ordre n'est pas important. On peut ajouter toutes les contraintes à la fin.

Les expressions régulières

Il est donc possible d'ajouter des contraintes avec des expressions régulières avec le caractère " ~ ". Ainsi il est possible d'écrire :

CREATE TABLE client (
 id integer,
 nom varchar,
    prenom varchar,
    email varchar
    CONSTRAINT email_correcte CHECK (email ~* '^[A-Za-z0-9._-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$')
);

Cette expression régulière permet de vérifier si l'adresse email est valide. C'est-à-dire contient que les caractères alpha numérique, points, tirets (bas ou haut).Ensuite, l'adresse doit contenir une arobase, un nom de domaine valide, etc...

Conclusion

Ces contraintes permettent de contrôler et de valider les données entrées dans la base de données.

Il existe des sites pour tester les expressions régulières (RegexPlanet) et des expressions prêtent à être utilisées (GitHub, Regular Expressions info) comme l'email, le numéro de téléphone, l'IP, etc...

Les expressions régulières sont un peu longues parfois et difficilement lisibles. Si c'est le cas pour certaine, vous pouvez utiliser des procédures stockées qui vérifient la validité de l'argument avec l'expression régulière et renvoie un booléen.