5.3. Contraintes

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 colonnes 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 colonnes peuvent être écrites comme des contraintes de tables, 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'une des opérandes est nulle, 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 plus d'une contrainte. 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 ficheir 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.

Astuce

Dans la plupart des bases de données, la majorité des colonnes peut être marquée 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.

Lorsqu'une contrainte d'unicité 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)
);

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

En général, une contrainte d'unicité est violée lorsqu'au moins deux lignes de la table possèdent des valeurs identiques sur toutes les colonnes de la contrainte. En revanche, deux valeurs NULL ne sont pas 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.

Techniquement, une contrainte de clé primaire n'est que la combinaison d'une contrainte d'unicité et d'une contrainte NOT 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)
    );

Une clé primaire indique qu'une colonne ou un groupe de colonnes peut être utilisé(e) comme identifiant unique des lignes de la table. (C'est une conséquence directe de la définition d'une clé primaire. Une contrainte d'unicité ne suffit pas à fournir un identifiant unique car elle n'exclut pas les valeurs NULL). Ceci est utile à la fois pour des raisons documentaires 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 plus une clé primaire. (Le nombre de contraintes UNIQUE NOT NULL, qui assurent 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.

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

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

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

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

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

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

Le Chapitre 6, Manipulation de données contient de plus amples informations sur l'actualisation et la suppression de données.

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.