[dogwood]

Hi

I am trying to change the name of a MySQL table column that I created. I incorrectly spelled the name and now need to change it from primeryid to primaryid.

Here is the table:

+-----------+------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------------------+----------------+
| primeryid | int(5) | | PRI | NULL | auto_increment |
| email | char(100) | | | | |
| username | char(100) | | | | |
| mimetype | char(1) | | | | |
| password | char(16) | | | | |
| lastlogin | datetime | | | 0000-00-00 00:00:00 | |
| admin | tinyint(4) | | | 0 | |
+-----------+------------+------+-----+---------------------+----------------+

I know that you use the ALTER TABLE Syntax I however get an error when I use the following.

mysql> alter table account
-> change primeryid primaryid int(5) auto_increment null primary key;

The error is ERROR 1068: Multiple primary key defined. All help welcome.

[smeger]

I use phpMyAdmin for stuff like this. The error that your getting is saying that a different column is defined as the primary (for indexing), and you can only have a single primary defined.

[dogwood]

I cannot install myPHPadmin on my ISP server, so I need to know the syntax for SQL via the MySQL client in the terminal.

[Vasudevelopa]

I'm not sure if this will work, try doing exactly what you did only leave primary key out. then do it again with primary key.

-> change primeryid primaryid int(5) auto_increment null;
-> change primaryid primaryid int(5) auto_increment null primary key;

[el_humpo]

Just drop the column and add it again.

alter table account drop primeryid;
alter table account add primaryid int(5) not null primary key auto_increment first;

The first keyword at the end of the SQL statement will put the primaryid as the first column in the table - otherwise the primaryid will be the last column.

[MadBrowser]

Have you tried it without the primary key reference:

alter table account change primeryid primaryid int(5) auto_increment null;

?? That should work, and then you could add the primary key later, if it was dropped, which I am not sure it would be.

[dogwood]

This I found to be finally the solution:-

mysql> alter table account
-> change primeryid primaryid int(5);

It only seems to work if you include the the_old_name the_new_name and the int(5) in the statement, and exclude the rest.

This keeps the data in the column intact and changes the column name only. If I was to drop the column so I could add it later I would also lose all the data in the column.

[howardm4]

off topic question.....

I have a column that is defined as a varchar(20) and now needs to be
wider (say, 30 or so). Can I alter table similar to this w/o dropping the
column (and losing the data) ????

[Vasudevelopa]

ALTER TABLE tablename MODIFY fieldname varchar(30);

Use MODIFY to change the definition of a field or CHANGE to change definition and name of a field.