5.3. Contraintes

5.3.1. Contraintes de Vérification

Une contrainte de vérification est le type de contrainte le plus générique qui soit. Elle vous permet de spécifier 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 products (
    product_no integer,
    name text,
    price numeric CHECK (price > 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 products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

Alors, pour spécifier une contrainte nommée, utilisez le mot-clé CONSTRAINT suivi d'un identifiant 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 sur que le prix de promotion soit inférieur au prix normal.

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

Les deux premières contraintes devrait 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 peut ê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 products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

ou même

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0 AND price > discounted_price)
);

C'est une question de goût.

Des noms peuvent être affectés à des contraintes de table de la même façon que les contraintes de colonne :

       CREATE TABLE products (
       product_no integer,
       name text,
       price numeric,
       CHECK (price > 0),
       discounted_price numeric,
       CHECK (discounted_price > 0),
       CONSTRAINT valid_discount CHECK (price > discounted_price)
     );
     

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.

5.3.2. Contraintes Non NULL

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

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

Une contrainte non NULL est toujours écrite comme une contrainte de colonne. Une contrainte non NULL est l'équivalente fonctionnelle de créer une 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 sur, une colonne peut avoir plus d'une contrainte. Écrivez juste les contraintes les unes après les autres:

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 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 que la colonne doit être NULL. La contrainte NULL n'est pas définie dans le standard SQL et ne devrait pas être utilisé 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 products (
    product_no integer NULL,
    name text NULL,
    price numeric NULL
);

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

5.3.3. Contraintes Uniques

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

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

est écrit comme contrainte de colonne et

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

est écrit comme contrainte de table.

Si une contrainte unique fait référence à un groupe de colonnes, celles-ci sont listées séparées 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 products (
    product_no integer CONSTRAINT must_be_different UNIQUE,
    name text,
    price numeric
);

En général, une contrainte unique est violée lorsqu'il y a au moins deux rangées dans une table ou la valeur de toutes les colonnes inclus dans la contrainte sont égales. Par contre, les valeurs NULL ne sont pas assimilées à une égalités dans cette comparaison. Ceci veut dire qu'il est possible de stocker un nombre illimité de rangées qui contiennent 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 qui sont prévues pour être portable.

5.3.4. Clés Primaires

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 accepteront les mêmes données:

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

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price 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 peuvent être utilisés comme identifiant unique pour les rangées 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 identifiant 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 rangées a probablement besoin de connaître la clé primaire d'une table pour pouvoir identifier les rangées de manière unique correctement.

Une table peut avoir 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 appliquée par PostgreSQL, mais il vaut mieux la respecter autant que possible.

5.3.5. Clés Étrangères

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 rangées d'une autre table. Nous disons que ceci maintient l'intégrité référentielle entre deux tables liées.

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

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

Disons aussi que vous avez une table stockant les commandes de ces produits. Nous voulons aussi 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 orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

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

Nous disons que, dans cette situation, la table de commandes est la table référente et la table products est la table référé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 orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);

parce qu'en l'absence d'une liste de colonnes, 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 other_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 votre propre nom 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 peut être 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 products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

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 :

• Interdire d'effacer un produit référé

• Effacer aussi les commandes

• Autre chose ?

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 products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

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 vous n'avez rien spécifié. (La différence essentielle entre ces deux choix est que NO ACTION autorise la déférence de la vérification plus tard 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 satisferait pas la clé étrangère, l'opération échouera.

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.

Il y a plus d'informations sur la mise à jour et la suppression de données dans Chapitre 6.

Enfin, nous devrions dire que 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. Ceux-ci sont expliqués dans la documentation de référence pour CREATE TABLE.