Les types de données sont un moyen de restreindre la nature des données qui peuvent être stockées dans une table. Pour beaucoup d'applications, toutefois, la contrainte fournie par ce biais est trop grossière. Par exemple, une colonne qui contient le prix d'un produit ne doit accepter que des valeurs positives. Mais il n'existe pas de type de données standard qui n'accepte que des valeurs positives. Un autre problème peut provenir de la volonté de contraindre les données d'une colonne par rapport aux autres colonnes ou lignes. Par exemple, dans une table contenant des informations de produit, il ne peut y avoir qu'une ligne par numéro de produit.
Pour cela, SQL permet de définir des contraintes sur les colonnes et les tables. Les contraintes donnent autant de contrôle sur les données des tables qu'un utilisateur peut le souhaiter. Si un utilisateur tente de stocker des données dans une colonne en violation d'une contrainte, une erreur est levée. Cela s'applique même si la valeur vient de la définition de la valeur par défaut.
La contrainte de vérification est la contrainte la plus générique qui soit. Elle permet d'indiquer que la valeur d'une colonne particulière doit satisfaire une expression booléenne (valeur de vérité). Par exemple, pour obliger les prix des produits à être positifs, on peut utiliser :
CREATE TABLE produits (
no_produit integer,
nom text,
prix numeric CHECK (prix > 0)
);
La définition de contrainte vient après
le type de données, comme pour les définitions de valeur par défaut. Les
valeurs par défaut et les contraintes peuvent être données dans
n'importe quel ordre. Une contrainte de vérification s'utilise avec
le mot-clé CHECK
suivi d'une expression entre
parenthèses. L'expression de la contrainte implique habituellement la
colonne à laquelle elle s'applique, la contrainte n'ayant dans le cas
contraire que peu de sens.
La contrainte peut prendre un nom distinct. Cela clarifie les messages d'erreur et permet de faire référence à la contrainte lorsqu'elle doit être modifiée. La syntaxe est :
CREATE TABLE produits (
no_produit integer,
nom text,
prix numeric CONSTRAINT prix_positif CHECK (prix > 0)
);
Pour indiquer une contrainte nommée, on utilise le mot-clé
CONSTRAINT
suivi d'un identifiant et de la
définition de la contrainte (si aucun nom n'est précisé,
le système en choisit un).
Une contrainte de vérification peut aussi faire référence à plusieurs colonnes. Dans le cas d'un produit, on peut vouloir stocker le prix normal et un prix réduit en s'assurant que le prix réduit soit bien inférieur au prix normal.
CREATE TABLE produits (
no_produit integer,
nom text,
prix numeric CHECK (prix > 0),
prix_promotion numeric CHECK (prix_promotion > 0),
CHECK (prix > prix_promotion)
);
Si les deux premières contraintes n'offrent pas de nouveauté, la troisième utilise une nouvelle syntaxe. Elle n'est pas attachée à une colonne particulière, mais apparaît comme un élément distinct dans la liste des colonnes. Les définitions de colonnes et ces définitions de contraintes peuvent être définies dans un ordre quelconque.
Les deux premières contraintes sont appelées contraintes de colonne, tandis que la troisième est appelée contrainte de table parce qu'elle est écrite séparément d'une définition de colonne particulière. Les contraintes de colonne peuvent être écrites comme des contraintes de table, mais l'inverse n'est pas forcément possible puisqu'une contrainte de colonne est supposée ne faire référence qu'à la colonne à laquelle elle est attachée (PostgreSQL ne vérifie pas cette règle, mais il est préférable de la suivre pour s'assurer que les définitions de tables fonctionnent avec d'autres systèmes de bases de données). L'exemple ci-dessus peut aussi s'écrire :
CREATE TABLE produits ( no_produit integer, nom text, prix numeric, CHECK (prix > 0), prix_promotion numeric, CHECK (prix_promotion > 0), CHECK (prix > prix_promotion) );
ou même :
CREATE TABLE produits ( no_produit integer, nom text, prix numeric CHECK (prix > 0), prix_promotion numeric, CHECK (prix_promotion > 0 AND prix > prix_promotion) );
C'est une question de goût.
Les contraintes de table peuvent être nommées, tout comme les contraintes de colonne :
CREATE TABLE produits (
no_produit integer,
nom text,
prix numeric,
CHECK (prix > 0),
prix_promotion numeric,
CHECK (prix_promotion > 0),
CONSTRAINT promo_valide CHECK (prix > prix_promotion)
);
Une contrainte de vérification est satisfaite si l'expression est évaluée vraie ou NULL. Puisque la plupart des expressions sont évaluées NULL si l'un des opérandes est nul, elles n'interdisent pas les valeurs NULL dans les colonnes contraintes. Pour s'assurer qu'une colonne ne contient pas de valeurs NULL, la contrainte NOT NULL décrite dans la section suivante peut être utilisée.
PostgreSQL ne supporte pas les contraintes
CHECK
qui référencent les données d'autres tables que
celle contenant la nouvelle ligne ou la ligne mise à jour en cours de
vérification. Alors qu'une contrainte CHECK
qui viole
cette règle pourrait apparaitre fonctionner dans des tests simples, il
est possible que la base de données atteigne un état dans lequel la
condiction de la contrainte est fausse (à cause de changements
supplémentaires en dehors de la ligne impliquée). Ceci sera la cause d'un
échec du rechargement de la sauvegarde d'une base. La restauration
pourrait échouer même quand l'état complet de la base est cohérent avec
la contrainte, à cause de lignes chargées dans un autre différent qui
satisferait la contrainte. Si possible, utilisez les contraintes
UNIQUE
, EXCLUDE
, et
FOREIGN KEY
pour exprimer des restrictions
inter-lignes et inter-tables.
Si ce que vous désirez est une vérification unique avec certaines lignes au moment de l'insertion, plutôt qu'une garantie de cohérence maintenue en permanence, un trigger personnalisé peut être utilisé pour l'implémenter. (Cette approche évite le problème de sauvegarde/restauration car pg_dump ne réinstalle les triggers qu'après chargement des données, donc cette vérification ne sera pas effectuée pendant une sauvegarde/restauration.)
PostgreSQL suppose que les conditions des
contraintes CHECK
sont immutables, c'est-à-dire
qu'elles donneront toujours le même résultat pour la même ligne en
entrée. Cette supposition est ce qui justifie l'examen des contraintes
CHECK
uniquement quand les lignes sont insérées ou
mises à jour, et non pas à d'autres moments. (Cet avertissement sur la
non référence aux données d'autres tables est en fait un cas particulier
de cette restriction.)
Un exemple d'une façon habituelle de casser cette supposition est de
référencer une fonction utilisateur dans une expression
CHECK
, puis de changer le comportement de cette
fonction. PostgreSQL n'interdit pas cela, mais
il ne notera pas qu'il y a des lignes dans la table qui violent
maintenant la contrainte CHECK
. Ceci sera la cause
d'un échec de la restauration d'une sauvegarde de cette base. La façon
recommandée de gérer de tels changements revient à supprimer la
contrainte (en utilisant ALTER TABLE
), d'ajuster la
définition de la fonction, et d'ajouter de nouveau la contrainte, ce qui
causera une nouvelle vérification des lignes de la table.
Une contrainte NOT NULL indique simplement qu'une colonne ne peut pas prendre la valeur NULL. Par exemple :
CREATE TABLE produits ( no_produit integer NOT NULL, nom text NOT NULL, prix numeric );
Une contrainte NOT NULL est toujours écrite comme une contrainte de
colonne. Elle est fonctionnellement équivalente à la création d'une
contrainte de vérification CHECK (
. Toutefois, dans PostgreSQL,
il est plus efficace de créer explicitement une contrainte NOT NULL.
L'inconvénient est que les contraintes de non-nullité ainsi créées ne
peuvent pas être explicitement nommées.
nom_colonne
IS NOT NULL)
Une colonne peut évidemment avoir plusieurs contraintes. Il suffit d'écrire les contraintes les unes après les autres :
CREATE TABLE produits ( no_produit integer NOT NULL, nom text NOT NULL, prix numeric NOT NULL CHECK (prix > 0) );
L'ordre n'a aucune importance. Il ne détermine pas l'ordre de vérification des contraintes.
La contrainte NOT NULL
a un contraire ; la contrainte
NULL
. Elle ne signifie pas que la colonne doit
être NULL, ce qui est assurément inutile, mais sélectionne le comportement
par défaut, à savoir que la colonne peut être NULL. La contrainte
NULL
n'est pas présente dans le standard SQL et ne doit pas
être utilisée dans des applications portables (elle n'a été ajoutée
dans PostgreSQL que pour assurer la
compatibilité avec d'autres bases de données). Certains utilisateurs
l'apprécient néanmoins, car elle permet de basculer aisément d'une
contrainte à l'autre dans un fichier de script. On peut, par exemple, commencer avec :
CREATE TABLE produits ( no_produit integer NULL, nom text NULL, prix numeric NULL );
puis insérer le mot-clé NOT
en fonction des besoins.
Dans la plupart des bases de données, il est préférable que la majorité des colonnes soient marquées NOT NULL.
Les contraintes d'unicité garantissent l'unicité des données contenues dans une colonne ou un groupe de colonnes par rapport à toutes les lignes de la table. La syntaxe est :
CREATE TABLE produits (
no_produit integer UNIQUE,
nom text,
prix numeric
);
lorsque la contrainte est écrite comme contrainte de colonne et :
CREATE TABLE produits (
no_produit integer,
nom text,
prix numeric,
UNIQUE (no_produit)
);
lorsqu'elle est écrite comme contrainte de table.
Pour définir une contrainte unique pour un groupe de colonnes, saisissez- la en tant que contrainte de table avec les noms des colonnes séparés par des virgules :
CREATE TABLE exemple (
a integer,
b integer,
c integer,
UNIQUE (a, c)
);
Cela précise que la combinaison de valeurs dans les colonnes indiquées est unique sur toute la table. Sur une colonne prise isolément, ce n'est pas nécessairement le cas (et habituellement cela ne l'est pas).
Une contrainte d'unicité peut être nommée, de la façon habituelle :
CREATE TABLE produits (
no_produit integer CONSTRAINT doit_etre_different UNIQUE,
nom text,
prix numeric
);
Ajouter une contrainte unique va automatiquement créer un index unique B-tree sur la colonne ou le groupe de colonnes listées dans la contrainte. Une restriction d'unicité couvrant seulement certaines lignes ne peut pas être écrite comme une contrainte unique, mais il est possible de forcer ce type de restriction en créant un index partiel unique.
En général, une contrainte d'unicité est violée si plus d'une ligne de la table possède des valeurs identiques sur toutes les colonnes de la contrainte. En revanche, deux valeurs NULL ne sont jamais considérées égales. Cela signifie qu'il est possible de stocker des lignes dupliquées contenant une valeur NULL dans au moins une des colonnes contraintes. Ce comportement est conforme au standard SQL, mais d'autres bases SQL n'appliquent pas cette règle. Il est donc préférable d'être prudent lors du développement d'applications portables.
Une contrainte de type clé primaire indique qu'une colonne, ou un groupe de colonnes, peuvent être utilisés comme un identifiant unique de ligne pour cette table. Ceci nécessite que les valeurs soient à la fois uniques et non NULL. Les définitions de table suivantes acceptent de ce fait les mêmes données :
CREATE TABLE produits ( no_produit integer UNIQUE NOT NULL, nom text, prix numeric );
CREATE TABLE produits (
no_produit integer PRIMARY KEY,
nom text,
prix numeric
);
Les clés primaires peuvent également contraindre plusieurs colonnes ; la syntaxe est semblable aux contraintes d'unicité :
CREATE TABLE exemple (
a integer,
b integer,
c integer,
PRIMARY KEY (a, c)
);
Ajouter une clé primaire créera automatiquement un index unique B-tree
sur la colonne ou le groupe de colonnes listés dans la clé primaire, et
forcera les colonnes à être marquées NOT NULL
.
L'ajout d'une clé primaire créera automatiquement un index B-tree unique sur la colonne ou le groupe de colonnes utilisé dans la clé primaire.
Une table a, au plus, une clé primaire. (Le nombre de contraintes UNIQUE NOT NULL, qui assurent pratiquement la même fonction, n'est pas limité, mais une seule peut être identifiée comme clé primaire.) La théorie des bases de données relationnelles impose que chaque table ait une clé primaire. Cette règle n'est pas forcée par PostgreSQL, mais il est préférable de la respecter.
Les clés primaires sont utiles pour la documentation et pour les applications clientes. Par exemple, une application graphique qui permet la modifier des valeurs des lignes a probablement besoin de connaître la clé primaire d'une table pour être capable d'identifier les lignes de façon unique. Le système de bases de données utilise une clé primaire de différentes façons. Par exemple, la clé primaire définit les colonnes cibles par défaut pour les clés étrangères référençant cette table.
Une contrainte de clé étrangère stipule que les valeurs d'une colonne (ou d'un groupe de colonnes) doivent correspondre aux valeurs qui apparaissent dans les lignes d'une autre table. On dit que cela maintient l'intégrité référentielle entre les deux tables.
Soit la table de produits, déjà utilisée plusieurs fois :
CREATE TABLE produits ( no_produit integer PRIMARY KEY, nom text, prix numeric );
Soit également une table qui stocke les commandes de ces produits. Il est intéressant de s'assurer que la table des commandes ne contient que des commandes de produits qui existent réellement. Pour cela, une contrainte de clé étrangère est définie dans la table des commandes qui référence la table produits :
CREATE TABLE commandes (
id_commande integer PRIMARY KEY,
no_produit integer REFERENCES produits (no_produit),
quantite integer
);
Il est désormais impossible de créer des commandes pour lesquelles
les valeurs non NULL de no_produit
n'apparaissent pas dans la table
produits.
Dans cette situation, on dit que la table des commandes est la table qui référence et la table des produits est la table référencée. De la même façon, il y a des colonnes qui référencent et des colonnes référencées.
La commande précédente peut être raccourcie en
CREATE TABLE commandes (
id_commande integer PRIMARY KEY,
no_produit integer REFERENCES produits,
quantite integer
);
parce qu'en l'absence de liste de colonnes, la clé primaire de la table de référence est utilisée comme colonne de référence.
Une contrainte de clé étrangère peut être nommée de la façon habituelle.
Une clé étrangère peut aussi contraindre et référencer un groupe de colonnes. Comme cela a déjà été évoqué, il faut alors l'écrire sous forme d'une contrainte de table. Exemple de syntaxe :
CREATE TABLE t1 (
a integer PRIMARY KEY,
b integer,
c integer,
FOREIGN KEY (b, c) REFERENCES autre_table (c1, c2)
);
Le nombre et le type des colonnes contraintes doivent correspondre au nombre et au type des colonnes référencées.
Parfois, il est utile que l'« autre table » d'une clé étrangère soit la même table ; elle est alors appelée une clé étrangère auto-référencée. Par exemple, si vous voulez que les lignes d'une table représentent les nœuds d'une structure en arbre, vous pouvez écrire
CREATE TABLE tree ( node_id integer PRIMARY KEY, parent_id integer REFERENCES tree, name text, ... );
Un nœud racine aura la colonne parent_id
à
NULL, et les enregistrements non NULL de
parent_id
seront contraints de référencer
des enregistrements valides de la table.
Une table peut contenir plusieurs contraintes de clé étrangère. Les relation n-n entre tables sont implantées ainsi. Soit des tables qui contiennent des produits et des commandes, avec la possibilité d'autoriser une commande à contenir plusieurs produits (ce que la structure ci-dessus ne permet pas). On peut pour cela utiliser la structure de table suivante :
CREATE TABLE produits ( no_produit integer PRIMARY KEY, nom text, prix numeric ); CREATE TABLE commandes ( id_commande integer PRIMARY KEY, adresse_de_livraison text, ... ); CREATE TABLE commande_produits ( no_produit integer REFERENCES produits, id_commande integer REFERENCES commandes, quantite integer, PRIMARY KEY (no_produit, id_commande) );
La clé primaire de la dernière table recouvre les clés étrangères.
Les clés étrangères interdisent désormais la création de commandes qui ne sont pas liées à un produit. Qu'arrive-t-il si un produit est supprimé alors qu'une commande y fait référence ? SQL permet aussi de le gérer. Intuitivement, plusieurs options existent :
interdire d'effacer un produit référencé ;
effacer aussi les commandes ;
autre chose ?
Pour illustrer ce cas, la politique suivante est implantée sur l'exemple de relations n-n évoqué plus haut :
quand quelqu'un veut retirer un produit qui est encore
référencé par une commande
(au travers de commande_produits
), on
l'interdit ;
si quelqu'un supprime une commande, les éléments de la commande sont aussi supprimés.
CREATE TABLE produits ( no_produit integer PRIMARY KEY, nom text, prix numeric ); CREATE TABLE commandes ( id_commande integer PRIMARY KEY, adresse_de_livraison text, ... ); CREATE TABLE commande_produits ( no_produit integer REFERENCES produits ON DELETE RESTRICT, id_commande integer REFERENCES commandes ON DELETE CASCADE, quantite integer, PRIMARY KEY (no_produit, id_commande) );
Restreindre les suppressions et les réaliser en cascade sont les deux
options les plus communes. RESTRICT
empêche la
suppression d'une ligne référencée. NO ACTION
impose
la levée d'une erreur si des lignes référençant existent lors de la
vérification de la contrainte. Il s'agit du comportement par
défaut en l'absence de précision. La différence entre
RESTRICT
et NO ACTION
est l'autorisation par
NO ACTION
du report de la vérification à la fin de la
transaction, ce que RESTRICT
ne permet pas.
CASCADE
indique que, lors de la suppression d'une ligne
référencée, les lignes la référençant doivent être automatiquement
supprimées. Il existe deux autres options :
SET NULL
et SET DEFAULT
.
Celles-ci imposent que les colonnes qui référencent dans les lignes référencées
soient
réinitialisées à NULL ou à leur valeur par défaut, respectivement, lors
de la suppression d'une ligne référencée. Elles ne dispensent pas pour
autant d'observer les contraintes. Par exemple, si une action précise
SET DEFAULT
, mais que la valeur par défaut ne
satisfait pas la clé étrangère, l'opération échoue.
À l'instar de ON DELETE
existe ON
UPDATE
, évoqué lorsqu'une colonne référencée est modifiée
(actualisée). Les actions possibles sont les mêmes. Dans ce cas,
CASCADE
signifie que les valeurs mises à jour dans
la colonne référencée doivent être copiées dans les lignes de référence.
Habituellement, une ligne de référence n'a pas besoin de satisfaire la
clé étrangère si une de ses colonnes est NULL. Si la clause MATCH
FULL
est ajoutée à la déclaration de la clé étrangère, une ligne
de référence échappe à la clé étrangère seulement si toutes ses colonnes de
référence sont NULL (donc un mélange de valeurs NULL et non NULL échoue
forcément sur une contrainte MATCH FULL
). Si vous ne
voulez pas que les lignes de référence soient capables d'empêcher la
satisfaction de la clé étrangère, déclarez les colonnes de référence comme
NOT NULL
.
Une clé étrangère doit référencer les colonnes qui soit sont une clé primaire, soit forment une contrainte d'unicité. Cela signifie que les colonnes référencées ont toujours un index (celui qui garantit la clé primaire ou la contrainte unique). Donc les vérifications sur la ligne de référence seront performantes. Comme la suppression d'une ligne de la table référencée ou la mise à jour d'une colonne référencée nécessitera un parcours de la table référée pour trouver les lignes correspondant à l'ancienne valeur, il est souvent intéressant d'indexer les colonnes référencées. Comme cela n'est pas toujours nécessaire et qu'il y a du choix sur la façon d'indexer, l'ajout d'une contrainte de clé étrangère ne crée pas automatiquement un index sur les colonnes référencées.
Le Chapitre 6 contient de plus amples informations sur l'actualisation et la suppression de données. Voir aussi la description de la syntaxe des clés étrangères dans la documentation de référence sur CREATE TABLE.
Une clé étrangère peut faire référence à des colonnes qui constituent une clé primaire ou forment une contrainte d'unicité. Si la clé étrangère référence une contrainte d'unicité, des possibilités supplémentaires sont offertes concernant la correspondance des valeurs NULL. Celles-ci sont expliquées dans la documentation de référence de CREATE TABLE.
Les contraintes d'exclusion vous assurent que si deux lignes sont comparées sur les colonnes ou expressions spécifiées en utilisant les opérateurs indiqués, au moins une de ces comparaisons d'opérateurs renverra false ou NULL. La syntaxe est :
CREATE TABLE cercles ( c circle, EXCLUDE USING gist (c WITH &&) );
Voir aussi CREATE
TABLE ... CONSTRAINT ... EXCLUDE
pour plus de détails.
L'ajout d'une contrainte d'exclusion créera automatiquement un index du type spécifié dans la déclaration de la contrainte.