STRICT-Tabellen in SQLite: wenn „drei“ nicht mehr in eine INTEGER-Spalte passt

Das hier ist kein erfundenes Beispiel, sondern der Grund, warum ich diesen Beitrag schreibe:

CREATE TABLE lose (id INTEGER PRIMARY KEY, menge INTEGER);
INSERT INTO lose (menge) VALUES ('drei');

SELECT menge, typeof(menge) FROM lose;
-- drei | text

Kein Fehler, keine Warnung. In einer Spalte, die INTEGER heißt, steht Text. SQLite versucht zu konvertieren; klappt das verlustfrei ('42' → 42), wird konvertiert – klappt es nicht, wird der ursprüngliche Wert genommen. Das nennt sich type affinity und ist ausdrücklich so gebaut.

Der Ärger kommt später: SUM(menge) zählt den Textwert als 0, ORDER BY menge sortiert gemischt, und irgendwo in einer Auswertung steht eine Zahl, die um genau diesen einen Datensatz danebenliegt. Nichts stürzt ab, nichts meldet sich – die Zahl ist einfach falsch.

Ein Wort schaltet es ab

Seit SQLite 3.37.0 (November 2021) gibt es STRICT-Tabellen. Das Schlüsselwort steht hinter der schließenden Klammer:

CREATE TABLE lose (
    id     INTEGER PRIMARY KEY,
    menge  INTEGER NOT NULL,
    notiz  TEXT
) STRICT;

INSERT INTO lose (menge) VALUES ('drei');
-- Error: cannot store TEXT value in INTEGER column lose.menge

INSERT INTO lose (menge) VALUES ('42');
SELECT menge, typeof(menge) FROM lose;
-- 42 | integer      ← verlustfrei konvertierbar, also erlaubt

Der Unterschied ist genau der richtige: '42' wird weiterhin sauber zu einer Zahl, weil dabei nichts verloren geht. 'drei' wird abgewiesen, statt still als Text zu landen.

Was STRICT sonst noch verlangt

Jede Spalte braucht einen Typ. CREATE TABLE t (id INTEGER PRIMARY KEY, x) STRICT scheitert mit „missing datatype for t.x".

Nur sechs Typnamen sind erlaubt: INT, INTEGER, REAL, TEXT, BLOB und ANY. Alles andere fliegt raus – auch die aus anderen Datenbanken gewohnten:

CREATE TABLE t (id INTEGER PRIMARY KEY, name VARCHAR(50)) STRICT;
-- Error: unknown datatype for t.name: "VARCHAR(50)"

Das wirkt zunächst wie ein Rückschritt, ist aber ehrlicher: VARCHAR(50) hat in SQLite noch nie auf 50 Zeichen begrenzt. Der Name hat eine Prüfung versprochen, die es nicht gab. Wenn du eine Längenbegrenzung willst, schreib sie hin, wo sie auch greift:

name TEXT NOT NULL CHECK (length(name) <= 50)

ANY ist der Notausgang. Eine Spalte mit ANY nimmt in einer STRICT-Tabelle weiterhin alles an, ganz ohne Umwandlung – praktisch für ein Feld, in dem tatsächlich Verschiedenes liegt.

Jede PRIMARY-KEY-Spalte ist automatisch NOT NULL. In gewöhnlichen Tabellen ist das aus historischen Gründen nicht so, und auch das ist eine dieser Fallen, die man einmal im Leben sucht.

Die Falle beim Umstellen

INTEGER PRIMARY KEY ist ein Alias für die interne rowid. INT PRIMARY KEY ist es nicht – obwohl beide Typnamen erlaubt sind und fast gleich aussehen. Wer beim Aufräumen aus Versehen das kürzere schreibt, bekommt eine Tabelle, die sich anders verhält als vorher. Schreib INTEGER aus.

Und: STRICT gilt pro Tabelle, nicht pro Datenbank. Du kannst also neue Tabellen streng anlegen und bestehende erst einmal lassen. Ein PRAGMA integrity_check prüft in STRICT-Tabellen zusätzlich die Typen des vorhandenen Inhalts – ein guter erster Schritt, bevor du eine alte Tabelle umziehst.

Lohnt sich das?

Für mich ja, und zwar ausnahmslos bei allem Neuen. Alle Projekte hinter bymw.de laufen auf SQLite – das CMS, die Wetter-App, die Spiel-Backends. Eine Datenbank, die einen falschen Wert zurückweist, statt ihn schweigend anzunehmen, erspart genau die Sorte Fehler, die man erst in einer Auswertung bemerkt und dann nicht mehr rückwärts auflösen kann.

Der Preis sind ein paar CHECK-Bedingungen mehr und der Verzicht auf Typnamen, die ohnehin nur Dekoration waren. Das ist ein guter Tausch.

Quellen

• SQLite: STRICT Tables
• SQLite: Datatypes In SQLite (Type Affinity)