Adding a new column with auto increment to an existing table

[wright67uk]

Hello,

 

Im trying to add a new column to an existing table with the name 'ID' with 'auto_increment',  im still learning about the ins and outs of mysql.

When I add the column;

 

ALTER TABLE `business` ADD `ID` VARCHAR( 30 ) NOT NULL AUTO_INCREMENT AFTER `Name` 

 

I get the error; #1063 - Incorrect column specifier for column 'ID'

 

Im basically trying to add a unique id number to each listing in my database, 1,2,3,4,5,6 etc.

 

+----+---------+

| id | name    |

+----+---------+

|  1 | dog    |

|  2 | cat    |

|  3 | penguin |

|  4 | lax    |

|  5 | whale  |

|  6 | ostrich |

+----+---------+

 

hence id being the new column im trying to create.

 

What am I doing wrong?

[Vel]

I'm not 100% with this, as I'm fairly new myself, but I think you can only use auto increment on integers. Try setting the column to an integer instead of variable character and try again.

 

ALTER TABLE `business` ADD `ID` INT AUTO_INCREMENT AFTER `Name` 

 

You also don't want it to be Not Null, as when the data is submitted it will be null, and then the database will automatically fill in the field.

[Muddy_Funster]

you can't auto inc a varchar field!

ALTER TABLE `business` ADD `ID` VARCHAR( 30 ) NOT NULL AUTO_INCREMENT AFTER `Name`

[floridaflatlander]

 

ALTER TABLE table-name ADD COLUMN id auto_increment MEDIUMINT(10) BEFORE name

 

You'll have to play with it, adding an auto_increment column to a table with existing data, hmm I don't know.

 

One time I wanted to add a not null column and I couldn't do it, I had to add the column, insert something into the column, then I renamed it not null. You may have to do something like that too.

[mikosiko]

.. other already told you that an Auto-Increment field MUST be a number and in addition:

 

- Must be a unique auto-increment in one table and it must be defined as a Key.

- BEFORE is no part of the syntaxes of ALTER TABLE

- If you want to insert the auto-increment field as the first one ... use FIRST (as in the example below)

 

try this:

ALTER TABLE `table-name` ADD COLUMN `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (`id`);

[wright67uk]

Thankyou ever so much for your help and advice.

 

The only problem I have here is that I already have a primary key, how can I add into the sql query that I would like to change the primary key to the newly created column?

[mikosiko]

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html  happy study