Now I want to add a primary key id column but it throws the error:
#1062 - Duplicate entry '0' for key 'PRIMARY'
I already tried this: Add primary key to existing table
Asked
Active
Viewed 1.3k times
3
ReinstateMonica3167040
- 732
- 9
- 27
Sugumar Venkatesan
- 4,019
- 8
- 46
- 77
-
As you can see you have more than one entry with id 0 in your table. A Primary key must be unique. So you have to make the id's unique before you can add the Primary key – Jens May 10 '17 at 12:43
-
@Jens do I need to manually change it? – Sugumar Venkatesan May 10 '17 at 12:44
-
Yes i think so.. – Jens May 10 '17 at 12:52
2 Answers
3
When you creates a new column, a default value is asigned (in your case will be 0), so you need to specify wich values will it have (besides you can tell it to the column to be autoincremental, and it will do the work for you for the new entries of rows). You have to change all the values to be differents between them, the id key MUST be unique
To change all your ids, in mysql you can do:
SET @new_id=0;
UPDATE your_table
SET id = @new_id := @new_id + 1
where id = 0
developer_hatch
- 15,898
- 3
- 42
- 75
-
1
-
I add you a way to update your table in mysql :), now you know how to do it – developer_hatch May 10 '17 at 12:55
-
No problem pal, I have always loved to help, your question will help a lot! – developer_hatch May 10 '17 at 12:56
0
First please change ids of the table with below query
SET @counter = 1
UPDATE #tablename
SET @counter = id = @counter + 1
And then Apply primary key.
Poorna Rao
- 335
- 4
- 20
-
It shows the following error #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 'DECLARE counter int SET counter = 0 UPDATE images_proposals SET counter = ' at line 1 – Sugumar Venkatesan May 10 '17 at 12:50
-