add unqiue id field to existing table?

[elklabone]

Hello,

I have an existing mysql table with about 3,000 rows in it (table called "members"). I'm using a "username" field as the unique field, but have decided that I want to add an autoincrementing unique id as the primary key. This primary key will be used as a foreign key in other tables, so I figure an id number will take up less room (and thus be more efficient) than a username...

also, users change their username from time to time, and if the primary key of the "members" table is username, and username is a foreign key in another table, I'm hosed if they change their username.

Is there an easy way to add a new field "id" to this table, make it autoincrementing, and automatically fill this field with numbers, starting at 1?

Regards,

Mark

[siwis]

I don't think you can add an auto incrementing unique content field to a table after it has been created.

Here's my take on what I reckon you could do to "fix" it

RENAME TABLE members (dunno the specific command)
CREATE TABLE members (params) don't forget to add your new field
Query the data in the renamed table and drop it back into the new members table.

Should work, just might have a small amount of downtime :-)

Alternatively
CREATE members_new
Query and drop in from members
DROP TABLE members
RENAME members_new to members