How add unique key to existing table with non uniques rows

Question

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

User · Accepted Answer

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

User · Answer

Set Multiple Unique key into table   ALTER TABLE table name ADD CONSTRAINT UC table name UNIQUE  field1 field2

User · Answer

I had to solve a similar problem  I inherited a large source table from MS Access with nearly 15000 records that did not have a primary key  which I had to normalize and make CakePHP compatible  One convention of CakePHP is that every table has a the primary key  that it is first column and that it is called  id   The following simple statement did the trick for me under MySQL 5 5   ALTER TABLE  database name   table name   ADD COLUMN  id  INT NOT NULL AUTO INCREMENT FIRST  ADD PRIMARY KEY   id      This added a new column  id  of type integer in front of the existing data   FIRST  keyword   The AUTO INCREMENT keyword increments the ids starting with 1  Now every dataset has a unique numerical id   Without the AUTO INCREMENT statement all rows are populated with id   0

User · Answer

Either create an auto-increment id or a UNIQUE id and add it to the natural key you are talking about with the 4 fields  this will make every row in the table unique

User · Answer

For MySQL   ALTER TABLE MyTable ADD MyId INT AUTO INCREMENT PRIMARY KEY

User · Answer

I am providing my solution with the assumption on your business logic   Basicall in my design i will allow the table to store only one record for a user-game combination   So I will add a composite key to the table   PRIMARY KEY   user id   game id

User · Answer

The proper syntax would be - ALTER TABLE Table Name ADD UNIQUE  column name      Example   ALTER TABLE  0 value addition setup ADD UNIQUE   value code

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

Examples related to mysql

Examples related to unique-key