Home / Database Structure / Tables / Question No: 27

I am trying to add an AUTO_INCREMENT field to a table that already exists and I keep receiving errors when trying to do it. Can be pointed me in the right direction on how it can be done? I have access via phpMyAdmin.

ALTER TABLE [table] ADD [fieldname] [integer type] not null auto_increment key;
Example query:

ALTER TABLE buyers ADD buyer_no INT NOT NULL AUTO_INCREMENT KEY;

or if you want it to be the primary key:

ALTER TABLE [table] ADD [fieldname] [integer type] 
NOT NULL AUTO_INCREMENT PRIMARY KEY;

Example query:

ALTER TABLE buyers ADD buyer_no INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

Keep in mind that you can have only one AUTO_INCREMENT column and it must be part of a key.