Thimble Smith e-mailed me on Wed Mar 1 08:34:34 2000
(Re: "Re: is there any way to AUTO_INCREMENT fields with non-zero DEFAULTs?")
>
>On Tue, Feb 29, 2000 at 10:42:08PM -0500, Josh Hartmann wrote:
>>AUTO_INCREMENT assigns a value = (largest value in that column) + 1.
>
>In 3.23 this is no longer the case. MySQL knows what the most recent
>auto_increment ID for the table was, and will increment that. So even
>if you have deleted several of your highest-numbered rows, you will
>still get a fresh auto_increment ID on the next insert.
I remember a discussion on this some time ago, but I don't remember if
there was ever a resolution...
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?
(Often times, I'll create a table with an auto_increment field just
for the purpose of administration. ie. the data may be a list of
names and it is easier to specify a key from an auto_increment field
in an UPDATE rather than having to specify both the first & last
names to get the specific row I want. These keys aren't foreign keys
or used in any joins, so they can be reused.)
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
So, either this is a bug and should be fixed (since 32767 != -30 and
trying to insert another value causes an error), or the field should
automatically be declared UNSIGNED and the above would generate a error
when the table is CREATE()d (since -30 is signed).
Anybody have any thoughts or ideas??
--
Nem W Schlecht schlecht@stripped
NDSU Library http://www.nodak.edu/~nem/
"Perl did the magic. I just waved the wand."
