Rows with a primary key of `null` or empty string cannot be edited/deleted/viewed

[simonw]

Just spotted that if you set the id (primary key) of a column to null you get this:

Clicking on that "None" text navigates to http://127.0.0.1:7744/data/blobs/None which says 404 "Row not found", and clicking the edit icon gives you this:

Clicking the delete icon gives you this:

But when you confirm you get this:

Meanwhile if the primary key value is "" (empty string) you get this:

There's nothing to click on to view the row page - in the HTML it's this:

<a href="/data/blobs/"></a>

And clicking the edit and deleting icons does nothing, their HTML is:

<tr data-row="">
...
<span class="row-inline-actions" aria-label="Row actions">
  <button
    type="button"
    class="row-inline-action row-inline-action-edit"
    aria-label="Edit row "
    title="Edit row"
    data-row-action="edit"
  >
    <svg>...</svg>
  </button>
  <button
    type="button"
    class="row-inline-action row-inline-action-delete"
    aria-label="Delete row "
    title="Delete row"
    data-row-action="delete"
  >
    <svg>...</svg>
  </button>
</span>
</tr>

[simonw]

Having null primary keys is weird, and blank primary keys is almost as weird.

I had Claude run some experiments: https://claude.ai/share/d621f05e-ac4a-4f94-be55-f9658555533b

TEXT PRIMARY KEY

A single blank string '' inserts fine, but a second one fails with a UNIQUE constraint failed error — blank strings are treated like any other real value and must be unique. NULL behaves completely differently: you can insert as many NULL primary keys as you want. I got three rows where two had NULL ids sitting right next to each other. This is the famous SQLite quirk — it allows NULLs in a declared PRIMARY KEY column, which violates the SQL standard but is documented behavior kept for backwards compatibility.

INTEGER PRIMARY KEY (the INTEGER spelling)

This one is special because INTEGER PRIMARY KEY is an alias for the internal rowid. A blank string '' fails immediately with datatype mismatch — you simply can't put a text value in a rowid column. NULL "works" but not in the way you'd think: instead of storing NULL, SQLite auto-assigns the next available integer, so the two NULL inserts became ids 1 and 2. The column never actually contains a NULL.

The subtle trap: INT vs INTEGER

My second experiment shows that the exact spelling matters enormously. A column declared INT PRIMARY KEY (rather than INTEGER) is not a rowid alias, so it behaves like the text case — multiple NULLs are allowed (I got two rows both with NULL ids), and a blank string is accepted once but rejected on the second insert. Only the literal keyword INTEGER triggers the rowid-aliasing magic.

[simonw]

Problem is we need to support any SQLite database that the user brings to us.

To keep our lives a little simpler we could have Datasette itself refuse to set a blank or null primary key through its UI. That feels sensible to me.

[simonw]

The fact that you can have multiple rows with the same primary key if that primary key is null is deeply distressing.

[simonw]

After some more experiments you can only have null primary keys on a rowid table (so SQLite still has an invisible unique ID per row) - non-rowid tables carry an implicit not null on their primary keys.

[simonw]

One easy-ish option would be that any time we run our own CREATE TABLE we set not null on any primary key we create.

[simonw]

SQLite STRICT tables don't allow null primary keys:

Columns that are part of the PRIMARY KEY are implicitly NOT NULL. However, even though the PRIMARY KEY has an implicit NOT NULL constraint, when a NULL value is inserted into an INTEGER PRIMARY KEY column, the NULL is automatically converted into a unique integer, using the same rules for INTEGER PRIMARY KEY on ordinary, non-strict tables.

STRICT was introduced in SQLite version 3.37.0 (2021-11-27). We still support 3.25.0.