53

I have the following table in MySQL version 5.5.24

DROP TABLE IF EXISTS `momento_distribution`;

CREATE TABLE IF NOT EXISTS `momento_distribution`
  (
     `momento_id`       INT(11) NOT NULL,
     `momento_idmember` INT(11) NOT NULL,
     `created_at`       DATETIME DEFAULT NULL,
     `updated_at`       DATETIME DEFAULT NULL,
     `unread`           TINYINT(1) DEFAULT '1',
     `accepted`         VARCHAR(10) NOT NULL DEFAULT 'pending',
     `ext_member`       VARCHAR(255) DEFAULT NULL,
     PRIMARY KEY (`momento_id`, `momento_idmember`),
     KEY `momento_distribution_FI_2` (`momento_idmember`),
     KEY `accepted` (`accepted`, `ext_member`)
  )
ENGINE=InnoDB
DEFAULT CHARSET=latin1;

It has lots of data with many-to-one relations with two other tables with ondelete=restrict and onupdate=restrict.

Now, I need to change the structure and introduce separate primary key in the table, while still keeping existing relations and data. For that, I executed the following query:

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

Unfortunately, my second query failed with the following error:

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

Can someone please point out the issue? I guess that the issue is the existing relation, but I don't want to lose the existing relation or data, that has several thousand rows. Is there any way to do this without losing data?

EDIT: By viewing data, I got that the newly created column has the value '0' in it. Probably this is not allowing to change the Primary Key due to duplicate records (in new Primary Key)

I have more than 8,000 rows, so I can't change it manually. Is there any way to assign rowid to a new Primary Key?

ReinstateMonica3167040
  • 732
  • 9
  • 27
Kapil Sharma
  • 10,135
  • 8
  • 37
  • 66
  • 1
    Your edit explaining that you had a default value of 0 helped me out. Seems like in my case my tables were created (without an id, taking default 0) before the auto_increment was added – Wouter Vanherck Apr 17 '19 at 06:45
  • I used to have the same error. In my case, the problem was that I was managing a non-empty table. I solved by do TRUNCATE TABLE name_table and then I was able to add the Primary Key – franz1 Sep 09 '19 at 09:14

9 Answers9

68

You need to specify the primary key as auto-increment 

CREATE TABLE `momento_distribution`
  (
     `momento_id`       INT(11) NOT NULL AUTO_INCREMENT,
     `momento_idmember` INT(11) NOT NULL,
     `created_at`       DATETIME DEFAULT NULL,
     `updated_at`       DATETIME DEFAULT NULL,
     `unread`           TINYINT(1) DEFAULT '1',
     `accepted`         VARCHAR(10) NOT NULL DEFAULT 'pending',
     `ext_member`       VARCHAR(255) DEFAULT NULL,
     PRIMARY KEY (`momento_id`, `momento_idmember`),
     KEY `momento_distribution_FI_2` (`momento_idmember`),
     KEY `accepted` (`accepted`, `ext_member`)
  )
ENGINE=InnoDB
DEFAULT CHARSET=latin1$$

With regards to comment below, how about:

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

A PRIMARY KEY is a unique index, so if it contains duplicates, you cannot assign the column to be unique index, so you may need to create a new column altogether

Adi
  • 5,089
  • 6
  • 33
  • 47
Miroslav
  • 1,960
  • 1
  • 13
  • 26
  • Thanks Merca, But I gave above query just as reference to table structure. I'm not creating new table. I already have table with lot of rows so only alter query is the option. Currently momento_id & momento_idmember are foreign keys and composite key. I need to remove them as primary key and introduce new column as primary key. – Kapil Sharma Aug 29 '12 at 14:24
  • Sorry but edit will also not work. `momento_id` is already primary key along with `momento_idmember`. I need new column as primary key. Check my post, I already added column successfully with query **ALTER TABLE `momento_distribution` ADD `id` INT( 11 ) NOT NULL FIRST;**. Now need to make it as primary. – Kapil Sharma Aug 29 '12 at 14:39
  • A PRIMARY KEY is a unique index, so if it contains duplicates, you cannot assign the column to be unique index, so you may need to create a new column altogether – Miroslav Aug 29 '12 at 14:41
  • Yes that's the reason. Please check edit in question at the end. There is @rowid in MySQL. Trying to assign it as value. Is it possible? – Kapil Sharma Aug 29 '12 at 14:43
  • Thanks. Was struggling with that. It really helped. – Koushik Das May 15 '16 at 06:08
  • I exported table structure in PHPMyAdmin and autoincrement was somehow left out on the primary key on import – Peter Chaula Aug 14 '17 at 11:17
  • I am having this issue on a table I KNOW TO BE EMPTY. I don't want to sue auto-increment because I am manually setting the key values – ThisGuyCantEven Sep 16 '19 at 20:36
25

Run the following query in the mysql console:

SHOW CREATE TABLE momento_distribution

Check for the line that looks something like

CONSTRAINT `momento_distribution_FK_1` FOREIGN KEY (`momento_id`) REFERENCES `momento` (`id`)

It may be different, I just put a guess as to what it could be. If you have a foreign key on both 'momento_id' & 'momento_idmember', you will get two foreign key names. The next step is to delete the foreign keys. Run the following queries:

ALTER TABLE momento_distribution DROP FOREIGN KEY momento_distribution_FK_1
ALTER TABLE momento_distribution DROP FOREIGN KEY momento_distribution_FK_2

Be sure to change the foreign key name to what you got from the CREATE TABLE query. Now you don't have any foreign keys so you can easily remove the primary key. Try the following:

ALTER TABLE  `momento_distribution` DROP PRIMARY KEY

Add the required column as follows:

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

This query also adds numbers so you won't need to depend on @rowid. Now you need to add the foreign key back to the earlier columns. For that, first make these indexes:

ALTER TABLE  `momento_distribution` ADD INDEX (  `momento_id` )
ALTER TABLE  `momento_distribution` ADD INDEX (  `momento_idmember` )

Now add the foreign keys. Change the Reference Table/column as you need:

ALTER TABLE  `momento_distribution` ADD FOREIGN KEY ( `momento_id`) REFERENCES  `momento` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT 
ALTER TABLE  `momento_distribution` ADD FOREIGN KEY ( `momento_idmember`) REFERENCES  `member` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT

Hope that helps. If you get any errors, please edit the question with the structure of the reference tables & the error code(s) that you are getting.

Félix Adriyel Gagnon-Grenier
  • 8,422
  • 10
  • 52
  • 65
14

check if your field with the primary key is set to auto increment

RickPat
  • 487
  • 6
  • 6
9

this step work perfectly for me.. you can try it

  1. Add indexing
  2. Add Autoincrement
  3. Add Primary Key

hope it work for you too. good luck

Bill Tanthowi Jauhari
  • 91
  • 1
  • 2
6

Set your PRIMARY KEY as AUTO_INCREMENT.

Faizan Khalid
  • 125
  • 2
  • 9
1

For me, i forget to add AUTO_INCREMENT to my primary field and inserted data without id.

Rohit Dubey
  • 1,234
  • 15
  • 15
0

Set AUTO_INCREMENT to PRIMARY KEY

WapShivam
  • 964
  • 12
  • 20
0

I faced this error, Used typeorm migrations Api, This error as stated above is as a result of id not being auto incremented, so if you are using typeorm's Api migration to write the migration, when you are writing your id column, use this to set id column. { name: "id", type: "int auto_increment", isPrimary: true }

Ukpa Uchechi
  • 604
  • 1
  • 6
  • 10
-1

Drop the Table and Create again. This worked for me.

Jitendra
  • 19
  • 1