Cours MariaDB / Modifier une table par étapes avec ALTER TABLE

ALTER TABLE modifie la structure d’une table MariaDB existante : colonnes, types et contraintes notamment. Vérifiez d’abord que les lignes présentes respectent les nouvelles règles, puis séparez l’ajout d’une colonne, le remplissage des données et l’application des contraintes pour repérer les échecs et limiter les pertes d’information.

Conserver la définition actuelle et préparer la récupération

Une modification de structure ne se réduit pas à changer une étiquette. Réduire un type, imposer NOT NULL ou ajouter UNIQUE peut échouer à cause des lignes existantes, ou entraîner des conversions. Conservez la définition avec SHOW CREATE TABLE, une sauvegarde appropriée des données et la liste des requêtes et applications qui dépendent des colonnes. Reproduisez le changement dans un environnement distinct avant une utilisation réelle.

Les règles NULL, DEFAULT, NOT NULL et CHECK aident à définir le sens de la nouvelle contrainte. Préparez aussi la manière de retrouver les données en cas de changement destructeur : enregistrer uniquement la nouvelle définition ne suffit pas à récupérer les anciennes valeurs.

Changement Contrôle préalable Risque
Ajouter NOT NULL Nombre et signification des valeurs NULL Politique nécessaire pour les données absentes
Ajouter UNIQUE Groupes de valeurs répétées Échec en présence de doublons incompatibles
Réduire une longueur de texte Longueur maximale en caractères Troncature ou refus selon les conditions
Supprimer une colonne Requêtes dépendantes et besoin de conservation Suppression de ses valeurs enregistrées

La référence ALTER TABLE de MariaDB décrit les opérations prises en charge. Pour un changement existant, relevez aussi les droits nécessaires et les options réellement disponibles sur votre version.

Préparer des produits existants avant d’ajouter leur code

Connectez-vous au serveur d’apprentissage avec course_user dans shopcourse. Le compte doit pouvoir créer, lire, modifier et supprimer les objets d’exercice, ainsi que modifier leur structure. Le guide du client MariaDB et des fichiers UTF-8 explique la connexion. Enregistrez le bloc complet de préparation ci-dessous en UTF-8, par exemple dans C:/mariadb-lab/c38-preparation.sql, après avoir créé le dossier. Dans le client MariaDB, lancez SOURCE C:/mariadb-lab/c38-preparation.sql; avec le chemin réel. Ces instructions ne s’exécutent pas directement dans l’invite du système.

Avant de créer la table, contrôlez le serveur et la base avec SELECT VERSION(), DATABASE();, puis les noms existants avec SHOW TABLES;. Si products_38_lab existe déjà, examinez sa définition et ses lignes avant de décider si elle peut être nettoyée. Ne remplacez pas des données à conserver. Gardez le test d’erreur volontaire pour plus tard, dans une instruction séparée, plutôt que de placer tous les blocs dans un script exécuté sans contrôle.

USE shopcourse;
CREATE TABLE products_38_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p38_price CHECK (price >= 0),
  CONSTRAINT ck_p38_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_38_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

Vous obtenez six produits. Les identifiants 1 à 6 représentent un carnet, un stylo, une tasse, une lampe, un classeur et un câble. Les chaînes de catégories correspondent à la papeterie, aux articles de maison et à l’électronique. Conservez les littéraux coréens en UTF-8 pour reproduire le même jeu de données, et comparez les identifiants.

Nous ajoutons un code sku aux produits déjà présents. L’abréviation SKU (Stock Keeping Unit) désigne ici un code d’identification de produit. La règle P001 à P006 est propre à cet exercice : dans une application, définissez la longueur, la stabilité et le périmètre d’unicité des codes avant de les attribuer. Ne supposez pas que LPAD sur trois positions convient à tous les identifiants possibles.

Ajouter une colonne nullable, la remplir et comparer les comptes

Commencez par accepter NULL pour pouvoir renseigner les lignes existantes avant de rendre le code obligatoire. Le bloc suivant affiche la définition actuelle, ajoute la colonne, remplit les valeurs manquantes et effectue trois contrôles :

SHOW CREATE TABLE products_38_lab;
ALTER TABLE products_38_lab ADD COLUMN sku VARCHAR(20) NULL;
UPDATE products_38_lab SET sku=CONCAT('P',LPAD(product_id,3,'0'))
WHERE sku IS NULL;
SELECT COUNT(*) AS rows_total,COUNT(sku) AS known_sku,
 COUNT(DISTINCT sku) AS unique_sku FROM products_38_lab;

Juste après l’ajout, les six anciennes lignes ont sku à NULL. Après l’UPDATE, elles possèdent les codes P001 à P006. Les colonnes de contrôle rows_total, known_sku et unique_sku doivent toutes valoir 6. COUNT(sku) ne compte pas les NULL ; COUNT(DISTINCT sku) compte les codes distincts non NULL.

Si un compte est différent, arrêtez-vous avant l’ALTER suivant. Recherchez les valeurs manquantes et les groupes de codes répétés, puis corrigez-les selon une règle explicite. Le WHERE sku IS NULL évite de réécrire un code déjà présent, mais ne prouve pas à lui seul que les codes conservés sont corrects ou uniques.

Ne considérez pas ALTER puis UPDATE comme une seule transaction annulable. Les instructions provoquant une validation implicite ont leurs propres frontières. Notez quelle étape a réussi ; si l’exécution est interrompue, relisez la définition et les données avant de reprendre, au lieu de relancer aveuglément ADD COLUMN.

Appliquer NOT NULL, UNIQUE et CHECK puis vérifier un refus

Une fois les comptes réconciliés, appliquez les contraintes et relisez la définition enregistrée :

ALTER TABLE products_38_lab
 MODIFY COLUMN sku VARCHAR(20) NOT NULL,
 ADD CONSTRAINT uq_p38_sku UNIQUE(sku),
 ADD CONSTRAINT ck_p38_name CHECK(CHAR_LENGTH(product_name)>0);
SHOW CREATE TABLE products_38_lab;
SELECT product_id,product_name,sku FROM products_38_lab ORDER BY product_id;

Les lignes affichent les identifiants 1 à 6 avec les codes P001 à P006. sku est désormais obligatoire et unique. La contrainte sur le nom exige une longueur en caractères supérieure à zéro ; elle ne constitue pas une validation complète d’un nom métier, par exemple d’un texte composé seulement d’espaces.

Avec MODIFY COLUMN, comparez la définition actuelle et réénoncez les caractéristiques à conserver : type, longueur, règle NULL, valeur par défaut et autres attributs pertinents. Ne supposez pas que tous les attributs omis seront automatiquement préservés. Vérifiez SHOW CREATE TABLE après l’opération.

Exécutez maintenant séparément l’instruction volontairement incorrecte suivante. Elle tente d’attribuer au produit 2 le code P001 déjà utilisé par le produit 1 :

UPDATE products_38_lab SET sku='P001' WHERE product_id=2;

Une erreur de clé dupliquée, généralement 1062, est attendue. Recherchez ensuite les identifiants 1 et 2 et leurs sku : P001 doit rester sur le produit 1 et P002 sur le produit 2. Dans une base d’exercice, vérifiez aussi qu’un sku NULL et un nom de longueur nulle sont refusés. Ne désactivez pas les contraintes pour faire passer ces essais. Les conditions des contraintes sont détaillées dans la référence des définitions de table.

Évaluer les verrous et les options de changement en ligne

Selon la version, le moteur, la modification et la taille de la table, un ALTER peut nécessiter une copie interne ou des verrous. Les options ALGORITHM et LOCK ne rendent pas toutes les modifications possibles sans interruption. Vérifiez les combinaisons prises en charge et le comportement en cas de refus sur la version utilisée.

Une transaction longue peut conserver des verrous de métadonnées et retarder ALTER TABLE. La durée observée sur ces six lignes ne permet pas de prédire celle d’une table réelle. Examinez les volumes de données et d’index, l’espace disque temporaire nécessaire, les attentes de l’application et le temps de récupération. Selon le contexte, prévoyez une fenêtre de maintenance et un déploiement progressif qui reste compatible avec l’ancienne et la nouvelle structure.

Distinguer une suppression de colonne d’une restauration

Supprimer la colonne ajoutée efface les valeurs qu’elle a accumulées. Cela ne rétablit pas nécessairement l’état initial des données ni les écritures intervenues entre-temps. Un changement susceptible de perdre de l’information exige une sauvegarde antérieure et une procédure de restauration vérifiée, pas seulement un ALTER inverse.

Dans cet exercice, comparez les six noms, les prix et les stocks à leurs valeurs de préparation avant de nettoyer la table. Vérifiez également la définition et l’état réel si une étape s’est arrêtée. Sauvegardez ce qui doit être conservé, puis supprimez uniquement la table jetable ci-dessous. DROP TABLE enlève structure et données et n’est pas annulé par un ROLLBACK ordinaire ; gardez ce nettoyage hors des essais de modification réversible.

DROP TABLE shopcourse.products_38_lab;