How to add primary key to MySQL table?

Question

This is what I tried, but it fails:

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

Does anyone have a clue?

Answer 1

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

ALTER TABLE goods ADD PRIMARY KEY(id)

As for why your script didn't work, you need to specify PRIMARY KEY , not just the word PRIMARY :

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

Answer 2

If your table is quite large, it is best not to use the statement:

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

because it copies all the data into a temporary table, modifies the table and then copies it back. It’s better to do it manually. Rename your table:

 rename table goods to goods_old; 

create a new table with the primary key and all the necessary indexes:

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

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

- USE IT 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 2000 seconds to add PK to a table with ~ 200 million rows.

Answer 3

Existing column

If you want to add a primary key constraint to an existing column, all of the syntax above 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; 

Answer 4

Not sure if this applies to anyone else, but I prefer an identifier table for the first column in the database. The syntax for this is:

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

This is only a slight improvement over the first answer. If you want him to be in a different position,

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

NTN, -ft

Answer 5

This code works in my mysql db:

 ALTER TABLE `goods` ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (`id`); 

Answer 6

 ALTER TABLE GOODS MODIFY ID INT(10) NOT NULL PRIMARY KEY; 

Answer 7

Use this query,

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

Answer 8

Try it,

 alter table goods add column `id` int(10) unsigned primary key auto_increment 

Answer 9

Remove quotes to work properly ...

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