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

alter table add unique constraint mysql
unique key example
create table with primary key and unique constraint in sql server
mysql drop unique key
mysql add unique index
alter unique key in mysql
add unique key to existing table sql
alter table add unique constraint oracle

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

How add unique key to existing table (with non-unique rows)?, You can add unique key to existing table with the help of alter command. The syntax is as follows −ALTER TABLE yourTableName ADD  Adding a UNIQUE Constraint to an Existing Table : Unique « Constraints « SQL Server / T-SQL Adding a UNIQUE Constraint to an Existing Table. ALTER TABLE

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

Example

ALTER TABLE  0_value_addition_setup ADD UNIQUE (`value_code`)

Can set unique constraint in table have data not unique SQL Server , You can delete all the data for that row and then add the data again after applying the unique key constraint. You may try to create a temporary  SQL> I've modified the table to increase the length of the ename column and increase the size of the empno column, then loaded 999,000 rows into the table. Now it's time to create the non-unique index: SQL> create index emp_idx on emp (empno); Index created.

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

SQL UNIQUE Constraint, The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE constraint. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. UNIQUE, LastName varchar(255) NOT NULL, ADD CONSTRAINT UC_Person UNIQUE (ID,​LastName);  No you can't, and if you create a basic unique constraint a unique index will be created automatically - so it will fail if duplicate values exist. You can specify an existing index as part of the constraint definition, usually a unique one - say from dropping and recreating the constraint, without having to rebuild the index.

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

Create Unique Constraints, You can create a unique constraint in SQL Server 2019 (15.x) by using SQL Server Management Studio or Transact-SQL to ensure no duplicate values are entered in specific In the Indexes/Keys dialog box, click Add. After I moved back to Europe and Malta in order to set up our operations here, I was approached by a old friend of mine who wanted to know how to add a UNIQUE constraint and remove duplicates on a table, while keeping the newest records.

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

Using MySQL UNIQUE Index To Prevent Duplicates, However, each table can have only one primary key. If you want to add a unique constraint to an existing table, you can use the Another important point is that the UNIQUE constraint does not apply to NULL values except for the BDB​  Depends on the database as each database has a different way to add sequence numbers. I would alter the table to add the column then write a db script in groovy/python/etc to read in the data and update the id with a sequence. Once the data has been set, I would add a sequence to the table that starts after the top number.

How do I rebuild a UNIQUE index if there are duplicate values , How do I know if the UNIQUE constraint on my index has been violated? https​://help.heroku.com/H3LU3G7Z/how-do-i-rebuild-a-non-unique-corrupted-index if rebuilding a UNIQUE index fails due to an error about a duplicate key. then add the new constraint ALTER TABLE <table_name> ADD PRIMARY KEY USING  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).

Ask TOM "Primary key with Non Unique Index", SQL> ALTER TABLE TAB_TEST ADD CONSTRAINT ID_PK PRIMARY KEY (ID) USING INDEX ( CREATE SQL> INSERT INTO TAB_TEST VALUES ( 2, 'J2' ); 1 row created. We can use a non-unique index to enforce a unique constraint. The combination of values in column c2 and c3 will be unique across the whole table. The value of the column c2 or c3 needs not to be unique. Adding unique constraint using a unique index. Sometimes, you may want to add a unique constraint to an existing column or group of columns. Let’s take a look at the following example.

Insight into SQL Server Unique Constraints – {coding}Sight, Since a table can have only one primary key, you can use a unique constraint Note that when you add a unique constraint to an existing table, the If there are such values, the constraint will not be added returning an error. Unique Key in SQL. A unique key is a set of one or more than one fields/columns of a table that uniquely identify a record in a database table. You can say that it is little like primary key but it can accept only one null value and it cannot have duplicate values.

Comments
  • You can't have duplicates if you create a unique key.
  • what is an IDENTITY column ? I can find no reference of it in the doc except something related to AUTO_INCREMENT : dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html
  • As mentioned by @challet the key word in MySQL is NOT IDENTITY but AUTO_INCREMENT. dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html
  • For clarity the mysql syntax is: ALTER TABLE TABLE_NAME ADD Id INT AUTO_INCREMENT PRIMARY KEY
  • 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 .
  • The question is tagged with 'mysql', so the solution should apply to that DBMS
  • 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.
  • This isn't really an answer. This should be a comment.