ALTER TABLE în SQLite: explicație și exemple

ALTER TABLE t RENAME TO t_nou; ALTER TABLE t RENAME COLUMN c TO c_nou; ALTER TABLE t ADD COLUMN definitie; ALTER TABLE t DROP COLUMN c; ALTER TABLE t ALTER COLUMN c SET NOT NULL; -- 3.53.0 ALTER TABLE t ALTER COLUMN c DROP NOT NULL; -- 3.53.0

Ce face `ALTER TABLE`

ALTER TABLE în SQLite este mai limitat decât în MySQL sau PostgreSQL. Poți redenumi tabelul (RENAME TO), redenumi o coloană (RENAME COLUMN, din 3.25.0), adăuga o coloană la final (ADD COLUMN), șterge o coloană (DROP COLUMN, din 3.35.0) și, din 3.53.0, pune sau scoate NOT NULL pe o coloană (ALTER COLUMN ... SET NOT NULL / DROP NOT NULL).

ADD COLUMN are reguli: coloana nouă nu poate fi PRIMARY KEY sau UNIQUE, nu poate fi coloană generată STORED, iar dacă are NOT NULL trebuie să aibă o valoare DEFAULT diferită de NULL. DROP COLUMN refuză coloanele din cheia primară, cele UNIQUE, indexate sau folosite în vederi, declanșatori, chei străine sau coloane generate.

Pentru orice altceva (schimbarea tipului, a ordinii coloanelor, adăugarea unei constrângeri UNIQUE), documentația SQLite descrie reconstruirea tabelului: creezi un tabel nou cu structura dorită, copiezi datele, ștergi tabelul vechi și îl redenumești pe cel nou, totul într-o tranzacție.

Exemple

Exemplele de pe această pagină pornesc de la datele de mai jos. Rulează mai întâi acest cod, apoi exemplul.

CREATE TABLE produse (id INTEGER PRIMARY KEY, nume TEXT, pret INTEGER);
INSERT INTO produse (nume, pret) VALUES ('Caiet', 12), ('Pix', 4);

Exemplul 1: Adăugare, redenumire, ștergere

Coloana stoc primește valoarea implicită 0 pe rândurile existente. La final, .schema arată structura rezultată: SQLite rescrie textul CREATE TABLE păstrat în schemă.

ALTER TABLE produse ADD COLUMN stoc INTEGER NOT NULL DEFAULT 0;
ALTER TABLE produse RENAME COLUMN nume TO denumire;
SELECT * FROM produse;

ALTER TABLE produse DROP COLUMN stoc;
ALTER TABLE produse RENAME TO articole;
.schema articole

Rezultat

╭────┬──────────┬──────┬──────╮
│ id │ denumire │ pret │ stoc │
╞════╪══════════╪══════╪══════╡
│  1 │ Caiet    │   12 │    0 │
│  2 │ Pix      │    4 │    0 │
╰────┴──────────┴──────┴──────╯
CREATE TABLE "articole" (id INTEGER PRIMARY KEY, denumire TEXT, pret INTEGER);

Exemplul 2: NOT NULL fără valoare implicită

Tabelul are deja rânduri, deci SQLite nu are ce valoare să pună în noua coloană NOT NULL:

ALTER TABLE produse ADD COLUMN categorie TEXT NOT NULL;

Rezultat (eroare)

Error near line 4: Cannot add a NOT NULL column with default value NULL

Exemplul 3: Schimbarea tipului prin reconstruirea tabelului

Vrem ca pret să fie REAL și numele să fie obligatoriu și unic. Facem un tabel nou, copiem datele cu INSERT ... SELECT, ștergem tabelul vechi și îl redenumim pe cel nou. Tranzacția face ca schimbarea să fie completă sau deloc.

BEGIN;
CREATE TABLE produse_nou (id INTEGER PRIMARY KEY, nume TEXT NOT NULL UNIQUE, pret REAL);
INSERT INTO produse_nou (id, nume, pret) SELECT id, nume, pret FROM produse;
DROP TABLE produse;
ALTER TABLE produse_nou RENAME TO produse;
COMMIT;

SELECT id, nume, pret, typeof(pret) AS tip FROM produse;