Deleting a row does not retire its id

Most spreadsheet importers are written as a replace. Read the sheet, clear the table, insert the rows. It is the shortest correct-looking implementation, and for a table nothing else points at, it is genuinely fine.

The moment one other table stores a reference to those rows, a replace stops being an update. What happens instead is not one failure. It is at least six different ones, and which one you get is decided by declarations in your schema that the import code never mentions.

An import that said exactly what it did

In October a small trading-ledger app, DDancingDeath/AadhatManagementApp, filed and fixed issue #67 in a single day: "[Bug] Item Excel import deletes every item and re-creates them with new IDs (orphans stock and bills)". It carried the labels P0 and area:stock, in a milestone called "Sprint 1 - Safety".

The code it names is worth reading, because it is not careless. At the pre-fix commit the import begins by asking permission, in a dialog that describes the behaviour accurately:

'This will delete ALL existing items and replace them with items from the Excel file. Continue?',

Then it does that, under a comment that is equally honest:

// Delete all existing items
for (const item of AppState.items) {
    await FirebaseService.deleteItem(item.id);
}
AppState.items = [];

Nothing is hidden. The dialog says "replace", the comment says "Delete all", and the loop does both. The gap is not between what the code says and what it does. It is between "replace the catalogue" and "update the catalogue", which read as the same sentence in English and are not the same operation once a bill has written an item id down.

That the writes are one per row with no transaction is a second problem, and a post here already covers it: catching the exception does not undo the writes. This post is about the ids.

The fallback that hides it

The interesting part of this codebase is that someone already thought about dangling references. Bill lines do not resolve through a database join. They go through a resolver in utils/stock-replay.js, and it has a deliberate second chance in it:

if (item.itemId) {
    if (catalogueIds.has(item.itemId)) return item.itemId;
    // [comment omitted, quoted below]
}
const target = normalize(item.name);
if (target && idByName.has(target)) {
    return idByName.get(target);
}
return target || (item.itemId || '__unknown__');

The comment on the line left out of that block is the design, stated plainly. It says the stored itemId "points to a deleted catalogue entry", and that resolution is therefore to "fall through to name matching". (The comment is elided above only because it contains a dash this site's copy gate bars.)

So after a delete-and-re-add, an old bill line whose itemId no longer exists falls through to its stored name, finds the freshly inserted item whose English or Hindi name matches it, and resolves to the new id. The stock replay keeps working. The bug does not show up.

That is why a P0 data-safety issue can sit in a shipping app: in the ordinary case the fallback quietly repairs the damage, so nothing looks wrong. What the fallback actually does is make the catalogue id decorative and the name load-bearing, which is a different data model from the one the rest of the app is written against.

And the two name matchers in this codebase are not the same matcher. The catalogue's duplicate check normalises to Unicode NFC, trims, collapses inner whitespace and lower-cases. The replay's resolver, on the line above, trims and lower-cases only. Transcribing both rules into a separate implementation and running the pairs:

pair                                      catalogue check   replay resolver
one inner space vs two                    same item         different
Hindi nukta: precomposed vs decomposed    same item         different

Both matter for a sheet that has been round-tripped through Excel and a human. Two spaces where there was one is an ordinary typing accident. The Hindi case is structural: this app stores a hindiName for every item, and Devanagari nukta characters have both a precomposed and a decomposed spelling, so the two forms are the same text under NFC and different byte strings without it.

The consequence is a matcher that disagrees with itself. The catalogue would refuse to let you create the second spelling as a separate item, because its duplicate check says the name is already taken. The replay, handed the same two spellings, resolves them to different keys. So the one path that was supposed to rescue the orphaned bill lines fails exactly when the name changed in a way the catalogue considers no change at all. Resolution then returns the normalised name string rather than any catalogue id, and the usage count, which only counts keys that are catalogue ids, drops the line:

catalogue state                     resolved key      a catalogue id?
before the import, old id present   "OLDID001"        true
re-added, name byte-identical       "NEWID777"        true
re-added, one extra inner space     "cotton bale"     false
re-added, name edited in the sheet  "cotton bale"     false

The fix the project shipped is the right one and it is not subtle: commit e5152d3, "fix(items): Excel import merges into the catalogue instead of wiping it (#67)". It never deletes. It matches each sheet row to an existing item by an id column first and then by name, updates matched items in place with set(..., { merge: true }), leaves items absent from the sheet alone, batches the writes, shows a preview of the counts before writing anything, and adds an id column to the export so that export, edit and import round-trips. Its test asserts the negative directly:

expect(FirebaseService.deleteItem).not.toHaveBeenCalled();

The same import, six different outcomes

The app above stores its data in a document store, and its delete loop removes each item document by id without looking at the bills that point to it, so nothing could refuse the delete. That is our reading of the code, not a point the issue makes, and it is why the orphaning was silent there rather than loud.

But the more useful question is what a relational engine does, because the answer is not "it stops you". Here is one bill line for 10 of "Cotton Bale", then a delete-everything-then-insert import of a two-row sheet, run in SQLite 3.45.1:

schema                                sheet row order         the bill line afterwards
INTEGER PRIMARY KEY, fk OFF (default) unchanged               reads "Cotton Bale"
INTEGER PRIMARY KEY, fk OFF (default) the two rows swapped    reads "Jute Bale"
same + AUTOINCREMENT, fk OFF          unchanged               points at id 1, which no longer exists
same + AUTOINCREMENT, fk OFF          the two rows swapped    points at id 1, which no longer exists
INTEGER PRIMARY KEY, fk ON            unchanged               refused: FOREIGN KEY constraint failed
INTEGER PRIMARY KEY, fk ON            the two rows swapped    refused: FOREIGN KEY constraint failed
fk ON, ON DELETE CASCADE              unchanged               the bill line was deleted
fk ON, ON DELETE CASCADE              the two rows swapped    the bill line was deleted
fk ON, ON DELETE SET NULL             unchanged               item_id set to NULL
fk ON, ON DELETE SET NULL             the two rows swapped    item_id set to NULL

Row two is the one to sit with. The bill has not been orphaned, flagged or broken. It now says the business sold 10 of a different product, and every join still succeeds. Nothing anywhere reports an error. Reordering two rows in a spreadsheet changed a financial record, because the delete freed the integer key and the insert handed it to whichever row the sheet listed first.

That happens because a plain INTEGER PRIMARY KEY in SQLite is the rowid, and SQLite's documentation gives the rule for a new one: "a ROWID that is one larger than the largest ROWID in the table prior to the insert. If the table is initially empty, then a ROWID of 1 is used." Empty the table and the numbering starts again, in whatever order the sheet lists the rows. AUTOINCREMENT changes the algorithm "to prevent the reuse of ROWIDs over the lifetime of the database", so the id is not reused and the reference dangles instead. A dangling id is strictly better here: it is wrong in a way you can find with a query.

Two more things in that table are worth naming. Foreign keys were not enforced in rows one to four, and not because they were switched off for the test: PRAGMA foreign_keys reads 0 on a fresh connection, so a REFERENCES clause sitting in the schema is inert until something turns it on. The documentation says as much: foreign key constraints "are disabled by default (for backwards compatibility), so must be enabled separately for each database connection." It is also per connection, which is measurable on a single database file: three connections to the same file, one leaving the pragma alone, one setting it on and one off, and the identical DELETE is refused on exactly one of them. And ON DELETE CASCADE, which is what a schema author writes to keep references tidy, turns an item import into the deletion of a bill line. The strictest-looking option on that list destroys the most.

PostgreSQL 16.15, same tables, same import (18.6 gave identical results):

schema              sheet_row_order      the bill line afterwards
no foreign key      unchanged            points at id 1, which no longer exists
no foreign key      the two rows swapped points at id 1, which no longer exists
no ON DELETE clause unchanged            refused: SQLSTATE 23503
no ON DELETE clause the two rows swapped refused: SQLSTATE 23503
ON DELETE CASCADE   unchanged            the bill line was deleted
ON DELETE SET NULL  unchanged            item_id set to NULL

The difference from SQLite is the first two rows. PostgreSQL's sequence keeps counting through a DELETE, so the re-added rows took ids 3 and 4 and the old reference to id 1 dangles. Nothing was validated: the key allocator simply did not hand id 1 out again.

That protection belongs to the DELETE, not to PostgreSQL. The other common way to clear a table before an import is TRUNCATE ... RESTART IDENTITY, and RESTART IDENTITY is documented as "Automatically restart sequences owned by columns of the truncated table(s)." The same import, clearing the table that way, in 16.15 and 18.6 alike:

clear with                             schema            sheet row order       the bill line afterwards
TRUNCATE ... RESTART IDENTITY          no foreign key    unchanged             reads "Cotton Bale"
TRUNCATE ... RESTART IDENTITY          no foreign key    the two rows swapped  reads "Jute Bale"
TRUNCATE ... RESTART IDENTITY          any foreign key   either                refused: SQLSTATE 0A000
TRUNCATE ... RESTART IDENTITY CASCADE  any foreign key   either                the bill line was deleted

The second row is SQLite's silent wrong product, back in PostgreSQL. The last row is a sixth ending. CASCADE on TRUNCATE is defined as "Automatically truncate all tables that have foreign-key references to any of the named tables", so the whole bill table is emptied, even where its foreign key says ON DELETE SET NULL. Whether a delete-and-re-add import corrupts a record or merely breaks a link comes down to the key allocator and the statement that clears the table, and the import's row loop sees neither.

So the same import, written once, has six endings: a correct-looking wrong value, a dangling reference, a refusal, a deleted dependent row, a nulled column, and an emptied dependent table. The import code is identical in all of them. What differs is a pragma, a keyword, an ON DELETE clause and the statement that clears the table.

What the contract actually is

An import is a merge keyed by identity. A row's identity has to be something the sheet carries and the database agrees with, which means either an exported id column that round-trips, or a natural key the sheet genuinely owns. Replacing a row is not updating it, because the id is not a property of the row's contents. It is a name other tables are holding.

Which makes the export half of the contract load-bearing too, and that half also fails. In the same week, the reference implementation of UW Markdown, an open format for commercial real-estate underwriting records, filed UW-Markdown#484: "uwmd-excel cannot import a workbook it exported from a record without full operating-statement detail". Export a freshly initialised record and import it straight back, unedited, and the importer exits 1 with Workbook subtotal "Effective Gross Income" has been altered. The exporter writes a missing subtotal as #N/A or as a literal value; the importer's validation wants an exact SUM(...) formula in that cell. Per the issue, all 84 of the tool's starter records (12 asset classes by 7 stages) fail this way while the shipped examples pass. If export then import is not an identity function, "export, edit the ids, import" is not a workflow you can offer.

Where TableRune fits

TableRune is our browser-based spreadsheet and analytics tool: the file is never uploaded, and a Rust engine plans each pipeline into SQL that DuckDB-Wasm runs on your device. Its import is the pattern this post is about, a whole-table replace: CREATE OR REPLACE TABLE "dataset" AS SELECT * FROM read_csv_auto(...). That is safe here for the reason the post predicts. Nothing in TableRune stores a reference to a row: there are no foreign keys, and a pipeline step names columns, never row ids. A saved workflow, per its own source comment, "stores ONLY the steps (small JSON), not the data", so you can "reload it later onto any dataset".

Reading that code for this post found the same mistake one level up. A saved step stored two things that described the data at the moment the step was made, and a reloaded workflow trusted both. Both were fixed on 6 November 2026, before this post went out, in production commit bb7409f.

A stored file name. Merge, append and compare join against one fixed table that holds whichever second file is imported now, but each step also stored the name of the file it was built with. Compare writes that name into its output: rows found only in the second file are marked only in jan.csv. Reload a saved compare after importing feb.csv and every one of February's unmatched rows said only in jan.csv, in the data and in any export of it. The step labels showed the old name too. Now the name comes from the file actually imported, and a workflow that needs a second file says "This workflow uses a second file; import one first." when there is none.

A stored column list. Steps that change one column and keep the rest (fill, change type, rename, split, combine, the text cleaners, trim, custom sort and drop) were turned into a SELECT of the column list stored when the step was made, and remove duplicate rows grouped by that list. Reload a workflow onto a file with an extra column and those steps dropped it, with no error: the table just came back narrower. Remove blank rows looked only at the stored columns, so a row whose only data was in the new column was judged blank and deleted. Each of those steps now passes through every column present when it runs (DuckDB's *), remove duplicate rows compares every current column, and remove blank rows checks every current column.

That second case is this post's argument in miniature. A list of columns is an identity claim about a table, made once and then trusted, and the moment the table changed underneath it the list quietly decided which data still existed.

Two smaller things changed with them. A workflow reloaded onto a file missing a column it needs now names the column and the step that needs it, instead of suggesting the user had forgotten quotes around a text value. And the Workflows panel says "Loaded" only when the pipeline actually rebuilt; it used to say so before the rebuild had finished, even when it then failed. A failed workflow load still changes nothing: the step list, undo history and saved session stay as they were.

One gap is still open and worth stating. If importing a new second file triggers a rebuild that fails, the error explains why, but the grid keeps showing the previous result until the pipeline is fixed.

What this post covers, and what it does not

The SQLite and PostgreSQL tables were measured on 6 November 2026, twice, on two machines with separate scripts: SQLite 3.45.1 via Python 3.11.18 and 3.12.3, and PostgreSQL 16.15, with every PostgreSQL case repeated on 18.6, all on x86_64, on the two-table schema shown and no other. Those numbers are behaviour of those two builds, not a statement about every release or every engine, and the SQLite rowid result in particular depends on the key being a plain INTEGER PRIMARY KEY.

The quoted documentation is SQLite's foreign key and autoincrement pages and PostgreSQL 16's TRUNCATE page, as fetched on 6 November 2026. Every behaviour quoted from them was also measured.

AadhatManagementApp's code was read at the pre-fix commit and at the fix, and was not run. The normaliser comparison is a transcription of its two rules into a separate implementation, not its code executing, so it is evidence about the rules as written. That the document store could not refuse the delete is our reading of the code, not something the issue says or that was tested. UW-Markdown#484 is reported from the issue text only: neither that tool nor its fix was run, and the fix made in its PR was not read.

No count of affected projects or reports appears anywhere in this post, and none should be inferred from the two issues cited.

The TableRune claims come from reading the engine, route and library source at production commit d71562d and again at bb7409f, where the fixes landed, and from the fix commits' own end-to-end tests, not from our marketing copy. That bb7409f is what tablerune.com serves was checked in its live JavaScript on 6 November 2026. Whether a chart or export path binds to something this post did not look at was not checked.