CODEWITHSUNDEEP
mysqlsql
Jen
Jen

Reputation: 1338

Can't drop either add primary key

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!

Upvotes: 0

Views: 1634

Answers (2)

Kapil
Kapil

Reputation: 987

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.

Upvotes: 0

Darshan Mehta
Darshan Mehta

Reputation: 30839

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;

Upvotes: 1

Related Questions