asked on

Can't alter a table

Hi,
I added a field called "id" to a table,
now I have the following error :

MySQL Error Number 1063
Incorrect Column specifier for column 'id'

I added a field id NUMERIC(5) as primary key

what's wrong ?
Also, what type is suggested for a id as primary key ?
Thank u .

racek

maybe you forgotten NOT NULL, beacouse PRIMARY KEY must by NOT NULL... like:
CREATE TABLE xxx (id int not null primary key, nex_col int, next_col2 varchar(225))

racek

... and if you want to add primary key through ALTER TABLE, you must use auto_increment

ALTER TABLE mytable ADD column id (int not null auto_increment primary key)

matthew016

ASKER

not null is checked ... I'm using mysql administrator

racek

if your table contents data, you cannot add primary key without data... auto_increment create unique values.
can you sen your ALTER TABLE statement?

matthew016

ASKER

There is no data in the table,

ALTER TABLE `le_hall`.`utilisateurs` ADD COLUMN `id` NUMERIC(5) NOT NULL AUTO_INCREMENT AFTER `description`,
DROP PRIMARY KEY,
ADD PRIMARY KEY(`id`);

racek

alter table ax drop column old_pk_column;
alter table `le_hall`.`utilisateurs` add column (id NUMERIC(5) not null auto_increment primary key AFTER `description`)

matthew016

ASKER

alter table ax ?
what is ax ?

racek

sorry
alter table `le_hall`.`utilisateurs` drop column old_pk_column;
alter table `le_hall`.`utilisateurs` add column (id NUMERIC(5) not null auto_increment primary key AFTER `description`)

matthew016

ASKER

I have no idea on how to execute sql code in mysql administrator,
but I removed the PK then saved,
then I try to add the new PK id and I have still the error

ASKER CERTIFIED SOLUTION

racek

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

matthew016

ASKER

well with INTEGER type it works,
but i always used NUMERIC(5)
Whats wrong with it ?

racek

I¨v got it - one more step

alter table `le_hall`.`utilisateurs` drop column old_pk_column;
alter table `le_hall`.`utilisateurs` add column (id int not null auto_increment primary key)

alter table `le_hall`.`utilisateurs` MODIFY id NUM(5)?

racek

sorry ; not ? at end

alter table `le_hall`.`utilisateurs` MODIFY id NUM(5);

matthew016

ASKER

hmmm weird, i'll leave it as int

thanks.