Update table to make primary key out of two foreign keys-CodePudding

Have a table sample_tag which already exists with 1000's of entries.

It consists of two foreign keys, sample_id and tag_id.

However, the database is allowing duplicate sample_id/tag_id records to be created.

Without creating a new table, is there a SQL statement to update the sample_tag table such that the two foreign keys, together, function as its primary key?

Database is MySQL using phpMyAdmin

CodePudding user response：

The main challenge is how you resolve the duplicates. If there are already duplicate keys, then you'll need to address these as a separate step (either deleting the duplicate, or merging any other columns as you see fit).

To check for duplicates, try:

SELECT sample_id, tag_id, COUNT( * )
FROM sample_tag
GROUP BY sample_id, tag_id;

If there are none, then you can go ahead and delete any existing primary key, and create a new one.