creating a key in a table

[pelegk2]

i want to chamge a column that is defined as :
order_id int(8) unsinged notnull
and tried to do :
alter TABLE tblprodoreder add PRIMARY KEY (order_id)
but i get "duplicate entry '0' for key 1"
what to do?
and how do i define it as autoincementae too?
thnaks i nadvance
peleg

[Drakla]

You must have repeated data in that column. Primary Keys have the same restrictions as unique keys.

Once the duplicate data is gone you might want to drop any primary key if they're already defined.

alter table tblprodoreder drop primary key;

alter table tblprodoreder add primary key (int_order);

alter table tblprodoreder modify int_order int unsigned not null auto_increment;

[pelegk2]

this line

alter table tblprodoreder add primary key (order_id); 

i get the same error!
maybe beacuse i have a default value 0 for all the ccells in that col?!?!?!!

[Drakla]

Well you could do

alter table tblprodoreder drop column int_order;

alter table tblprodoreder add column int_order int unsigned auto_increment PRIMARY KEY;

If the values already in that column don't matter - you can add FIRST at the end if you want the column at the start, as I prefer with primary keys.

[pelegk2]

it worked!
all the problem started when i tried to do it with the mysql-front gui 🙁