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 d'une 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 vaut NULL, 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.

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 (nom_colonne IS NOT NULL). 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.

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.

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 d'unicité 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 d'unicité 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 d'unicité, 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. Par défaut, 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 peut être modifié en ajoutant la clause NULLS NOT DISTINCT, comme :

CREATE TABLE products (
    product_no integer UNIQUE NULLS NOT DISTINCT,
    name text,
    price numeric
);

ou :

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    UNIQUE NULLS NOT DISTINCT (product_no)
);

Le comportement par défaut peut être spécifié explicitement en utilisant NULLS DISTINCT. Le traitement par défaut des NULL dans les contraintes d'unicité est défini par l'implémentation suivant le standard SQL, alors que d'autres implémentations ont un comportement différent. Donc faites attention quand vous développez des applications qui ont pour but d'être 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 de 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 relations 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 :

Pour illustrer ce cas, la politique suivante est implantée sur l'exemple de relations n-n évoqué plus haut :

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.

Le choix approprié de l'action ON DELETE dépend du type d'objets que les tables ciblées représentent. Quand la table référençante représente quelque chose qui est un composant de ce qui est représenté par la table référencée et ne peut pas exister indépendamment, alors CASCADE pourrait être approprié. Si les deux tables représentent des objets indépendants, alors RESTRICT ou NO ACTION est plus approprié ; une application qui souhaite réellement supprimer les deux objets devra être explicite là-dessus et exécuter les deux commandes de suppression. Dans l'exemple ci-dessus, les éléments d'une commande font partie de cette commande, et il est pratique qu'ils soient supprimés automatiquement si une commande est supprimée. Mais les produits et les commandes sont deux choses différentes, et de ce fait, faire en sorte que supprimer un produit cause la suppression automatique de certains éléments de commandes peut être considéré comme problématique. Les actions SET NULL ou SET DEFAULT peuvent être appropriées si la relation d'une clé étrangère représente des informations supplémentaires. Par exemple, si la table produits contenait une référence à un gestionnaire de produit et que l'enregistrement correspondant à ce gestionnaire était supprimé, alors configurer le gestionnaire du produit à NULL ou à une valeur par défaut pourrait être utile.

Les actions SET NULL et SET DEFAULT peuvent prendre une liste de colonnes pour spécifier les colonnes à configurer. Normalement, toutes les colonnes de la clé étrangère sont configurés ; configurer uniquement un sous-ensemble est utile dans quelques cas spéciaux. Considérez l'exemple suivant :

CREATE TABLE tenants (
    tenant_id integer PRIMARY KEY
);

CREATE TABLE users (
    tenant_id integer REFERENCES tenants ON DELETE CASCADE,
    user_id integer NOT NULL,
    PRIMARY KEY (tenant_id, user_id)
);

CREATE TABLE posts (
    tenant_id integer REFERENCES tenants ON DELETE CASCADE,
    post_id integer NOT NULL,
    author_id integer,
    PRIMARY KEY (tenant_id, post_id),
    FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL (author_id)
);

Sans la spécification de la colonne, la clé étrangère aurait aussi configurer la colonne tenant_id à null, mais la valeur de cette colonne est toujours essentielle, vu qu'elle fait partie de la clé primaire.

À l'instar de ON DELETE, il existe ON UPDATE, évoqué lorsqu'une colonne référencée est modifiée (actualisée). Les actions possibles sont les mêmes, sauf que les listes de colonnes ne peuvent pas être utilisées avec SET NULL et SET DEFAULT. 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é, soit sont les colonnes d'un index d'unicité non partiel. Cela signifie que les colonnes référencées ont toujours un index pour permettre des recherches efficaces pour savoir si une ligne référençante a une correspondance. 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.