MySQL Downgrading Composite Primary Key to be more restrictive

Question

I've got the following table used to track users who are watching a support ticket

CREATE TABLE IF NOT EXISTS crm_ticketwatcher (
  ticketid int(10) unsigned NOT NULL DEFAULT '0',
  employeeid int(10) unsigned NOT NULL DEFAULT '0',
  contactid int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (ticketid,employeeid,contactid)
) ENGINE=MyISAM;

I want to have a downgrade script that removes contactid from the primary key.

The table content looks a bit like this

ticketid|employeeid|contactid
--------|----------|---------
5       |5         |0
5       |8         |0
5       |0         |2
5       |0         |3

When I run my (unsuccessful) downgrade script,

ALTER TABLE crm_ticketwatcher DROP PRIMARY KEY, ADD PRIMARY KEY (ticketid, employeeid);

I get the following error:

ERROR 1062 (23000) at line 1: Duplicate entry '306-0' for key 'PRIMARY'

because there are now 2 rows with the primary key set (5, 0)

What's the best way to drop the additional rows from the table, saving the last occurring one?

We're on MySQL 5.7.13, so the IGNORE keyword is not an option.

Thanks.

Answer 1

As documented under ALTER TABLE Syntax:

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only one row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.

Therefore, you can do:

ALTER IGNORE TABLE crm_ticketwatcher
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (ticketid, employeeid);

Note however that which of the "duplicate" rows is preserved will be indeterminate.

If you want a more determinate outcome, or are using MySQL v5.7.4 and later (from which IGNORE has been removed), you can first perform a self-join with the multiple-table DELETE syntax:

DELETE c1
FROM   crm_ticketwatcher c1
  JOIN crm_ticketwatcher c2 USING (ticketid, employeeid)
WHERE  c1.contactid < c2.contactid -- or whatever logic you prefer