SQL ALTER TABLE pour ajouter une colonne : le guide complet

Ajouter une colonne à une table existante en SQL est une tâche courante, que tu devras effectuer à mesure que ton schéma de base de données évolue. L’instruction ALTER TABLE te permet de modifier la structure d’une table existante sans perdre de données. Ce guide explique comment utiliser ALTER TABLE pour ajouter des colonnes, avec des exemples concrets.

Comprendre ALTER TABLE

La commande ALTER TABLE sert à ajouter, supprimer ou modifier des colonnes dans une table existante. Elle permet aussi de modifier les contraintes d’une table. Ce tutoriel se concentre spécifiquement sur l’ajout de nouvelles colonnes.

Pour ajouter une nouvelle colonne, tu dois indiquer le nom de la colonne, son type de données et les éventuelles contraintes ou valeurs par défaut. L’opération est atomique et n’affecte pas les données existantes.

Syntaxe de base pour ajouter une colonne

Voici la syntaxe de base pour ajouter une nouvelle colonne à une table :

ALTER TABLE table_name
ADD column_name column_type [constraint];

• table_name : le nom de la table à laquelle tu ajoutes une colonne.
• column_name : le nom de la nouvelle colonne.
• column_type : le type de données de la nouvelle colonne (par ex. VARCHAR, INT, DATE, etc.).
• constraint : facultatif. Toute contrainte supplémentaire comme NOT NULL, UNIQUE ou des valeurs DEFAULT.

Exemple : ajouter une colonne

Commençons avec une table employees toute simple :

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(50)
);

-- Current data in employees table:
INSERT INTO employees (id, name, position) VALUES (1, 'Alice', 'Engineer');
INSERT INTO employees (id, name, position) VALUES (2, 'Bob', 'Manager');

SELECT * FROM employees;

Résultat :

+----+-------+----------+
| id | name  | position |
+----+-------+----------+
|  1 | Alice | Engineer |
|  2 | Bob   | Manager  |
+----+-------+----------+

Ajouter une nouvelle colonne pour le salaire

Ajoutons une nouvelle colonne nommée salary de type DECIMAL(10, 2), qui peut stocker des nombres jusqu’à 10 chiffres, dont 2 décimales :

ALTER TABLE employees
ADD salary DECIMAL(10, 2);

Cette commande modifie la table employees pour y inclure la nouvelle colonne salary. Les lignes existantes auront des valeurs NULL dans cette colonne jusqu’à ce que tu les mettes à jour.

Vérifier la structure de la table

Vérifie que la colonne a bien été ajoutée avec DESCRIBE ou SHOW COLUMNS (selon ton système de base de données) :

DESCRIBE employees;

Résultat :

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(100)| YES  |     | NULL    |       |
| position | varchar(50) | YES  |     | NULL    |       |
| salary   | decimal(10,2)| YES |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

Ajouter une colonne avec une valeur par défaut

Tu peux aussi ajouter une colonne avec une valeur par défaut. Voici comment ajouter une colonne hire_date qui prend par défaut la date du jour :

ALTER TABLE employees
ADD hire_date DATE DEFAULT CURRENT_DATE;

Les nouvelles lignes insérées dans la table employees auront automatiquement hire_date défini à la date du jour si aucune valeur n’est fournie.

Ajouter une colonne avec une contrainte NOT NULL

Ajouter une colonne avec une contrainte NOT NULL à une table existante demande une attention particulière. Tu ne peux pas simplement ajouter une colonne NOT NULL à une table qui contient déjà des données sans fournir de valeur par défaut, car cela violerait la contrainte pour les lignes existantes.

Le problème de NOT NULL avec des données existantes

Cette commande échouera si la table contient déjà des lignes :

-- This will fail on tables with existing data
ALTER TABLE employees
ADD phone_number VARCHAR(20) NOT NULL;

L’erreur se produit parce que les lignes existantes auraient des valeurs NULL dans la nouvelle colonne, ce qui viole la contrainte NOT NULL.

Solution 1 : ajouter la colonne avec une valeur par défaut

La solution la plus simple consiste à fournir une valeur par défaut :

ALTER TABLE employees
ADD phone_number VARCHAR(20) NOT NULL DEFAULT 'Unknown';

Ça fonctionne parce que :

1. La colonne est ajoutée avec la valeur par défaut pour toutes les lignes existantes
2. La contrainte NOT NULL est respectée immédiatement
3. Les insertions futures devront fournir une valeur ou utiliser la valeur par défaut

Solution 2 : un processus en plusieurs étapes

Pour mieux contrôler les valeurs, procède en plusieurs étapes :

-- Step 1: Add the column as nullable
ALTER TABLE employees
ADD phone_number VARCHAR(20);

-- Step 2: Update existing rows with appropriate values
UPDATE employees 
SET phone_number = 'Unknown' 
WHERE phone_number IS NULL;

-- Step 3: Add the NOT NULL constraint
ALTER TABLE employees
ALTER COLUMN phone_number SET NOT NULL;

Remarque : la syntaxe de l’étape 3 varie selon le système de base de données :

• PostgreSQL: ALTER COLUMN phone_number SET NOT NULL
• MySQL: MODIFY COLUMN phone_number VARCHAR(20) NOT NULL
• SQL Server: ALTER COLUMN phone_number VARCHAR(20) NOT NULL

Solution 3 : des valeurs par défaut conditionnelles

Tu peux définir des valeurs par défaut différentes selon les données existantes :

-- Add column with temporary default
ALTER TABLE employees
ADD department VARCHAR(50) NOT NULL DEFAULT 'TBD';

-- Update with conditional logic based on position
UPDATE employees 
SET department = CASE 
    WHEN position = 'Engineer' THEN 'Engineering'
    WHEN position = 'Manager' THEN 'Management'
    ELSE 'General'
END;

-- Optionally remove the default if you don't want it for future inserts
ALTER TABLE employees
ALTER COLUMN department DROP DEFAULT;

Bonnes pratiques pour les colonnes NOT NULL

1. Anticipe : pense aux contraintes NOT NULL dès la conception initiale de la table
2. Utilise des valeurs par défaut pertinentes : choisis des valeurs par défaut cohérentes avec ta logique métier
3. Teste d’abord : teste toujours les opérations ALTER TABLE sur une copie de tes données
4. Documente les changements : garde une trace des modifications de schéma pour informer ton équipe
5. Pense à la migration des données : prévois comment remplir la nouvelle colonne avec des données pertinentes

Vérifier les contraintes NOT NULL

Après avoir ajouté une colonne NOT NULL, vérifie que la contrainte est bien en place :

-- Check table structure
DESCRIBE employees;

-- Verify constraint by attempting to insert NULL (should fail)
INSERT INTO employees (id, name, position, phone_number) 
VALUES (3, 'Carol', 'Analyst', NULL);
-- Error: Column 'phone_number' cannot be null

Opérations associées

Maintenant que tu sais ajouter des colonnes, tu voudras peut-être découvrir d’autres opérations ALTER TABLE :

• Comment renommer des colonnes avec ALTER TABLE
• PostgreSQL : changer le type d’une colonne
• Comment ajouter des colonnes dans Teradata

Ces opérations sont essentielles pour maintenir et faire évoluer ton schéma de base de données au fil des besoins de ton application.

Conclusion

Utiliser ALTER TABLE pour ajouter des colonnes permet de modifier le schéma sans perte de données. Cette flexibilité t’aide à adapter ta base de données aux besoins changeants de ton application. Sauvegarde toujours tes données avant de modifier le schéma et vérifie la compatibilité avec la logique existante de ton application.

Pour des opérations de base de données plus avancées et pour gérer efficacement tes bases SQL, essaie Beekeeper Studio, qui offre une interface intuitive pour gérer tes schémas et tes opérations SQL.