Page 1 of 1
AutoIncrement
Posted: Tue Sep 03, 2002 12:01 pm
by xLechugasx
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
Posted: Wed Sep 04, 2002 1:37 am
by Takuma
That's what increment suppose to do...
Posted: Wed Sep 04, 2002 3:14 am
by volka
Since the post disturbed me a bit, I just tested it again with a simple table
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;
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.
Posted: Wed Sep 04, 2002 3:16 am
by mikeq
How are you removing your records
delete from ....
or
TRUNCATE
Posted: Wed Sep 04, 2002 6:02 am
by gite_ashish
i tried both the options:
1. DELETE FROM <table>;
2. TRUNCATE TABLE <table>;
After that the tables auto_increment starts from 1.
I think this is what it is suppose to do.
i m using 3.23.36... quite old now !
Posted: Wed Sep 04, 2002 6:45 am
by twigletmac
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:
Code: Select all
ALTER TABLE your_table AUTO_INCREMENT = 20
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