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?

You can't have duplicates if you create a unique key. – Tchoupi Mar 06 '13 at 18:38 — Tchoupi, Mar 06 '13 at 18:38

score 157 · Accepted Answer · edited Aug 28 '19 at 12:54

157

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.

edited Aug 28 '19 at 12:54

Mohamed Nuur

5,536
6
39
55

answered Mar 06 '13 at 19:17

Igor Lozovsky

2,275
2
15
14

2

what is an IDENTITY column ? I can find no reference of it in the doc except something related to AUTO_INCREMENT : http://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html – challet Jan 07 '16 at 13:39
4

As mentioned by @challet the key word in MySQL is NOT IDENTITY but AUTO_INCREMENT. http://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html – MER Jan 11 '16 at 22:28
1

For clarity the mysql syntax is: ALTER TABLE TABLE_NAME ADD Id INT AUTO_INCREMENT PRIMARY KEY – Brett Sutton Oct 29 '18 at 01:06
The OP did not (explicitly) specify a particular database. IDENTITY is the correct syntax for MS/Oracle SQL-Server and perhaps other non MySQL/MariaDB dialects, for them use the AUTO_INCREMENT syntax . – fcm Mar 10 '19 at 10:48
2

The question is tagged with 'mysql', so the solution should apply to that DBMS – Andrew Lalis Apr 17 '19 at 10:05

score 33 · Answer 2 · edited Aug 31 '19 at 19:03

33

The proper syntax would be - ALTER TABLE Table_Name ADD UNIQUE (column_name)

Example

ALTER TABLE  0_value_addition_setup ADD UNIQUE (`value_code`)

edited Aug 31 '19 at 19:03

Shubham Dixit

9,242
4
27
46

answered Oct 04 '16 at 10:19

ranojan

819
8
11

17

Although this code may help to solve the problem, it doesn't explain _why_ and/or _how_ it answers the question. Providing this additional context would significantly improve its long-term educational value. Please [edit] your answer to add explanation, including what limitations and assumptions apply. – Toby Speight Oct 05 '16 at 14:13
1

This marks the column unique if there are no duplicate entries. If there are some, it errors in `Duplicate entry 'my_value' for key 'my_key' ` – Fanky Sep 15 '22 at 13:23

score 11 · Answer 3 · edited Jan 05 '14 at 17:49

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).

score 3 · Answer 4 · edited Apr 23 '21 at 01:34

3

I am providing my solution with the assumption on your business logic. Basically 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`)

edited Apr 23 '21 at 01:34

bertdida

4,988
2
16
22

answered Mar 06 '13 at 20:39

Slowcoder

2,060
3
16
21

score 3 · Answer 5 · answered Jun 01 '18 at 07:16

3

Set Multiple Unique key into table

ALTER TABLE table_name
ADD CONSTRAINT UC_table_name UNIQUE (field1,field2);

answered Jun 01 '18 at 07:16

prakash kumar

237
1
2
12

score 2 · Answer 6 · answered Mar 06 '13 at 18:43

2

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...

answered Mar 06 '13 at 18:43

Hituptony

2,740
3
22
44

score 0 · Answer 7 · answered Jul 15 '19 at 20:12

0

For MySQL:

ALTER TABLE MyTable ADD MyId INT AUTO_INCREMENT PRIMARY KEY;

answered Jul 15 '19 at 20:12

Vette

511
5
10

score 0 · Answer 8 · answered Apr 23 '21 at 02:53

If yourColumnName has some values doesn't unique, and now you wanna add an unique index for it. Try this:

CREATE UNIQUE INDEX [IDX_Name] ON yourTableName (yourColumnName) WHERE [id]>1963 --1963 is max(id)-1

Now, try to insert some values are exists for test.

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

8 Answers8

Linked

Related