update tables

slimjim

Can anyone help me figure out how to alter a table where I would drop primarykey and add a new primarykey

The new Primarykey would also be a new colum that is auto_increment..

But also add the auto_increment #'s to all rows in that table ?
Now I got this far, but not sure how to add auto_increment #'s to all rows in table...

When I did just the below, all it did was leave the id colum empty for all rows.

ALTER TABLE `members` ADD `id` varchar(40) NOT NULL auto_increment BEFORE email;

saraadmin

Two things:

1) Has your new primary key got the same name as the old one? It can't have.

2) You can't have an auto_increment-ing varchar field. Use a numerical field instead. Also you can only have one auto_increment field per table and it must be a key field.

Do it in three steps:

1) add a new column for the new key field.

2) use a PHP for loop to populate the new column with the values from the old key field.

3) remove the old key field and designate the new one as the key field.

slimjim

ok i figured out how to add and drop PRIMARY but Now, how can I ALTER a table so that when it adds a new colum, it is At Beginning of Table, instead of after a colum.

I figured out how to add to a table using AFTER, but how to get to add to a table so that the newly added colum can be at Beginning of Table..

slimjim

Does anyone know how to ALTER a table so that when adding a colum, it goes before all other colums, Instead of using AFTER a colum ?

Or is it even possible, cause I cant find any info on that..

saraadmin

This is a bit of a MySQL question really, but...

ALTER TABLE c ADD COLUMN a FIRST;

If 'b' was the first column in the table, then, after the ALTER statement is executed, 'a' will be the new first column.

(I think there is/was a 'BEFORE' keyword in some SQLs...but there isn't in recent MySQL versions).