Contraintes

Maintenant que vous avons vu les types, explorons ce qu’on appelle les contraintes ou constraints. Il existe des situations où lors de la construction d’une table, un utilisateur souhaite contraindre les données d’une colonne ou de la table à certaines valeurs : obliger les valeurs à être non-nulles, à être discrètes, à entrer dans une catégorie, etc. Pour cela, il existe des mots clés à utiliser dans des instructions DDL.

Pour mieux comprendre, créons une table avec ses attributs, leurs types et leurs contraintes.

CREATE TABLE students (
id SERIAL PRIMARY KEY,
nom VARCHAR(100) NOT NULL,
prenom VARCHAR(100) NOT NULL,
id_university INT REFERENCES university (id),
university VARCHAR(100),
ville VARCHAR(100) CONSTRAINT verif_uni UNIQUE,
localisation VARCHAR(2) CHECK (localisation in ('N', 'S', 'E', 'O', 'NE', 'NO', 'SE', 'SO')),
/*FOREIGN KEY (id_university) REFERENCES university (id) ON DELETE CASCADE,*/
CONSTRAINT ville_fr CHECK (ville in ('Paris', 'Lyon', 'Lille', 'Marseille', 'Bordeaux'))
);

Il existe trois façons de créer des contraintes et toutes ont une utilité différente :

• Contrainte de colonne
• Contrainte de table
• Nommer une contrainte

La requête de création ci-dessus nous présente ces trois façons de créer des contraintes.

Les premières lignes de notre requête sont des contraintes de colonnes, les deux dernières lignes sont des contraintes de table, dont la dernière est une nomination de contrainte de table.

Les contraintes de colonne sont propres à une colonne et s’écrivent directement lors de la création de la colonne. C’est le cas pour toutes les lignes de la requête sauf les deux dernières. Notez également qu’il est possible de nommer une contrainte de colonne.

Les contraintes de table peuvent avoir la même utilisation que des contraintes de colonne en fonction de vos préférences. Cependant, l’utilisation d’une contrainte de table permet d’ajouter des fonctionnalités comme créer des contraintes sur un groupe de colonne. Les deux dernières lignes de notre requête sont des contraintes de table. Il est possible de nommer les contraintes de table exactement comme les contraintes de colonne.

Contrainte UNIQUE

ville VARCHAR(100) CONSTRAINT verif_uni UNIQUE

Cette ligne de code créé un attribut ‘ville’ de type caractère variable limité à 100 caractères. On contraint la colonne avec la contrainte UNIQUE, ce qui signifie que chaque valeur de la colonne doit être unique. Il ne peut donc pas y avoir plusieurs lignes avec la même ville. De plus, nous nommons cette contrainte ‘verif_uni’ grâce au mot clé CONSTRAINT.

Cette nomination permet facilement de supprimer la contrainte avec l’instruction ALTER TABLE students DROP CONSTRAINT verif_uni.

Si vous voulez par exemple que le couple nom/prenom des étudiants soit UNIQUE, vous pouvez grouper l’unicité en utilisant une contrainte de table.

CONSTRAINT nom_pre_uni UNIQUE (nom, prenom)

Contrainte CHECK

CONSTRAINT ville_fr CHECK (ville in ('Paris', 'Lyon', 'Lille', 'Marseille', 'Bordeaux'))

Cette ligne de code ajoute une contrainte de table CHECK nommée ‘ville_fr’ à la colonne ‘ville’. La contrainte CHECK force la colonne correspondante à satisfaire une expression booléenne. Si le résultat de CHECK est TRUE, alors la transaction est acceptée. Si le résultat de CHECK est FALSE, alors il y a une erreur. Dans notre exemple, lorsque nous insérons une ligne, la valeur de ‘ville’ doit obligatoirement faire partie de la liste (‘Paris’, ‘Lyon’, ‘Lille’, ‘Marseille’, ‘Bordeaux’).

Dans le même sens, nous avons ajouté une contrainte de colonne CHECK que nous n’avons pas nommé :

localisation VARCHAR(2) CHECK (localisation in ('N', 'S', 'E', 'O', 'NE', 'NO', 'SE', 'SO'))

Contrainte NOT NULL

La contrainte NOT NULL force les valeurs d’une colonne à ne pas pouvoir prendre la valeur NULL.

nom VARCHAR(100) NOT NULL,
prenom VARCHAR(100) NOT NULL

Une contrainte NOT NULL doit obligatoirement être déclarée comme contrainte de colonne et ne peut pas être nommée.

Contrainte PRIMARY KEY

La contrainte PRIMARY KEY indique que chaque valeur de cette colonne (ou groupe de colonne) sera considérée comme l’identifiant unique d’un tuple (ligne). Une PRIMARY KEY impose le fait d’être UNIQUE et NOT NULL. De ce fait, les deux lignes de codes suivantes sont équivalentes :

id SERIAL PRIMARY KEY == id SERIAL UNIQUE NOT NULL

NB : Le type SERIAL créé un objet séquence dans la base de données, rattaché à la colonne ‘id’ de la table ‘students’. Ce type est par défaut NOT NULL car il génère un entier à chaque nouvelle insertion dans la table.

Comme pour la contrainte UNIQUE, la contrainte PRIMARY KEY peut contraindre plusieurs colonnes. Si nous voulons que le couple nom/prenom soit la clé primaire, il faut ajouter une contrainte de table :

CONSTRAINT pk_nom_prenom PRIMARY KEY (nom, prenom)

Contrainte FOREIGN KEY

Ce sont les associations PRIMARY KEY et FOREIGN KEY qui créent toutes les relations d’une base de données relationnelle, elles maintiennent l’intégrité référentielle entre les tables. La contrainte FOREIGN KEY stipule que chaque valeur de cette colonne fait référence à une valeur d’une table étrangère, où se trouve la PRIMARY KEY correspondante (référentielle).

NB : lorsqu’on déclare une clé étrangère dans une contrainte de colonne, PostgreSQL offre la possibilité d’entrer directement le mot clé REFERENCES. Cependant, pour déclarer une clé étrangère dans une contrainte de table, il faut obligatoirement utiliser les mots clés FOREIGN KEY et REFERENCES.

Partons du principe qu’il existe une table ‘university’ qui réfère la liste des universités françaises. Une université est associée à un étudiant. Il existe une colonne ‘id’, clé primaire de la table ‘university’.

id_university INT REFERENCES university (id)

Cette ligne de code créé une colonne ‘id_university’ de type entier qui fait référence à la colonne ‘id’ de la table ‘university’ par le mot clé REFERENCES, indirectement la contrainte FOREIGN KEY. Pour qu’une telle colonne existe, il faut qu’il existe une colonne référentielle ‘id’ dans la table ‘university’.

La colonne ‘id_university’ de notre table ‘students’ prend une valeur uniquement si cette valeur existe dans la colonne ‘id’ de la table ‘university’. En créant cette clé étrangère, on s’assure que les ‘id_university’ existent réellement dans la table ‘university’.

Comme pour les contraintes UNIQUE et PRIMARY KEY, une contrainte FOREIGN KEY peut contraindre un groupe de colonnes.

NB : il est impossible de supprimer une ligne de la table ‘university’ si la clé primaire est référencée en tant que clé étrangère dans une autre table, comme ‘students’. Pour supprimer une ligne, il faudrait supprimer cette ligne dans toutes les tables où la clé primaire de ‘university’ est référencée. Afin d’éviter de le faire manuellement, surtout si cette colonne est référencée dans de nombreuses tables, il existe un mot clé ON DELETE CASCADE.

FOREIGN KEY (id_university) REFERENCES university (id) ON DELETE CASCADE

Le mot clé CASCADE permet de supprimer automatiquement la ligne dans la table fille, si on supprime la ligne dans la table mère. (Là où la clé primaire de la table mère est référencée en tant que clé étrangère dans cette table fille).

De la même manière, il existe de nombreux mots clés pour gérer la suppression ou la modification de données référencées. Ici les plus communs :

–ON DELETE SET NULL : si une ligne est supprimée dans la table mère, remplace la donnée référencée par NULL sans supprimer la ligne.
–ON DELETE RESTRICT : interdit toute suppression sans essayer l’opération.
–ON DELETE NO ACTION : (valeur par défaut) interdit toute suppression après avoir essayé l’opération.
–ON UPDATE CASCADE : si une donnée est modifiée dans la table mère, remplace la donnée référencée par la valeur de la table mère.

Afin de mieux comprendre la structure de nos classes, leurs attributs et les clés primaires et étrangères, visualisons ce schéma :

Nous pouvons visualiser ici la relation entre les tables ‘students’ et ‘university’. Chaque table a ses propres attributs, ses propres types et formats, cependant il existe une relation entre ces tables par l’identifiant de l’université. Un étudiant a un identifiant, un nom, un prénom, des informations le concernant et l’identifiant de son université. Cet identifiant provient de la table ‘university’ qui liste toutes les universités. C’est une clé étrangère ‘id_university’ qui fait référence à une clé primaire ‘id’. L’intégrité référentielle fait qu’un identifiant universitaire d’un étudiant existe réellement dans une table de référence (university).

Nous irons plus loin dans les schémas physiques dans le cours suivant.