如何在 MySQL 中为多列指定唯一约束?

发布于 07-15 19:25 字数 184 浏览 11 评论 0原文

我有一个表:

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

如何使用户、电子邮件、地址列唯一 - 即确保不存在任何对所有三列都具有相同值的行?

I have a table:

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

How can I make the columns user, email, address unique - i.e., ensure that there isn't any pair of rows that has identical values for all three columns?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(15

顾冷2024-07-22 19:25:50

要添加唯一约束,您需要使用两个组件:

ALTER TABLE - 更改表架构,

ADD UNIQUE - 添加唯一约束。

然后,您可以使用格式 'name'('column1', 'column2'...) 定义新的唯一键。

因此,对于您的特定问题,您可以使用以下命令:

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

To add a unique constraint, you need to use two components:

ALTER TABLE - to change the table schema and,

ADD UNIQUE - to add the unique constraint.

You then can define your new unique key with the format 'name'('column1', 'column2'...)

So for your particular issue, you could use this command:

ALTER TABLE `votes` ADD UNIQUE `unique_index`(`user`, `email`, `address`);
痕至2024-07-22 19:25:50

我有一个 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 `my_uniq_id` (`id_box_elements`,`id_router`)
);

UNIQUE KEY 按预期工作,它允许 id_box_elements 和 id_router 的多个 NULL 行。

我正在运行 MySQL 5.1.42,所以可能对上面讨论的问题有一些更新。 幸运的是,它有效,并希望它能保持这种状态。

I have a MySQL table:

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 `my_uniq_id` (`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.

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.

恏ㄋ傷疤忘ㄋ疼2024-07-22 19:25:50

如果行中有 NULL 值,则多列唯一索引在 MySQL 中不起作用,因为 MySQL 将 NULL 视为唯一值,并且至少目前在多列索引中没有逻辑来解决它。 是的,这种行为很疯狂,因为它限制了多列索引的许多合法应用,但事实就是如此......到目前为止,这是一个在 MySQL 上被标记为“不会修复”的错误错误跟踪...

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

他是夢罘是命2024-07-22 19:25:50

你试过这个吗?

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

Have you tried this ?

UNIQUE KEY `thekey` (`user`,`email`,`address`)
各自安好2024-07-22 19:25:50

这适用于 mysql 版本 5.5.32

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

This works for mysql version 5.5.32

ALTER TABLE  `tablename` ADD UNIQUE (`column1` ,`column2`);
山有枢2024-07-22 19:25:50

MySql 5 或更高版本的行为如下(我刚刚测试过):

  • 您可以定义涉及可为空列的唯一约束。 假设您定义了一个唯一的约束 (A, B),其中 A 不可为空,但 B 是
  • 在评估此类约束时,您可以拥有 (A, null) 任意多次(相同的 A 值!),
  • 您只能拥有一个 (A ,不为空 B) 对

示例:
产品名称、产品版本
“玻璃”,空
“玻璃”,空
'wine', 1

现在,如果您尝试再次插入 ('wine' 1) ,它将报告约束违规
希望这可以帮助

MySql 5 or higher behaves like this (I've just tested):

  • 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

Example:
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

原谅过去的我2024-07-22 19:25:50

您可以通过phpMyAdmin添加多列唯一索引。 (我在版本 4.0.4 中进行了测试)

导航到目标表的结构页面。 向其中一列添加唯一索引。 展开结构页面底部的索引列表以查看您刚刚添加的唯一索引。 单击编辑图标,在接下来的对话框中,您可以向该唯一索引添加其他列。

You can add multiple-column unique indexes via phpMyAdmin. (I tested in version 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.

森林迷了鹿2024-07-22 19:25:50

本教程对我有用

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

https://www.mysqltutorial.org/mysql-unique-constraint/< /a>

this tutorial works for me

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

https://www.mysqltutorial.org/mysql-unique-constraint/

魂归处2024-07-22 19:25:50

我这样做:

CREATE UNIQUE INDEX index_name ON TableName (Column1, Column2, Column3);

我对唯一 index_name 的约定是 TableName_Column1_Column2_Column3_uindex

I do it like this:

CREATE UNIQUE INDEX index_name ON TableName (Column1, Column2, Column3);

My convention for a unique index_name is TableName_Column1_Column2_Column3_uindex.

风筝有风,海豚有海2024-07-22 19:25:50

如果您在 mysql 中创建表,则使用以下命令:

create table package_template_mapping (
mapping_id  int(10) not null auto_increment  ,
template_id int(10) NOT NULL ,
package_id  int(10) NOT NULL ,
remark      varchar(100),
primary key (mapping_id) ,
UNIQUE KEY template_fun_id (template_id , package_id)
);

If You are creating table in mysql then use following :

create table package_template_mapping (
mapping_id  int(10) not null auto_increment  ,
template_id int(10) NOT NULL ,
package_id  int(10) NOT NULL ,
remark      varchar(100),
primary key (mapping_id) ,
UNIQUE KEY template_fun_id (template_id , package_id)
);
浮生面具三千个2024-07-22 19:25:50

要添加唯一索引,需要以下内容:

1) table_name
2) 索引名称
3)要添加索引的列

ALTER TABLE  `tablename` 
ADD UNIQUE index-name
(`column1` ,`column2`,`column3`,...,`columnN`);

在您的情况下,我们可以创建唯一索引,如下所示:

ALTER TABLE `votes`ADD 
UNIQUE <votesuniqueindex>;(`user` ,`email`,`address`);

For adding unique index following are required:

1) table_name
2) index_name
3) columns on which you want to add index

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`);
甜扑2024-07-22 19:25:50

如果您想避免将来重复。 创建另一列,例如 id2。

UPDATE tablename SET id2 = id;

现在在两列上添加唯一性:

alter table tablename add unique index(columnname, id2);

If you want to avoid duplicates in future. Create another column say id2.

UPDATE tablename SET id2 = id;

Now add the unique on two columns:

alter table tablename add unique index(columnname, id2);
瀟灑尐姊2024-07-22 19:25:50

首先删除现有的重复项

delete a from votes as a, votes as b where a.id < b.id 
and a.user <=> b.user and a.email <=> b.email 
and a.address <=> b.address;

然后添加唯一约束

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

使用以下命令验证约束

SHOW CREATE TABLE votes;

请注意,如果用户、电子邮件、地址中的任何一个具有空值,则它们将被视为唯一。

First get rid of existing duplicates

delete a from votes as a, votes as b where a.id < b.id 
and a.user <=> b.user and a.email <=> b.email 
and a.address <=> b.address;

Then add the unique constraint

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

Verify the constraint with

SHOW CREATE TABLE votes;

Note that user, email, address will be considered unique if any of them has null value in it.

温折酒2024-07-22 19:25:50

对于 PostgreSQL...
它对我来说没有索引; 它给了我一个错误,所以我这样做了:

alter table table_name
add unique(column_name_1,column_name_2);

PostgreSQL给了唯一索引自己的名字。 我想你可以在表的选项中更改索引的名称,如果需要更改......

For PostgreSQL...
It didn't work for me with index; it gave me an error, so I did this:

alter table table_name
add unique(column_name_1,column_name_2);

PostgreSQL gave unique index its own name. I guess you can change the name of index in the options for the table, if it is needed to be changed...

霊感2024-07-22 19:25:50

在 MySQL Workbench 中:

  1. 创建索引
  2. 选择列
  3. 从类型菜单中选择 UNIQUE

MySQL Workbench GUI - 索引选项卡

In MySQL Workbench:

  1. Make an Index
  2. Select the Columns
  3. From the Type menu choose UNIQUE

MySQL Workbench GUI - Indexes tab

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文