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

mysql unique-key
yAnTar picture yAnTar · Mar 6, 2013 · Viewed 183.5k times · Source

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?

Answer

Igor Lozovsky picture Igor Lozovsky · Mar 6, 2013

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.

Related questions

difference between primary key and unique key

I'm using mysql database. I have a confusion between primary key and unique key. Please help me where should I create primary and unique key. I mean in which situation we create unique key or primary key .

Read More
How to remove unique key from mysql table

I need to remove a unique key from my mysql table. How can remove that using mysql query. I tried this but it is not working alter table tbl_quiz_attempt_master drop unique key; Please help me Thanks

Read More
Difference between Key, Primary Key, Unique Key and Index in MySQL

When should I use KEY, PRIMARY KEY, UNIQUE KEY and INDEX?

Read More