No Auto Increment Column Syntax Works on Every SQL Engine

A schema meant for several engines needs ids that number themselves, and no single way of asking for that works on all four. SERIAL comes closest, and on SQLite it fails without a word.

AUTO_INCREMENT belongs to MySQL and MariaDB, AUTOINCREMENT to SQLite, and GENERATED BY DEFAULT AS IDENTITY to PostgreSQL. SERIAL is PostgreSQL’s as well, and MySQL and MariaDB accept it as BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. SQLite accepts any type name, so SERIAL is only a name there and the column stays NULL: SQLite numbers rows for a column declared INTEGER PRIMARY KEY and nothing else.

What Each Engine Answered

The example

CREATE TABLE t (id SERIAL PRIMARY KEY, x INTEGER);
INSERT INTO t (x) VALUES (5);
SELECT id FROM t;

gave these answers:

Every Form BareSQL Checks

BareSQL Portable checks 6 forms of this, and each was run on all four engines. yes means the engine ran it and the engines that ran it agreed, no means the engine refused it, and a value is what the engine answered where they disagreed.

What to Write Instead

No column definition numbers rows on all four engines. Keep one schema file per engine for these tables, or let the application assign the ids.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:1:20: warning: SERIAL numbers rows on PostgreSQL, MySQL and MariaDB; on SQLite it is only a type name and the column stays NULL. [auto-increment]
    SERIAL
    No column definition numbers rows on all four engines. Keep one schema file per engine for these tables, or let the application assign the ids.

1 problem: 1 warning.

baresql rules auto-increment prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.