Search code examples
phpmysqlprimary-keyauto-increment

MySQL | primary key needs resetting

I have a table which stores the ID of support cases using the primary key (column name = caseid).

I have now got to about 100,000 caseid and the number is just too big. I wish to somehow start from a lower number such as 1000.

How do I achieve something like this by not having to delete/archive existing records and not having to change the unique caseid's to another column (keep it as the primary key column)

Solution

  • To reset Primary Key, you can follow below steps:

    1. Create temporary table with structure same as main table. Let's say table name is tbl_cases

      CREATE TABLE tbl_cases_tmp LIKE tbl_cases;
ALTER TABLE tbl_cases_tmp ADD old_caseid int NOT NULL DEFAULT '0';

    2. DUMP all data from tbl_cases to tbl_cases_tmp. caseid will be stored in old_caseid column.

      INSERT INTO tbl_cases_tmp (name, summary, old_caseid)
SELECT name, summary, caseid FROM tbl_cases;

    3. For any other tables having references to tbl_cases. Let's say tbl_reference

      UPDATE tbl_reference tr 
JOIN tbl_cases_tmp tc
ON tr.caseid = tc.old_caseid
SET tr.caseid = tc.caseid;

    Before using Steps 4 and 5, ensure your tables tbl_cases_tmp and all references are properly updated.

    1. Drop tbl_cases

      DROP table tbl_cases;

    2. Rename tbl_cases_tmp to tbl_cases

      RENAME TABLE tbl_cases_tmp TO tbl_cases;