mysqldatabaserdbmsunique-key

How add unique key to existing table (with non uniques rows)

I want to add complex unique key to existing table. Key contains from 4 fields (user_id, game_id, date, time). But table have non unique rows. I understand that I can remove all duplicate dates and after that add complex key.

Maybe exist another solution without searching all duplicate data. (like add unique ignore etc).

UPD I searched, how can remove duplicate mysql rows - i think it's good solution. Remove duplicates using only a MySQL query?

Solution

  • You can do as yAnTar advised

    ALTER TABLE TABLE_NAME ADD Id INT AUTO_INCREMENT PRIMARY KEY

    OR

    You can add a constraint

    ALTER TABLE TABLE_NAME ADD CONSTRAINT constr_ID UNIQUE (user_id, game_id, date, time)

    But I think to not lose your existing data, you can add an indentity column and then make a composite key.