2

I have an existing table with lot of rows (around 10k rows) with two columns as primary keys as it is acting as middle table of many-to-many relation between two other table.

For new requirements, I need to assign add new column (say id) which must be primary key with auto increment values. I ran following queries:

ALTER TABLE  `momento_distribution` ADD  `id` INT( 11 ) NOT NULL FIRST;
ALTER TABLE  `momento_distribution` DROP PRIMARY KEY , ADD PRIMARY KEY (  `id` );

First query run successfully but second query generated following error:

1062 - Duplicate entry '0' for key 'PRIMARY'

Reason is obvious, new column id got 0 as default value and Primary key can't have duplicate values.

Now before I can run second query, I need to set incremental value for new column like 1,2,3...

In Oracle, I know, this can be done through rowid. MySQL also have its equivalent @rowid. Can someone please suggest a query to set @rowid as column value for column id?

Please Note: This had to be done through query as I can't change 10000 rows manually.

Kapil Sharma
  • 10,135
  • 8
  • 37
  • 66

2 Answers2

4

You need to set it to AUTO_INCREMENT at the same time, that will populate it;

ALTER TABLE momento_distribution 
  ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Demo here.

EDIT: If you have an existing primary key, you'll need to drop that at the same time;

ALTER TABLE momento_distribution 
  DROP PRIMARY KEY, 
  ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
  • Thanks @Joachim. +1 for SQL Fiddle demo (I knew JS Fiddle but not SQLFiddle). This gives me confidence. Unfortunately I left office for the day. Waiting eagerly to test that on me table. Thanks again. – Kapil Sharma Aug 29 '12 at 17:35
  • Sorry @Joachim but it will not work. I just added primary key to your table like **name VARCHAR(32) PRIMARY KEY** and it failed. In my case too, I already have existing Primary key. – Kapil Sharma Aug 29 '12 at 17:45
  • Edited with a statement that replaces the existing primary key. The SQLFiddle table was just an example table to show how the alter table worked, what you want is the statement above; `ALTER TABLE momento_distribution DROP PRIMARY KEY, ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;` – Joachim Isaksson Aug 29 '12 at 18:01
2

Same question asked by same user differently. Refer to that question.

MySQL 1062 - Duplicate entry '0' for key 'PRIMARY'

In short, 1. Remove existing FK 2. Remove existing PK 3. Run your first query as

ALTER TABLE  `momento_distribution` ADD  `id` INT( 11 ) PRIMARY KEY AUTO_INCREMENT NOT NULL FIRST;

which will also assign unique number without depending on @rowid 4. Add FK to earlier columns, if needed.

Community
  • 1
  • 1