Ajouter/changer des contraintes et redéfinir une table

Les données qu'on utilise — legacy_user

Les tables en production ont souvent besoin de contraintes qui n'étaient pas incluses dans la conception initiale.

Des choses comme « attacher une valeur par défaut à cette colonne », « rendre email sans doublons (UNIQUE) », ou « marquer ce champ obligatoire comme NOT NULL ».

Cet article couvre l'ajout de colonnes et le rajout de valeurs par défaut, et la procédure de reconstruction de table pour ajouter des contraintes à une table existante.

Avant les exercices, jette un œil aux définitions de colonnes et aux données d'exemple de la table legacy_user.

① Exécute PRAGMA table_info(legacy_user); pour vérifier les noms de colonnes, les types, la clé primaire, NOT NULL et les valeurs par défaut.

② Exécute SELECT * FROM legacy_user; pour prévisualiser toutes les lignes.

ALTER TABLE ADD COLUMN — rajouter une colonne et une valeur par défaut

Pour ajouter une nouvelle colonne à une table existante, utilise ALTER TABLE table ADD COLUMN nom_col type contrainte.

Attacher DEFAULT valeur définit la valeur par défaut utilisée quand la colonne est omise, et au moment de l'ALTER la valeur remplit aussi chaque ligne existante.

ADD COLUMN accepte même NOT NULL combiné à DEFAULT (les lignes existantes sont remplies avec la valeur par défaut, donc elles ne violent pas NOT NULL).

En revanche, ADD COLUMN ne peut pas attacher directement UNIQUE ou un NOT NULL sans valeur par défaut.

Ceux-là nécessitent une « reconstruction de table ».

-- Ajouter une colonne avec DEFAULT (utiliser une table jetable pour voir le comportement)
DROP TABLE IF EXISTS alter_demo;
CREATE TABLE alter_demo(id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO alter_demo VALUES (1,'Alice'),(2,'Bob');

-- Ajouter created_on avec un DEFAULT → les lignes existantes prennent aussi la valeur
ALTER TABLE alter_demo ADD COLUMN created_on TEXT DEFAULT '2024-01-01';

-- NOT NULL est aussi accepté quand il est combiné à DEFAULT
ALTER TABLE alter_demo ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

SELECT * FROM alter_demo ORDER BY id;

Imagine le besoin : « ajouter une colonne status à la table user et traiter tous les utilisateurs existants comme active. » (Exécute correctement et l'explication apparaît.)

① Ajoute une colonne TEXT nommée status à legacy_user avec une valeur par défaut de 'active'.

② Puis exécute SELECT * FROM legacy_user; et confirme que la colonne status des 2 lignes existantes est remplie avec la valeur par défaut.

Ajouter des contraintes après coup signifie reconstruire la table

En MySQL tu peux attacher des contraintes directement à une table existante, comme ALTER TABLE legacy_user ADD CONSTRAINT uq_email UNIQUE (email);.

Dans la console de ce cours, rajouter UNIQUE ou NOT NULL à une table existante passe par la procédure de reconstruction de table : « créer une nouvelle table avec les contraintes voulues, copier les données existantes dedans, et remplacer l'ancienne table ».

MySQL / PostgreSQL exécutent aussi ce schéma de reconstruction en interne pour certaines opérations (comme les changements de type de colonne), selon l'outil.

La procédure suit le schéma ci-dessous : désactive la vérification de clé étrangère, puis CREATE la nouvelle table → copie toutes les lignes → DROP l'ancienne table → RENAME la nouvelle table → restaure la vérification.

À propos de ADD CONSTRAINT / AUTO_INCREMENT / commentaires de colonne en MySQL

Les syntaxes ALTER TABLE ... ADD CONSTRAINT, AUTO_INCREMENT et les commentaires de colonne (COMMENT '...') montrées ci-dessous sont de la syntaxe MySQL.

La console du navigateur dans ce cours ne peut pas les exécuter, donc ces deux blocs code sont en lecture seule (n'essaie pas de les exécuter).

Dans la console de ce cours, rajoute des contraintes via la reconstruction de table dans la section ci-dessous, et utilise INTEGER PRIMARY KEY pour la numérotation automatique.

-- Syntaxe MySQL (ne pas l'exécuter dans la console du cours — à lire simplement)

-- MySQL : attacher une contrainte UNIQUE directement à une table existante
ALTER TABLE legacy_user ADD CONSTRAINT uq_email UNIQUE (email);

-- MySQL : passer en NOT NULL via MODIFY
ALTER TABLE legacy_user MODIFY email VARCHAR(255) NOT NULL;

-- MySQL : la numérotation automatique est l'attribut de colonne AUTO_INCREMENT ; les descriptions sont des commentaires de colonne
CREATE TABLE member (
  member_id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL UNIQUE COMMENT 'login email'
);

-- Dans la console de ce cours, obtiens l'équivalent AUTO_INCREMENT via INTEGER PRIMARY KEY
CREATE TABLE member (
  member_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

-- Squelette de la reconstruction de table (utiliser la table jetable rebuild_demo pour voir le comportement)
DROP TABLE IF EXISTS rebuild_demo;
CREATE TABLE rebuild_demo(id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO rebuild_demo VALUES (1,'a@example.com'),(2,'b@example.com');

-- ① Désactiver la vérification de clé étrangère
PRAGMA foreign_keys=OFF;

-- ② Nouvelle table avec contraintes voulues (email NOT NULL UNIQUE)
CREATE TABLE rebuild_demo_new(
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

-- ③ Copier les données existantes
INSERT INTO rebuild_demo_new(id, email)
SELECT id, email FROM rebuild_demo;

-- ④ Supprimer l'ancienne table, ⑤ renommer la nouvelle avec l'ancien nom
DROP TABLE rebuild_demo;
ALTER TABLE rebuild_demo_new RENAME TO rebuild_demo;

-- ⑥ Restaurer la vérification de clé étrangère
PRAGMA foreign_keys=ON;

-- Confirmer que les contraintes sont en place (un email en doublon va maintenant errer)
SELECT sql FROM sqlite_master WHERE name='rebuild_demo';

Imagine le besoin : « rendre la colonne email de legacy_user obligatoire et sans doublons (NOT NULL et UNIQUE). » Exécute la procédure principale de reconstruction de table de l'article, étape par étape, exactement comme montrée dans le schéma ci-dessus.

① Désactive la vérification de clé étrangère (PRAGMA foreign_keys=OFF;).

② CREATE une nouvelle table nommée legacy_user_new avec id (INTEGER clé primaire), name (TEXT) et email (TEXT, NOT NULL, UNIQUE).

③ Copie chaque ligne de la table existante avec INSERT INTO legacy_user_new(...) SELECT ... FROM legacy_user;.

④ DROP l'ancien legacy_user et RENAME legacy_user_new en legacy_user.

⑤ Restaure la vérification de clé étrangère à ON.

⑥ Enfin, exécute SELECT sql FROM sqlite_master WHERE name='legacy_user'; et confirme que le nouveau schéma contient UNIQUE / NOT NULL.

Reproduis la même reconstruction sur une table jetable et vis comment la contrainte UNIQUE du nouveau schéma bloque effectivement un doublon. Cet exercice est autonome et utilise une table dédiée email_check.

① DROP TABLE IF EXISTS email_check; puis CREATE avec id (INTEGER clé primaire) et email (TEXT, NOT NULL, UNIQUE).

② INSERT une ligne (1, 'a@example.com') dans email_check.

③ Puis INSERT une autre ligne (2, 'a@example.com') dont la valeur email existe déjà. Elle viole la contrainte UNIQUE, donc une erreur est le résultat correct pour le second INSERT.