Auto-Increment, Sequences und Identity im Datenbankvergleich

1. Warum jede Datenbank eigene Nummerierungsmechanismen hat

Ein automatisch generierter, eindeutiger Primaerschluessel ist eines der am haeufigsten benoetigten Features im relationalen Datenbankdesign. Fast jede Tabelle mit einem Auto-Increment-Feld erspart der Anwendung, selbst fuer Eindeutigkeit zu sorgen, und vermeidet Race Conditions beim gleichzeitigen Einfuegen mehrerer Zeilen. Trotzdem hat sich in der Praxis kein einheitlicher Mechanismus durchgesetzt, sondern vier grundverschiedene Ansaetze, die historisch unabhaengig voneinander entstanden sind, lange bevor SQL-Standards sie einheitlich regeln konnten.

Der Unterschied liegt nicht nur in der Syntax, sondern in der zugrunde liegenden Architektur: MySQL koppelt die Nummerierung eng an die Tabelle selbst, waehrend PostgreSQL, SQL Server und Oracle Sequence-Objekte als eigenstaendige, von der Tabelle unabhaengige Datenbankobjekte behandeln. Diese architektonische Entscheidung hat direkte Konsequenzen fuer Nebenlaeufigkeit, Transaktionsverhalten, Luecken in der Nummerierung und die Frage, wie mehrere Tabellen sich einen gemeinsamen Nummernkreis teilen koennen. Wer Auto-Increment-Verhalten zwischen Datenbanken vergleicht, muss diese architektonische Ebene verstehen, nicht nur die Oberflaechen-Syntax.

2. MySQL AUTO_INCREMENT im Detail

MySQL implementiert automatische Nummerierung ueber das Spaltenattribut AUTO_INCREMENT, das direkt bei der Spaltendefinition angegeben wird. Der Zaehler ist Teil der Tabellenmetadaten und wird bei InnoDB seit Version 8.0 persistent im Redo Log gefuehrt, sodass er auch nach einem Server-Neustart konsistent bleibt, waehrend aeltere MySQL-Versionen den Zaehler beim Neustart aus dem Maximalwert der Tabelle rekonstruierten und dadurch bereits vergebene, aber wieder geloeschte Werte erneut htten vergeben koennen.

Ein wichtiger Unterschied zu den anderen Datenbanken: Jede Tabelle hat maximal einen AUTO_INCREMENT-Zaehler, ein gemeinsamer Nummernkreis fuer mehrere Tabellen ist ohne zusaetzliche Anwendungslogik nicht moeglich. Zudem greift MySQL bei parallelen Inserts standardmaessig auf einen tabellenweiten Lock fuer den Zaehler zurueck, dessen genaues Verhalten ueber die Systemvariable innodb_autoinc_lock_mode gesteuert wird und bei Bulk-Inserts erhebliche Auswirkungen auf die Parallelitaet hat.

-- MySQL: AUTO_INCREMENT column tied directly to the table
CREATE TABLE orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO orders (customer_id) VALUES (101);
SELECT LAST_INSERT_ID();  -- returns the auto-generated id of this session

-- Reset the counter (dangerous on production tables with existing data)
ALTER TABLE orders AUTO_INCREMENT = 1000;

-- innodb_autoinc_lock_mode controls concurrency behavior for bulk inserts
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

3. PostgreSQL SERIAL, IDENTITY und Sequences als eigenstaendige Objekte

PostgreSQL loest automatische Nummerierung ueber ein eigenstaendiges Datenbankobjekt namens Sequence, das unabhaengig von jeder Tabelle existiert und ueber nextval(), currval() und setval() gesteuert wird. Die klassische Kurzschreibweise SERIAL ist syntaktischer Zucker, der im Hintergrund eine Sequence anlegt und sie als Default-Wert an die Spalte bindet. Seit PostgreSQL 10 gibt es zusaetzlich die ANSI-konformere GENERATED ALWAYS AS IDENTITY-Syntax, die dieselbe Sequence-Mechanik nutzt, aber verhindert, dass die Spalte versehentlich mit einem expliziten Wert ueberschrieben wird, was bei SERIAL ohne zusaetzliche Constraints moeglich waere.

Weil Sequences eigenstaendige Objekte sind, kann sich PostgreSQL eine einzige Sequence problemlos ueber mehrere Tabellen teilen lassen, ein Anwendungsfall, der bei MySQL zusaetzliche Anwendungslogik erfordert. Ausserdem laeuft nextval() ausserhalb der umgebenden Transaktion, ein Rollback gibt den bereits gezogenen Wert nicht zurueck, was PostgreSQL bewusst so implementiert hat, um Sperren auf dem Sequence-Objekt bei hoher Nebenlaeufigkeit zu vermeiden.

-- PostgreSQL: modern IDENTITY syntax (preferred since PostgreSQL 10)
CREATE TABLE orders (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Legacy SERIAL shorthand creates an implicit sequence behind the scenes
CREATE TABLE legacy_orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL
);

-- A sequence is an independent object and can be shared across tables
CREATE SEQUENCE shared_reference_seq START 1000;
SELECT nextval('shared_reference_seq');

4. SQL Server IDENTITY und SEQUENCE-Objekte

SQL Server bietet historisch die spaltenbasierte IDENTITY(start, increment)-Eigenschaft, die aehnlich wie MySQLs AUTO_INCREMENT direkt an eine einzelne Spalte gebunden ist, jedoch mit einem konfigurierbaren Startwert und Inkrement. Seit SQL Server 2012 existieren zusaetzlich eigenstaendige SEQUENCE-Objekte, die konzeptionell PostgreSQLs Sequences entsprechen und ueber NEXT VALUE FOR abgefragt werden, unabhaengig von einer bestimmten Tabelle.

Ein praktischer Vorteil von SEQUENCE-Objekten gegenueber IDENTITY: Sie koennen den naechsten Wert bereits vor dem eigentlichen Insert abrufen, etwa um ihn in mehreren zusammenhaengenden Tabellen zu verwenden, waehrend IDENTITY erst nach dem Insert ueber SCOPE_IDENTITY() zugaenglich ist. SEQUENCE unterstuetzt zudem CACHE-Groessen zur Performance-Optimierung bei hoher Insert-Rate, mit dem Kompromiss groesserer Luecken bei einem Server-Neustart, da gecachte, aber ungenutzte Werte verloren gehen.

-- SQL Server: classic IDENTITY property on the column
CREATE TABLE orders (
  id INT IDENTITY(1,1) PRIMARY KEY,
  customer_id INT NOT NULL,
  created_at DATETIME2 NOT NULL DEFAULT SYSDATETIME()
);

INSERT INTO orders (customer_id) VALUES (101);
SELECT SCOPE_IDENTITY();  -- id generated by the last insert in this scope

-- Standalone SEQUENCE object, independent of any single table
CREATE SEQUENCE shared_reference_seq
  START WITH 1000 INCREMENT BY 1 CACHE 50;

SELECT NEXT VALUE FOR shared_reference_seq;

5. Oracle Sequences und NEXTVAL/CURRVAL

Oracle war historisch der Vorreiter des Sequence-Konzepts und kannte lange gar kein spaltengebundenes Auto-Increment, sondern nur explizite CREATE SEQUENCE-Objekte, die per sequence_name.NEXTVAL in einem INSERT abgefragt werden mussten. Erst seit Oracle 12c gibt es mit GENERATED ALWAYS AS IDENTITY eine komfortablere, ANSI-naehere Syntax, die intern weiterhin eine klassische Sequence verwendet, aber die manuelle NEXTVAL-Referenz ueberfluessig macht.

Oracles CURRVAL gibt den zuletzt in der aktuellen Session gezogenen Wert zurueck, ohne einen neuen zu generieren, ein Feature, das fuer die Referenzierung des gerade erzeugten Schluessels in Folgeinserts innerhalb derselben Transaktion praktisch ist. Wichtig: CURRVAL ist erst nach mindestens einem vorherigen NEXTVAL-Aufruf in derselben Session gueltig, ein haeufiger Fehler bei neuen Oracle-Entwicklern, die CURRVAL ohne vorherigen NEXTVAL-Aufruf verwenden und einen Fehler erhalten.

-- Oracle: classic sequence, explicit NEXTVAL reference required
CREATE SEQUENCE orders_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE orders (
  id NUMBER PRIMARY KEY,
  customer_id NUMBER NOT NULL,
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

INSERT INTO orders (id, customer_id)
VALUES (orders_seq.NEXTVAL, 101);

-- CURRVAL returns the value already fetched in this session, no new value
SELECT orders_seq.CURRVAL FROM dual;

-- Oracle 12c+: modern IDENTITY syntax, no manual NEXTVAL needed
CREATE TABLE orders_modern (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id NUMBER NOT NULL
);

6. Luecken in der Sequenz: warum sie entstehen und meist okay sind

Ein wiederkehrendes Missverstaendnis in Entwicklerteams ist die Annahme, ein Auto-Increment-Wert muesse luecklos fortlaufend sein. In der Praxis entstehen Luecken regelmaessig: ein zurueckgerollter Transaktion hat bereits einen Sequence-Wert gezogen, der nicht zurueckgegeben wird, ein fehlgeschlagener Insert wegen einer Constraint-Verletzung verbraucht ebenfalls einen Wert, und gecachte Sequence-Bloecke gehen bei einem Server-Neustart verloren. Alle vier besprochenen Datenbanken teilen dieses Verhalten grundsaetzlich, mit unterschiedlicher Ausprägung je nach Cache-Konfiguration.

Fuer die allermeisten Anwendungsfaelle sind diese Luecken vollkommen unproblematisch, da ein Primaerschluessel lediglich Eindeutigkeit garantieren muss, keine luecklose Zaehlung. Probleme entstehen erst, wenn Anwendungscode faelschlicherweise auf luecklose IDs angewiesen ist, etwa fuer fortlaufende Rechnungsnummern mit gesetzlichen Anforderungen. In solchen Faellen ist ein datenbankgenerierter Primaerschluessel der falsche Mechanismus, stattdessen braucht es eine separate, explizit verwaltete Zaehlertabelle mit entsprechendem Locking.

7. Verteilte Systeme: Grenzen bei Multi-Master und Sharding

Alle vier besprochenen Mechanismen wurden fuer den Betrieb auf einem einzelnen, zentralen Datenbankserver konzipiert und stossen in verteilten Architekturen an klare Grenzen. Bei Multi-Master-Replikation mit mehreren gleichzeitig schreibenden Knoten wuerden zwei Knoten unabhaengig voneinander denselben naechsten Wert generieren, was zu Kollisionen fuehrt. MySQL loest dieses Problem in Multi-Master-Setups traditionell mit versetzten Inkrement-Offsets pro Knoten, PostgreSQL-Cluster-Loesungen greifen auf aehnliche Offset-Strategien oder auf UUID-basierte Schluessel zurueck.

Bei horizontalem Sharding wird das Problem noch deutlicher: Ein zentraler Sequence-Generator wuerde zum Flaschenhals und Single Point of Failure fuer alle Shards. Verbreitete Loesungen sind hier UUID- oder ULID-basierte Schluessel, die ohne zentrale Koordination generiert werden koennen, oder Snowflake-aehnliche Verfahren, die Zeitstempel, Knoten-ID und lokalen Zaehler in einem einzigen numerischen Wert kombinieren. Wer eine Anwendung von Anfang an fuer Sharding plant, sollte auf klassisches Auto-Increment ganz verzichten und direkt einen dieser verteilten Ansaetze waehlen.

8. Migrationspfade: Sequence-Werte korrekt uebernehmen

Bei einer Migration zwischen den vier Systemen ist der haeufigste Fehler, die Zieldatenbank mit den migrierten Daten zu befuellen, aber den internen Zaehlerstand des Zielsystems zu vergessen. Ohne explizite Korrektur startet die Zieldatenbank ihren Auto-Increment-Zaehler bei 1 und kollidiert beim ersten Insert nach der Migration sofort mit einer bereits importierten Zeile. Jede Datenbank bietet dafuer einen eigenen Reparaturbefehl: MySQL ALTER TABLE ... AUTO_INCREMENT = n, PostgreSQL setval(), SQL Server DBCC CHECKIDENT und Oracle ein Neuanlegen der Sequence mit passendem Startwert.

Ein zweiter haeufiger Fehler bei der Migration von Oracle oder SQL Server zu PostgreSQL oder MySQL: Die Zielsysteme unterstuetzen keine geteilte Sequence ueber mehrere Tabellen in derselben eleganten Form, sodass eine Anwendung, die sich auf einen gemeinsamen Nummernkreis verlaesst, umgeschrieben werden muss. Ein Migrationsplan sollte deshalb explizit pruefen, ob irgendeine Tabelle eine geteilte Sequence nutzt, bevor der eigentliche Datentransfer beginnt.

-- Fixing the counter after a data migration, per database

-- MySQL: set the next auto-increment value explicitly
ALTER TABLE orders AUTO_INCREMENT = 50001;

-- PostgreSQL: align the sequence with the current max id
SELECT setval(
  pg_get_serial_sequence('orders', 'id'),
  (SELECT MAX(id) FROM orders)
);

-- SQL Server: reseed the identity counter
DBCC CHECKIDENT ('orders', RESEED, 50000);

-- Oracle: recreate the sequence starting past the highest migrated id
DROP SEQUENCE orders_seq;
CREATE SEQUENCE orders_seq START WITH 50001 INCREMENT BY 1;

9. Mechanismen im direkten Vergleich

Die folgende Tabelle stellt die vier besprochenen Mechanismen fuer automatische Nummerierung nebeneinander und zeigt, worauf bei einer Migration oder einem Multi-Datenbank-Setup zu achten ist.

Wer eine Anwendung von MySQL auf eines der Sequence-basierten Systeme migriert, gewinnt die Moeglichkeit geteilter Nummernkreise, muss aber die abweichende Transaktionssemantik von nextval() beruecksichtigen, da diese Aufrufe wie beschrieben ausserhalb der Transaktions-Rollback-Logik laufen.

10. Zusammenfassung

Die Mechanismen fuer Auto-Increment, Sequences und Identity-Spalten unterscheiden sich zwischen MySQL, PostgreSQL, SQL Server und Oracle in ihrer grundlegenden Architektur, nicht nur in der Syntax. MySQL bindet den Zaehler eng an die Tabelle, PostgreSQL, SQL Server und Oracle behandeln Sequences als eigenstaendige, teilbare Objekte mit eigener Transaktionssemantik ausserhalb von Rollbacks. Luecken in der Nummerierung sind bei allen vier Systemen normales, erwartetes Verhalten und kein Bug.

Bei Migrationen zwischen den Systemen ist die explizite Korrektur des Zaehlerstands im Zielsystem der haeufigste vergessene Schritt, ebenso wie die Pruefung, ob geteilte Sequences ueber mehrere Tabellen im Einsatz sind. Fuer verteilte Architekturen mit Sharding oder Multi-Master-Replikation stossen alle vier klassischen Mechanismen an Grenzen, weshalb UUID-, ULID- oder Snowflake-basierte Ansaetze dort die robustere Wahl sind.

11. FAQ: Auto-Increment, Sequences und Identity im Vergleich