AutoIncrement

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
xLechugasx
Forum Newbie
Posts: 14
Joined: Wed Aug 28, 2002 9:56 am

AutoIncrement

Post 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
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post by Takuma »

That's what increment suppose to do...
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
Last edited by volka on Wed Sep 04, 2002 3:17 am, edited 1 time in total.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

How are you removing your records

delete from ....

or

TRUNCATE
User avatar
gite_ashish
Forum Contributor
Posts: 118
Joined: Sat Aug 31, 2002 11:38 am
Location: India

Post 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 !
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
Post Reply