* Rhino:
You'll need three alter statements:
- one to add the new column
- one to get rid of the old primary key
- one to set the new column as the primary key
In addition to adding the new column, there will be neccessary to populate
the column with unique values. A primary key must allways
From: bruce
table foo
username varchar(50), primary
what i want...
userid int(), primary
username varchar(50)
with userid listed before username!!
can someone tell me what the commands are that i need to enter to get
this!!
http://dev.mysql.com/doc/mysql/en/alter-table.html
ALTER TABLE
bruce wrote:
hi..
i have a table that i want to add a column to, and define the columm to be
the primary key. however, i already have a column in the table that's used
as the primary. can someone tell me the approach/commands i should use to
get my results?
table foo
username varchar(50),
From: Philippe Poelvoorde
[Note,
ALTER TABLE foo ADD userid integer unsigned NOT NULL auto_increment
BEFORE username, ADD PRIMARY KEY(userid);
does not seems to work, possibly a bug ?
]
No, you forgot the DROP PRIMARY KEY ;-)
ALTER TABLE `foo` DROP PRIMARY KEY, ADD `userid` INTEGER UNSIGNED
Jigal van Hemert wrote:
From: Philippe Poelvoorde
[Note,
ALTER TABLE foo ADD userid integer unsigned NOT NULL auto_increment
BEFORE username, ADD PRIMARY KEY(userid);
does not seems to work, possibly a bug ?
]
No, you forgot the DROP PRIMARY KEY ;-)
well not really, that's the statement
You'll need three alter statements:
- one to add the new column
- one to get rid of the old primary key
- one to set the new column as the primary key
Something like this, i.e. I've left out some details of the syntax:
ALTER TABLE FOO ADD USERID CHAR(8);
ALTER TABLE FOO DROP PRIMARY KEY;
ALTER
From: Philippe Poelvoorde
No, you forgot the DROP PRIMARY KEY ;-)
well not really, that's the statement before ! ;-)
Sorry, I misread your mail.
So is that normal that I can't specify BEFORE username ?
Yes, because MySQL only supports AFTER `column_name` ;-P
