CODEWITHSUNDEEP
mysqlduplicatesprimary-keymultiple-columns
jurgen
jurgen

Reputation: 325

Delete single column from primary key in mysql

I have a table with a primary key that consists of 5 columns. I found out that one of these columns should not be in the primary key because it is creating duplicates. I'm trying and failing at removing this column (both from the primary key and just entirely from the table).

Initially I just used MySQL Workbench to unselect the column as PK and just clicked Apply. The workbench generates this SQL:

ALTER TABLE `mydb`.`mytable` 
DROP PRIMARY KEY,
ADD PRIMARY KEY (`column1`, `column2`, `column3`, `column4`);

This causes the following error:

ERROR 1062: Duplicate entry '624634475-17-2-19000' for key 'PRIMARY'

I'm guessing that the database doesn't like the existence of duplicate entries. So I did some searching on how to delete duplicates and found this:

ALTER IGNORE TABLE mytable ADD UNIQUE (column1, column2, column3, column4)

This query also fails with an error:

Error Code: 1062. Duplicate entry '624634475-17-2-19000' for key 'column1'

I don't care which duplicate survives, I just want my primary key to be correct and to have some data left over at the end.

Upvotes: 0

Views: 916

Answers (2)

JimmyB
JimmyB

Reputation: 12620

I don't care which duplicate survives, I just want my primary key to be correct

If your new PK consists of columns 1 through 4, and columns 1 through 5 are unique at the moment, you can can use

SELECT column1, column2, column3, column4, MIN(column5)
FROM mytable
GROUP BY column1, column2, column3, column4

to get exactly one record for each unique combination of columns 1...4.

Then you can go ahead and delete all records from mytable where the (old) PK does not match the values selected above.

After that, columns 1...4 will be unique and you can re-create your PK constraint.

Upvotes: 0

jurgen
jurgen

Reputation: 325

I found a solution. You can delete records from a table by comparing the records to each other in this way:

DELETE t FROM table t, table u WHERE t.column2 < u.column2 AND t.column1 = u.column1 AND t.column3 = u.column3 AND t.column4 = u.column4 AND t.column5 = u.column5;

In this query column2 is the one I want to delete while the other 4 columns are the ones I want to keep in the primary key. All records that are equal in the new definition will be deleted except for one.

Once done I was able to successfully run the original ALTER statement.

Upvotes: 1

Related Questions