MySQL add column add column with primary key syntax error

Question

I am trying to add a column to one of my database tables, but there is a syntax error and I cannot find the problem ...

My current database table looks like this:

component + tag_id + item_id ------------|----------|----------- com_content | 23 | 2642 com_content | 26 | 3481 com_content | 35 | 1868 com_content | 85 | 5827 com_content | 89 | 7882 

I want it to look like this where "id" is auto-increment and all columns are part of the primary key

  id + component + tag_id + item_id -----|--------------|----------|----------- 1 | com_content | 23 | 2642 2 | com_content | 26 | 3481 3 | com_content | 35 | 1868 4 | com_content | 85 | 5827 5 | com_content | 89 | 7882 

This is my request:

 DROP PRIMARY KEY ALTER TABLE gitags_items ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST PRIMARY KEY (id,component,tag_id,item_id) 

However, I get this error message:

 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY KEY ALTER TABLE gitags_items ADD COLUMN id INT NOT NULL AUTO_INC' at line 1 

Any hints / pointers would be much appreciated

Answer 1

the "ALTER TABLE" bit should be the first, and then each part should be separated by a comma:

 ALTER TABLE gitags_items DROP PRIMARY KEY, ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id,component,tag_id,item_id); 

but I'm not sure that you can delete and create the primary key in the same state.

Answer 2

This works well, there is a problem with commas, there is no need to discard the primary key, since you are going to install it yourself

 ALTER TABLE gitags_items ADD id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY(id,component,tag_id,item_id); 

Answer 3

First you must make the primary key of the "id" column before setting auto-increment.

Answer 4

Try adding the following code to my.cnf

 sql-mode="NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO" 

then restart mysql

 # service mysqld restart