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?
Run the following query in the mysql console:
Check for the line that looks something like
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:
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:Add the required column as follows:
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:
Now add the foreign keys. Change the Reference Table/column as you need:
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.
check if your field with the primary key is set to auto increment
You need to specify the primary key as auto-increment
With regards to comment below, how about:
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
Set your PRIMARY KEY as AUTO_INCREMENT.
this step work perfectly for me.. you can try it
hope it work for you too. good luck