Make values of a column sequential

from the Artful Common Queries page

You have a table tbl with a column whose values can be freely overwritten and which needs to be filled with perfectly sequential values starting with 1:
SET @i=0;
UPDATE tbl SET keycol=(@i:=@i+1); 
But more often, what you need is a mechanism for guaranteeing an unbroken sequence of integer key values whenever a row is inserted.

Here is the bad news: auto_increment does not provide this logic, for example it does not prevent inserton failures, editing of the column, or subsequent deletion. The good news is that the logic for guaranteeing an unbroken sequence is straightforward:

(i) use InnoDB

(ii) create a table with the desired sequence in one column and further column(s) to track use (to use such a table for sequential columns in multiple other tables, provide a tracking column for each such table)

(iii) write Triggers to fetch the next available sequential key from that table, mark it used, and assign it on Insert

(iv) put all such logic inside transaction blocks

(v) prohibit deletion in the table.

Last updated 17 Aug 2019

Return to the Artful Common Queries page