Re: InnoDB and auto_increment fields

[Paul DuBois]

At 11:26 -0500 10/25/02, Chris Boget wrote:
>sql, query (*sigh*, I hate this filter)
>
>I have an auto_increment key set up on my InnoDB table.
>Whenever I delete all the records, the number isn't reset.
>However, for my ISAM tables, whenever I delete all the
>records, the auto_increment number is reset.

That's how ISAM tables work.

>Is there a reason for this in InnoDB?

Yes: That's how InnoDB tables work.  (So do MyISAM tables.)

The handler for each table type handles AUTO_INCREMENT columns
slightly differently.  You're running across one of those differences.

>   Is there a way that I
>can reset the auto_increment number when all the records
>are deleted?

ALTER TABLE t AUTO_INCREMENT = 1;

>   I have a temporary table that has records
>added and deleted all the time.  I wouldn't want the record
>number to eventually reach the ceiling for the datatype of
>the field.
>
>Any insight would be greatly appreciated.
>
>Chris