MySQL | primary key needs resetting

Question

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)

Basically `ALTER TABLE tablename AUTO_INCREMENT = 1000`. However, with InnoDB, you cannot set the new index to a value equal to or lower than the current highest used index. If that's your case, the only way would be to `TRUNCATE TABLE tablename` but that will, of course, purge all data from it. — kalatabe, Nov 27 '15 at 11:34
If you have so many records, then your auto_increment id must cover the entire range. I do not see any way to do what you would like to achive. — Shadow, Nov 27 '15 at 11:39
"and the number is just too big" - please elaborate the exact use case where the number is "too big". It's not "too big" for a link e.g. I guess you mean "too many digits to type"; in that case: who's typing the digits, when, where and how? — VolkerK, Nov 27 '15 at 11:41
altering increment number would start overriding existing records though.. — Eclipse, Nov 27 '15 at 11:45
Altering the auto increment number can't lead to overriding existing values: this is the primary key, all values are unique in the table. — Jocelyn, Nov 27 '15 at 11:47
What is your table structure? (the result of SHOW CREATE TABLE your_table) — Jocelyn, Nov 27 '15 at 11:50
Possible duplicate of [MySQL: Reorder/Reset auto increment primary key?](http://stackoverflow.com/questions/740358/mysql-reorder-reset-auto-increment-primary-key) — Jocelyn, Nov 27 '15 at 11:56
Aesthetic decisions are not a good criteria to tamper with the db — Mihai, Nov 27 '15 at 11:57
Solution to your problem is trivial. You simply **do nothing**. It's not what you want to read, but that's the only viable solution. Hacks like resetting PK or even reusing PK values will lead to problems. Just leave it alone. — Mjh, Nov 27 '15 at 12:01
Add a new column for arbitrary id's and leave the primary key alone. — Tschallacka, Nov 27 '15 at 12:13

genespos · Answer 1 · 2015-11-27T11:55:24.423

0

What about find lowest id and subtract its Value from all ids?

Then you'll be able to re-set the id to a lower number

Edit:

This suppose that there are unused ids and no recods related to them

edited Nov 27 '15 at 11:55

answered Nov 27 '15 at 11:46

genespos

3,211
6
38
70

What about all the other tables referencing this id? This is likely to break data consistency. – Jocelyn Nov 27 '15 at 11:49
@Jocelyn Naturally it needs to modify all ids into all tables. Every id Will always be unique – genespos Nov 27 '15 at 11:51

Samir Selia · Accepted Answer · 2015-11-27T12:08:14.447

To reset Primary Key, you can follow below steps:

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';

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;

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.

Drop tbl_cases
```
DROP table tbl_cases;
```

Rename tbl_cases_tmp to tbl_cases

RENAME TABLE tbl_cases_tmp TO tbl_cases;

score 0 · Answer 3 · answered Nov 27 '15 at 12:16

In case your problem is with conveying the caseid e.g. from the customer via phone to the help desk you might consider leaving the actual case id as-is but change the alphabet/set of digits when showing it.
E.g. switch from decimal to hexadecimal and you've increased the range of values that can be displayed as four digits/characters from 9999 to 65535 (hex:ffff).
Now consider a different set of digits like 3479ACEFHJKLMNPRTUVWXY* and the range of numbers that can be displayed using only four digits/characters increases quite a lot.

<?php
echo getCode(234255), "\r\n"; // up until "here": four digits
echo getCode(234256), "\r\n"; // ok, now it's five
echo getCode(5100000), "\r\n"; // but stays five until > 5 millions

function toBase(/* positiv integer*/ $n, array $alphabet) {
    $retval = '';
    do {
        $retval = $alphabet[ $n%count($alphabet) ] . $retval;
        $n = intval( $n / count($alphabet) );
    }
    while( ($n=intval($n)) > 0);
    return $retval;
}

function getCode(/*int*/ $caseid) {
    static $alphabet = ['3','4','7','9','A','C','E','F','H','J','K','L','M','N','P','R','T','U','V','W','X','Y'];
    return toBase($caseid, $alphabet);
}

prints

YYYY
43333
YTYAA

*) an alphabet containing only unambiguous characters.

MySQL | primary key needs resetting

3 Answers3