Créer des tables SQL et modifier des données

La définition d’une table décrit ce que chaque ligne peut contenir. Elle n’ajoute aucune ligne. Dans une boutique en ligne, chaque produit nécessite des champs distincts pour son identifiant, son nom, son prix et son nombre d’articles en stock.

Construire la définition

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC(8, 2) NOT NULL,
  in_stock INTEGER DEFAULT 0
);

Lis de l’extérieur vers l’intérieur. CREATE TABLE products nomme la nouvelle table. Les parenthèses contiennent les définitions de ses colonnes. Chaque ligne indique un nom de colonne, un type et les éventuelles règles de cette colonne. Les virgules séparent les définitions ; la dernière n’est pas suivie d’une virgule. Le point-virgule termine l’instruction. Après son exécution réussie, SELECT * FROM products; ne renvoie aucune ligne.

Cette déclaration de clé génère des identifiants dans SQLite. Dans PostgreSQL, utilise id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY pour les générer. La déclaration décimale se comporte aussi différemment selon le système ; choisir un stockage exact fait partie du choix du type.

Donner une colonne à chaque information

Une ligne de client pourrait contenir id, first_name, last_name, email et signup_date. Réunir « Ana Ruiz, ana@example.com » dans un seul champ rend difficiles la recherche et la vérification séparées du nom et de l’adresse e-mail. Nomme les champs selon leur contenu. Des noms courts en minuscules reliés par des tirets bas, comme unit_price, sont faciles à réutiliser.

Évite les mots-clés comme noms : orders fonctionne, tandis que order sans guillemets entre en conflit avec le mot-clé SQL ORDER. Un nom contenant des espaces doit être placé entre guillemets doubles, comme dans "order date", chaque fois que tu l’utilises. PostgreSQL convertit les noms sans guillemets en minuscules : Products et products sans guillemets désignent donc le même nom.

Les apostrophes ont un autre rôle : elles encadrent les valeurs textuelles comme 'Desk lamp'. Les guillemets doubles désignent les noms. PostgreSQL lit "Desk lamp" comme un nom de colonne, pas comme le texte d’un produit.

Vérifier ce qui a été créé

Une virgule finale avant ) provoque une erreur de syntaxe. Une virgule manquante en provoque souvent une aussi, mais SQLite peut lire plusieurs mots comme un seul nom de type. CREATE TABLE t (a INTEGER b TEXT); crée une colonne appelée a, et non deux colonnes. Examine la définition :

• Interpréteur SQLite : .schema products affiche l’instruction de création.
• SQL dans SQLite : PRAGMA table_info(products); liste les colonnes et leurs types.
• psql de PostgreSQL : \d products affiche les colonnes, leurs types et leurs contraintes.

CREATE TABLE IF NOT EXISTS products (...) évite une erreur lorsque le nom est déjà présent. Cette instruction ne compare pas et ne met pas à jour l’ancienne définition. Une table à laquelle manque une colonne nécessaire reste dans cet état.

Créer une copie ponctuelle

CREATE TABLE cheap_products AS
SELECT name, price FROM products WHERE price < 10;

Cette instruction crée des colonnes et copie les lignes de la requête. Les modifications ultérieures des produits ne mettent pas la copie à jour. Dans SQLite et PostgreSQL, la nouvelle table n’hérite pas de la clé primaire ni des règles NOT NULL ou CHECK de la source.

CREATE, ALTER et DROP appartiennent au langage de définition de données, ou LDD : ils modifient la structure. INSERT, UPDATE et DELETE relèvent du langage de manipulation de données, ou LMD : ils modifient les lignes. SELECT lit les données ; selon les ouvrages, il est inclus dans le LMD ou placé dans une famille propre aux requêtes.

Choisis un type selon l’usage de la valeur. Une quantité doit permettre les calculs. Un code postal américain doit conserver ses caractères, y compris les zéros initiaux. Les deux peuvent contenir des chiffres, mais doivent être stockés différemment.

Choisir les types numériques et textuels

Dans PostgreSQL, NUMERIC(8, 2) compte huit chiffres au total, dont deux après la virgule. Sa plus grande valeur positive est 999 999,99. L’insertion de 12,345 stocke 12,35 ; 1 234 567,00 provoque un dépassement car il faut sept chiffres avant la virgule. DECIMAL est un autre nom pour cette famille de types.

SQLite accepte NUMERIC(8, 2) mais n’impose ni cette précision ni cette échelle. Il stocke 19,99 en virgule flottante et conserve 12,345 sans arrondi. Stocke 1 999 centimes entiers pour un montant exact de 19,99. La virgule flottante donne des approximations des fractions binaires : SELECT 0.1 + 0.2 = 0.3; dans SQLite renvoie 0. PostgreSQL traite ces littéraux décimaux sans conversion comme des valeurs numériques exactes ; convertis-les en DOUBLE PRECISION pour voir le résultat approximatif.

Les nombres textuels sont triés caractère par caractère : '100', '25', '9' apparaissent dans cet ordre. Les quantités numériques nécessitent des colonnes numériques. Le code postal américain '02139' doit être stocké sous forme de texte ; le stockage en entier fait perdre son zéro initial.

Dans PostgreSQL, VARCHAR(20) rejette un texte de plus de 20 caractères. Son type CHAR(n) complète les textes plus courts avec des espaces jusqu’à une longueur fixe. TEXT n’a pas de limite de caractères déclarée. SQLite traite VARCHAR(n) et CHAR(n) comme du texte, sans imposer la longueur ni ajouter de remplissage.

Stocker les dates et les valeurs oui/non

PostgreSQL possède DATE, TIME, TIMESTAMP et BOOLEAN. Il rejette une DATE impossible comme '2026-02-30'. SQLite n’a pas de type de stockage dédié aux dates. Utilise du texte ISO comme '2026-03-15', dont l’ordre des caractères correspond à celui des dates. Le texte au format américain classe mal les dates d’années différentes : '12/01/2025' est trié après '03/15/2026'.

Un horodatage ajoute une heure, comme dans '2026-03-15 14:30:00'. PostgreSQL utilise TRUE et FALSE pour les booléens ; SQLite stocke 1 et 0 et accepte ces mots-clés. MySQL traite BOOLEAN comme un petit entier ; SQL Server utilise BIT.

Savoir ce que le système impose

Les tables SQLite ordinaires utilisent l’affinité de type, une préférence de stockage. Une colonne INTEGER convertit '5' en 5 mais accepte 'abc' comme texte. PostgreSQL rejette un texte qui ne peut pas devenir un entier. SQLite 3.37.0 et les versions ultérieures prennent en charge les tables STRICT, dont les types autorisés sont INT, INTEGER, REAL, TEXT, BLOB et ANY.

Pour les commandes, choisis un id entier, une adresse e-mail textuelle, une DATE PostgreSQL ou une date en texte ISO dans SQLite, NUMERIC(10, 2) dans PostgreSQL ou des centimes entiers dans SQLite pour le total, et un indicateur booléen ou 0/1 pour le paiement. NULL signifie que la valeur est manquante ou inconnue ; ce n’est ni un type ni l’équivalent de zéro ou d’une chaîne vide. Une colonne l’accepte sauf si une règle l’interdit.

Une contrainte fait rejeter par la base de données les données qui enfreignent une règle déclarée. La vérification s’effectue lors de l’insertion ou de la mise à jour de lignes, quelle que soit l’application qui transmet l’instruction. Des règles différentes protègent différentes parties d’un enregistrement de produit.

Combiner des règles dans une table

Cette définition SQLite génère la clé et vérifie les autres champs. Pour PostgreSQL, remplace la ligne de clé par une clé primaire sur une colonne d’identité.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  sku TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  price NUMERIC(8, 2) NOT NULL CHECK (price > 0),
  in_stock INTEGER NOT NULL DEFAULT 0 CHECK (in_stock >= 0)
);

NOT NULL exige une valeur. UNIQUE empêche la répétition d’un code SKU. CHECK (price > 0) rejette les prix nuls ou négatifs. Le nombre d’articles en stock a pour valeur par défaut 0 et doit rester positif ou nul. La déclaration numérique de SQLite ne permet pas des calculs décimaux exacts : utilise donc des centimes entiers lorsque l’exactitude compte.

Distinguer une omission de NULL

Insère une lampe de bureau avec le code SKU 'LAMP-01', le nom 'Desk lamp' et le prix 24,99, en omettant le stock dans la liste des colonnes. L’insertion réussit et le stock reçoit 0. Un stock explicitement NULL ne demande pas la valeur par défaut : il échoue à cause de NOT NULL.

Une tasse à café au prix de -3,50 échoue à la vérification du prix. Un deuxième produit avec le code SKU 'LAMP-01' échoue à cause de UNIQUE, même si son nom et son prix diffèrent. Un stylo sans nom échoue à cause de NOT NULL. Lis la règle ou la colonne nommée dans l’erreur et corrige la saisie.

La chaîne vide est une valeur réelle. name TEXT NOT NULL accepte '' dans SQLite et PostgreSQL. Ajoute CHECK (name <> '') pour la rejeter. Oracle traite la chaîne vide comme NULL : la même saisie y est donc soumise à une règle différente.

Vérifier les combinaisons et les valeurs manquantes

Une règle de colonne suit son type. Une règle de table est un élément distinct dans la définition de la table et peut comparer des colonnes :

CHECK (end_date >= start_date),
UNIQUE (order_id, product_id)

La première rejette une date de fin antérieure à la date de début. La seconde rejette une paire répétée : un produit peut figurer dans différentes commandes, mais pas deux fois dans la même commande. Une table peut avoir plusieurs contraintes d’unicité.

Une vérification rejette une ligne lorsque sa condition est fausse. Si le prix est NULL, price > 0 est inconnu : la vérification est donc satisfaite. C’est pourquoi le prix du produit nécessite aussi NOT NULL. PostgreSQL, SQLite et MySQL autorisent normalement plusieurs NULL avec UNIQUE ; SQL Server en autorise un seul dans une contrainte d’unicité sur une colonne. PostgreSQL 15 et les versions ultérieures proposent UNIQUE NULLS NOT DISTINCT lorsque les valeurs manquantes doivent aussi entrer en conflit. Les versions de MySQL antérieures à 8.0.16 ignoraient les règles CHECK.

Donne un nom utile à une règle avec CONSTRAINT price_positive CHECK (price > 0). Ce nom aide à l’identifier dans une erreur et à la supprimer ultérieurement. Une contrainte vérifie exactement son expression : CHECK (email LIKE '%@%') repère l’absence d’arobase, mais ne peut pas détecter une faute de frappe dans une adresse qui correspond par ailleurs au motif.

Une clé primaire identifie une ligne de façon fiable. Elle peut être constituée d’une colonne ou d’une combinaison de colonnes, mais une table possède au plus une clé primaire. La clé doit être unique et présente pour que d’autres instructions puissent désigner une ligne sans dépendre de sa description.

Choisir un identifiant stable

Une clé naturelle est un identifiant existant dans le monde réel, comme l’ISBN d’un livre. Une clé de substitution est un identifiant artificiel, souvent un entier généré. L’adresse e-mail d’un client peut changer ; un identifiant client généré peut rester le même malgré ce changement.

Une clé composite identifie une combinaison :

PRIMARY KEY (order_id, product_id)

Une commande peut contenir plusieurs produits et un produit peut figurer dans plusieurs commandes. La paire identifie une ligne de commande. Aucune des deux colonnes n’a besoin d’être unique à elle seule.

PostgreSQL, MySQL et SQL Server imposent la présence des clés primaires. Les tables SQLite ordinaires conservent une exception historique : une clé primaire autre que INTEGER PRIMARY KEY peut contenir NULL. Utilise code TEXT PRIMARY KEY NOT NULL pour une clé textuelle. Les tables SQLite STRICT comblent aussi cette lacune.

Laisser la base de données générer le numéro

Omets id de la liste des colonnes de l’insertion pour demander une valeur générée. Dans PostgreSQL, un simple INTEGER PRIMARY KEY n’en génère pas. Le code PostgreSQL plus ancien utilise souvent SERIAL.

Dans SQLite, INTEGER PRIMARY KEY est un alias du numéro de ligne interne. Pars des identifiants générés 1, 2 et 3, puis supprime 3. L’identifiant normalement généré ensuite est de nouveau 3, soit un de plus que le maximum actuel. Avec INTEGER PRIMARY KEY AUTOINCREMENT, c’est 4 : un numéro attribué à une ligne validée n’est pas réutilisé. Un numéro issu d’une insertion annulée peut toutefois être réutilisé. Cette garantie plus forte entraîne un travail supplémentaire : choisis-la donc lorsque l’interdiction de réutiliser un numéro est nécessaire.

Lire les identifiants sans les prendre pour des décomptes

Les trous sont normaux. Les suppressions en laissent et PostgreSQL ne réattribue pas les numéros de séquence après des insertions qui échouent ou sont annulées. Utilise COUNT(*) pour compter les lignes et un horodatage pour connaître l’heure d’un événement. Le plus grand identifiant ne répond de manière fiable à aucune de ces deux questions.

La forme GENERATED ALWAYS de PostgreSQL rejette normalement un identifiant explicite comme 50. GENERATED BY DEFAULT et SERIAL acceptent les valeurs explicites, mais fournir 50 ne fait pas avancer le générateur au-delà de 50. Une valeur générée ultérieurement peut entrer en conflit avec celle-ci.

Pour utiliser la clé d’une nouvelle ligne dans une ligne liée, récupère-la :

INSERT INTO customers (first_name, last_name, email)
VALUES ('Dev', 'Patel', 'dev@example.com')
RETURNING id;

RETURNING fonctionne dans PostgreSQL et SQLite 3.35.0 ou version ultérieure. SQLite propose aussi last_insert_rowid(), MySQL propose LAST_INSERT_ID() et SQL Server utilise OUTPUT. Lis le résultat de l’insertion au lieu de deviner l’identifiant suivant.

Le customer_id d’une commande doit pointer vers un client existant. Une clé étrangère fait vérifier ce lien par la base de données. La table qui contient la référence est la table enfant ; celle vers laquelle elle pointe est la table parente.

Déclarer et contrôler le lien

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id)
);

Ici, customers doit d’abord exister et son id doit être une clé primaire ou une clé unique. Fais correspondre les types des deux côtés. La forme de table, FOREIGN KEY (customer_id) REFERENCES customers(id), déclare le même lien séparément de la colonne.

Lorsque le contrôle est activé, une commande pour le client 99 échoue si aucun client 99 n’existe. Mettre à jour une commande pour l’associer à ce client absent échoue aussi. Une clé étrangère qui accepte les valeurs manquantes peut contenir NULL, ce qui signifie qu’il n’y a aucune ligne liée ; NOT NULL rend le lien obligatoire.

Dans SQLite, exécute PRAGMA foreign_keys = ON; à chaque nouvelle connexion avant de démarrer une transaction. Un changement de paramètre dans une transaction n’a aucun effet. PostgreSQL et SQL Server contrôlent normalement les clés étrangères, tout comme le moteur InnoDB utilisé par défaut dans MySQL.

Prévoir la suppression d’une ligne parente

Prenons les clients 1, Ana, et 2, Ben. Les commandes 101 et 102 appartiennent à Ana ; 103 appartient à Ben. Examinons la suppression du client 1 selon différentes déclarations :

SET NULL nécessite une colonne qui accepte les valeurs manquantes. SET DEFAULT doit toujours respecter la clé étrangère : une valeur par défaut non manquante doit identifier une ligne parente existante. Une valeur par défaut NULL fonctionne lorsque les liens manquants sont autorisés.

Choisis selon le sens des données. Les lignes de commande appartiennent souvent entièrement à leur commande : une cascade peut donc convenir. Pour les commandes à conserver, il faut une action bloquante lorsqu’on essaie de supprimer leur client. Un lien facultatif vers un fournisseur peut être défini à NULL. Appliquer des cascades partout permet à une seule suppression d’effacer plus d’enregistrements que le nom de sa table ne le laisse penser.

Respecter l’ordre des dépendances

Crée les tables parentes avant les tables enfants et insère les lignes parentes avant les lignes qui les référencent. Pour supprimer des données ou des tables liées, commence par les enfants et termine par les parentes. Une action ON UPDATE offre les mêmes choix lorsque la clé parente change ; ON UPDATE CASCADE copie la nouvelle clé dans les lignes enfants.

RESTRICT vérifie immédiatement. NO ACTION, la valeur par défaut, peut attendre la fin de l’instruction, ou la fin de la transaction pour une contrainte différable. Les deux bloquent une suppression ordinaire qui laisserait des références sans correspondance. Garde les identifiants générés stables pour que la modification des informations d’un client ne nécessite pas celle de toutes les références à ce client.

INSERT ajoute des lignes à une table existante. Sa liste de colonnes établit la correspondance entre les valeurs que tu fournis et les champs qui les reçoivent. Écris-la explicitement pour qu’une modification ultérieure de la table ne change pas silencieusement le sens de ta saisie.

Associer les valeurs selon leur ordre

INSERT INTO products (sku, name, price, in_stock)
VALUES ('LAMP-01', 'Desk lamp', 24.99, 12);

Lis les correspondances dans l’ordre : le code SKU reçoit 'LAMP-01', le nom reçoit 'Desk lamp', le prix reçoit 24,99 et le stock reçoit 12. Il faut quatre valeurs pour ces quatre colonnes. Un nombre incorrect, comme deux valeurs pour trois colonnes, provoque un échec.

Sans la liste, les valeurs suivent l’ordre des colonnes déclaré dans la table. SQLite exige une valeur pour toutes les colonnes ; PostgreSQL en accepte moins et attribue leurs valeurs par défaut aux colonnes finales. Une liste explicite évite de dépendre de l’un ou l’autre de ces comportements. Elle permet aussi de placer les colonnes dans un ordre clair pour la saisie.

Une paire inversée peut être pire qu’une erreur de syntaxe. Fournir 9,99 comme nom et 'Desk lamp' comme prix conduit PostgreSQL à rejeter le prix non numérique. L’affinité ordinaire de SQLite peut stocker le texte « 9.99 » comme nom et le texte « Desk lamp » comme prix sans erreur. Relis la ligne insérée avec SELECT pour vérifier la correspondance.

Écrire correctement les valeurs littérales

L’apostrophe doublée permet de stocker une seule apostrophe. Les nombres n’ont ni symbole monétaire ni séparateur de milliers ; une virgule sépare les valeurs, donc une virgule dans un montant sans guillemets modifie le nombre de valeurs. 'NULL' est le texte de quatre lettres « NULL », pas une valeur manquante.

Laisser les champs omis se remplir

Une colonne omise reçoit sa valeur par défaut, ou NULL si elle n’en a pas. Une clé générée reçoit une valeur générée. Ce dernier comportement dépend de la déclaration : INTEGER PRIMARY KEY dans SQLite génère des identifiants ; PostgreSQL nécessite une déclaration d’identité à cette fin. Dans PostgreSQL, une simple clé primaire entière omise lors d’une insertion provoque un échec car sa valeur serait NULL.

PostgreSQL, MySQL et SQL Server autorisent DEFAULT à la place d’une valeur. SQLite ne l’accepte pas dans une liste VALUES ; omets plutôt cette colonne. Un NULL explicite ne demande jamais la valeur par défaut.

Ajouter plusieurs lignes ensemble

INSERT INTO customers (first_name, last_name, email) VALUES
  ('Ana', 'Ruiz', 'ana@example.com'),
  ('Ben', 'Okafor', 'ben@example.com'),
  ('Chloe', 'Nguyen', 'chloe@example.com');

Dans une table vide dont la clé générée commence à 1, ces clients reçoivent les identifiants 1, 2 et 3. Avec la gestion ordinaire des contraintes, l’instruction multiligne réussit ou échoue dans son ensemble. Si une ligne enfreint une règle, aucune des lignes de cette instruction n’est insérée.

Une erreur UNIQUE indique une clé ou une valeur en double ; NOT NULL indique une donnée manquante ; CHECK indique une condition non satisfaite ; et une erreur de clé étrangère indique un lien invalide. Corrige la valeur ou la correspondance voulue avant de réessayer.

Les lignes n’ont pas besoin d’être saisies une par une. Une requête peut fournir les lignes à insérer, et une règle de conflit peut déterminer ce qui se passe lorsqu’une valeur unique existe déjà. Dans les deux cas, il faut faire correspondre précisément les données voulues.

Copier le résultat d’une requête

INSERT INTO archived_orders (id, customer_id, order_date, total)
SELECT id, customer_id, order_date, total
FROM orders
WHERE order_date < '2026-01-01';

Il n’y a pas de clause VALUES : la requête fournit les valeurs. Fais correspondre les colonnes sélectionnées à la liste cible selon leur ordre. La table cible doit déjà exister et ses règles s’appliquent toujours.

Prévisualise le SELECT seul. Les commandes 201, 202 et 204 portent les dates 2025-11-03, 2025-12-19 et 2025-10-30 et les montants 45,00, 120,50 et 60,00. Les commandes 203 et 205 datent de 2026. La requête renvoie trois lignes pour un total de 225,50, et l’insertion copie ces trois lignes. Elle peut aussi ne copier aucune ligne si le filtre ne trouve rien.

Une seconde exécution essaie de les copier à nouveau. Une clé primaire dans la table cible provoque alors un échec au lieu de stocker silencieusement des doublons. INSERT ... SELECT est une opération de copie : elle ne supprime pas les lignes sources et ne maintient pas les deux tables synchronisées.

Les données importées d’un fichier passent souvent d’abord par une table intermédiaire. L’interpréteur SQLite utilise .import --csv file.csv staging ; psql utilise \copy. Dans une requête ultérieure, TRIM(email) supprime les espaces en début et en fin de chaîne et NULLIF(TRIM(email), '') transforme une adresse e-mail vide ou composée d’espaces en NULL. Les contraintes de la table de destination vérifient ensuite les lignes nettoyées.

Lire le résultat d’une écriture

INSERT INTO customers (first_name, last_name, email)
VALUES ('Dev', 'Patel', 'dev@example.com')
RETURNING id;

Le résultat fournit l’identifiant client généré à utiliser dans une ligne liée. PostgreSQL et SQLite 3.35.0 ou version ultérieure prennent en charge RETURNING pour les insertions, mises à jour et suppressions. MySQL utilise LAST_INSERT_ID() pour un identifiant généré ; SQL Server utilise OUTPUT.

Décider comment traiter les doublons

Avec stock (sku TEXT PRIMARY KEY, qty INTEGER NOT NULL), supposons que 'LAMP-01' ait une quantité de 12. Une insertion simple proposant le même code SKU et une quantité de 20 échoue. Dans SQLite et PostgreSQL :

INSERT INTO stock (sku, qty) VALUES ('LAMP-01', 20)
ON CONFLICT (sku) DO UPDATE
SET qty = stock.qty + excluded.qty;

stock.qty est la quantité existante, 12. excluded.qty est la quantité proposée, 20. Cette forme stocke 32, ce qui convient à une livraison supplémentaire. SET qty = excluded.qty stocke 20, ce qui convient à un nombre de remplacement. DO NOTHING conserve 12 et ignore la ligne en conflit. Chaque variante part de la même quantité initiale, 12.

Un upsert combine l’insertion d’une nouvelle ligne et une mise à jour en cas de conflit. La cible du conflit doit être protégée par une contrainte d’unicité adaptée. La famille d’instructions équivalente dans MySQL est ON DUPLICATE KEY UPDATE. Un simple INSERT ne corrige pas un client existant : il crée une autre ligne ou provoque une erreur d’unicité. Utilise UPDATE pour une ligne dont tu sais qu’elle existe, et un upsert lorsqu’elle existe peut-être déjà.

UPDATE modifie des valeurs dans des lignes existantes. Il indique une table, attribue de nouvelles valeurs avec SET, puis choisit les lignes ciblées avec WHERE. Les contraintes de la table vérifient les valeurs obtenues.

Définir un ou plusieurs champs

UPDATE products
SET price = 22.50, in_stock = 30
WHERE id = 3;

Le produit de clé 3 reçoit les deux valeurs ; les autres lignes conservent les leurs. Les affectations sont séparées par des virgules. Écrire SET price = 22.50 AND in_stock = 30 ne signifie pas deux affectations. PostgreSQL rejette cette écriture ; SQLite traite le membre de droite comme une expression booléenne et peut stocker 0 ou 1 dans le prix.

L’absence de WHERE est du SQL valide et l’instruction s’applique à toutes les lignes. UPDATE products SET price = 0; fixe le prix de chaque produit à zéro si les règles de la table l’autorisent. Un aperçu avec la condition voulue montre si la cible comprend une ligne, plusieurs lignes ou aucune.

Calculer à partir de la valeur stockée

UPDATE products
SET price = price * 1.10;

Un prix de 20,00 devient 22,00 après une augmentation de 10 %. Un prix de 19,99 donne un résultat non arrondi de 21,989. PostgreSQL stocke 21,99 dans une colonne NUMERIC(8, 2). SQLite n’impose pas l’échelle : SET price = ROUND(price * 1.10, 2) demande donc deux décimales. Pour stocker des montants exacts dans SQLite, conserve des centimes entiers et indique explicitement comment les arrondir.

L’expression doit inclure l’ancienne valeur lorsque la modification est relative. SET in_stock = in_stock - 1 retire une unité ; SET in_stock = 1 remplace le nombre par un.

Dans SQLite et PostgreSQL, toutes les expressions à droite lisent la ligne avant la mise à jour. UPDATE t SET a = b, b = a; échange les valeurs d’une ligne, qui passent de 1 et 2 à 2 et 1. La mise à jour d’une seule table dans MySQL évalue les affectations de gauche à droite : la même forme fixe donc les deux valeurs à 2. Ne suppose pas que ce comportement est identique d’un système à l’autre.

Tenir compte des règles et des valeurs manquantes

Utilise SET phone = NULL pour effacer un champ de téléphone. Pour trouver les valeurs de téléphone manquantes, utilise WHERE phone IS NULL. Une égalité avec NULL ne les trouve pas.

Prenons les prix 12,00, 3,00 et 8,50 protégés par CHECK (price > 0). Soustraire 5 à chaque prix ferait passer le deuxième à -2,00. Avec la gestion ordinaire des contraintes, cet échec annule toute l’instruction : même les prix qui seraient valides restent donc inchangés.

La mise à jour d’une clé primaire doit aussi respecter les clés étrangères liées. Une modification de clé bloquée échoue tant que des lignes enfants pointent vers l’ancienne clé ; ON UPDATE CASCADE leur transmet la nouvelle valeur. Garder les identifiants générés stables évite ce travail supplémentaire lorsque les informations descriptives changent.

Interpréter le nombre de lignes affectées

psql indique un résultat comme UPDATE 3 ; changes() dans SQLite indique le nombre de lignes concernées par la dernière instruction de modification. Les deux comptent les lignes correspondantes et mises à jour même lorsque la valeur attribuée est égale à l’ancienne. Un décompte de trois ne garantit pas trois valeurs visiblement différentes. Compare le nombre à l’aperçu et examine ensuite les champs concernés, ou utilise RETURNING pour voir directement les lignes affectées.

DELETE supprime des lignes entières. Il laisse la table prête à contenir de nouvelles lignes. Pour effacer un seul champ, on utilise UPDATE ... SET column = NULL ; DELETE email FROM customers n’est pas du SQL valide.

Suivre une suppression filtrée

La boutique possède les commandes suivantes :

DELETE FROM orders
WHERE status = 'cancelled'
  AND order_date < '2026-01-01';

La commande 201 satisfait les deux conditions, tout comme 204. La commande 203 est annulée mais trop récente ; 202 est assez ancienne mais expédiée. L’instruction supprime deux lignes et conserve 202, 203 et 205. DELETE FROM orders; sans condition supprimerait les cinq.

Utiliser des données liées pour choisir les lignes

DELETE FROM order_items
WHERE order_id IN (
  SELECT id FROM orders WHERE status = 'cancelled'
);

La requête interne produit les identifiants des commandes annulées. IN vérifie si l’order_id de chaque ligne de commande figure dans cette liste. Exécute la requête interne seule pour examiner les clés, puis prévisualise les lignes de commande avec la même condition.

Les clés étrangères influent aussi sur la suppression. Une ligne parente référencée par des lignes enfants peut être protégée par NO ACTION ou RESTRICT ; une cascade supprime les lignes enfants ; SET NULL les conserve mais efface le lien. Vérifie ces actions lorsque tu évalues l’effet de la suppression d’une ligne parente, car la table directement nommée n’est peut-être pas la seule modifiée.

Choisir ce qui doit rester

PostgreSQL, MySQL, SQL Server et Oracle prennent en charge TRUNCATE ; SQLite ne le prend pas en charge. Les règles de transaction et de dépendance de cette instruction varient selon la base de données. Même une instruction qui semble vider rapidement une table doit respecter ces règles.

Conserver l’historique avec une suppression logique

UPDATE customers
SET deleted_at = '2026-03-15'
WHERE id = 7;

Une suppression logique marque la ligne sans l’effacer. Elle peut préserver l’historique et permettre une restauration par effacement de la marque. Les requêtes portant sur les clients actifs doivent inclure WHERE deleted_at IS NULL ; sinon, les clients marqués apparaissent toujours. Marquer des données ne les efface pas.

Une suppression validée ne peut pas être annulée par un ROLLBACK ultérieur. La restauration nécessite une copie enregistrée ou une sauvegarde. Dans une transaction ouverte, une annulation peut encore restaurer les lignes supprimées. Vérifie les identifiants sélectionnés et le nombre de lignes affectées avant de rendre le résultat permanent.

Choisir les lignes est souvent la partie la plus difficile d’une modification. Le même filtre, sans conséquence dans une requête, peut supprimer ou mettre à jour trop de lignes lorsqu’il est réutilisé dans une écriture. Traduis la règle voulue en condition, puis examine les lignes auxquelles elle correspond réellement.

Prévisualiser la condition exacte

SELECT id, status, order_date FROM orders
WHERE status = 'cancelled'
  AND order_date < '2026-01-01';

Cet aperçu renvoie les commandes 201 et 204 dans l’exemple des cinq commandes de la boutique. Réutilise exactement cette condition dans la suppression. La réécrire en changeant la commande crée une nouvelle sélection qui n’a pas été vérifiée.

Pour une ligne précise, utilise sa clé primaire : WHERE id = 42 correspond à une ligne au maximum. Un nom descriptif comme 'Desk lamp' peut être commun à plusieurs produits.

Regrouper explicitement les alternatives

AND est prioritaire sur OR. Compare :

WHERE status = 'cancelled'
   OR status = 'expired' AND order_date < '2026-01-01'

avec :

WHERE (status = 'cancelled' OR status = 'expired')
  AND order_date < '2026-01-01'

La première condition accepte toutes les commandes annulées, quelle que soit leur date, ainsi que les commandes expirées avant 2026. Elle correspond à 201, 203 et 204. La seconde applique le test de date aux deux statuts et correspond à 201 et 204. La commande 203 montre la différence parce qu’elle est annulée en 2026.

Inclure volontairement les valeurs manquantes

WHERE customer_id = NULL ne correspond à aucune ligne. Les valeurs manquantes nécessitent IS NULL ou IS NOT NULL. Un test négatif présente aussi une lacune : status <> 'shipped' n’inclut pas un statut manquant, car le résultat de la comparaison est inconnu.

Si un statut manquant appartient au groupe voulu, écris :

WHERE status <> 'shipped' OR status IS NULL

Lorsque tu ajoutes une autre condition, regroupe toute cette alternative entre parenthèses pour que les deux branches satisfassent cette condition.

La recherche par motif peut dépasser la description voulue. LIKE '%lamp%' correspond à « Desk lamp » et à « Clamp ». Le LIKE par défaut de SQLite ignore aussi la casse des lettres anglaises : il trouve donc « Lamp shade ». Un motif est utile pour trouver des correspondances possibles, mais examine-les avant de les modifier.

Choisir une comparaison à une liste pour une liste

UPDATE orders SET status = 'on hold'
WHERE customer_id IN (
  SELECT id FROM customers WHERE state = 'CA'
);

La sous-requête peut fournir plusieurs identifiants de clients ; IN teste leur appartenance à la liste. Remplacer IN par = demande un seul résultat scalaire. PostgreSQL rejette plusieurs lignes dans ce cas, tandis que SQLite utilise silencieusement la première ligne renvoyée et manque les autres clients.

Après la modification, compare le nombre de lignes affectées à l’aperçu. PostgreSQL et SQLite peuvent aussi ajouter RETURNING * pour afficher les lignes affectées. Pour une écriture risquée, démarre une transaction avant de modifier les données, examine le résultat, puis choisis COMMIT ou ROLLBACK.

Une transaction regroupe des instructions liées en une seule unité de travail. Tu décides de conserver leurs modifications ensemble avec COMMIT ou de les annuler ensemble avec ROLLBACK. Cette propriété du tout ou rien s’appelle l’atomicité, le A de ACID.

Conserver les deux volets d’un transfert

Le compte de Maya, identifiant 1, contient 500,00. Le compte de Leo, identifiant 2, contient 120,00. Transférer 75,00 nécessite deux modifications :

BEGIN;
UPDATE accounts SET balance = balance - 75.00 WHERE id = 1;
UPDATE accounts SET balance = balance + 75.00 WHERE id = 2;
SELECT id, balance FROM accounts WHERE id IN (1, 2);
COMMIT;

Après la première mise à jour, Maya a 425,00. Après la seconde, Leo a 195,00. La vérification affiche les deux soldes avant la validation et le total reste de 620,00. Pour ces soldes décimaux exacts, utilise NUMERIC dans PostgreSQL ; dans SQLite, une version utilisant des montants exacts stocke 50 000 et 12 000 centimes et transfère 7 500 centimes.

Sans transaction explicite en mode de validation automatique, la première mise à jour pourrait devenir permanente avant l’exécution de la seconde. Une panne entre les deux conserverait le retrait sans le dépôt. Une transaction permet de décider une seule fois de conserver ou d’annuler les deux mises à jour. Si une instruction échoue, traite l’erreur avant de considérer le transfert comme terminé.

Comprendre la délimitation par défaut

Avec la validation automatique de la base de données, chaque instruction autonome réussie est validée comme une transaction distincte. C’est le comportement initial habituel de SQLite, PostgreSQL, MySQL et SQL Server. Une bibliothèque de programmation ou un client peut choisir d’autres paramètres et démarrer une transaction pour toi.

BEGIN; démarre une transaction explicite dans SQLite et PostgreSQL. SQLite et SQL Server utilisent aussi BEGIN TRANSACTION ; PostgreSQL et MySQL acceptent START TRANSACTION. Oracle démarre une transaction lors d’une modification de données et attend normalement COMMIT dans des outils comme SQL*Plus, sauf si la validation automatique est activée.

COMMIT termine la transaction et conserve ses modifications. ROLLBACK la termine et annule ses modifications non validées. Une annulation ultérieure ne peut pas revenir sur une validation antérieure.

Examiner et annuler

BEGIN;
DELETE FROM orders;
SELECT * FROM orders;
ROLLBACK;

Ta connexion voit une table vide au moment du SELECT, puis voit de nouveau les cinq commandes après l’annulation. Le résultat vide était réel dans la transaction, mais n’était pas encore permanent. Les autres connexions ne voient normalement pas les modifications non validées.

Une transaction ne peut te protéger d’une mauvaise sélection de lignes que si tu examines le résultat et choisis de l’annuler. SQL peut mettre à jour une ligne non voulue sans erreur : aucune règle de la base de données ne sait quel compte tu voulais désigner. Compare les identifiants, valeurs et nombres renvoyés au résultat voulu avant de valider.

Garde les transactions courtes et termine-les. Les transactions ouvertes peuvent conserver des verrous qui font attendre les autres connexions ; SQLite n’autorise qu’une seule connexion en écriture à la fois. Fermer une connexion avec une transaction inachevée annule ses modifications en attente. Lorsqu’une bibliothèque démarre automatiquement une transaction, le programme doit appeler sa méthode de validation pour conserver le travail.

Une instruction en échec n’a pas le même effet dans toutes les bases de données. Tu dois savoir ce qui reste en attente avant de décider comment rétablir la situation. Un point de sauvegarde peut fournir une étape plus proche à laquelle revenir dans une transaction ouverte.

Suivre une erreur

Après le transfert, Maya a 425,00 et Leo a 195,00. La table comporte CHECK (balance >= 0). Démarre une transaction et soustrais 30,00 du solde de Maya : son solde en attente est de 395,00. Essaie ensuite de soustraire 300,00 du solde de Leo. Son solde serait alors de -105,00 : la deuxième instruction échoue donc.

Dans SQLite, valider à ce stade conserverait uniquement la déduction du solde de Maya. Dans PostgreSQL, COMMIT sur la transaction en échec la termine en l’annulant. Une procédure claire consiste à exécuter ROLLBACK, à corriger la cause et à relancer les instructions liées. Les deux soldes reviennent alors à 425,00 et 195,00 avant la nouvelle tentative corrigée.

Annuler une partie ultérieure avec un point de sauvegarde

BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE id = 1;
SAVEPOINT after_first;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
ROLLBACK TO after_first;
COMMIT;

Le point de sauvegarde vient après l’augmentation du solde de Maya. ROLLBACK TO annule l’augmentation ultérieure du solde de Leo mais conserve celle de Maya. Les soldes finaux sont de 435,00 et 195,00. Cette instruction ne termine pas la transaction englobante ; c’est COMMIT qui le fait.

RELEASE after_first supprime le point de sauvegarde sans annuler les modifications effectuées après lui. Ce n’est pas une validation permanente indépendante au sein de la transaction englobante. PostgreSQL permet aussi à ROLLBACK TO un point de sauvegarde établi avant une erreur de quitter l’état d’échec et de poursuivre à partir de ce point.

Vérifier si les modifications de structure peuvent être annulées

SQLite et PostgreSQL autorisent CREATE TABLE, ALTER TABLE et DROP TABLE dans des transactions. Par exemple, BEGIN; DROP TABLE orders; ROLLBACK; restaure la table et ses lignes.

Dans MySQL et Oracle, les instructions ordinaires du LDD sur les tables permanentes entraînent des validations implicites. Un CREATE TABLE, ALTER TABLE, DROP TABLE ou TRUNCATE TABLE peut valider les modifications antérieures en attente tout en effectuant sa propre opération de structure. Une annulation ultérieure ne peut pas annuler ce travail antérieur. Ne suppose pas qu’une transaction autour d’un script donne le même comportement à tous les dialectes SQL.

Pour une modification de données risquée, démarre la transaction, exécute l’écriture, examine les valeurs et le nombre de lignes, puis valide ou annule. Par exemple, examine les prix des articles d’éclairage après UPDATE products SET price = ROUND(price * 1.10, 2) WHERE category = 'lighting';. Prends rapidement ta décision pour que la transaction ne fasse pas attendre d’autres opérations.

ALTER TABLE modifie la structure d’une table existante tout en conservant ses lignes. UPDATE modifie les valeurs contenues dans ces lignes. L’ajout d’un champ de remise nécessite ALTER TABLE ; l’augmentation des remises déjà stockées nécessite UPDATE.

Ajouter et renommer des champs

ALTER TABLE customers ADD COLUMN phone TEXT;
ALTER TABLE customers RENAME COLUMN phone TO mobile;
ALTER TABLE customers RENAME TO clients;

SQLite et PostgreSQL utilisent ces mêmes formes. Une nouvelle colonne donne sa valeur par défaut aux lignes existantes, ou NULL si elle n’en a pas. ADD COLUMN active INTEGER NOT NULL DEFAULT 1 attribue la valeur 1 à active dans chaque ligne actuelle. Ajouter email TEXT NOT NULL sans valeur par défaut échoue sur une table contenant des lignes, car les lignes existantes n’auraient pas une valeur obligatoire.

SQLite prend en charge RENAME COLUMN depuis la version 3.25.0 et DROP COLUMN depuis la version 3.35.0. Lors d’un renommage, mets à jour le code applicatif et les requêtes enregistrées qui utilisent l’ancien nom. Le code SQL extérieur à la base de données ne peut pas découvrir automatiquement le nouveau nom.

Remplir les données avant de les rendre obligatoires

Dans PostgreSQL, ajoute une colonne qui accepte les valeurs manquantes, remplis les lignes existantes avec des valeurs adaptées, puis rends-la obligatoire :

ALTER TABLE customers ADD COLUMN email TEXT;
UPDATE customers SET email = 'unknown@example.com'
WHERE email IS NULL;
ALTER TABLE customers ALTER COLUMN email SET NOT NULL;

Cet exemple illustre le remplissage d’un champ obligatoire ; il ne déclare pas l’adresse e-mail unique. Une véritable colonne d’adresses e-mail uniques nécessite des valeurs valides et distinctes. SET NOT NULL échoue s’il reste des valeurs manquantes. PostgreSQL permet aussi de modifier un type avec ALTER COLUMN price TYPE NUMERIC(10, 2), de définir une valeur par défaut et d’ajouter une vérification nommée. Les lignes existantes doivent satisfaire toute nouvelle règle ajoutée.

Supprimer un champ ou reconstruire une table

ALTER TABLE customers DROP COLUMN mobile; supprime le champ et toutes les valeurs qu’il contient. Des dépendances peuvent bloquer l’opération. SQLite refuse de supprimer une colonne de clé primaire, unique, indexée ou de clé étrangère, ou encore une colonne utilisée par un autre objet du schéma. PostgreSQL peut supprimer les objets dépendants avec CASCADE, notamment une vue qui utilise la colonne.

SQLite ne peut pas modifier le type d’une colonne existante ni ajouter des contraintes de clé primaire, d’unicité, de clé étrangère ou de vérification à des colonnes existantes avec un simple ALTER. La version 3.53.0 ajoute ALTER COLUMN ... SET NOT NULL et DROP NOT NULL, mais pas la modification générale des contraintes. Une colonne nouvellement ajoutée peut comporter une vérification ou une référence autorisée, mais ne peut pas avoir PRIMARY KEY ou UNIQUE ; lorsque le contrôle des clés étrangères est activé, une référence ajoutée nécessite une valeur par défaut NULL.

Pour une modification plus large dans SQLite :

1. Note les index, déclencheurs et vues à recréer. Si le contrôle des clés étrangères est activé, désactive-le avant la transaction.
2. Démarre une transaction et crée une nouvelle table avec les règles voulues.
3. Copie les lignes en indiquant explicitement les colonnes dans INSERT INTO new_products (...) SELECT ... FROM products;.
4. Supprime l’ancienne table et renomme la nouvelle en products.
5. Recrée les objets dépendants et exécute PRAGMA foreign_key_check;. Résous les problèmes signalés avant de valider.
6. Valide, puis réactive le contrôle des clés étrangères s’il était activé auparavant.

MySQL modifie les définitions avec MODIFY COLUMN ; SQL Server utilise ALTER COLUMN. Traduis une modification de structure dans la syntaxe prise en charge par la base de données cible avant de l’exécuter.

DROP TABLE supprime le contenant ainsi que son contenu. Après DROP TABLE archived_orders;, les requêtes ne peuvent plus lire cette table parce qu’elle n’existe plus. Ses contraintes, ses index et ses déclencheurs sont supprimés avec elle.

Choisir le niveau de suppression

DROP TABLE IF EXISTS archived_orders; évite une erreur si la table est absente. Il supprime néanmoins une table existante et ses données. Un script d’installation qui supprime et recrée des tables détruit des données s’il est exécuté sur une base contenant des enregistrements que tu souhaites conserver.

Respecter les liens entre les tables

Supprime les tables enfants avant les parentes, dans l’ordre inverse de la création habituelle. Dans PostgreSQL, supprimer customers alors qu’une clé étrangère de commande en dépend échoue. DROP TABLE customers CASCADE; supprime aussi la clé étrangère dépendante et les éventuelles vues dépendantes. La table des commandes et ses lignes restent, mais l’association avec le client n’est plus contrôlée.

Ce CASCADE de PostgreSQL diffère du ON DELETE CASCADE d’une clé étrangère. Le premier supprime des objets dépendants de la base de données lors d’une modification de structure. Le second supprime des lignes enfants lorsqu’une ligne parente est supprimée. Savoir quelle action est utilisée évite de prévoir incorrectement les données restantes.

Lorsque le contrôle des clés étrangères est activé dans SQLite, la suppression d’une table entraîne d’abord une suppression implicite de ses lignes. Les lignes enfants qui les référencent peuvent la bloquer. Si l’association déclare ON DELETE CASCADE, ces lignes enfants peuvent être supprimées ; SET NULL peut effacer leurs liens. Supprimer une table exige donc la même attention aux références que supprimer des lignes, ainsi qu’une attention à la structure de table supprimée.

Préserver une possibilité de restauration

Avant de supprimer ou de reconstruire une table contenant des données réelles, conserve une copie utilisable. Voici une copie rapide des données à l’intérieur de la base :

CREATE TABLE products_backup AS
SELECT * FROM products;

Dans SQLite et PostgreSQL, cette instruction conserve les données sélectionnées mais pas les contraintes d’origine. Elle ne permet pas de reconstruire entièrement la définition de la table. Une exportation ou une sauvegarde de la base conserve les données nécessaires à la restauration à l’extérieur de la table modifiée.

SQLite et PostgreSQL permettent aussi de tester une suppression de table dans une transaction :

BEGIN;
DROP TABLE archived_orders;
ROLLBACK;

L’annulation restaure la table et ses lignes parce que la suppression n’a pas été validée. Une fois validée, la suppression ne peut pas être annulée par un ROLLBACK ultérieur. Dans les bases de données qui valident implicitement les instructions du LDD, encadrer la suppression par une syntaxe de transaction n’offre pas cette possibilité d’annulation. Examine la base de données cible et les tables concernées avant d’exécuter un script de reconstruction.