From: Thimble Smith
Date: March 1 2000 10:15pm
Subject: Re: is there any way to AUTO_INCREMENT fields with non-zero DEFAULTs?
List-Archive: http://lists.mysql.com/mysql/29703
Message-Id: <20000301171511.R6645@threads.polyesthetic.msg>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

On Wed, Mar 01, 2000 at 03:06:41PM -0600, Nem W Schlecht wrote:
>There are two points about auto_increment fields I'd like to discuss:
>
>1) In many cases, I do want a "fresh" ID from an auto_increment field, but
>   sometimes (when I have smaller tables, say with a tinyint auto_increment
>   field) I'd like the old (3.22) functionality, since I'm dealing with a
>   small set of data and ids.  Is there the possiblity of adding something
>   like a "RECYCLE" flag to the field definition, allowing for the old
>   functionality?

I'm not sure what would be involved in doing this; basically I think
the old code was performing an actual MAX(id)+1 query, so doing that
in your application might not be so bad (but then you have to lock
tables and so forth...).

Once we have "RECYCLE", do we need to add "FIRST_AVAILABLE" too?  :)
I guess I'm just not sure how widely applicable this is, and whether
it should be part of MySQL or just part of your applications.  I think
that, had auto_increment not worked that way earlier, you would not
have gotten used to using it that way.  But...maybe it would be useful
and not add too much bulk.

>2) I'm wondering if any field with an auto_increment should be
>   automatically declared as an unsigned field?  Trying to use
>   negative numbers causes an error to occur:
>
>   CREATE TEMPORARY TABLE foo (
>   	ikey SMALLINT AUTO_INCREMENT PRIMARY KEY,
>	name CHAR(30)
>   ) AUTO_INCREMENT=-30
>   ;
>   INSERT INTO foo VALUES (NULL, "Nem");
>   INSERT INTO foo VALUES (NULL, "Monty");
>
>   You'll get:
>     ERROR 1062 at line 8: Duplicate entry '32767' for key 1

I think that looks like a bug.  :)  I'd like to handle it in the
same way that, say, trying to define a CHAR(N) AUTO_INCREMENT
field is handled:

  mysql> create table bogus (id char(2) not null auto_increment primary key);
  ERROR 1063: Incorrect column specifier for column 'id'

Tim
-- 
Tim Smith   < tim@stripped >  :MySQL Development Team:  Boone, NC  USA.