MySQL-使一对值唯一 [英] MySQL - Make a pair of values unique

问题描述

我有一个表，其中包含两个ID的int值.这些ID可以单独显示在表格中任何次数，但在一起只能出现一次.

I have a table with two int values that are IDs. On their own these IDs can show up any number of times in the table, but together they should only ever appear once.

是否可以使一对值唯一，并且仍然允许单个值多次显示?

作为后续措施，如果可能的话，可以将这对值用作键吗?我目前在第3列有一个唯一的键自动递增值.

As a follow up, if this is possible can the pair of values be used as a key? I currently have a 3rd column for a unique auto increment value for my key.

推荐答案

它称为复合键.

如果要将实际PK更改为复合PK，请使用

If you want to change your actual PK to a composite one, use

Alter table <your table> drop PRIMARY KEY;
Alter table <your table> drop COLUMN <your autoincremented column>;

Alter table <your table> add [constraint <constraint name>] PRIMARY KEY (<col1>, <col2>);

您也可以只添加一个唯一约束(您的PK将是相同的，并且唯一对...必须是唯一的).

You can also just add a unique constraint (your PK will be the same, and unique pairs... will have to be unique).

alter table <your table> add [constraint <constraint name>] unique index(<col1>, <col2>);

就个人而言，我将推荐第二种解决方案(简单PK +唯一约束)，但这只是个人观点.您可以在Google上搜索有关组合键的优缺点参数.

Personnally, I would recommend the second solution (simple PK + unique constraint), but that's just a personal point of view. You can google for pros and cons arguments about composite keys.

[]之间的部分是可选的.

The part between [] are optional.

编辑

如果要在create table语句中执行此操作

If you wanna do this in the create table statement

对于复合pk

CREATE TABLE Test(
    id1 int NOT NULL, 
    id2 int NOT NULL,
    id3 int NOT NULL,
    PRIMARY KEY (id1, id2)
);

获取唯一索引

CREATE TABLE Test1(
    id1 int NOT NULL AUTO_INCREMENT, 
    id2 int NOT NULL,
    id3 int NOT NULL,
    PRIMARY KEY (id1),
    UNIQUE KEY (id2, id3)
);

这篇关于MySQL-使一对值唯一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！