CODEWITHSUNDEEP
mysqldatabaserdbmsunique-key
yAnTar
yAnTar

Reputation: 4610

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?

Upvotes: 92

Views: 258997

Answers (9)

Profesor
Profesor

Reputation: 1

ALTER TABLE table_name (field)

Upvotes: -1

Tona Dinh
Tona Dinh

Reputation: 11

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.

Upvotes: 0

Slowcoder
Slowcoder

Reputation: 2120

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

Upvotes: 3

ranojan
ranojan

Reputation: 837

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

Example

ALTER TABLE  0_value_addition_setup ADD UNIQUE (`value_code`)

Upvotes: 34

Igor Lozovsky
Igor Lozovsky

Reputation: 2415

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.

Upvotes: 170

Vette
Vette

Reputation: 541

For MySQL:

ALTER TABLE MyTable ADD MyId INT AUTO_INCREMENT PRIMARY KEY;

Upvotes: 0

prakash kumar
prakash kumar

Reputation: 237

Set Multiple Unique key into table

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

Upvotes: 3

user3149374
user3149374

Reputation: 121

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

Upvotes: 12

Hituptony
Hituptony

Reputation: 2860

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

Upvotes: 2

Related Questions