How to add a primary key to a MySQL table?

Question

After adding the column, you can always add the primary key:

ALTER TABLE goods ADD PRIMARY KEY(id)

As to why your script wasn't working, you need to specify PRIMARY KEY, not just the word PRIMARY:

alter table goods add column `id` int(10) unsigned primary KEY AUTO_INCREMENT;

Existing Column

If you want to add a primary key constraint to an existing column all of the previously listed syntax will fail.

To add a primary key constraint to an existing column use the form:

ALTER TABLE `goods`
MODIFY COLUMN `id` INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT;

If your table is quite big better not use the statement:

alter table goods add column `id` int(10) unsigned primary KEY AUTO_INCREMENT;

because it makes a copy of all data in a temporary table, alter table and then copies it back. Better do it manually. Rename your table:

rename table goods to goods_old;

create new table with primary key and all necessary indexes:

create table goods (
    id  int(10) unsigned not null AUTO_INCREMENT
    ... other columns ...
    primary key (id)
);

move all data from the old table into new, disabling keys and indexes to speed up copying:

-- USE THIS FOR MyISAM TABLES:

SET UNIQUE_CHECKS=0;
    ALTER TABLE goods DISABLE KEYS;
        INSERT INTO goods (... your column names ...) SELECT ... your column names FROM goods_old; 
    ALTER TABLE goods ENABLE KEYS;
SET UNIQUE_CHECKS=1;

OR

-- USE THIS FOR InnoDB TABLES:

SET AUTOCOMMIT = 0; SET UNIQUE_CHECKS=0; SET FOREIGN_KEY_CHECKS=0;
    INSERT INTO goods (... your column names ...) SELECT ... your column names FROM goods_old; 
SET FOREIGN_KEY_CHECKS=1; SET UNIQUE_CHECKS=1; COMMIT; SET AUTOCOMMIT = 1;

It takes 2 000 seconds to add PK to a table with ~200 mln rows.

Not sure if this matters to anyone else, but I prefer the id for the table to be the first column in the database. The syntax for that is:

ALTER TABLE your_db.your_table ADD COLUMN `id` int(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT FIRST;

Which is just a slight improvement over the first answer. If you wanted it to be in a different position, then

ALTER TABLE unique_address ADD COLUMN `id` int(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT AFTER some_other_column;

HTH, -ft

Use this query,

alter table `table_name` add primary key(`column_name`);