Hello.
On Mon 2003-03-17 at 18:08:08 -0600, [EMAIL PROTECTED] wrote:
> I have an table with a column
>
> number INT NOT NULL PRIMARY KEY
>
> This table contains many records and there are gaps in the number
> sequence. I would like to modify this column to use the AUTO INCREMENT
> feature but I need to preserve the present numbering sequence (which is
> monotonically increasing) including the holes. It is not clear from the
> manual or my MySQL book exactly how to do this.
>
> Is this possible? if so what is the correct ALTER TABLE command?
Yes. First, make a backup of your tables. Although I don't expect any
problems: Better safe than sorry.
AUTO_INCREMENT columns won't reuse gaps. If you add a new value, it
will be at least MAX(value)+1. Could be higher, if you deleted some
rows in-between.
The command is
ALTER TABLE your_table MODIFY number INT NOT NULL AUTO_INCREMENT PRIMARY KEY
Done.
Note that if your column wouldn't be NOT NULL already and indeed
contain some NULL values, I would expect them to be handled like a
NULL for the AUTO_INCREMENT in a normal INSERT, i.e. it would get
MAX(value)+1 (and so on for all other rows where number<=>NULL).
As I said, this is not an issue in your current case.
HTH,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
