Is it possible that a primary key is not autoincrement?

Question

I am trying to insert an element in a table where there are already 3 rows.

Its a table called usuarios=[id (primary, autoincrement), fid, first_name, last_name....]

So there are already 3 rows with id's: 0,1,2

And when I am trying to execute this query (note I am not setting value for id attribute)

INSERT INTO usuarios (fid,email,pass,first_name,last_name,avatar,bday,fecha,id_loc,id_loc_from) 
       VALUES       (-1,'[email protected]','72253f579e7dc003da754dad4bd403a6','','','',NOW(),NOW(),'','')

I get this mysql error:

Duplicate entry '0' for key 1

extra: I don't know how this 3 items where inserted (if via interface, by console query, ..)

So question is, how can I make sure that the Primary Keyis autoincrement, and if not; how to set it? (will that solve the problem?)

Answer 1

You should be able to set it as auto-increment using this statement if you have rights to alter the table:

ALTER TABLE usuarios  modify id INT(11) NOT NULL AUTO_INCREMENT;

Although I've seen reports of bugs that recommend instead dropping the colulmn and recreating it, if the above doesn't work for some reason. the syntax recommended in those posts is:

ALTER TABLE usuarios  
         DROP COLUMN id;

ALTER TABLE usuarios  
         ADD COLUMN idINT(11) NOT NULL AUTO_INCREMENT FIRST;

Warning: Do this in a test database first. You'd want to be careful doing this if the table is using this as a foreign key. It could fail at best, or break all the relationships at worst.

There's tons of info at the MySql reference manual.