0

I have this table:

CREATE TABLE `event_schedule_tag` (                                                                                                   
  `event_schedule_id` bigint(20) NOT NULL,                                                                                                                   
  `tag_id` bigint(20) NOT NULL,                                                                                                                              
  KEY `event_schedule_id` (`event_schedule_id`),                                                                                                             
  KEY `tag_id` (`tag_id`),                                                                                                                                   
  CONSTRAINT `event_schedule_tag_ibfk_1` FOREIGN KEY (`event_schedule_id`) REFERENCES `event_schedule` (`id`)                                                
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci

And I want to add primary key over the two columns. But when I execute

alter table event_schedule_tag add primary key(event_schedule_id, tag_id);

I get: ERROR 1062 (23000): Duplicate entry '1130915-260' for key 'PRIMARY'

and when I execute

alter table event_schedule_tag drop primary key;

I get: ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists

What is the way out?

EDIT: I got the error message wrong. I though it says "primary key already exists" while the meaning is: "there are duplicates in the table -> can't create primary key" which makes much more sense now. I deleted duplicates and created primary key with no problem. Thanks!

Jen
  • 1,206
  • 9
  • 30
  • 3
    Primary key did not get created due to the duplicate entry.So, there is no point trying to drop it. Remove the duplicates first. – Kapil Apr 14 '17 at 11:13
  • Refer http://stackoverflow.com/questions/27492732/mysql-duplicate-error-with-alter-ignore-table, this is answer to your question. – Anil Apr 14 '17 at 11:13
  • `ERROR 1062 (23000): Duplicate entry '1130915-260' for key 'PRIMARY'` Says schedule id 1130915 with combination of tag id 260 entry is more than one. so first you should handle this situation first then you able to create composite key of both. – Anurag Dadheech Apr 14 '17 at 11:14
  • @Kapil oh, now I see! I didn't get the erorr right as I though it says "There already is a primary key" – Jen Apr 14 '17 at 11:29
  • @JanKoutný I have updated my comments in answers.Please upvote or mark it as answer if it helped you. Thanks! – Kapil Apr 14 '17 at 11:40

2 Answers2

1

You can't add a constraint into the existing table due to duplicate data. Assuming you don't want to delete anything from existing table, another way would be to do it via following steps:

  1. Create another table with LIKE operator (it will have same structure as event_schedule_tag table)
  2. Add primary key constraint on that table
  3. Insert all unique rows from current table to new table
  4. Rename the tables or change your backend to use the new table.

SQL statements would look like this:

CREATE TABLE `event_schedule_tag_unique` LIKE `event_schedule_tag`;

ALTER TABLE event_schedule_tag_unique add primary key(event_schedule_id, tag_id);

INSERT INTO event_schedule_tag_unique (event_schedule_id, tag_id)
SELECT event_schedule_id, tag_id FROM event_schedule_tag
GROUP BY event_schedule_id, tag_id
HAVING COUNT(*) = 1;

RENAME TABLE event_schedule_tag TO event_schedule_tag_archive;

RENAME TABLE event_schedule_tag_unique TO event_schedule_tag;
Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
0

Primary key never got created due to the duplicate entry. So, there is no point trying to drop it. Primary key needs to have unique values.So,remove the duplicates first to create the primary key.

Kapil
  • 987
  • 5
  • 11