How to add a primary key to a 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 tip

User · Answer

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;

User · Answer

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

User · Answer

Try this   alter table goods add column  id  int 10  unsigned primary key auto increment

User · Answer

Use this query   alter table  table name  add primary key  column name

User · Answer

For me  none of suggestions worked giving me the errors of syntax  so I just gave a try using phpmyadmin version 4 9 2    10 4 10-MariaDB  and added id column with auto-increment primary key  Id column was nicely added from the first element   Query output was   ALTER TABLE table name ADD id INT NOT NULL AUTO INCREMENT FIRST  ADD PRIMARY KEY  id

User · Answer

This code work in my mysql db     ALTER TABLE  goods  ADD COLUMN  id  INT NOT NULL AUTO INCREMENT  ADD PRIMARY KEY   id

User · Answer

Remove  quotes to work properly     alter table goods add column id int 10  unsigned primary KEY AUTO INCREMENT

User · Answer

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

User · Answer

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

User · Answer

ALTER TABLE GOODS MODIFY ID INT 10  NOT NULL PRIMARY KEY

[mysql] How to add a primary key to a MySQL table?

Existing Column

Examples related to mysql