如何指定MySQL中多个列的惟一约束?

I have a table:

我有一个表:

table votes (
    id,
    user,
    email,
    address,
    primary key(id),
);

Now I want to make the columns user, email, address unique (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.
当然，这个例子只是……一个例子。所以请不要担心语义。

9 个解决方案

#1

1170

ALTER TABLE `votes` ADD UNIQUE `unique_index`(`user`, `email`, `address`);

#2

178

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.

正如所期望的那样，它允许多个空行id_box_elements和id_router。

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，所以可能有关于上面讨论的问题的更新。幸运的是，它成功了，希望它能一直保持下去。

#3

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

如果在MySQL中有一个空值，那么多列惟一索引在MySQL中是无效的，因为MySQL将NULL视为一个惟一的值，并且至少目前没有在多列索引中使用它的逻辑。是的，这种行为是疯狂的，因为它限制了多列索引的合法应用，但是它是…到目前为止，它是一个在MySQL bug跟踪上“不会修复”的bug……

#4

Have you tried this ?

你试过这个吗?

UNIQUE KEY `thekey` (`user`,`email`,`address`)

#5

This works for mysql version 5.5.32

这适用于mysql版本5.5.32。

ALTER TABLE  `tablename` ADD UNIQUE (`column1` ,`column2`);

#6

You can add multiple-column unique indexes via phpMyAdmin. (I tested in version 4.0.4)

您可以通过phpMyAdmin添加多个列的唯一索引。(我在4.0.4版测试)

Navigate to the structure page for your target table. Add a unique index to one of the columns. Expand the Indexes list 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.

导航到目标表的结构页面。向其中一个列添加惟一索引。展开结构页面底部的索引列表，以查看刚刚添加的惟一索引。单击edit图标，在下面的对话框中，您可以为该惟一索引添加额外的列。

#7

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
您可以定义包含空列的惟一约束。假设您定义了一个惟一的约束(a, B)，其中a不是空的，而是B。
when evaluating such a constraint you can have (A, null) as many times you want (same A value!)
在对这种约束进行评估时，您可以有(a, null)多次您想要的(相同的值!)
you can only have one (A, not null B) pair
你只能有一个(A，不是空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)，它将会报告一个约束违背希望这是有帮助的。

#8

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

#9

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

#1