Introduction à l’instruction ALTER TABLE en SQL

En SQL, on distingue principalement trois types de commandes :

• DDL – Data Definition Language (langage de définition des données) qui regroupe les commandes SQL servant à définir le schéma de la base : création de tables, spécifications, modifications, etc. Exemples : CREATE, DROP, ALTER, etc. Les changements induits par des commandes DDL ne peuvent pas être annulés (pas de rollback).

• DML – Data Manipulation Language (langage de manipulation des données) qui permet d’exécuter des requêtes de sélection, insertion, mise à jour et suppression. Les changements induits par des commandes DML peuvent être annulés. Exemples : SELECT, INSERT, UPDATE et DELETE.

• DCL – Data Control Language (langage de contrôle des données) qui permet de définir les droits, permissions et autres contrôles de votre système de base de données.

Dans ce tutoriel, vous allez voir comment utiliser l’instruction ALTER TABLE, qui relève du DDL. Nous allons couvrir les points suivants :

• Pourquoi utiliser l’instruction ALTER TABLE
• Créer une table dans une base PostgreSQL
• Exécuter différentes variantes de ALTER TABLE dans PostgreSQL

Remarque : ce tutoriel n’explique pas comment créer une table, insérer des données ni sélectionner des données dans PostgreSQL. Pour un rappel sur ces sujets, consultez cet article.

Pourquoi utiliser l’instruction ALTER TABLE

Les spécifications d’une base évoluent avec le temps. Par exemple, vous pouvez avoir besoin d’ajouter une colonne à une table, de définir une nouvelle clé primaire, ou encore de changer le type d’une colonne. Supprimer la table et la recréer n’est souvent pas envisageable. L’instruction ALTER TABLE vous permet de faire tout cela très simplement. Avec ALTER TABLE, vous pouvez :

• ajouter, modifier ou supprimer une colonne ;
• ajouter ou supprimer une contrainte.

Vous pouvez aussi activer/désactiver des contraintes, mais ce point n’est pas couvert ici.

Nous allons illustrer chacun de ces cas d’usage. Avant cela, il vous faut une table PostgreSQL pour démarrer. Dans la section suivante, vous allez créer une table simple.

Créer une table dans une base PostgreSQL

Vous allez créer une table nommée student_details dans PostgreSQL (via l’outil pgAdmin) avec les colonnes et types suivants :

• student_id (integer)
• student_full_name (character)
• student_year (integer)

Nous commençons avec trois colonnes. Au fil du tutoriel, vous ajouterez d’autres colonnes et modifierez la table de différentes façons. La requête ci-dessous crée la table selon ces spécifications :

CREATE TABLE student_details(
 student_id SERIAL,
 student_full_name VARCHAR (50),
 student_year VARCHAR (100)
);
Propulsé par

Insérons aussi quelques enregistrements :

• INSERT INTO student_details(student_full_name, student_year) VALUES('John Doe','1st');
• INSERT INTO student_details(student_full_name, student_year) VALUES('Chris Villa','3rd');

Comme le type de student_id est SERIAL, PostgreSQL incrémente automatiquement la valeur de la colonne ; ce type est interprété par défaut comme un integer.

La table student_details devrait ressembler à ceci :

Votre table est prête dans PostgreSQL. Voyons maintenant d’autres usages de ALTER TABLE.

Exécuter différentes instructions ALTER TABLE dans PostgreSQL

Dans cette section, vous verrez l’utilisation de ALTER TABLE de deux manières :

• ajouter, modifier et supprimer une colonne ;
• ajouter et supprimer une contrainte.

Ajouter, modifier ou supprimer une colonne

Commençons par ajouter une colonne appelée student_grade (type caractère) à la table :

ALTER TABLE student_details ADD student_grade VARCHAR(5);
Propulsé par

La table devrait maintenant ressembler à ceci :

Remarquez que, pour les lignes déjà insérées, la colonne student_grade contient la valeur null. C’est le comportement implicite de la plupart des SGBDR. Vous pouvez bien sûr exécuter une requête de mise à jour pour renseigner ces valeurs. Passons à la modification d’une colonne avec ALTER TABLE. Nous allons réduire la taille maximale de student_year de 100 à 10, car réserver autant d’espace pour trois caractères (1st, 2nd ou 3rd) n’est pas pertinent.

ALTER TABLE student_details ALTER COLUMN student_year TYPE VARCHAR(10);
Propulsé par

Voici le résultat :

Vous pouvez aussi renommer une colonne. Exemple :

ALTER TABLE student_details RENAME student_id TO student_roll;
Propulsé par

Cette requête renomme la colonne student_id en student_roll. Vous pouvez le vérifier avec une requête SELECT ; le résultat devrait ressembler à l’illustration suivante :

Voyons maintenant comment supprimer une colonne d’une table :

ALTER TABLE student_details DROP COLUMN student_year;
Propulsé par

Cette requête supprime la colonne student_year ; la table devient :

Sachez que si la colonne supprimée est référencée par une autre table, vous risquez de rencontrer des erreurs d’intégrité référentielle.

Passons à l’ajout et à la suppression de contraintes.

Ajouter et supprimer une contrainte.

La table student_details n’a pas de clé primaire. Une clé primaire combine les contraintes not null et unique. Définissons la colonne student_roll (ex-student_id) comme clé primaire de student_details :

ALTER TABLE student_details ADD PRIMARY KEY (student_roll);
Propulsé par

Vous pouvez aussi ajouter d’autres contraintes. Par exemple, ajouter une contrainte check sur student_grade afin que ses valeurs soient toujours A, B ou C.

ALTER TABLE student_details ADD CONSTRAINT year_check CHECK (student_grade IN ('A', 'B', 'C'));
Propulsé par

year_check est le nom de la contrainte que vous ajoutez à la table. Si vous tentez maintenant d’insérer l’entrée suivante, vous obtiendrez une erreur :

INSERT INTO student_details(student_full_name, student_grade) VALUES('David Lune','3rd');
Propulsé par

L’erreur ressemble à ceci :

La suppression d’une contrainte est similaire : indiquez le nom de la contrainte à supprimer. Supprimons la contrainte year_check :

ALTER TABLE student_details DROP CONSTRAINT year_check;
Propulsé par

Vérifions. Si la contrainte year_check a bien été supprimée, alors la requête INSERT INTO student_details(student_full_name, student_grade) VALUES('David Lune','3rd'); ne doit plus provoquer d’erreur. La table contient désormais l’enregistrement :

Vous pouvez également expérimenter d’autres contraintes CHECK.

C’est tout pour ce tutoriel !

Ce tutoriel vous a présenté l’instruction ALTER TABLE pour modifier efficacement le schéma d’une table. Pour développer vos compétences SQL, vous pourriez être intéressé par les cours suivants proposés par DataCamp :