如何为 MySQL 中的多列指定唯一约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/635937/
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 do I specify unique constraint for multiple columns in MySQL?
提问by Niyaz
I have a table:
我有一张桌子：
table votes (
id,
user,
email,
address,
primary key(id),
);
Now I want to make the columns user, email, addressunique (together).
现在我想让列用户、电子邮件、地址唯一（一起）。
How do I do this in MySql?
我如何在 MySql 中做到这一点？
Of course the example is just... an example. So please don't worry about the semantics.
当然，这个例子只是……一个例子。所以请不要担心语义。
回答by jonstjohn
ALTER TABLE `votes` ADD UNIQUE `unique_index`(`user`, `email`, `address`);
回答by Frodik
I have a MySQL table:
我有一个 MySQL 表：
CREATE TABLE `content_html` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_box_elements` int(11) DEFAULT NULL,
`id_router` int(11) DEFAULT NULL,
`content` mediumtext COLLATE utf8_czech_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_box_elements` (`id_box_elements`,`id_router`)
);
and the UNIQUE KEY works just as expected, it allows multiple NULL rows of id_box_elements and id_router.
并且 UNIQUE KEY 按预期工作，它允许 id_box_elements 和 id_router 的多个 NULL 行。
I am running MySQL 5.1.42, so probably there was some update on the issue discussed above. Fortunately it works and hopefully it will stay that way.
我正在运行 MySQL 5.1.42，所以上面讨论的问题可能有一些更新。幸运的是它有效，希望它会保持这种状态。
回答by niksoft
Multi column unique indexes do not work in MySQL if you have a NULL value in row as MySQL treats NULL as a unique value and at least currently has no logic to work around it in multi-column indexes. Yes the behavior is insane, because it limits a lot of legitimate applications of multi-column indexes, but it is what it is... As of yet, it is a bug that has been stamped with "will not fix" on the MySQL bug-track...
如果行中有 NULL 值，则多列唯一索引在 MySQL 中不起作用，因为 MySQL 将 NULL 视为唯一值，并且至少目前没有在多列索引中解决它的逻辑。是的，这种行为很疯狂，因为它限制了多列索引的许多合法应用程序，但事实就是如此......到目前为止，这是一个在 MySQL 上标记为“不会修复”的错误错误跟踪...
回答by Erick
Have you tried this ?
你试过这个吗？
UNIQUE KEY `thekey` (`user`,`email`,`address`)
回答by rizon
This works for mysql version 5.5.32
这适用于 mysql 版本 5.5.32
ALTER TABLE `tablename` ADD UNIQUE (`column1` ,`column2`);
回答by Cristian Botiza
MySql 5 or higher behaves like this (I've just tested):
MySql 5 或更高版本的行为如下（我刚刚测试过）：
- you can define unique constraints involving nullable columns. Say you define a constraint unique (A, B) where A is not nullable but B is
- when evaluating such a constraint you can have (A, null) as many times you want (same A value!)
- you can only have one (A, not null B) pair
- 您可以定义涉及可为空列的唯一约束。假设您定义了一个唯一的 (A, B) 约束，其中 A 不可为空，但 B 是
- 在评估这样的约束时，您可以根据需要多次使用 (A, null)（相同的 A 值！）
- 你只能有一对 (A, not null B)
Example: PRODUCT_NAME, PRODUCT_VERSION 'glass', null 'glass', null 'wine', 1
示例：PRODUCT_NAME, PRODUCT_VERSION 'glass', null 'glass', null 'wine', 1
Now if you try to insert ('wine' 1) again it will report a constraint violation Hope this helps
现在，如果您再次尝试插入 ('wine' 1) 它将报告违反约束希望这会有所帮助
回答by Vince K
You can add multiple-column unique indexes via phpMyAdmin. (I tested in version 4.0.4)
您可以通过phpMyAdmin添加多列唯一索引。（我在 4.0.4 版本中测试过）
Navigate to the structurepage for your target table. Add a unique index to one of the columns. Expand the Indexeslist on the bottom of the structure page to see the unique index you just added. Click the edit icon, and in the following dialog you can add additional columns to that unique index.
导航到目标表的结构页面。向其中一列添加唯一索引。展开结构页面底部的索引列表以查看您刚刚添加的唯一索引。单击编辑图标，在接下来的对话框中，您可以向该唯一索引添加其他列。
回答by Sam Malayek
I do it like this:
我这样做：
CREATE UNIQUE INDEX index_name ON TableName (Column1, Column2, Column3);
My convention for a unique index_nameis TableName_Column1_Column2_Column3_uindex.
我对独特的约定index_name是TableName_Column1_Column2_Column3_uindex.
回答by sandeep vanama
For adding unique index following are required:
添加唯一索引需要以下内容：
1) table_name
2) index_name
3) columns on which you want to add index
1) table_name
2) index_name
3) 要添加索引的列
ALTER TABLE `tablename`
ADD UNIQUE index-name
(`column1` ,`column2`,`column3`,...,`columnN`);
In your case we can create unique index as follows:
在您的情况下，我们可以按如下方式创建唯一索引：
ALTER TABLE `votes`ADD
UNIQUE <votesuniqueindex>;(`user` ,`email`,`address`);
回答by kumar
If you want to avoid duplicates in future. Create another column say id2.
如果您想避免将来重复。创建另一列说 id2。
UPDATE tablename SET id2 = id;
Now add the unique on two columns:
现在在两列上添加唯一的：
alter table tablename add unique index(columnname, id2);
