How to add a primary key to a MySQL table?
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.