Generierte Spalten in SQLite – abgeleitete Werte, die nie veralten

Irgendwann steht in jeder Tabelle ein Wert, der eigentlich aus anderen folgt: der Bruttopreis aus Netto und Steuersatz, der Monat aus dem Datum, der kleingeschriebene Name für die Suche. Pflegt die Anwendung ihn mit, stimmt er genau so lange, wie alle Schreibwege daran denken.

Seit SQLite 3.31 gibt es dafür generierte Spalten.

Virtuell: wird bei jeder Abfrage berechnet

CREATE TABLE artikel (
  id       INTEGER PRIMARY KEY,
  name     TEXT NOT NULL,
  netto    INTEGER NOT NULL,                 -- in Cent
  satz     REAL NOT NULL DEFAULT 0.19,
  brutto   INTEGER GENERATED ALWAYS AS (CAST(ROUND(netto * (1 + satz)) AS INTEGER)) VIRTUAL
);

INSERT INTO artikel (name, netto) VALUES ('Schraubendreher', 1999);
SELECT name, netto, brutto FROM artikel;      -- 1999 | 2379

VIRTUAL kostet keinen Speicherplatz; der Wert entsteht beim Lesen. Das ist der Standard und in den meisten Fällen die richtige Wahl.

Gespeichert: kostet Platz, spart Rechenzeit

ALTER TABLE kunden ADD COLUMN
  name_klein TEXT GENERATED ALWAYS AS (lower(name)) STORED;

CREATE INDEX kunden_name_klein ON kunden (name_klein);

STORED schreibt den Wert mit in die Datei. Das lohnt, wenn der Ausdruck teuer ist oder – wie hier – ein Index darauf liegt und die Spalte in jeder Suche vorkommt.

Der praktische Klassiker: Auswertung nach Monat

CREATE TABLE buchungen (
  id        INTEGER PRIMARY KEY,
  betrag    INTEGER NOT NULL,
  gebucht   INTEGER NOT NULL,                          -- Unixzeit
  monat     TEXT GENERATED ALWAYS AS (strftime('%Y-%m', gebucht, 'unixepoch')) VIRTUAL
);

CREATE INDEX buchungen_monat ON buchungen (monat);

SELECT monat, SUM(betrag) FROM buchungen GROUP BY monat ORDER BY monat DESC;

Ohne die Spalte müsste die Gruppierung strftime(...) je Zeile rechnen und könnte keinen Index nutzen.

Was erlaubt ist – und was nicht

• Der Ausdruck darf nur Spalten derselben Zeile verwenden: keine Unterabfragen, keine anderen Tabellen.
• Er muss deterministisch sein: date('now') ist nicht erlaubt, strftime('%Y', spalte, 'unixepoch') schon.
• Beim Einfügen wird die Spalte nicht mitgeschrieben – ein INSERT mit Wert darauf ist ein Fehler.
• Mit ALTER TABLE ADD COLUMN lassen sich nur VIRTUAL-Spalten nachrüsten; für STORED musst du die Tabelle neu bauen.

Fallstrick

Generierte Spalten sind kein Ersatz für saubere Modellierung: Wenn der Steuersatz sich ändert, ändert sich rückwirkend auch der Bruttopreis aller alten Zeilen – bei einer Rechnung ist das falsch, dort gehört der damals gültige Betrag festgeschrieben. Faustregel: Berechnen, was sich aus der Zeile ergibt; speichern, was historisch gelten muss. Und prüfe die SQLite-Version deines Servers (SELECT sqlite_version();), bevor du dich darauf verlässt – vor 3.31 gibt es die Funktion nicht.

Wenn du ein Datenmodell planst, das mitwachsen soll, schau ich gern drüber: bymw.de.

Quellen

• Generated Columns (sqlite.org)
• ALTER TABLE (sqlite.org)
• Date And Time Functions (sqlite.org)