5.4. Contraintes

5.4.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 que doit satisfaire la valeur d'une certaine colonne. 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 identifiant et de la définition de contrainte.

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 produits (
    no_produit integer,
    nom text,
    prix numeric CHECK (prix > 0),
    prix_solde numeric CHECK (prix_solde > 0),
    CHECK (prix > prix_solde)
);

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, des définitions de colonnes tandis que l'inverse n'est pas forcément possible. L'exemple ci-dessus aurait pu s'écrire:

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

ou même

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

C'est une question de goût.

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

5.4.2. Contraintes Non Nulles

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

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

Une contrainte non nulle est toujours écrite comme une contrainte de colonne. Une contrainte non nulle est l'équivalente fonctionnelle de créer une contrainte CHECK (nom_colonne IS NOT NULL), mais dans PostgreSQL, créer une contrainte explicitement non nulle est plus efficace. L'inconvénient est que vous ne pouvez pas donner de noms explicites à des contraintes non nulles 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 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. Au lieu, ceci définit le comportement par défaut que la colonne doit être nulle. 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 produits (
    no_produit integer NULL,
    nom text NULL,
    prix numeric NULL
);

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

5.4.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 produits (
    no_produit integer UNIQUE,
    nom text,
    prix numeric
);

est écrit comme contrainte de colonne et

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

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)
);

Il est aussi possible d'attribuer des noms à des contraintes uniques:

CREATE TABLE produits (
    no_produit integer CONSTRAINT must_be_different UNIQUE,
    nom text,
    prix 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 chacune des colonnes correspondantes qui font partie de la contrainte sont égales. Par contre, les valeurs nulles ne sont pas assimilées à une égalités dans cette situation. Ceci veut dire qu'il est possible de stocker un nombre illimité de rangées qui contiennent une valeur nulle 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.4.4. Clés Primaires

Techniquement, une contrainte de clé primaire est tout simplement une combinaison d'une contrainte unique et d'une contrainte non nulle. Donc, les définitions de tables suivantes accepteront 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,
    name 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 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 nulles.) 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 nulles). 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.4.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 produits (
    no_produit integer PRIMARY KEY,
    name text,
    prix 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,
    no_produit integer REFERENCES produits (no_produit),
    quantity 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.

Nous disons que, dans cette situation, la table de commandes est la table référente et la table produits 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,
    no_produit integer REFERENCES produits,
    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 sur, le nombre et le type des colonnes contraintes doit correspondre aux nombres et types des colonnes référées.

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 produits (
    no_produit integer PRIMARY KEY,
    name text,
    prix numeric
);

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

CREATE TABLE order_items (
    no_produit integer REFERENCES produits,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (no_produit, 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 spécifier. 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 produits (
    no_produit integer PRIMARY KEY,
    name text,
    prix numeric
);

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

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

Restreindre et mettre en cascade les effacements sont les deux options les plus récurrentes. RESTRICT peut aussi s'écrire NO ACTION et c'est aussi le cas par défaut si on ne précise rien du tout. Il y'a deux autres options pour ce qui devrait se passer sur les colonnes de clé étrangère lorsqu'une clé primaire est effacée : SET NULL et SET DEFAULT. Notez que ceci ne vous permet pas de passer outre ces contraintes. Par exemple, si une action précise SET DEFAULT mais que la valeur par défaut ne satisfait pas la clé étrangère, la suppression de la clé primaire peut échouer.

Sur le même principe que ON DELETE, il y a aussi ON UPDATE qui est évoqué lorsqu'une clé primaire 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 soit une clé primaire ou former 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 nulles. Ceux-ci sont expliqués dans la documentation de référence pour CREATE TABLE.