You typically wouldn't want to renumber a key field, as that would break
any relations you have with other tables, unless you renumber all of
those as well. As MySQL doesn't expressly support foreign keys, and has
no idea about the relations between your tables, there is no way for
MySQL to renumber your table(s) for you. MySQL's behavior is, in fact,
the correct behavior. A key should _never_ be reused.
That said, it should be a trivial script for you to write in [your
favorite language] to renumber your tables yourself (while maintaining
foreign key integrity).
Finally, unless I'm mistaken, assuming the table you wish to renumber
has no relations to other tables, I'm pretty sure you can cause MySQL to
"renumber" your column for you by dropping, it, and then readding it.
I.e.
alter table island drop id_seq;
alter table island add id_seq int unsigned not null primary key
auto_increment;
Should have the net effect of renumbering the id field for you
sequentially.
Al
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]
> m] On Behalf Of Bryan Capitano
> Sent: Sunday, October 14, 2001 10:21 AM
> To: mysql
> Subject: Consequtive, Sequential auto-increment?
>
>
> When I delete items from a table where I've got an
> auto-incremented primary key/index, call it table_id, I'd
> like to keep this column consequtive and sequential. In other
> words, when I delete items from the middle, then the column
> is no longer "sequential" because there is a number missing.
> Is there a function to re-index the column?
>
>
> --
> CAPITANO WEB CONSULTING
> Bryan R. Capitano
> CELL: 541-760-6288
> HOME: 541-929-5060
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
