☰

resequence_ids()

public function resequence_ids(string $table_name): bool

Description

Resequences the ID column of a table to eliminate gaps, making IDs sequential starting from 1. Useful after deleting records to maintain clean, sequential IDs. The method uses a two‑pass update with temporary negative IDs to avoid primary key conflicts, wrapped in a transaction for atomicity.

Parameters

Parameter Type Description Default Required
table_name string The name of the table whose IDs should be resequenced. - Yes

Return Value

Type Description
bool True if resequencing was successful.

Exceptions

  • Exception - If any database operation fails; the transaction is rolled back and the exception is re‑thrown.

Example #1: Basic Resequencing

The code sample below demonstrates how to resequence IDs in a table after deletions.

PHP
// After deleting some records, make IDs sequential again
if ($this->db->resequence_ids('products')) {
    echo "Product IDs have been resequenced successfully.";
}

Example #2: Resequencing After Bulk Deletion

The code sample below demonstrates resequencing as part of a cleanup routine.

PHP
// Delete inactive users
$this->db->query("DELETE FROM users WHERE active = 0");

// Resequence the IDs to remove gaps
try {
    $this->db->resequence_ids('users');
    echo "User IDs resequenced after cleanup.";
} catch (Exception $e) {
    echo "Failed to resequence IDs: " . $e->getMessage();
}

Example #3: Maintenance Script for Multiple Tables

The code sample below demonstrates resequencing IDs across multiple tables in a maintenance script.

PHP
$tables_to_resequence = ['categories', 'tags', 'statuses'];

foreach ($tables_to_resequence as $table) {
    if ($this->db->table_exists($table)) {
        try {
            $this->db->resequence_ids($table);
            echo "Resequenced IDs for table '$table'.";
        } catch (Exception $e) {
            echo "Failed to resequence '$table': " . $e->getMessage() . "";
        }
    }
}

Important Notes

  • The $table_name parameter is required.
  • Uses a database transaction; if any step fails, all changes are rolled back.
  • Employs a two‑pass algorithm:
    1. First pass: assign temporary negative IDs to avoid conflicts
    2. Second pass: assign new sequential IDs starting from 1
  • Resets the auto‑increment counter to 1 after resequencing.
  • If the table is empty, simply sets auto‑increment to 1 and returns true.
  • Performance considerations:
    • For large tables, this operation can be resource‑intensive
    • Locks the table during execution (via transaction)
    • Consider doing during low‑traffic periods
  • Foreign‑key constraints: If other tables reference these IDs, resequencing will break those references. Use with caution in systems with complex relationships.
  • Always backup your data before using this method in production.
  • Primarily intended for:
    1. Development and testing environments
    2. Lookup tables with simple relationships
    3. Systems where sequential IDs are required for external compliance