Re: auto_increment not resettable

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello,

[email protected] wrote:
> in MySQL 4.0.16 (linux x86 rpms), auto_increment doesn't seem to be
> resettable, neither using "alter table auto_increment" nor by inserting
> an earlier key.
>
> create table x (id int auto_increment, primary key (id));
> insert into x values ();
> insert into x values ();
> insert into x values ();
> delete from x where id in (1,2);
> alter table x auto_increment = 1; #this doesn't work
> insert into x (id) values (1); #and this doesn't work either
> insert into x values();
> select * from x;
>
> this yields
> 1
> 3
> 4
> instead of
> 1
> 2
> 3
>
> thanks!

This is intended behavior!

It is impossible to reset auto_increment to values lower than existing max value
in column.

With your test case:

create table x (id int auto_increment, primary key (id));
insert into x values ();
insert into x values ();
insert into x values ();
delete from x where id in (1,2);

select * from x;
+----+
| id |
+----+
|  3 |
+----+

At this point there is existing value 3 thus allowed minimum for
auto_increment is 4 !

The reason of auto_increment is to provide mechanism which ensures that newly
inserted value will be always bigger than maximum values which *existed* in
table before.

This is in order to ensure auto_increment values are never reused as they are
usually used as foreign key values referencing to other tables. Using such
values breaks dependencies.

At the other hand inserting non-existing auto_increment values is possible if
specified explicitly as you did in your example:


insert into x (id) values (1);

select * from x;
+----+
| id |
+----+
|  1 |
|  3 |
+----+

As long as you don't break PRIMARY KEY constraints it is Ok to INSERT or UPDATE
 auto_increment column.

This way consistent behaviour is provided.

Best regards

- --
  Want to swim with the dolphins? (April 14-16, 2004)
  http://www.mysql.com/uc2004/

  For technical support contracts, visit https://order.mysql.com/?ref=msal
    __  ___     ___ ____  __
   /  |/  /_ __/ __/ __ \/ /    Mr. Alexander Keremidarski <[email protected]>
  / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Full-Time Developer
 /_/  /_/\_, /___/\___\_\___/   Sofia, Bulgaria
         <___/  www.mysql.com


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE//uQNeehWBZ4HcYkRAqVGAJ4yjSOg8M6vbd2MRv0ZQUMJKXX8HgCeLmEI
YURzqYdXBph7Yd+C2e9OnbM=
=k5El
-----END PGP SIGNATURE-----


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]