Re: reindex primary key autoincrement at mysql

From: The Natural Philosopher <tnp_at_invalid.invalid>
Date: Fri, 29 Dec 2017 10:49:29 +0000
Message-ID: <p256jq$sec$1_at_dont-email.me>

On 29/12/17 10:42, Axel Schwenke wrote:
> On 29.12.2017 10:51, Jakub wrote:

>>
>> How to re index primary key autoincrement column

>
> I can only guess what you mean by "re index the PK", but if you want to
> change the PK of existing rows, i.e. to remove gaps in the numbers, then the
> simple answer is: YOU NEVER DO THAT.
>
> The PK has only one purpose: to uniquely identify a row; and in most cases
> to do so for the life time of the whole database (which may well exceed the
> life time of a single row). That means that a PK is never assigned twice,
> not even after the original row has been deleted. Not even if the row
> existed only spuriously, i.e. as part of a rolled back transaction.
>
> If you need a running counter, then create one on the fly. Or if your needs
> require that, add another field to your table. But DONT MESS WITH THE PK!
>
I have reset autoncriment pks on tables used temporarily, befier importing them into the main databse.

(sales made off-internet, on database copy, needing to be synched with main sales database: once synched the copy database was cleared and PK reset to 1)

-- 
"The great thing about Glasgow is that if there's a nuclear attack it'll 
look exactly the same afterwards."

Billy Connolly

Received on Fri Dec 29 2017 - 11:49:29 CET