Re: auto_increment not resettable

At 12:53 -0500 1/9/04, [email protected] wrote:
>didn't it work differently in previous versions?

No.  It has never been possible to reset the sequence counter lower
than the maximum value currently present in the table.  It is possible
to reuse values in gaps that occur below the maximum, but you can do
so only by inserting those values explicitly, not by generating auto-increment
values automatically.


>
>it's a sensible feature to allow you to "fill in the gaps" in the
>sequence. our real case involves some keys significantly greater than
>all of the other keys (those few keys start at 10000000).
>
>we used to be able to easily import records from a different installation of
>our db application. By simply configuring the other installation to start
>auto_incrementing in a different range, we effectively got a whole new
>key space, without using the LAST_INSERT_ID(expr) or multi-column
>primary key hacks.
>
>On Fri, Jan 09, 2004 at 07:25:34PM +0200, Alexander Keremidarski wrote:
>>  -----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]


-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/

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