Skip to content


Paweł Salawa edited this page Aug 3, 2019 · 3 revisions


Language: Tcl
Plugin for language: ScriptingTcl
How to use: Create custom SQL function. Type: Scalar. Suggested name: resequence
Function arguments table, column
Function usage: SELECT resequence('tableName', 'idColumnName');
Description: For table column that has sequence of ID numbers, but with gaps (due to deletions, etc), this function re-sequences values, so the numbers are subsequent and do not have gaps. It automatically detects any foreign tables referencing to this updated column and updated foreign column values too. As result returns how many IDs were resequenced and in which tables did it update them.


lassign $argv table column
set currentIds [db eval "SELECT `$column` FROM `$table`"]

set fkTablesToUpdate [list]
set tables [db eval "select name from sqlite_master where type = 'table'"]
foreach tab $tables {
  if {$tab == $table} {
  set fkRows [db rows "pragma foreign_key_list($tab)"]
  foreach row $fkRows {
    lassign $row id deq fkTable localCol fkCol
    if {$fkTable == $table && $fkCol == $column} {
      lappend fkTablesToUpdate [list $tab $localCol]

db eval "PRAGMA foreign_keys = false"

set updated [list $table]
set seq 0
foreach id $currentIds {
  db eval "update `$table` set `$column` = $seq where `$column` = $id"
  foreach fkTabCol $fkTablesToUpdate {
    lassign $fkTabCol fkTab fkCol
    db eval "update `$fkTab` set `$fkCol` = $seq where `$fkCol` = $id"
  incr seq

db eval "PRAGMA foreign_keys = true"

foreach fkTabCol $fkTablesToUpdate {
  lassign $fkTabCol fkTab fkCol
  lappend updated $fkTab

return "Resequenced $seq IDs in tables: [join $updated {, }]"
You can’t perform that action at this time.