5.3. Contraintes

Une contrainte de vérification est le type de contrainte le plus générique qui soit. Elle vous permet de spécifier que l'expression d'une certaine colonne doit satisfaire une expression booléenne. Par exemple, pour obliger des prix de produits positifs, on pourrait utiliser :

CREATE TABLE produits (
    no_produit integer,
    nom text,
    prix numeric CHECK (prix > 0)
);

Comme vous pouvez le voir, la définition de contrainte vient après le type de données comme 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 contrainte de vérification peut impliquer la colonne ainsi contrainte, sinon la contrainte n'aurait pas beaucoup de sens.

Vous pouvez aussi donner à la contrainte un nom diffèrent. Ceci clarifie les messages d'erreur et vous permet de faire référence à la contrainte lorsque vous avez besoin de la modifier. La syntaxe est :

CREATE TABLE produits (
    no_produit integer,
    nom text,
    prix numeric CONSTRAINT positive_price CHECK (prix > 0)
);

Alors, pour spécifier une contrainte nommée, utilisez le mot-clé CONSTRAINT suivi d'un identifieur et de la définition de contrainte (si vous ne donnez pas de nom à la contrainte, le système choisira un nom pour vous).

Une contrainte de vérification peut faire référence à plusieurs colonnes. Admettons que vous souhaitez stocker un prix normal et un prix de promotion, et être certain que le prix de promotion soit 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)
);

Les deux premières contraintes devraient vous être familières. La troisième utilise une nouvelle syntaxe. Elle n'est pas attachée à une colonne particulière, elle apparaît comme un élément distinct dans la liste de colonnes séparées par des virgules. Les définitions de colonnes et ces définitions de contraintes peuvent être définies dans un ordre quelconque.

On dit que les deux premières contraintes sont des contraintes de colonnes tandis que la troisième est une contrainte de table parce qu'elle est écrite séparément de toute définition de colonne tandis que l'inverse n'est pas forcément possible car une contrainte de colonne est supposé faire uniquement référence à la colonne à laquelle elle est attachée (PostgreSQL™ ne force pas cette règle mais vous devriez la suivre si vous voulez que les définitions de votre table fonctionnent avec d'autres systèmes de bases de données) L'exemple ci-dessus aurait pu 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.

Des noms peuvent être affectés à des contraintes de table de la même façon que pour 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 valid_discount CHECK (prix > prix_promotion)
);

Il faut noter qu'une contrainte de vérification est satisfaite si l'expression est évaluée à vrai ou la valeur NULL. Puisque la plupart des expressions seront évaluées à la valeur NULL si l'un des opérandes est NULL, elles n'empêchent pas les valeurs NULL dans les colonnes contraintes. Pour s'assurer qu'une colonne ne contient pas de valeurs NULL, la contrainte non-NULL décrite dans la section suivante peut être utilisée.

Les contraintes de vérification sont utiles pour améliorer la performance des tables partitionées. Pour les détails, voir Section 5.9, « Partitionnement ».

Une contrainte non NULL dit simplement qu'une colonne ne peut pas prendre la valeur NULL. Un exemple de syntaxe :

CREATE TABLE produits (
    no_produit integer NOT NULL,
    nom text NOT NULL,
    prix numeric
);

Une contrainte non NULL est toujours écrite comme une contrainte de colonne. Une contrainte non NULL est l'équivalence fonctionnelle de la création d'contrainte CHECK (nom_colonne IS NOT NULL), mais dans PostgreSQL™, créer une contrainte explicitement non NULL est plus efficace. L'inconvénient est que vous ne pouvez pas donner de noms explicites à des contraintes non NULL créées de cette manière.

Bien sûr, une colonne peut avoir plus d'une contrainte. Écrivez juste 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'importe pas. Il ne détermine pas dans quel ordre les contraintes seront vérifiées.

La contrainte NOT NULL a un opposé ; la contrainte NULL. Ceci ne veut pas dire que la colonne doit être NULL, ce qui serait inutile. À la place, ceci sélectionne le comportement par défaut, à savoir que la colonne peut être NULL. La contrainte NULL n'est pas définie dans le standard SQL et ne devrait 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 car elle facilite le fait d'activer une contrainte dans un fichier de script. Par exemple, vous pourriez commencer avec :

CREATE TABLE produits (
    no_produit integer NULL,
    nom text NULL,
    prix numeric NULL
);

et puis insérer le mot-clé NOT suivant vos besoins.

Les contraintes uniques garantissent que les données contenues dans la colonne ou un groupe de colonnes est unique par rapport à toutes les lignes de la table. La syntaxe est :

CREATE TABLE produits (
    no_produit integer UNIQUE,
    nom text,
    prix numeric
);

quand elle est saisie comme une contrainte de colonne et

CREATE TABLE produits (
    no_produit integer,
    nom text,
    prix numeric,
    UNIQUE (no_produit)
);

quand elle est saisie comme une contrainte de table.

Si une contrainte unique fait référence à un groupe de colonnes, celles-ci sont listées en les séparant par des virgules :

CREATE TABLE exemple (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

Ceci spécifie que la combinaison de valeurs dans les colonnes indiquées est unique pour toute la table bien qu'une seule des colonnes puisse ne pas être (et habituellement n'est pas) unique.

Vous pouvez affecter votre propre nom pour une contrainte unique, de la façon habituelle :

CREATE TABLE produits (
    no_produit integer CONSTRAINT doit_etre_different UNIQUE,
    nom text,
    prix numeric
);

En général, une contrainte unique est violée lorsqu'il y a au moins deux lignes dans une table où la valeur de toutes les colonnes inclus dans la contrainte sont égales. Par contre, les valeurs NULL ne sont pas assimilées à une égalité dans cette comparaison. Ceci veut dire qu'il est possible de stocker des lignes dupliquées contenant une valeur NULL dans au moins l'une des colonnes contraintes. Ce comportement est conforme au standard SQL mais nous avons été informé que d'autres bases SQL ne suivent pas cette règle. Alors, soyez prudents en développant des applications prévues pour être portables.

Techniquement, une contrainte de clé primaire est tout simplement une combinaison d'une contrainte unique et d'une contrainte non NULL. Donc, les définitions de tables suivantes acceptent 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 contraindre sur plus d'une colonne ; la syntaxe est semblable aux contraintes uniques :

CREATE TABLE exemple (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

Une clé primaire indique qu'une colonne ou un groupe de colonnes peut être utilisé comme identifieur unique pour les lignes de la table (ceci est une conséquence directe de la définition d'une clé primaire. Notez qu'une contrainte unique ne donne pas par elle-même un identifieur unique car elle n'exclut pas les valeurs NULL). Ceci est pratique à la fois pour des raisons de documentation et pour les applications clientes. Par exemple, une application graphique qui permet de modifier les valeurs de lignes a probablement besoin de connaître la clé primaire d'une table pour pouvoir identifier les lignes de manière unique.

Une table a au mieux une clé primaire (tandis qu'elle peut avoir plusieurs contraintes uniques et non NULL). La théorie des bases de données relationnelles dit que chaque table doit avoir une clé primaire. Cette règle n'est pas forcée par PostgreSQL™ mais il vaut mieux la respecter autant que possible.

Une contrainte de clé étrangère stipule que les valeurs dans cette colonne (ou un groupe de colonnes) doit correspondre aux valeurs apparaissant dans des lignes d'une autre table. Nous disons que ceci maintient l'intégrité référentielle entre les deux tables.

Disons que vous avez la table de produits que nous avons déjà utilisée plusieurs fois :

CREATE TABLE produits (
    no_produit integer PRIMARY KEY,
    nom text,
    prix numeric
);

Disons aussi que vous avez une table stockant les commandes de ces produits. Nous voulons nous assurer que la table des commandes ne contienne que des commandes concernant des produits qui existent réellement. Alors, nous définissons une contrainte de clé étrangère dans la table des commandes qui référence la table produit:

CREATE TABLE commandes (
    id_commande integer PRIMARY KEY,
    no_produit integer REFERENCES produits (no_produit),
    quantite integer
);

Maintenant, il est impossible de créer des commandes avec une entrée no_produit qui n'apparaît pas dans la table produits.

Dans cette situation, nous disons que la table des commandes est la table référente et la table des produits est la table référencée. De la même façon, il y a des colonnes référentes et des colonnes référées.

On peut aussi raccourcir la commande ci-dessus en

CREATE TABLE commandes (
    id_commande integer PRIMARY KEY,
    no_produit integer REFERENCES produits,
    quantite integer
);

parce qu'en l'absence d'une liste de colonne, la clé primaire de la table référente est utilisée comme colonne référée.

Une clé étrangère peut aussi contraindre et référencer un groupe de colonnes. Comme d'habitude, il faut aussi l'écrire sous forme de contrainte de table. Voici un exemple de syntaxe :

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES autre_table (c1, c2)
);

Bien sûr, le nombre et le type des colonnes contraintes doivent correspondre au nombre et au type des colonnes référées.

Vous pouvez affecter un nom qui vous est propre pour une contrainte de clé étrangère de la façon habituelle.

Une table peut contenir plus d'une contrainte de clé étrangère. Ceci est utilisé pour implémenter des relations n à n entre tables. Disons que vous avez des tables contenant des produits et des commandes mais vous voulez maintenant autoriser une commande qui contient peut-être beaucoup de produits (ce que la structure ci-dessus ne permet pas). On pourrait utiliser cette structure de table :

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 order_items (
    no_produit integer REFERENCES produits,
    id_commande integer REFERENCES commandes,
    quantite integer,
    PRIMARY KEY (no_produit, id_commande)
);

Notez aussi que la clé primaire chevauche les clés étrangères dans la dernière table.

Nous savons que les clés étrangères n'autorisent pas la création de commandes qui ne sont pas liés à un produit. Et si un produit est retiré après qu'une commande qui y réfère soit créée ? SQL vous permet aussi de le gérer. Intuitivement, nous avons plusieurs options :

Pour illustrer ce cas, implémentons la politique suivante sur l'exemple de relations n à n évoquée plus haut: Quand quelqu'un veut retirer un produit qui est encore référencé par un ordre (via ordre_items), on l'interdit. Si quelqu'un retire une commande, les éléments de l'ordre sont aussi retiré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 order_items (
    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 supprimer en cascade sont les deux options les plus communes. RESTRICT empêche la suppression d'une ligne référencée. NO ACTION signifie que si des lignes de références existent lors de la vérification de la contrainte, une erreur est levée. Ceci est le comportement par défaut si rien n'est spécifié (la différence essentielle entre ces deux choix est que NO ACTION autorise de différer la vérification dans la transaction alors que RESTRICT ne le permet pas). CASCADE spécifie que, quand une ligne référencée est supprimée, les lignes la référençant devraient aussi être automatiquement supprimées. Il existe deux autres options : SET NULL et SET DEFAULT. Celles-ci font que les colonnes de références soient initialisées à NULL ou à leur valeur par défaut, respectivement quand la ligne référencée est supprimée. Notez qu'elles ne vous excusent pas d'observer les contraintes. Par exemple, si une action spécifie SET DEFAULT mais que la valeur par défaut ne satisfait pas la clé étrangère, l'opération échoue.

Sur le même principe que ON DELETE, il y a aussi ON UPDATE qui est évoqué lorsqu'une colonne référencée est modifiée (mise à jour). Les actions possibles sont les mêmes.

Le Chapitre 6, Manipulation de données contient plus d'informations sur la mise à jour et la suppression de données.

Enfin, la clé étrangère peut référencer des colonnes qui sont une clé primaire ou forment une contrainte unique. Si la clé étrangère référence une contrainte unique, il y a des possibilités supplémentaires selon que l'on souhaite faire correspondre les valeurs NULL. Celles-ci sont expliquées dans la documentation de référence pour CREATE TABLE.