Re: converting an existing column to auto increment
Thanks for the speedy reply. I will try it out on some test tables. Liz On Mon, 17 Mar 2003, Paul DuBois wrote: At 18:08 -0600 3/17/03, Liz Buckley-Geer wrote: I have an table with a column numberINT 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? Interesting question. My tests suggest that, yes, you can do this. But make sure you backup the table first. The statement I used was ALTER TABLE tbl_name MODIFY number INT NOT NULL AUTO_INCREMENT; Thanks Liz -- - 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
converting an existing column to auto increment
I have an table with a column numberINT 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? Thanks Liz - 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
Re: converting an existing column to auto increment
Hello. On Mon 2003-03-17 at 18:08:08 -0600, [EMAIL PROTECTED] wrote: I have an table with a column numberINT 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
Re: converting an existing column to auto increment
At 18:08 -0600 3/17/03, Liz Buckley-Geer wrote: I have an table with a column numberINT 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? Interesting question. My tests suggest that, yes, you can do this. But make sure you backup the table first. The statement I used was ALTER TABLE tbl_name MODIFY number INT NOT NULL AUTO_INCREMENT; Thanks Liz -- Paul DuBois http://www.kitebird.com/ sql, query - 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
