| Re: deleted primary key can't be reused? [message #181917 is a reply to message #181916] |
Sun, 23 June 2013 11:05   |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma:
|
Senior Member |
|
|
On 23/06/13 10:05, Gordon Burditt wrote:
>> Just reset the auto increment to 1 and it should jump to the next available empty number, if your first untaken id is 98, then the next insert will take that position.
>>
>> ALTER TABLE `table_name` AUTO_INCREMENT =1;
> Funny, that statement, followed by SHOW CREATE `table_name`,
> seems to do nothing successfully. On both MyISAM and InnoDB tables,
> MySQL 5.5.31, the auto_increment value doesn't change. I can change
> the value to a value max(`column_name`)+1 or higher, but not back to 1.
>
> I still think it used to work at some point. What I'm not sure about
> is the part about "jump to the next available empty number", but now
> it won't let me set up a test for that.
this works
mysql_query("delete from orders");
mysql_query("alter table orders auto_increment=1");
I used it to reset a table to empty., but you cant reset a table with a
mandatory index applied that isn't empty.,
You could create a temp table, copy the data to it, empty and reset the
original and then copy the data back...
I ran into this when trying to merge data from portable devices to the
main database. Read the moving data on te portable device and update the
main database with it, then clear all the tables in the portable
database and copy the full tables from the main one onto it, that it
needed to use.
I had to do that otherwise the data copy couldn't take place due to
overlapping IDs
I used to make a file in CSV formatfrom the main database including IDs
an LOAD FILE into the empty tables. IF that found an ID already
existing, it bombed. Even though the database had been deleted.
Perhaps someone in the SQL group knows the chapter and verse.,
--
Ineptocracy
(in-ep-toc’-ra-cy) – a system of government where the least capable to lead are elected by the least capable of producing, and where the members of society least likely to sustain themselves or succeed, are rewarded with goods and services paid for by the confiscated wealth of a diminishing number of producers.
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]