didn't it work differently in previous versions?
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,
>
> josh@xxxxxxxx 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 <salle@xxxxxxxxx>
> / /|_/ / // /\ \/ /_/ / /__ 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/bugs?unsub=gcdmb-bugs@xxxxxxxxxxx
|
