An identity column collides with imported ids on the next INSERT — why, and how do you reseed it?

## What actually happened An identity column has two independent parts: the values physically stored in the column, and a generator that remembers the last number it handed out. Nothing synchronises them. The generator advances **only when it is asked to generate** — that is, when an INSERT omits the column or writes `DEFAULT`. A migration typically does the opposite. It supplies the ids, because downstream data references them: ```sql -- column declared GENERATED BY DEFAULT AS IDENTITY INSERT INTO orders (order_id, customer_id, total) SELECT legacy_id, customer_id, total FROM staging_orders; -- ids 1 .. 100000 ``` The rows land with ids 1 to 100000. The generator is still at 1. The next application INSERT, which omits `order_id`, is offered 1 — and the primary key rejects it. The errors then continue one per insert as the generator crawls upward through occupied territory, which is why the symptom often looks like an intermittent outage rather than a clean failure. ## The repair Reseed the generator above the highest stored value. The ANSI form alters the column: ```sql SELECT MAX(order_id) FROM orders; -- 100000 ALTER TABLE orders ALTER COLUMN order_id RESTART WITH 100001; ``` `RESTART WITH n` sets the *next* value the generator will produce. Vendor equivalents: - MySQL: `ALTER TABLE orders AUTO_INCREMENT = 100001;` - SQL Server: `DBCC CHECKIDENT ('orders', RESEED, 100000);` — note this sets the **last** used value, so the next one is 100001 The off-by-one differs between these forms, which is exactly the sort of detail worth checking against a real row rather than reasoning about at 2 a.m. ## Do it in the same maintenance step The reseed belongs to the import, not to the incident that follows it. Two reasons. First, `MAX(id) + 1` is only trustworthy while nothing else is inserting; run it against a live table and a concurrent insert can land above your computed value, leaving you to repeat the whole exercise. Second, the window between the load and the reseed is a window in which every ordinary insert fails, so leaving it open is choosing an outage. A safe shape is: quiesce writes or run before cutover → load the rows → compute the maximum → reseed → verify with one throwaway insert-and-rollback → open traffic. Leave headroom above the maximum rather than landing exactly on it; nothing depends on the numbers being contiguous, and a comfortable gap costs nothing. ## Prevention **Declare the column `GENERATED ALWAYS`.** Then an accidental explicit id is a rejected statement at the moment it is written, not a landmine. A genuine import still gets through, but it has to say so: ```sql INSERT INTO orders (order_id, customer_id, total) OVERRIDING SYSTEM VALUE SELECT legacy_id, customer_id, total FROM staging_orders; ``` The visible clause is the point: it marks the statement as one that will leave the generator behind, and a review or a runbook can look for it. **Script the reseed with the load.** Whatever tool performs the migration should end with the `RESTART`. Treat the pair as one operation. **Beware of tooling that hides this.** Backup and restore utilities differ in whether they carry the generator's position with the data; a table restored into a fresh schema can arrive fully populated with a generator at 1. The same applies to environment refreshes that copy tables between databases with plain `INSERT ... SELECT`. Any process that moves rows *with* their keys is a process that must reseed. ## What not to do Do not paper over it in the application by computing `MAX(id) + 1` before each insert. That reintroduces the race the generator exists to eliminate, and it will produce duplicates under concurrency rather than errors — which is strictly worse, since the constraint turns them into failed writes at best and mismatched data at worst. Do not switch the column to `GENERATED BY DEFAULT` and have the application supply ids "to be safe". That is the same race with more code. Do not renumber the imported rows to sit above the generator instead of moving the generator. Their keys are referenced by other tables and, often, by data outside the database entirely; changing a key that other rows point at is a much larger operation than resetting a counter. ## The related check While you are there, verify the two other things an import can leave inconsistent: any sequence objects the schema uses directly (they reseed with `ALTER SEQUENCE seq RESTART WITH n`, and dropping the table does not remove them), and whether the identity column's declared type still has room — a legacy dataset whose keys reach into the billions can exhaust an `INTEGER` column that looked generous on an empty table.