SQLite WITHOUT ROWID Tables

SQLite WITHOUT ROWID Tables is a SQL statement in the SQLite Specific category. SQLite: creates a table without the implicit ROWID column. The PRIMARY KEY becomes the clustered index. The syntax is CREATE TABLE table_name (columns, PRIMARY KEY (col)) WITHOUT ROWID;. It returns rOWID-less table. A typical example: CREATE TABLE config ( key TEXT PRIMARY KEY, value TEXT ) WITHOUT ROWID; -- No ROWID column (saves space) -- Data is clustered by PRIMARY KEY (TEXT) -- Faster lookups by key, slower full scans -- When to use: -- Lookup tables (key->value) -- Composite primary key tables -- When storage space is critical -- Compared to default: -- Default: ROWID hidden column, data stored by ROWID order -- WITHOUT… A close relative is AUTOINCREMENT (SQLite), which sQLite: unlike AUTO_INCREMENT in MySQL, only INTEGER PRIMARY KEY columns auto-increment. AUTOINCREMENT guarantees unique increasing IDs. A close relative is ROWID / _ROWID_, which sQLite: every row has an implicit 64-bit ROWID. INTEGER PRIMARY KEY becomes an alias for ROWID.