Hi to all!
Why is it that if I create a table with an autoincrement id and if I delete all the contents from the table the id of the first new record created has 1?
Is there a way to avoid this?
Thanks,
Nicolas
AutoIncrement
Moderator: General Moderators
Since the post disturbed me a bit, I just tested it again with a simple table
hmmm...my mysql keeps the highest used autoincrement value for a table even if all records are deleted and a OPTIMIZE TABLE has run.
I think this is supposed to be so.
at least MS Access and MSSql-Server do it the same way.
Code: Select all
CREATE TABLE `tablename` (
`dId` tinyint(3) unsigned NOT NULL auto_increment,
`iop` tinyint(3) unsigned default NULL,
UNIQUE KEY `dId` (`dId`)
) TYPE=MyISAM;I think this is supposed to be so.
at least MS Access and MSSql-Server do it the same way.
Last edited by volka on Wed Sep 04, 2002 3:17 am, edited 1 time in total.
- gite_ashish
- Forum Contributor
- Posts: 118
- Joined: Sat Aug 31, 2002 11:38 am
- Location: India
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
As previously stated if you use DELETE FROM <table> or TRUNCATE TABLE <table> then the auto-increment field will be reset.
However, if you delete each record individually the auto-increment will not be affected. This is a bit of a tedious solution if you have lots of records so another thing you could try is to make a note of the latest ID value and add one to that value and use an ALTER TABLE statement:
after you have deleted the records (obviously replacing 20 with whatever the value actually is). Then the first record inserted into the database will have the ID value you set in the statement above.
But if you're deleting all the records from the table, why do you need to do this?
Mac
However, if you delete each record individually the auto-increment will not be affected. This is a bit of a tedious solution if you have lots of records so another thing you could try is to make a note of the latest ID value and add one to that value and use an ALTER TABLE statement:
Code: Select all
ALTER TABLE your_table AUTO_INCREMENT = 20But if you're deleting all the records from the table, why do you need to do this?
Mac