CREATE DOMAIN — Définir un nouveau domaine
CREATE DOMAINnom
[AS]type_donnee
[ COLLATEcollation
] [ DEFAULTexpression
] [contrainte
[ ... ] ] oùcontrainte
est : [ CONSTRAINTnom_contrainte
] { NOT NULL | NULL | CHECK (expression
) }
CREATE DOMAIN
crée un nouveau domaine. Un domaine est
essentiellement un type de données avec des contraintes optionnelles
(restrictions sur l'ensemble de valeurs autorisées). L'utilisateur qui
définit un domaine devient son propriétaire.
Si un nom de schéma est donné (par exemple, CREATE DOMAIN
monschema.mondomaine ...
), alors le domaine est créé dans le
schéma spécifié. Sinon, il est créé dans le schéma courant. Le nom du
domaine doit être unique parmi les types et domaines existant dans son
schéma.
Les domaines permettent d'extraire des contraintes communes à plusieurs tables et de les regrouper en un seul emplacement, ce qui en facilite la maintenance. Par exemple, plusieurs tables pourraient contenir des colonnes d'adresses email, toutes nécessitant la même contrainte de vérification (CHECK) permettant de vérifier que le contenu de la colonne est bien une adresse email. Définissez un domaine plutôt que de configurer la contrainte individuellement sur chaque table.
Pour pouvoir créer un domaine, vous devez avoir le droit
USAGE
sur le type sous-jacent.
nom
Le nom du domaine à créer (éventuellement qualifié du nom du schéma).
type_donnees
Le type de données sous-jacent au domaine. Il peut contenir des spécifications de tableau.
collation
Un collationement optionnel pour le domaine. Si aucun collationnement n'est spécifié, le domaine a le même comportement pour le collationement que son type sous-jacent.
DEFAULT expression
La clause DEFAULT
permet de définir une valeur par
défaut pour les colonnes d'un type de données du domaine. La valeur est
une expression quelconque sans variable (les sous-requêtes ne sont pas
autorisées). Le type de données de l'expression par défaut doit
correspondre à celui du domaine. Si la valeur par défaut n'est pas
indiquée, alors il s'agit de la valeur NULL.
L'expression par défaut est utilisée dans toute opération d'insertion qui ne spécifie pas de valeur pour cette colonne. Si une valeur par défaut est définie sur une colonne particulière, elle surcharge toute valeur par défaut du domaine. De même, la valeur par défaut surcharge toute valeur par défaut associée au type de données sous-jacent.
CONSTRAINT nom_contrainte
Un nom optionnel pour une contrainte. S'il n'est pas spécifié, le système en engendre un.
NOT NULL
Les valeurs de ce domaine sont protégées comme les valeurs NULL. Cependant, voir les notes ci-dessous.
NULL
Les valeurs de ce domaine peuvent être NULL. C'est la valeur par défaut.
Cette clause a pour seul but la compatibilité avec les bases de données SQL non standard. Son utilisation est découragée dans les applications nouvelles.
CHECK (expression
)
Les clauses CHECK
spécifient des contraintes
d'intégrité ou des tests que les valeurs du domaine doivent satisfaire.
Chaque contrainte doit être une expression produisant un résultat
booléen. VALUE
est obligatoirement utilisé pour se
référer à la valeur testée. Les expressions qui renvoient TRUE ou
UNKNOWN réussissent. Si l'expression produit le résultat FALSE, une
erreur est rapportée et la valeur n'est pas autorisée à être convertie
dans le type du domaine.
Actuellement, les expressions CHECK
ne peuvent ni
contenir de sous-requêtes ni se référer à des variables autres que
VALUE
.
Quand un domaine dispose de plusieurs contraintes
CHECK
, elles seront testées dans l'ordre alphabétique
de leur nom. (Les versions de PostgreSQL
antérieures à la 9.5 n'utilisaient pas un ordre particulier pour la
vérification des contraintes CHECK
.)
Les contraintes de domaine, tout particulièrement NOT
NULL
, sont vérifiées lors de la conversion d'une valeur vers le
type du domaine. Il est possible qu'une colonne du type du domaine soit lue
comme un NULL bien qu'il y ait une contrainte spécifiant le contraire. Par
exemple, ceci peut arriver dans une requête de jointure externe si la
colonne de domaine est du côté de la jointure qui peut être NULL. En voici
un exemple :
INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));
Le sous-SELECT vide produira une valeur NULL qui est considéré du type du domaine, donc aucune vérification supplémentaire de la contrainte n'est effectuée, et l'insertion réussira.
Il est très difficile d'éviter de tels problèmes car l'hypothèse générale
du SQL est qu'une valeur NULL est une valeur valide pour tout type de
données. Une bonne pratique est donc de concevoir les contraintes du
domaine pour qu'une valeur NULL soit acceptée, puis d'appliquer les
contraintes NOT NULL
aux colonnes du type du domaine
quand cela est nécessaire, plutôt que de l'appliquer au type du domaine
lui-même.
PostgreSQL suppose que les conditions
des contraintes CHECK
sont immuables, c'est-à-dire
qu'elles produisent toujours les mêmes résultats pour les mêmes valeurs
d'entrée. Cette supposition justifie que l'examen des contraintes
CHECK
est effectué seulement quand une valeur est
initialement convertie vers le type domaine, et pas à d'autres moments.
(C'est essentiellement le même traitement que les contraintes
CHECK
s'appliquant aux tables, comme décrit dans
Section 5.4.1.)
Un exemple typique contrevenant à cette supposition consiste à faire
référence à une fonction définie par l'utilisateur dans l'expression
CHECK
, puis de modifier le comportement de cette fonction.
PostgreSQL n'interdit pas cela,
mais il ne pourra pas remarquer qu'il y a des valeurs stockées dans
le type du domaine qui seraient en violation de la contrainte CHECK
.
Cette situation peut ainsi provoquer l'échec du rechargement d'une
sauvegarde faite par export. La méthode recommandée pour mener à bien
ce type de changement consiste à supprimer la contrainte (en utilisant
ALTER DOMAIN
), à changer la définition de la fonction,
puis à remettre la contrainte, ce qui la testera sur les données stockées.
Une bonne pratique revient à s'assurer que les expressions
CHECK
de domaines ne renvoient pas d'erreurs.
Créer le type de données code_postal_us
, et l'utiliser dans la
définition d'une table. Un test d'expression rationnelle est utilisé pour
vérifier que la valeur ressemble à un code postal US valide :
CREATE DOMAIN code_postal_us AS TEXT CHECK( VALUE ~ '^\d{5}$' OR VALUE ~ '^\d{5}-\d{4}$' ); CREATE TABLE courrier_us ( id_adresse SERIAL PRIMARY KEY, rue1 TEXT NOT NULL, rue2 TEXT, rue3 TEXT, ville TEXT NOT NULL, code_postal code_postal_us NOT NULL );
La commande CREATE DOMAIN
est conforme au standard SQL.