Partie 2 · SQL de base

Modifier et supprimer une relation

DROP TABLE relation supprime la table, ses données et sa définition. Si une table a été mal définie, mieux vaut la modifier que la reconstruire : on évite de perdre les données. C'est le rôle d'ALTER TABLE, qui permet d'ajouter/supprimer un attribut, une clé primaire ou un index, d'ajouter une contrainte d'unicité, de changer une valeur par défaut, une définition ou le nom de la table.

Besoin	Syntaxe du cours
Ajouter un attribut	`ALTER TABLE Personnes ADD fax DECIMAL(10,0)` ; position : `FIRST` ou `AFTER téléphone`. Le nom ne doit pas déjà exister.
Supprimer un attribut	`ALTER TABLE Personnes DROP prénom` : la colonne et ses valeurs disparaissent
Créer / supprimer une clé primaire	`ADD PRIMARY KEY (nom, prénom)` (seulement s'il n'y en a pas déjà) / `DROP PRIMARY KEY` (sans clé primaire, la commande est simplement ignorée)
Valeur par défaut	`ALTER attribut SET DEFAULT valeur` / `DROP DEFAULT` : n'affecte pas les lignes déjà insérées
Changer la définition	`MODIFY fax VARCHAR(14)` (même nom) ; `CHANGE fax num_fax VARCHAR(14)` (avec renommage)
Renommer la table	`ALTER TABLE Personnes RENAME Carnet`
Index	`ADD INDEX nom_complet (nom, prénom)` / `DROP INDEX nom_complet`

À toi de coder

Ajouter une colonne sans perdre les données

ALTER TABLE modifie la définition d'une relation après coup : ajouter ou supprimer une colonne, une clé, un index, changer un type ou le nom de la table. Plutôt que de supprimer la table et de la reconstruire (et de perdre ses données), on la modifie.

Ajoute la colonne fax (VARCHAR(14)) à Personnes. (Hors cours : MySQL accepte FIRST / AFTER pour choisir la place ; SQLite ajoute toujours en fin de table.)

import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE Personnes (id INTEGER PRIMARY KEY, nom TEXT, prenom TEXT, adresse TEXT, telephone TEXT);
INSERT INTO Personnes VALUES
  (1, 'Martin', 'Marc', '12 rue Lafayette, Paris', '0102030405'),
  (2, 'Dupond', 'Anne', '3 place Bellecour, Lyon', '0607080910'),
  (3, 'Martin', 'Pierre', '8 avenue de Paris, Lille', '0611223344'),
  (4, 'Chirac', 'Jacques', '1 rue du Port, Marseille', NULL);
CREATE TABLE Bibliotheque (nom TEXT, dernierlivre TEXT);
INSERT INTO Bibliotheque VALUES ('Dupond', 'Germinal'), ('Martin', 'Candide'), ('Chirac', 'Candide');
""")
con.execute("ALTER TABLE Personnes ___ fax VARCHAR(14)")
colonnes = [c[1] for c in con.execute("PRAGMA table_info(Personnes)")]
nb_lignes = con.execute("SELECT COUNT(*) FROM Personnes").fetchone()[0]

Ctrl/⌘+Entrée

Vérifie que tu as compris

Que risque-t-on avec ALTER TABLE Personnes DROP prénom quand UNIQUE(nom, prénom) existe ?

Vérifie que tu as compris

Quelle différence entre MODIFY et CHANGE ?

Optimiser

Après la suppression de grandes parties d'une table indexée, les index des tuples supprimés sont conservés et rallongent les sélections. OPTIMIZE TABLE Personnes supprime ces index obsolètes et comble les « trous ».