Aller au contenu
Encyclopédie informatique Édition 2026

Le langage SQL : tables, contraintes et droits

Partie 2 sur 4

Niveau de lecture :

3Les contraintes d’intégrité

Une contrainte d’intégrité est une clause qui contraint la modification des tables faite par les requêtes des utilisateurs, afin que les données saisies soient conformes aux données attendues. C’est une des grandes forces des bases de données : la règle est écrite une fois dans la base, et s’applique à tout le monde, quel que soit le programme, la personne ou l’erreur qui tente d’écrire. Sans contrainte, chaque programme doit refaire les contrôles, et il suffit d’un oubli, d’un vieux script ou d’une saisie directe pour introduire des lignes absurdes (un client sans nom, une commande sans client, un âge négatif).

Une ligne qui viole une contrainte est refusée en bloc : la base répond par une erreur, rien n’est écrit. La figure 1 résume les contraintes principales sur deux tables.

DEUX TABLES ET LEURS CONTRAINTES client id PRIMARY KEY nom NOT NULL email UNIQUE age CHECK : entre 0 et 130 pays DEFAULT 'FR’ commande id PRIMARY KEY client_id FOREIGN KEY, NOT NULL date_cmd DEFAULT CURRENT_DATE total CHECK : positif ou nul clé étrangère : chaque commande a un client réel Une ligne qui viole une contrainte est refusée en bloc : la base répond par une erreur et rien n’est écrit. Les contraintes protègent les données même quand le programme qui écrit contient une erreur.
Figure 1. Deux tables et leurs contraintes.

3.1Les contraintes sur une colonne

Elles se déclarent à la création de la table (« ces contraintes doivent être exprimées dès la création »), avec les mots clés suivants. On peut aussi les ajouter ensuite avec ALTER TABLE (partie 4).

  • DEFAULT définit la valeur donnée à un champ quand on n’en précise aucune. Cette valeur peut être une constante (nombre, texte), le mot NULL, ou une valeur calculée à l’instant de la saisie comme CURRENT_DATE (date du jour), CURRENT_TIME (heure) ou CURRENT_TIMESTAMP (date et heure). On trouve aussi USER, le nom de l’utilisateur qui écrit. Cela facilite la création des lignes et garantit qu’un champ ne reste pas vide.
  • NOT NULL impose qu’un champ soit renseigné : la base refuse d’insérer une ligne où il manque.
  • UNIQUE vérifie que la valeur saisie n’existe pas déjà dans la colonne : toutes les valeurs seront différentes (deux comptes ne peuvent avoir le même courriel). Les valeurs absentes (NULL) ne comptent pas comme des doublons dans la plupart des produits.
  • CHECK contient une condition logique qui doit être vraie pour toute valeur écrite. Si la valeur est NULL, le test n’est pas fait : pour interdire l’absence de valeur, il faut ajouter NOT NULL.

Voici un exemple bien construit :

CREATE TABLE client (
    id      INTEGER      PRIMARY KEY,
    nom     VARCHAR(80)  NOT NULL,
    prenom  VARCHAR(80)  NOT NULL,
    age     INTEGER      CONSTRAINT ck_age CHECK (age BETWEEN 0 AND 130),
    email   VARCHAR(120) NOT NULL UNIQUE
                         CONSTRAINT ck_email CHECK (email LIKE '%@%'),
    pays    CHAR(2)      DEFAULT 'FR'
);

Voici ce qu’on écrit parfois de travers, et qu’il faut éviter de recopier : check (Email LIKE "%@%") avec des guillemets doubles, alors qu’en SQL standard les textes s’écrivent entre apostrophes (les guillemets doubles servent à entourer des noms de colonnes) ; un âge borné à « moins de 100 », ce qui exclut des personnes bien réelles ; et char(30), qui complète le nom par des espaces, alors que VARCHAR est presque toujours préférable. Notez aussi que le contrôle LIKE '%@%' est très faible : il accepte « @ » tout seul. Une base ne remplace pas la vérification complète d’une adresse de courriel, qui se fait dans le programme ; la contrainte n’est qu’un filet de dernière instance.

Une précision d’actualité sur CHECK : certains produits l’ont longtemps accepté sans l’appliquer. C’était le cas de MySQL, qui lisait la clause et l’ignorait ; il ne la fait respecter que depuis la version 8.0.16 (2019). Sur une ancienne installation, votre contrainte peut donc ne rien faire. Il faut toujours tester qu’une contrainte refuse bien ce qu’elle devrait refuser.

3.2Nommer les contraintes

Le mot clé CONSTRAINT, suivi d’un nom, permet de baptiser une contrainte. La raison est simple : le nom s’affiche en cas de non-respect de la règle. Sans nom, la base en invente un (quelque chose comme SYS_C0012345) que personne ne comprendra à trois heures du matin devant un message d’erreur. Avec un nom parlant comme ck_age ou fk_commande_client, le message dit presque tout seul ce qui s’est passé. Prenez l’habitude de les nommer systématiquement, avec une convention : préfixe selon le type (pk_, fk_, ck_, uq_), puis table et colonne.

3.3La clé primaire

Une clé est la ou les colonnes dont la connaissance permet de désigner précisément une et une seule ligne. On déclare la clé primaire avec PRIMARY KEY. Ces colonnes ne peuvent plus prendre la valeur NULL, et deux lignes ne peuvent pas avoir la même combinaison de valeurs. Quand la clé comporte plusieurs colonnes, on les liste entre parenthèses, séparées par des virgules :

CREATE TABLE ligne_commande (
    commande_id INTEGER,
    produit_id  INTEGER,
    quantite    INTEGER NOT NULL CHECK (quantite > 0),
    PRIMARY KEY (commande_id, produit_id)
);

Beaucoup de systèmes créent automatiquement un index pour la clé primaire (voir partie 6). Un bon réflexe est de choisir une clé sans signification métier (un simple numéro généré par la base) plutôt qu’une donnée qui pourrait changer, comme un nom ou un numéro de téléphone : ce qui sert d’identité ne doit pas bouger.

3.4La clé étrangère et l’intégrité référentielle

Quand une liste de colonnes d’une table correspond à la clé primaire d’une autre table, on parle de clé étrangère. On la déclare avec FOREIGN KEY suivi de la ou des colonnes, puis de REFERENCES avec le nom de la table cible et de sa clé :

CREATE TABLE commande (
    id          INTEGER PRIMARY KEY,
    client_id   INTEGER NOT NULL,
    date_cmd    DATE DEFAULT CURRENT_DATE,
    CONSTRAINT fk_commande_client
        FOREIGN KEY (client_id) REFERENCES client (id)
);

La base garantit alors l’intégrité référentielle : toute commande renvoie à un client qui existe réellement. Impossible d’insérer une commande pour un client inconnu, impossible de supprimer un client qui a encore des commandes (sauf règle contraire, voir plus bas). On évite ainsi les « lignes orphelines », ces enregistrements dont le parent a disparu et qui produisent des erreurs ou des totaux faux.

3.5Que faire à la suppression ou à la modification ? ON DELETE et ON UPDATE

Que se passe-t-il si l’on supprime un client qui a des commandes ? Cela dépend de la règle que vous avez choisie. On présente parfois ON DELETE et ON UPDATE comme des « triggers » (gâchettes, ou déclencheurs). C’est une erreur de vocabulaire qu’il faut corriger : ce sont des actions référentielles, une partie de la déclaration de clé étrangère. Les vrais déclencheurs, eux, sont de petits programmes écrits à part (partie 7). Les quatre actions possibles sont rassemblées dans la figure 2 :

QUE DEVIENNENT LES COMMANDES QUAND ON SUPPRIME UN CLIENT ? (ON DELETE) RESTRICT / NO ACTION la suppression du client est refusée cas général : on garde l’historique CASCADE ses commandes disparaissent avec lui détail qui n’a de sens que rattaché SET NULL les commandes restent, sans client lien facultatif (colonne sans NOT NULL) SET DEFAULT les commandes passent au client par défaut rare : il faut une valeur valide La même règle existe pour ON UPDATE, qui décide du sort des commandes quand l’identifiant du client change. Choisir la règle fait partie de la conception : c’est une décision de métier, pas un détail technique.
Figure 2. Que deviennent les commandes quand on supprime un client ?
  • RESTRICT (ou NO ACTION, presque équivalent) : la suppression est refusée tant qu’il reste des lignes qui en dépendent. C’est le comportement par défaut et le plus prudent.
  • CASCADE : la suppression se propage aux lignes qui dépendent (les commandes du client sont supprimées avec lui). Bien pour des lignes de détail qui n’ont pas de sens seules (les lignes d’une commande) ; dangereux ailleurs, car une suppression peut en entraîner des milliers d’autres.
  • SET NULL : les lignes qui dépendent restent, mais leur clé étrangère est mise à NULL. Possible seulement si la colonne accepte NULL.
  • SET DEFAULT : la clé étrangère prend la valeur par défaut de la colonne (par exemple un client « inconnu »). Rare, et la valeur par défaut doit exister dans la table cible.

Pour la modification de la clé (ON UPDATE), les mêmes options existent : CASCADE répercute le nouvel identifiant, RESTRICT refuse le changement. On modifie rarement une clé, ce qui est une bonne raison de choisir des clés stables. Le choix de la règle n’est pas qu’un détail technique : c’est une décision de métier. Supprimer un client doit-il effacer ses factures ? Une loi peut obliger à les conserver pendant des années ; la règle RESTRICT (ou une simple désactivation du compte plutôt qu’une suppression) s’impose alors.

3.6Les assertions : une promesse de la norme

Il existe enfin les assertions : des conditions qui doivent rester vraies sur l’ensemble des tables, déclarées par CREATE ASSERTION nom CHECK (condition), souvent avec une sous-requête (par exemple « le total des quantités de lignes d’une commande ne dépasse jamais le stock »). Elles ne sont pas implémentées dans tous les SGBD, et la situation n’a guère changé : l’instruction fait partie de la norme depuis 1992, mais les grands produits (PostgreSQL, MySQL, Oracle, etc.) ne la proposent pas, car vérifier une condition portant sur plusieurs tables à chaque modification coûte très cher. En pratique, on obtient le même effet par des déclencheurs, par des contraintes sur les colonnes bien choisies, ou par le programme lui-même, dans une transaction.

Pour s’exercer

Exercice 1 : écrire une table

Écrivez l’instruction qui crée une table produit avec : un identifiant (clé primaire), un libellé obligatoire, un prix en euros (jamais négatif) et une date de création qui prend la date du jour par défaut.

Voir la réponse

CREATE TABLE produit (
    id        INTEGER       PRIMARY KEY,
    libelle   VARCHAR(120)  NOT NULL,
    prix      DECIMAL(10,2) NOT NULL
                            CONSTRAINT ck_prix CHECK (prix >= 0),
    cree_le   DATE          DEFAULT CURRENT_DATE
);

Le prix est un DECIMAL (jamais un nombre à virgule flottante pour de l’argent), la contrainte est nommée pour que l’erreur soit lisible.

Exercice 2 : trouver l’erreur

Que reprochez-vous à cette instruction ? CREATE TABLE eleve (nom char(30), age integer, check (age < 100), email char(50) check (email LIKE "%@%"))

Voir la réponse

Les guillemets doubles autour de %@% : le standard réclame des apostrophes ('%@%'). La borne d’âge « inférieure à 100 » exclut des personnes réelles et mélange deux sujets : choisir plutôt une borne large comme 130. char(30) complète le texte par des espaces : VARCHAR est préférable. Aucun champ n’est NOT NULL : un élève sans nom est possible. Il n’y a pas de clé primaire : deux élèves identiques ne se distinguent pas. Enfin, aucune contrainte n’est nommée, donc les messages d’erreur seront obscurs.

Exercice 3 : la règle de suppression

Pour chacun de ces liens, quelle règle ON DELETE choisissez-vous ? (a) lignes d’une commande vers la commande ; (b) commandes vers le client, sachant que la loi impose de garder les factures ; (c) tâches vers la personne qui en est responsable, une tâche pouvant rester sans responsable pendant un temps.

Voir la réponse

(a) CASCADE : les lignes n’ont pas de sens sans leur commande, elles disparaissent avec elle. (b) RESTRICT (ou NO ACTION) : on refuse de supprimer un client qui a des commandes, et l’on désactive le compte plutôt que de l’effacer. (c) SET NULL : la tâche reste, sans responsable, à condition que la colonne accepte NULL.

4Modifier et supprimer une table

Une base évolue : on ajoute une colonne, on change un type, on renomme une table. Ces opérations relèvent de la « Modification de table ». Elles utilisent ALTER TABLE (« modifier la table »), plus DROP (« éliminer ») pour supprimer.

4.1Ajouter, modifier, retirer une colonne

ALTER TABLE client ADD telephone VARCHAR(20);
ALTER TABLE client DROP COLUMN telephone;

Ajouter une colonne est une opération douce : les lignes existantes reçoivent NULL (ou la valeur par défaut). Retirer une colonne est radical : ses données sont perdues. On ne peut retirer une colonne que si elle ne fait partie ni d’une vue, ni d’un index, ni d’une contrainte d’intégrité : c’est la règle générale, la base refuse (ou demande un mot clé CASCADE) quand d’autres objets en dépendent.

Pour changer le type d’une colonne, la syntaxe varie selon les produits : MODIFY chez Oracle et MySQL (ALTER TABLE client MODIFY nom VARCHAR(120)), ALTER COLUMN ... TYPE chez PostgreSQL, ALTER COLUMN dans Access et d’autres. C’est l’exemple typique de variante dont il faut lire la documentation. Changer un type peut obliger la base à réécrire toute la table, donc à la bloquer un moment : sur une grosse table, on le planifie.

On peut aussi ajouter ou retirer une contrainte après coup :

ALTER TABLE client ADD CONSTRAINT uq_client_email UNIQUE (email);
ALTER TABLE client DROP CONSTRAINT uq_client_email;

Ajouter une contrainte sur une table déjà remplie échoue si des lignes existantes la violent : il faut d’abord les repérer et les corriger. Avoir déclaré les règles dès le début aurait évité ce nettoyage.

4.2Renommer, commenter

La forme RENAME TABLE ancien TO nouveau n’existe pas partout : on écrit plutôt ALTER TABLE ancien RENAME TO nouveau chez PostgreSQL et Oracle, RENAME TABLE chez MySQL. Renommer une table casse tous les programmes qui l’appellent par son ancien nom : on ne le fait qu’en coordination.

Autre instruction utile, souvent oubliée : COMMENT ON, qui documente une table ou une colonne dans la base elle-même.

COMMENT ON TABLE client IS 'Personnes ayant passé au moins une commande';
COMMENT ON COLUMN client.age IS 'Âge en années, borné à 130';

La syntaxe COMMENT table IS '...' est celle d’un produit particulier ; la forme ci-dessus, avec ON TABLE ou ON COLUMN, est la plus répandue. Une base commentée est un cadeau fait à celui qui l’héritera, c’est-à-dire souvent à vous-même dans un an. MySQL et d’autres écrivent plutôt le commentaire dans la définition de la colonne.

4.3Supprimer : DROP et TRUNCATE

La clause DROP élimine des vues, des index et même des tables. Elle est à utiliser avec parcimonie dans la mesure où elle est irréversible :

DROP VIEW   vue_clients_paris;
DROP INDEX  idx_client_nom;
DROP TABLE  client;

Appliqué à une table, DROP supprime à la fois les données et la structure. Pour effacer seulement les données en gardant la structure, on emploie TRUNCATE TABLE client (« tronquer »). C’est plus rapide que DELETE FROM client, car la base ne supprime pas ligne par ligne, mais plus brutal : pas de condition possible, et dans certains produits l’opération ne peut pas être annulée (elle valide immédiatement). Deux garde-fous :

  • exécuter ces commandes avec un compte qui n’a pas les droits dans la production (partie 8), pour qu’une fausse manipulation soit impossible ;
  • avoir une sauvegarde récente dont on a vérifié qu’elle se restaure (voir les sauvegardes). Une anecdote classique d’exploitation : le DROP TABLE tapé dans la mauvaise fenêtre, celle de la production au lieu de celle de test.

Une habitude des équipes prudentes : au lieu de supprimer tout de suite, renommer d’abord (client_a_supprimer_2026), attendre quelques semaines que personne ne se plaigne, puis supprimer pour de bon.