MySQL 如何向现有表添加唯一键（具有非唯一行）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15255304/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How add unique key to existing table (with non uniques rows)
提问by yAnTar
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.
我想向现有表添加复杂的唯一键。键包含 4 个字段 ( user_id, game_id, date, time)。但是表有非唯一行。我知道我可以删除所有重复的日期，然后添加复杂的密钥。
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?
UPD 我搜索过，如何删除重复的 mysql 行 - 我认为这是一个很好的解决方案。 仅使用 MySQL 查询删除重复项？
回答by Igor Lozovsky
You can do as yAnTar advised
你可以按照yAnTar的建议去做
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.
但我认为为了不丢失您现有的数据，您可以添加一个 indentity 列，然后创建一个组合键。
回答by radhason power
The proper syntax would be - ALTER TABLE Table_Name ADD UNIQUE (column_name)
正确的语法是 - ALTER TABLE Table_Name ADD UNIQUE (column_name)
Example
例子
ALTER TABLE 0_value_addition_setup ADD UNIQUE (`value_code`)
回答by user3149374
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:
我不得不解决一个类似的问题。我从 MS Access 继承了一个大型源表，其中有近 15000 条没有主键的记录，我必须对其进行规范化并使 CakePHP 兼容。CakePHP 的一个约定是每个表都有一个主键，它是第一列并且它被称为“id”。以下简单的语句在 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).
这在现有数据（“FIRST”关键字）前面添加了一个整数类型的新列“id”。AUTO_INCREMENT 关键字从 1 开始增加 id。现在每个数据集都有一个唯一的数字 id。（如果没有 AUTO_INCREMENT 语句，所有行都用 id = 0 填充）。
回答by Slowcoder
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`)
回答by Hituptony
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...
创建一个自动递增 id 或一个 UNIQUE id 并将其添加到您正在谈论的 4 个字段的自然键中。这将使表中的每一行都是唯一的...
回答by prakash kumar
Set Multiple Unique key into table
将多个唯一键设置为表
ALTER TABLE table_name
ADD CONSTRAINT UC_table_name UNIQUE (field1,field2);
回答by Vette
For MySQL:
对于 MySQL：
ALTER TABLE MyTable ADD MyId INT AUTO_INCREMENT PRIMARY KEY;
回答by user2649201
Beware of merely adding a UNIQUE key since it accepts more than one NULL value.
注意不要仅仅添加一个 UNIQUE 键，因为它接受多个 NULL 值。
