Renumber Primary Key 1

[wimhek]

Hi All

I have a database where I delete al lot and insert a lot.
My ID field (primary key, auto increment) will soon be 'overflowed'. Is there a command or script whre I can 'renumber' my ID field.

Awaiting suggestions ,
Wim.

 

[r937]

what is the datatype of the column? what is the current auto_increment value? are there any other tables with foreign keys referencing this column (whether or not you actually declared them as FOREIGN KEYs)?

r937.com | rudy.ca

 

[wimhek]

Hmm

No foreign keys, the database structure is quite simple.

CREATE TABLE `inclexcl` (
`ID` int(11) NOT NULL auto_increment,
`id_serv` int(3) default NULL,
`datum` date default NULL,
`incexctext` longblob,
PRIMARY KEY (`ID`));

Becaause I do a lot of deletions and a lot of insertions, the field ID can become to small. How kan I 'renumber' the ID field ?

 

[r937]

don't bother, unless you seriously expect more than a couple of billion rows

i think you will have other issues long before you ever get there



r937.com | rudy.ca

 

[Kozusnik]

Change the field to BIGINT(20). Then you go from 4 billion (4294967295 or 2^32 -1) numbers to 18 quintillion (18446744073709551615 or 2^64 -1).

The easiest way I know is to delete the ID field and recreate it. It will auto-number the field again starting with 1. If the ID field is being used to reference data from a relational perspective (other tables have that ID to reference this table), you can't change it. If you're not really using it...get rid of it.

ALTER TABLE table_name DROP ID;

OPTIMIZE TABLE table_name;

ALTER TABLE table_name ADD ID BIGINT( 20 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;

The OPTIMIZE isn't necessary, I just like a clean database.

Mark

 

[r937]

my point about the other issues was that 4 billion LONGBLOBs (the main column in that table) will require rethinking the whole storage question

r937.com | rudy.ca

 

[Kozusnik]

True, but he may only have 1,000 records left in the database after his deletes. I'm not sure what size his blobs are, but that's only 4 terabytes max...who doesn't have that to kick around these days.

Rudy is absolutely correct. Depending on your drive space and the size of your longblobs, you could easily run out of disk before running out of numbers.

Mark