Leçon 9.2 · Temps de lecture : ~9 min

L'instruction SQL ALTER TABLE modifie la structure d'une table existante sans la recréer : elle ajoute, modifie, renomme et supprime des colonnes, et ajoute ou supprime des contraintes. Dans cette leçon, vous verrez la syntaxe de chaque modification dans MySQL et PostgreSQL, pourquoi les données existantes peuvent bloquer une modification et comment trouver et supprimer une contrainte par son nom.

ALTER TABLE en SQL : modifier une table existante

Dans la leçon précédente, vous avez créé des tables avec CREATE TABLE. Les besoins évoluent : une table a besoin d'une nouvelle colonne, d'un champ texte plus long ou d'une contrainte oubliée. Supprimer puis recréer la table effacerait ses données ; on modifie donc la structure sur place avec ALTER TABLE.

Les bases d'exemple de SQLtest sont en lecture seule : ces instructions y renvoient une erreur de permission. Les exemples utilisent le schéma Sakila ; ils ont été vérifiés sur MySQL 8.0 et PostgreSQL 17.

Syntaxe d'ALTER TABLE

ALTER TABLE table_name
    action [, action ...];

L'action indique ce qu'il faut modifier. Un seul ALTER TABLE peut contenir plusieurs actions séparées par des virgules. Les plus courantes :

Ajouter une colonne

ALTER TABLE customer
ADD COLUMN loyalty_points INT NOT NULL DEFAULT 0;

Résultat : chaque client existant reçoit loyalty_points = 0 ; les nouvelles lignes reçoivent 0, sauf si l'INSERT fournit une valeur.

La nouvelle colonne est ajoutée à la fin de la table. MySQL peut la placer ailleurs avec AFTER column_name ou FIRST ; PostgreSQL l'ajoute toujours à la fin.

NOT NULL sans valeur par défaut

Si la table contient déjà des lignes, une colonne NOT NULL a besoin d'une valeur pour elles :

ALTER TABLE customer
ADD COLUMN phone VARCHAR(20) NOT NULL;

Résultat dans PostgreSQL : une erreur, column "phone" contains null values. MySQL accepte l'instruction et remplit les lignes existantes avec la valeur implicite du type : une chaîne vide ici, 0 pour les nombres.

Donnez donc toujours un DEFAULT quand vous ajoutez une colonne obligatoire à une table remplie, ou ajoutez la colonne en acceptant NULL, remplissez-la avec UPDATE et rendez-la NOT NULL seulement ensuite.

Modifier une colonne

Pour allonger la colonne email :

-- MySQL
ALTER TABLE customer
MODIFY COLUMN email VARCHAR(100) NOT NULL;

-- PostgreSQL
ALTER TABLE customer
ALTER COLUMN email TYPE VARCHAR(100);

Les deux bases traitent différemment le reste de la définition de la colonne :

• MODIFY dans MySQL remplace toute la définition de la colonne. Ce que vous ne répétez pas est perdu : ALTER TABLE customer MODIFY active SMALLINT; transforme une colonne NOT NULL DEFAULT 1 en colonne qui accepte NULL et n'a plus de valeur par défaut. Écrivez toujours la définition complète : MODIFY active SMALLINT NOT NULL DEFAULT 1.
• ALTER COLUMN dans PostgreSQL change une propriété à la fois et conserve les autres :

ALTER TABLE customer
    ALTER COLUMN active TYPE INT,
    ALTER COLUMN active SET DEFAULT 1,
    ALTER COLUMN email SET NOT NULL;

MySQL propose aussi CHANGE COLUMN old_name new_name definition, qui renomme et redéfinit une colonne en une seule étape.

Quand les données ne rentrent pas

La base vérifie les lignes existantes par rapport à la nouvelle définition :

• Réduire VARCHAR(50) à VARCHAR(10) échoue si une valeur est plus longue : « Data truncated for column 'email' » dans MySQL, « value too long for type character varying(10) » dans PostgreSQL.
• Ajouter NOT NULL échoue si la colonne contient NULL : « Invalid use of NULL value » dans MySQL, « column contains null values » dans PostgreSQL.

Corrigez d'abord les données, par exemple UPDATE customer SET email = '' WHERE email IS NULL;, puis relancez l'ALTER TABLE.

Renommer et supprimer

ALTER TABLE customer RENAME COLUMN email TO email_address;

ALTER TABLE customer DROP COLUMN loyalty_points;

ALTER TABLE customer RENAME TO client;

Résultat : la colonne prend un nouveau nom, la colonne loyalty_points est supprimée avec ses données et la table est renommée.

RENAME COLUMN fonctionne dans MySQL 8.0, MariaDB 10.5 et PostgreSQL ; SQL Server utilise la procédure sp_rename. Le renommage ne met pas à jour vos requêtes, vues et code applicatif : tout ce qui utilise l'ancien nom cesse de fonctionner.

DROP COLUMN ne peut pas être annulé par ROLLBACK dans MySQL, car le DDL valide implicitement (voir la leçon 8.4). Sauvegardez les données dont vous pourriez avoir besoin.

Ajouter des contraintes

Les contraintes de CREATE TABLE peuvent être ajoutées plus tard. Donnez un nom à chacune avec CONSTRAINT name, pour les retrouver et les supprimer facilement :

ALTER TABLE customer
ADD CONSTRAINT uq_customer_email UNIQUE (email);

ALTER TABLE payment
ADD CONSTRAINT chk_payment_amount CHECK (amount >= 0);

ALTER TABLE payment
ADD CONSTRAINT fk_payment_customer
    FOREIGN KEY (customer_id) REFERENCES customer (customer_id);

Une clé primaire s'ajoute de la même façon : ALTER TABLE table_name ADD PRIMARY KEY (column_name);.

Les données existantes peuvent bloquer une contrainte

Une nouvelle contrainte s'applique aux lignes déjà présentes dans la table. Si une seule ligne la viole, toute l'instruction échoue :

Trouvez les lignes problématiques avec un SELECT avant d'ajouter la contrainte. Par exemple, les paiements qui référencent un client inexistant :

SELECT p.payment_id, p.customer_id
FROM payment AS p
LEFT JOIN customer AS c ON c.customer_id = p.customer_id
WHERE c.customer_id IS NULL;

Résultat : les lignes orphelines qui bloqueraient la clé étrangère. Corrigez-les ou supprimez-les, puis ajoutez la contrainte.

Trouver et supprimer des contraintes

Pour supprimer une contrainte, il faut son nom. Si aucun nom n'a été donné, la base en a généré un, comme payment_ibfk_1 dans MySQL ou payment_customer_id_fkey dans PostgreSQL. La vue information_schema.table_constraints les liste :

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.table_constraints
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'payment'
ORDER BY CONSTRAINT_TYPE, CONSTRAINT_NAME;

Résultat sur Sakila : fk_payment_customer, fk_payment_rental et fk_payment_staff (FOREIGN KEY) et PRIMARY (PRIMARY KEY). Dans PostgreSQL, filtrez sur table_schema = 'public' au lieu de DATABASE().

Puis supprimez-la :

-- PostgreSQL, SQL Server and MySQL 8.0.19+
ALTER TABLE payment DROP CONSTRAINT fk_payment_customer;

-- MySQL: type-specific forms
ALTER TABLE payment DROP FOREIGN KEY fk_payment_customer;
ALTER TABLE payment DROP CHECK chk_payment_amount;
ALTER TABLE customer DROP INDEX uq_customer_email;

Dans MySQL, une contrainte UNIQUE est un index : elle se supprime donc avec DROP INDEX.

ALTER TABLE sur de grandes tables

Sur une table de plusieurs millions de lignes, certaines modifications réécrivent toute la table et bloquent les écritures pendant leur exécution. Ajouter une colonne qui accepte NULL ou une colonne avec une valeur par défaut constante est rapide dans les versions récentes : MySQL 8.0 le fait instantanément (ALGORITHM=INSTANT), tout comme PostgreSQL 11 et les versions suivantes. Changer le type d'une colonne, ajouter une clé étrangère à une grande table ou supprimer une colonne dans les anciennes versions peut prendre beaucoup de temps.

Sur les bases de production, testez d'abord la modification du schéma sur une copie et exécutez-la quand la charge est faible.

Points clés :

• ALTER TABLE modifie une table existante sur place : colonnes, contraintes et nom de la table.
• Ajoutez les colonnes obligatoires aux tables remplies avec un DEFAULT, ou remplissez-les avant de poser NOT NULL.
• Dans MySQL, MODIFY remplace toute la définition de la colonne ; répétez NOT NULL et DEFAULT, sinon ils sont perdus.
• Les lignes existantes doivent respecter le nouveau type, NOT NULL ou la contrainte, sinon l'instruction échoue.
• Nommez vos contraintes ; trouvez celles sans nom dans information_schema.table_constraints et supprimez-les avec DROP CONSTRAINT.
• Dans MySQL, ALTER TABLE valide implicitement et ne peut pas être annulé.

Questions fréquentes

Comment ajouter une colonne à une table existante en SQL ?

Utilisez ALTER TABLE table_name ADD COLUMN column_name data_type;, par exemple ALTER TABLE customer ADD COLUMN loyalty_points INT NOT NULL DEFAULT 0;. Les lignes existantes reçoivent la valeur par défaut, ou NULL si la colonne n'en a pas.

Comment changer le type de données d'une colonne en SQL ?

Dans MySQL, ALTER TABLE customer MODIFY COLUMN email VARCHAR(100) NOT NULL; avec la définition complète de la colonne. Dans PostgreSQL, ALTER TABLE customer ALTER COLUMN email TYPE VARCHAR(100);. La modification échoue si des valeurs existantes ne rentrent pas dans le nouveau type.

Comment ajouter une clé étrangère à une table existante ?

ALTER TABLE payment ADD CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id);. Chaque customer_id existant dans payment doit exister dans customer, sinon l'instruction échoue.

Comment supprimer une contrainte dont on ne connaît pas le nom ?

Cherchez-la dans information_schema.table_constraints pour la table, puis exécutez ALTER TABLE table_name DROP CONSTRAINT constraint_name;. Dans MySQL, les clés étrangères peuvent aussi être supprimées avec DROP FOREIGN KEY et les contraintes d'unicité avec DROP INDEX.

Questions d'entretien

Quelle est la différence entre MODIFY COLUMN dans MySQL et ALTER COLUMN dans PostgreSQL ?

MODIFY COLUMN remplace la définition complète de la colonne : les attributs non répétés, comme NOT NULL ou DEFAULT, sont supprimés. ALTER COLUMN dans PostgreSQL change une seule propriété, comme le type ou la valeur par défaut, et conserve le reste.

Pourquoi l'ajout d'un NOT NULL ou d'un UNIQUE peut-il échouer sur une table existante ?

La contrainte est vérifiée sur les lignes déjà présentes. NOT NULL échoue si une valeur est NULL, UNIQUE sur les doublons, une clé étrangère sur les valeurs absentes de la table référencée. Nettoyez d'abord les données.

Comment ajouter une colonne obligatoire à une grande table en production ?

L'ajouter avec un DEFAULT constant, ce qui est une modification rapide des métadonnées dans MySQL 8.0 et PostgreSQL 11+ ; ou l'ajouter en acceptant NULL, la remplir par lots avec UPDATE, puis poser NOT NULL. Tester sur une copie et l'exécuter à faible charge.