DROP TABLE i ALTER TABLE w SQLite: zmiany schematu w praktyce

Schematy się zmieniają, a SQLite pozwala je zmieniać (w większości)

Gdy tabela już istnieje, prędzej czy później zechcesz zmienić jej nazwę, dodać kolumnę, usunąć kolumnę albo przebudować całość. SQLite obsługuje typowe przypadki bezpośrednio przez DROP TABLE i ALTER TABLE, a na całą resztę daje udokumentowane obejście.

Haczyk: ALTER TABLE w SQLite jest dużo bardziej ograniczone niż w Postgres czy MySQL. Wiedza o tym, co potrafi, a czego nie, oraz znajomość wzorca przebudowy dla tego, czego nie potrafi, to większość umiejętności w tym temacie.

DROP TABLE usuwa tabelę i wszystko, co z nią związane

DROP TABLE usuwa tabelę, jej wiersze, indeksy i wszystkie zdefiniowane na niej triggery. Nie ma cofania:

CREATE TABLE scratch (
    id INTEGER PRIMARY KEY,
    note TEXT
);

INSERT INTO scratch (note) VALUES ('temporary'), ('also temporary');

DROP TABLE scratch;

SELECT name FROM sqlite_master WHERE type = 'table';

Tabeli już nie ma. Zapytanie do niej zgłosiłoby teraz no such table: scratch.

Jeśli nie masz pewności, czy tabela istnieje (częste w skryptach konfiguracyjnych), użyj IF EXISTS, aby instrukcja po cichu nic nie robiła, gdy tabeli brakuje:

CREATE TABLE scratch (id INTEGER PRIMARY KEY);

DROP TABLE IF EXISTS scratch;
DROP TABLE IF EXISTS scratch;
SELECT 'both drops succeeded' AS result;

Bez IF EXISTS drugie usunięcie zgłosiłoby błąd. Z nim oba wykonują się bez problemu.

Klucze obce mogą zablokować DROP

Jeśli egzekwowanie kluczy obcych jest włączone (PRAGMA foreign_keys = ON;), a inna tabela odwołuje się do tej, którą usuwasz, usunięcie się nie uda:

sqlite> PRAGMA foreign_keys = ON;
sqlite> DROP TABLE users;
Runtime error: FOREIGN KEY constraint failed

Masz kilka opcji: najpierw usuń tabelę, która się odwołuje, usuń odwołujące się wiersze albo przy tworzeniu zdefiniuj klucz obcy z ON DELETE CASCADE. SQLite nie złamie za ciebie po cichu integralności referencyjnej.

ALTER TABLE: cztery rzeczy, które potrafi

ALTER TABLE w SQLite obsługuje dokładnie cztery operacje:

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT
);

-- 1. Zmiana nazwy tabeli
ALTER TABLE posts RENAME TO articles;

-- 2. Zmiana nazwy kolumny
ALTER TABLE articles RENAME COLUMN body TO content;

-- 3. Dodanie kolumny
ALTER TABLE articles ADD COLUMN published_at TEXT;

-- 4. Usunięcie kolumny (SQLite 3.35+)
ALTER TABLE articles DROP COLUMN published_at;

SELECT sql FROM sqlite_master WHERE name = 'articles';

Każda z nich to jedna instrukcja. Pierwsze dwie są praktycznie darmowe, bo tylko aktualizują schemat. ADD COLUMN też jest szybkie: SQLite nie przepisuje tabeli, tylko zapisuje definicję nowej kolumny. DROP COLUMN jest cięższe, bo SQLite musi przepisać każdy wiersz, aby fizycznie usunąć dane kolumny.

ADD COLUMN z wartością domyślną

Nowa kolumna w istniejącej tabeli ma na starcie NULL w każdym wierszu, chyba że nadasz jej wartość domyślną:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);

INSERT INTO users (email) VALUES ('ada@example.com'), ('boris@example.com');

ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

SELECT * FROM users;

Oba istniejące wiersze dostają wartość 'active'. Wartość domyślna musi być stałą: SQLite nie pozwoli użyć CURRENT_TIMESTAMP ani żadnego innego niestałego wyrażenia jako wartości domyślnej w ADD COLUMN, bo potrzebuje wartości, którą może zastosować do każdego istniejącego wiersza bez obliczania jej osobno dla każdego.

Jeśli potrzebujesz NOT NULL bez wartości domyślnej, musisz dodać kolumnę dopuszczającą NULL, uzupełnić ją przez UPDATE, a potem przebudować tabelę, aby dodać ograniczenie. I tu dochodzimy do ograniczeń.

Czego ALTER TABLE nie potrafi

Rzeczy, które działają w Postgres czy MySQL, ale nie w SQLite:

• Zmiana typu kolumny (ALTER COLUMN ... TYPE ...).
• Zmiana wartości domyślnej kolumny w miejscu.
• Dodanie lub usunięcie NOT NULL, CHECK, UNIQUE lub PRIMARY KEY na istniejącej kolumnie.
• Dodanie klucza obcego do istniejącej kolumny.
• Zmiana kolejności kolumn.

Każda z tych prób kończy się błędem składni. SQLite w ogóle nie ma klauzuli ALTER COLUMN. Oficjalna odpowiedź jest dla wszystkich taka sama: przebuduj tabelę.

Wzorzec przebudowy

Gdy ALTER TABLE nie potrafi zrobić tego, czego potrzebujesz, tworzysz nową tabelę z docelowym schematem, kopiujesz dane, usuwasz starą tabelę i zmieniasz nazwę nowej na starą. Opakuj to w transakcję, aby wszystko wykonało się w całości albo wcale:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT,
    age TEXT
);

INSERT INTO users (email, age) VALUES ('ada@example.com', '36'), ('boris@example.com', '41');

BEGIN;

CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    age INTEGER CHECK (age > 0)
);

INSERT INTO users_new (id, email, age)
SELECT id, email, CAST(age AS INTEGER) FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

COMMIT;

SELECT * FROM users;

Teraz users.age to liczba całkowita z ograniczeniem CHECK, a email jest NOT NULL. Dane przeszły razem ze schematem.

Kilka rzeczy, o których warto pamiętać, robiąc to naprawdę:

• Na czas operacji wyłącz klucze obce. Jeśli inne tabele odwołują się do twojej, uruchom PRAGMA foreign_keys = OFF; przed transakcją i PRAGMA foreign_keys = ON; po niej. W przeciwnym razie DROP TABLE się nie uda. Tej pragmy nie da się zmienić wewnątrz transakcji, więc ustaw ją na zewnątrz.
• Odtwórz indeksy i triggery. Usunięcie starej tabeli usuwa też jej indeksy i triggery. Po zmianie nazwy dodaj je z powrotem do nowej tabeli.
• Sprawdź widoki. Widoki odwołujące się do tabeli wciąż wskazują starą nazwę w zapisanym SQL. Przebuduj te, które zależą od zmienionych kolumn.

Wzorzec przebudowy jest rozwlekły, ale niezawodny. Tak właśnie pod spodem działają narzędzia migracji, takie jak Alembic czy Rails, gdy pracują z SQLite.

Usuwanie kilku tabel

Nie ma jednej instrukcji do usuwania wielu tabel: uruchamiasz DROP TABLE dla każdej z nich. Jeśli chcesz je zgrupować, zrób to w transakcji:

CREATE TABLE a (id INTEGER);
CREATE TABLE b (id INTEGER);
CREATE TABLE c (id INTEGER);

BEGIN;
DROP TABLE a;
DROP TABLE b;
DROP TABLE c;
COMMIT;

SELECT name FROM sqlite_master WHERE type = 'table';

Opakowanie ich w transakcję oznacza, że albo wszystkie trzy usunięcia się udadzą, albo żadne. Przydaje się to przy usuwaniu powiązanych tabel, gdy w połowie coś może się nie udać z powodu kluczy obcych.

Co warto zapamiętać

• DROP TABLE usuwa tabelę razem z jej indeksami i triggerami. W skryptach idempotentnych używaj IF EXISTS.
• ALTER TABLE robi tylko cztery rzeczy: zmienia nazwę tabeli, zmienia nazwę kolumny, dodaje kolumnę i usuwa kolumnę.
• Wszystko inne (zmiany typów, nowe ograniczenia, klucze obce na istniejących kolumnach) wymaga przebudowy tabeli w transakcji.
• Przy przebudowie pamiętaj o kluczach obcych, indeksach, triggerach i widokach. Nie podążają automatycznie za danymi.

Dalej: wstawianie danych

Za tobą rozdział o tabelach i ograniczeniach, które nadają im kształt. Czas je wypełnić: następny rozdział zaczyna się od INSERT, w tym formy wielowierszowej, wartości domyślnych i tego, jak SQLite obsługuje wstawienia sprzeczne z ograniczeniami.