7.9.3. SQL-03 — Auto-Increment Primary Key

Tutorial 2 inserted Car rows with explicit Id values. This tutorial shows what happens when you omit the integer primary key — SQLite assigns the rowid for you, and insert returns it.

7.9.3.1. INTEGER PRIMARY KEY ↔ ROWID

In SQLite, INTEGER PRIMARY KEY is a special case: the column is the table’s ROWID alias and gets auto-assigned on insert when omitted. [sql_table] maps any int or int64 field marked @sql_primary_key to INTEGER PRIMARY KEY for exactly this behaviour:

[sql_table(name="Friends")]
struct Friend {
    @sql_primary_key Id : int
    Name : string
}

The generated DDL is CREATE TABLE "Friends"("Id" INTEGER PRIMARY KEY, "Name" TEXT NOT NULL).

7.9.3.2. Inserting without an Id

Construct a row with the PK field unset (zero) and the macro-generated _sql_pk_is_unset(row) returns true. The insert overload then picks the no-PK INSERT shape (INSERT INTO "Friends" ("Name") VALUES (?)), letting SQLite assign the rowid. The returned int64 is the assigned id:

with_sqlite(":memory:") <| $(db) {
    db |> create_table(type<Friend>)
    let last_id = db |> insert([
        Friend(Name="Tom"),
        Friend(Name="Rebecca"),
        Friend(Name="Jim"),
        Friend(Name="Roger"),
        Friend(Name="Robert")
    ])
    to_log(LOG_INFO, "The last Id of the inserted row is {last_id}\n")
    // The last Id of the inserted row is 5
}

For an array insert, the returned int64 is the rowid of the last inserted row (consistent with sqlite3_last_insert_rowid semantics).

7.9.3.3. Reading the last rowid directly

last_insert_rowid is the direct wrapper:

let id = db |> last_insert_rowid()

Use this when you need the most recent rowid in a context where the insert return value is no longer in scope.

7.9.3.4. Mixing explicit and assigned PKs

If you supply Friend(Id=42, Name="X"), the macro routes through the with-PK INSERT shape and uses your value verbatim — provided no existing row has the same Id. A duplicate Id raises SQLITE_CONSTRAINT and panics; the non-panicking try_insert returns Err(errmsg) instead. Tutorial 6 walks the constraint-violation handling story.

7.9.3.5. Quick reference

Helper	Description
`@sql_primary_key Id : int`	Maps to `INTEGER PRIMARY KEY`
`Friend(Name="Tom")`	Construct with PK = 0 → SQLite assigns rowid
`db \|> insert(row) : int64`	Single-row INSERT, returns assigned rowid
`db \|> insert(array<row>) : int64`	Batched INSERT, returns last assigned rowid
`db \|> last_insert_rowid() : int64`	Wraps `sqlite3_last_insert_rowid`