NOT NULL i DEFAULT w SQLite: ograniczenia kolumn, które działają

Dwa ograniczenia, które są warte swojej ceny

Większość błędów wynikających z niedbałego schematu ma jedno z dwóch źródeł: kolumnę, która jest NULL, choć nikt się tego nie spodziewał, albo kolumnę bez wartości, którą aplikacja uznała za oczywistą. NOT NULL i DEFAULT rozwiązują oba problemy, a ich dodanie prawie nic nie kosztuje.

CREATE TABLE users (
    id       INTEGER PRIMARY KEY,
    email    TEXT    NOT NULL,
    name     TEXT    NOT NULL DEFAULT 'anonim',
    joined   TEXT    NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

INSERT INTO users (email) VALUES ('rosa@example.com');

SELECT * FROM users;

Jedna kolumna jest wymagana i nie ma wartości zapasowej. Dwie mają wartości zapasowe. Wstawienie musiało podać tylko email, a resztę uzupełnił SQLite. To cała funkcja w jednym przykładzie: reszta tej strony dotyczy przypadków brzegowych.

NOT NULL znaczy „odrzuć NULL, bez wyjątków”

NOT NULL robi dokładnie to, co mówi. Każda próba wstawienia NULL do kolumny, czy to przez pominięcie jej w INSERT bez wartości domyślnej, czy przez jawne NULL, kończy się błędem:

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

-- To się nie uda: brakuje title.
INSERT INTO posts (id) VALUES (1);

Komunikat błędu brzmi:

Runtime error: NOT NULL constraint failed: posts.title

Ten sam wynik, jeśli przekażesz NULL bezpośrednio:

INSERT INTO posts (id, title) VALUES (1, NULL);
-- Runtime error: NOT NULL constraint failed: posts.title

Taka jest umowa. Jeśli kolumna jest logicznie wymagana, oznacz ją jako NOT NULL, a pozbędziesz się całej klasy błędów: żaden kod aplikacji nie przemyci NULL obok bazy.

DEFAULT daje wartość, gdy wywołujący jej nie poda

DEFAULT działa tylko wtedy, gdy INSERT w ogóle nie wspomina o kolumnie. Nie ratuje jawnego NULL:

CREATE TABLE tasks (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending'
);

INSERT INTO tasks (title) VALUES ('Napisać dokumentację');
INSERT INTO tasks (title, status) VALUES ('Wydać', 'done');

SELECT * FROM tasks;

Pierwsze wstawienie korzysta z wartości domyślnej. Drugie ją nadpisuje. Gdyby napisać INSERT INTO tasks (title, status) VALUES ('x', NULL), dostałbyś błąd NOT NULL constraint failed: kolumna została wymieniona, więc wartość domyślna nie zadziała.

Warto trzymać się takiego modelu myślowego: DEFAULT uzupełnia brakujące kolumny. NOT NULL odrzuca wartości null bez względu na to, jak trafiają. To niezależne funkcje, które dobrze się uzupełniają.

Wartości domyślne mogą być wyrażeniami

Literał to najczęstszy przypadek (DEFAULT 0, DEFAULT '', DEFAULT 'pending'), ale SQLite przyjmuje też wyrażenie w nawiasach. Tak oznaczasz wiersze czasem ich utworzenia albo generujesz losowy identyfikator:

CREATE TABLE events (
    id         INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    occurred   TEXT NOT NULL DEFAULT (CURRENT_TIMESTAMP),
    token      TEXT NOT NULL DEFAULT (lower(hex(randomblob(8))))
);

INSERT INTO events (name) VALUES ('login');
INSERT INTO events (name) VALUES ('logout');

SELECT * FROM events;

Kilka rzeczy, które warto wiedzieć:

• Wyrażenie jest obliczane przy każdym wstawieniu, a nie raz przy tworzeniu tabeli. Każdy wiersz dostaje własny znacznik czasu i własny token.
• CURRENT_TIMESTAMP, CURRENT_DATE i CURRENT_TIME to trzy specjalne słowa kluczowe, które nie wymagają nawiasów. Wszystko inne ich wymaga.
• Wyrażenie nie może odwoływać się do innych kolumn ani podzapytań: musi być samowystarczalne.

Jeśli kolumna ma być opcjonalna, ale automatycznie oznaczana, gdy jest obecna, usuń NOT NULL i zostaw wartość domyślną. Jeśli ma być wymagana i automatycznie oznaczana, użyj obu.

DEFAULT NULL jest poprawne (i czasem ma sens)

Zapis DEFAULT NULL działa tak samo jak brak wartości domyślnej: kolumna jest NULL, gdy nie podasz wartości. Warto go używać, gdy chcesz w schemacie jawnie zaznaczyć, że „brak wartości” to zamierzony stan początkowy:

CREATE TABLE profiles (
    id        INTEGER PRIMARY KEY,
    user_id   INTEGER NOT NULL,
    bio       TEXT DEFAULT NULL,
    avatar    TEXT
);

INSERT INTO profiles (user_id) VALUES (1);

SELECT * FROM profiles;

bio i avatar zachowują się tu identycznie. DEFAULT NULL przy bio to komentarz w postaci kodu: mówi każdemu, kto czyta schemat, że brak opisu to normalny stan, a nie przeoczenie.

Dodawanie NOT NULL do istniejącej tabeli

Tu robi się zawile. ALTER TABLE w SQLite jest celowo ograniczone: nie uruchomisz ALTER COLUMN ... SET NOT NULL, jak w Postgresie. To, co możesz zrobić, zależy od tego, czy kolumna już istnieje.

Dla zupełnie nowej kolumny ADD COLUMN ... NOT NULL działa, ale musisz podać wartość domyślną. Inaczej istniejące wiersze nagle zawierałyby NULL w kolumnie NOT NULL, co jest niemożliwe:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO products (name) VALUES ('widget'), ('gizmo');

-- Działa: istniejące wiersze dostają wartość domyślną.
ALTER TABLE products ADD COLUMN price REAL NOT NULL DEFAULT 0;

SELECT * FROM products;

Spróbuj tego samego bez wartości domyślnej, a dostaniesz błąd:

ALTER TABLE products ADD COLUMN sku TEXT NOT NULL;
-- Runtime error: Cannot add a NOT NULL column with default value NULL

Dla istniejącej kolumny nie ma zmiany w miejscu. Standardowy przepis to przebudowa: utwórz nową tabelę z potrzebnym ograniczeniem, skopiuj dane, usuń starą, zmień nazwę nowej. Omówimy to na stronie drop-and-alter-table. Na razie wystarczy wiedzieć, że to ograniczenie jest realne, i planować schemat z myślą o nim.

Realistyczne połączenie

Większość tabel produkcyjnych używa obu ograniczeń razem, żeby zapisać, „co według aplikacji musi być prawdą”:

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer    TEXT    NOT NULL,
    total_cents INTEGER NOT NULL DEFAULT 0,
    currency    TEXT    NOT NULL DEFAULT 'USD',
    status      TEXT    NOT NULL DEFAULT 'pending',
    created_at  TEXT    NOT NULL DEFAULT (CURRENT_TIMESTAMP),
    notes       TEXT
);

INSERT INTO orders (customer) VALUES ('Ada');
INSERT INTO orders (customer, total_cents, currency) VALUES ('Boris', 4999, 'EUR');

SELECT id, customer, total_cents, currency, status, created_at FROM orders;

Przeczytaj ten schemat od góry do dołu, a zgadniesz, co robi aplikacja, nie widząc ani linii kodu. customer jest wymagane i nie ma wartości zapasowej: wywołujący musi wiedzieć, dla kogo jest zamówienie. Kwota, waluta i status mają sensowne wartości domyślne, więc nawet najprostsze wstawienie daje spójny wiersz. notes jest opcjonalne. created_at uzupełnia baza, czyli jedyne miejsce, w którym powinno być uzupełniane.

Na tym polega wartość tych ograniczeń: zamieniają założenia w reguły, których pilnuje sama baza.

Częste pułapki

Krótka lista rzeczy, na które ludzie się łapią:

• Jawne NULL pokonuje DEFAULT. INSERT INTO t (col) VALUES (NULL) nie użyje wartości domyślnej. Kolumny musi nie być na liście kolumn.
• Domyślne wyrażenia wymagają nawiasów. DEFAULT CURRENT_TIMESTAMP działa (to jedno z trzech specjalnych słów kluczowych). DEFAULT lower(hex(randomblob(8))) nie działa, trzeba je opakować: DEFAULT (lower(hex(randomblob(8)))).
• NOT NULL i pusty napis to co innego. '' to poprawna wartość TEXT i nie wywoła ograniczenia. Jeśli chcesz zabronić także pustych napisów, to zadanie dla CHECK (następna strona).
• ADD COLUMN ... NOT NULL wymaga DEFAULT innego niż NULL. Bez tego SQLite odmawia zmiany.

Dalej: ograniczenia CHECK

NOT NULL i DEFAULT obejmują „musi istnieć” i „uzupełnij, jeśli brakuje”. Do kolejnej warstwy walidacji („musi być dodatnie”, „musi być jedną z tych wartości”, „data końcowa musi być po dacie początkowej”) SQLite ma ograniczenia CHECK, które pozwalają napisać dowolne wyrażenie logiczne, jakie musi spełniać każdy wiersz. O tym na następnej stronie.