Contraintes d’intégrité en SQL : guide avec exemples

Imaginez une base de données hospitalière où les allergies des patients ne peuvent pas être laissées vides, ou un système financier où les montants des transactions doivent être des nombres positifs. Dans ces situations, et bien d’autres, nous comptons sur les contraintes d’intégrité pour garantir des données exactes, cohérentes et fiables. 

En SQL, les contraintes d’intégrité sont des règles que l’on applique aux tables pour maintenir la qualité des données. Elles aident à éviter les erreurs, à faire respecter les règles métier et à s’assurer que nos données reflètent fidèlement les entités et les relations du monde réel.

Dans cet article, nous allons passer en revue les principaux types de contraintes d’intégrité en SQL, avec des explications claires et des exemples concrets sur une base PostgreSQL. Même si nous utilisons la syntaxe PostgreSQL, les concepts se transposent facilement à d’autres dialectes SQL.

Pour aller plus loin en SQL, consultez cette sélection de cours SQL.

Qu’est-ce qu’une contrainte d’intégrité en SQL ?

Prenons le cas d’une table qui stocke les informations des utilisateurs d’une application web. Certaines données, comme l’âge, peuvent être facultatives si elles n’entravent pas l’accès au service. En revanche, un mot de passe est indispensable pour se connecter. Pour le garantir, nous appliquerions une contrainte d’intégrité sur la colonne password de la table des utilisateurs pour imposer qu’elle soit renseignée pour chaque enregistrement.

En bref, les contraintes d’intégrité sont essentielles pour :

• Éviter les données manquantes.
• Garantir des types et des plages de valeurs conformes aux attentes.
• Maintenir des liens corrects entre les données de différentes tables.

Dans cet article, nous allons explorer les contraintes d’intégrité suivantes :

• PRIMARY KEY : identifie de façon unique chaque enregistrement d’une table.
• NOT NULL : empêche une colonne de contenir des valeurs NULL.
• UNIQUE : garantit l’unicité des valeurs d’une colonne ou d’un groupe de colonnes.
• DEFAULT : définit une valeur par défaut lorsqu’aucune n’est fournie.
• CHECK : impose qu’une colonne respecte une condition donnée.
• FOREIGN KEY : établit des relations entre tables en référant une clé primaire d’une autre table.

Cas d’étude : base de données d’université

Considérons une base relationnelle pour une université. Elle contient trois tables : students, courses et enrollments.

Table students

La table students regroupe les informations de tous les étudiants.

• student_id : identifiant de l’étudiant.
• first_name : prénom de l’étudiant.
• last_name : nom de famille de l’étudiant.
• email : adresse e‑mail de l’étudiant.
• major : spécialité/majeure de l’étudiant.
• enrollment_year : année d’inscription.

Table courses

La table courses recense les cours proposés par l’université.

• course_id : identifiant du cours.
• course_name : intitulé du cours.
• department : département rattaché.

Table enrollments

La table enrollments stocke quelles inscriptions lient quels étudiants à quels cours.

• student_id : identifiant de l’étudiant inscrit au cours.
• course_id : identifiant du cours.
• year : année d’inscription.
• grade : note obtenue par l’étudiant pour ce cours.
• is_passing_grade : booléen indiquant si la note est suffisante.

Tout au long de l’article, nous utiliserons cette base d’exemple et montrerons plusieurs façons d’assurer l’intégrité des données. Nous écrirons nos requêtes en syntaxe PostgreSQL, mais les concepts se généralisent aisément aux autres variantes SQL.

Contrainte PRIMARY KEY

L’université doit identifier chaque étudiant de manière unique. Utiliser les prénoms et noms n’est pas conseillé, car des doublons sont probables. Se baser sur l’e‑mail n’est pas idéal non plus, puisqu’un étudiant peut le changer.

La solution la plus courante consiste à attribuer un identifiant unique à chaque étudiant, stocké dans la colonne student_id. On applique alors une contrainte PRIMARY KEY à cette colonne pour garantir l’unicité.

On la définit dans la commande CREATE TABLE, juste après le type de la colonne :

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    major TEXT,
    enrollment_year INT
);Propulsé par

Cette requête crée la table des étudiants avec les six colonnes listées plus haut.

La contrainte PRIMARY KEY garantit que :

1. Chaque étudiant possède un student_id.
2. Chaque student_id est unique.

PRIMARY KEY sur plusieurs colonnes

Dans certains cas, il faut plusieurs colonnes pour identifier une ligne de manière unique. C’est le cas de enrollments. Un étudiant peut s’inscrire à plusieurs cours, donc plusieurs lignes partagent le même student_id. Et un cours accueille plusieurs étudiants, donc plusieurs lignes partagent le même course_id.

Aucun champ seul n’identifiant la ligne, chaque enregistrement d’inscription est déterminé par la combinaison student_id, course_id et year.

Quand plusieurs colonnes sont concernées, on déclare la PRIMARY KEY à la fin de la commande CREATE TABLE.

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    year INT,
    grade INT,
    is_passing_grade BOOLEAN,
    PRIMARY KEY (student_id, course_id, year)
);Propulsé par

Ajouter des contraintes après la création

On peut ajouter des contraintes de deux façons. Nous venons de voir comment faire lors de la création de la table.

Si la table existe déjà et que vous avez oublié la PRIMARY KEY, vous pouvez l’ajouter après coup avec ALTER TABLE :

ALTER TABLE enrollments
ADD CONSTRAINT enroll_pk 
PRIMARY KEY (student_id, course_id, year);Propulsé par

Dans la requête ALTER TABLE, nous avons nommé la contrainte enroll_pk (pour enrollment primary key). Le nom peut être libre, mais il est recommandé d’en choisir un qui décrit clairement son rôle.

Nommer ses contraintes est une bonne pratique, car :

• La référence est plus aisée, notamment pour les modifier ou les supprimer ultérieurement.
• Cela facilite l’organisation et la gestion des contraintes, surtout dans des bases riches en règles.

Contrainte NOT NULL

L’université veut s’assurer que le prénom, le nom et l’e‑mail de chaque étudiant sont enregistrés. Elle ne veut pas que le personnel oublie ces champs par inadvertance.

Pour cela, on applique des contraintes NOT NULL sur ces trois colonnes lors de la création :

CREATE TABLE students (
    student_id INT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT NOT NULL,
    major TEXT,
    enrollment_year INT
);Propulsé par

Cette requête utilise NOT NULL pour empêcher les valeurs NULL (indéfinies) dans first_name, last_name et email.

Pour ajouter NOT NULL à une table existante, on utilise ALTER TABLE :

ALTER TABLE students
ALTER COLUMN first_name SET NOT NULL,
ALTER COLUMN last_name SET NOT NULL,
ALTER COLUMN email SET NOT NULL;Propulsé par

Contrainte UNIQUE

Une adresse e‑mail est, par nature, unique à chaque personne. Lorsqu’un champ ne doit jamais comporter de doublon, mieux vaut l’imposer au niveau de la base pour prévenir les erreurs et garantir l’intégrité.

L’ajout se fait comme pour les autres contraintes.

CREATE TABLE students (
    ...
    email TEXT UNIQUE,
    ...
);Propulsé par

Dans notre cas, nous voulons aussi imposer NOT NULL. On peut cumuler plusieurs contraintes sur une même colonne, séparées par des espaces :

CREATE TABLE students (
    ...
    email TEXT UNIQUE NOT NULL,
    ...
);Propulsé par

L’ordre n’a pas d’importance : NOT NULL UNIQUE fonctionnerait tout autant.

Pour ajouter UNIQUE à une table existante :

ALTER TABLE students 
ADD CONSTRAINT unique_emails UNIQUE (email);Propulsé par

UNIQUE sur plusieurs colonnes

Supposons que l’on souhaite garantir l’unicité de l’intitulé des cours au sein d’un même département dans la table courses. La combinaison course_name + department doit donc être unique.

Avec plusieurs colonnes, on place la contrainte en fin de CREATE TABLE :

CREATE TABLE courses (
    course_id INT,
    course_name TEXT,
    department TEXT,
    UNIQUE (course_name, department)
);Propulsé par

On peut aussi l’ajouter via une modification de table, en passant le couple de colonnes :

ALTER TABLE courses 
ADD CONSTRAINT unique_course_name_department 
UNIQUE (course_name, department);Propulsé par

NOT NULL UNIQUE vs PRIMARY KEY 

Nous avons vu qu’une PRIMARY KEY impose l’unicité et l’absence de valeurs manquantes. Quelle est alors la différence entre :

course_id INT PRIMARY KEYPropulsé par

course_id INT UNIQUE NOT NULLPropulsé par

La différence tient à l’objectif et à l’usage.

Bien que les deux garantissent l’unicité et la non‑nullité, une table ne peut avoir qu’une seule PRIMARY KEY, destinée à identifier de manière unique chaque enregistrement.

À l’inverse, la combinaison NOT NULL UNIQUE peut s’appliquer à d’autres colonnes pour imposer des valeurs uniques non nulles, au service de règles métier spécifiques. Une table peut comporter autant de contraintes NOT NULL UNIQUE que nécessaire.

La coexistence des deux offre une plus grande flexibilité de conception, distinguant l’identifiant principal d’un enregistrement d’autres attributs importants et uniques de la table.

Contrainte DEFAULT

Après leur inscription, certains étudiants mettront un peu de temps à choisir leur majeure. L’université souhaite que la valeur de la colonne major soit la chaîne « Undeclared » pour ceux qui ne l’ont pas encore choisie.

Pour cela, nous définissons une valeur par défaut via la contrainte DEFAULT. Nous pouvons modifier la table students ainsi :

ALTER TABLE students
ALTER COLUMN major SET DEFAULT 'Undeclared';Propulsé par

Si l’on préfère définir DEFAULT lors de la création de la table, on le déclare après le type de la colonne :

CREATE TABLE students (
    ...
    major TEXT DEFAULT 'Undeclared',
    ...
);Propulsé par

Contrainte CHECK

Dans cette université, les notes vont de 0 à 100. Sans contrainte, la colonne grade de la table enrollment accepterait n’importe quel entier. On corrige cela avec une contrainte CHECK pour forcer des valeurs comprises entre 0 et 100.

ALTER TABLE enrollments
ADD CONSTRAINT grade_range CHECK (grade BETWEEN 0 AND 100);Propulsé par

De manière générale, les contraintes CHECK permettent de valider des conditions spécifiques que doivent respecter les données. Elles sont clés pour la cohérence et l’intégrité.

Une contrainte CHECK peut porter sur plusieurs colonnes. Utilisons‑la pour assurer la cohérence entre grade et is_passing_grade. Disons qu’une note est suffisante à partir de 60. Nous voulons donc que is_passing_grade soit TRUE si et seulement si la note est au moins 60. Voici comment la déclarer dans CREATE TABLE :

CREATE TABLE enrollments (
    ...
    grade INT,
    is_passing_grade BOOLEAN,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    )
);Propulsé par

Un problème subsiste : lors de l’inscription, un étudiant n’a pas encore de note. Nous devons donc autoriser NULL pour grade. Il faut alors adapter la contrainte sur la validation pour qu’is_passing_grade soit aussi NULL quand la note est indéfinie. Voici la version mise à jour :

CREATE TABLE enrollments (
    ...    
    grade INT NULL DEFAULT NULL,
    is_passing_grade BOOLEAN NULL DEFAULT NULL,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade IS NULL AND is_passing_grade IS NULL) OR
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    ),
    ...
);Propulsé par

Notez que nous avons indiqué NULL et une valeur par défaut NULL sur les colonnes grade et is_passing_grade : c’est uniquement pour clarifier la lecture.

Voyons maintenant les types de conditions que l’on peut imposer avec CHECK.

Conditions de plage

On garantit que les valeurs d’une colonne se situent dans une plage définie.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name BETWEEN min_value AND max_value);Propulsé par

Conditions de liste

On vérifie que la valeur d’une colonne appartient à une liste de valeurs spécifiques.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name IN ('Value1', 'Value2', 'Value3'));Propulsé par

Conditions de comparaison

On compare les valeurs d’une colonne à un seuil (supérieur à, inférieur à, etc.).

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name > some_value);Propulsé par

Conditions de correspondance de motif

On utilise la correspondance de motifs (par exemple avec LIKE ou SIMILAR TO) pour valider des textes.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name LIKE 'pattern');Propulsé par

Conditions logiques

On combine plusieurs conditions avec des opérateurs logiques (AND, OR, NOT).

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (condition1 AND condition2 OR condition3);Propulsé par

Conditions composites

On applique une condition portant sur plusieurs colonnes.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column1 + column2 < some_value);Propulsé par

Contrainte FOREIGN KEY

Les clés étrangères servent à lier des colonnes de deux tables pour garantir l’intégrité référentielle. Concrètement, une clé étrangère pointe vers une clé primaire d’une autre table, ce qui indique que les lignes sont liées. Cela évite d’insérer une ligne avec une valeur de clé étrangère qui ne correspond à aucune ligne de la table référencée.

Dans notre exemple, chaque enregistrement d’enrollments fait référence à un étudiant et un cours via student_id et course_id. Sans contrainte, rien n’assure que ces identifiants correspondent à des entrées existantes dans students et courses.

Voici comment l’imposer lors de la création de enrollments :

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    ...
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);Propulsé par

Remarquez que, pour être des clés étrangères valides, ces colonnes doivent référencer des clés primaires dans les tables students et courses.

Comme pour les autres contraintes, on peut aussi les ajouter après coup avec ALTER TABLE :

ALTER TABLE enrollments
ADD CONSTRAINT fk_student_id
FOREIGN KEY (student_id)
REFERENCES students(student_id);

ALTER TABLE enrollments
ADD CONSTRAINT fk_course_id
FOREIGN KEY (course_id)
REFERENCES courses(course_id);Propulsé par

Tout rassembler

Tout au long de cet article, nous avons présenté plusieurs contraintes d’intégrité et leur utilisation pour améliorer une base universitaire. Voici la version finale des commandes CREATE TABLE, qui combine l’ensemble.

Commençons par la table students :

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    major TEXT DEFAULT 'Undeclared',
    enrollment_year INT,
    CONSTRAINT year_check CHECK (enrollment_year >= 1900),
    CHECK (major IN (
        'Undeclared',
        'Computer Science',
        'Mathematics',
        'Biology',
        'Physics',
        'Chemistry',
        'Biochemistry'
    ))
);Propulsé par

Définissons ensuite la table courses :

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name TEXT NOT NULL,
    department TEXT NOT NULL,
    UNIQUE (course_name, department),
    CHECK (department IN (
        'Physics & Mathematics',
        'Sciences'
    ))
);Propulsé par

Enfin, définissons la table enrollments, qui établit les relations entre étudiants et cours :

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    year INT CHECK (year >= 1900),
    grade INT NULL DEFAULT NULL,
    is_passing_grade BOOLEAN NULL DEFAULT NULL,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade IS NULL AND is_passing_grade IS NULL) OR
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    ),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id, year)
);Propulsé par

Nous avons ajouté quelques contraintes supplémentaires dans cet exemple final. Une université dispose d’un ensemble connu de départements et de majeures. Par conséquent, les valeurs possibles pour les colonnes major et department sont finies et connues à l’avance. Dans ce type de situation, une contrainte CHECK est recommandée pour restreindre ces colonnes aux seules valeurs autorisées.

Conclusion

Nous avons passé en revue les différents types de contraintes d’intégrité en SQL et leur mise en œuvre avec PostgreSQL. Nous avons couvert les clés primaires, les contraintes NOT NULL, UNIQUE, DEFAULT, CHECK et FOREIGN KEY, avec des exemples pratiques pour chacune.

En maîtrisant ces notions, vous garantissez l’exactitude, la cohérence et la fiabilité de vos données.

Pour apprendre à structurer vos données efficacement, découvrez ce cours sur le Database Design.