MySQL 两列作为mysql中的主键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14206868/
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
Two column as a primary keys in mysql?
提问by Bohemian
Today I found out you can have a primary key using two columns (tsql). The PK must be unique but both columns do not (the combo must be unique).
今天我发现你可以使用两列（tsql）来拥有一个主键。PK 必须是唯一的，但两列都不是（组合必须是唯一的）。
I thought that was very cool. There were at least two SO question I asked where people yelled at me that I was doing my (mysql) databases wrong with only one person saying I did it fine. So... this leaves me some doubt
我觉得这很酷。至少有两个 SO 问题我问过，人们对我大喊大叫我的（mysql）数据库做错了，只有一个人说我做得很好。所以……这让我有些怀疑
Does this do what I think it does?
这是否符合我的想法？
create table User(
id INT primary key AUTO_INCREMENT ,
ipaddr TEXT NOT NULL ,
email TEXT NOT NULL
);
create table test(
a INT NOT NULL ,
b INT NOT NULL ,
dummy INT NOT NULL ,
FOREIGN KEY (a) REFERENCES User(id),
FOREIGN KEY (b) REFERENCES User(id),
PRIMARY KEY(a,b)
);
I ran the below so it appears that i is doing what i think (combos must be unique. But same value in a column don't need to be unique). Should i be aware of something? There must be a reason no one mentioned this to me in regards to mysql?
我运行了下面，所以看起来我正在做我认为的事情（组合必须是唯一的。但列中的相同值不需要是唯一的）。我应该知道些什么吗？关于mysql，没有人向我提到这一点一定是有原因的吗？
mysql> insert into test(a,b,dummy) select 1,1,1;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into test(a,b,dummy) select 1,2,2;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into test(a,b,dummy) select 2,1,3;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into test(a,b,dummy) select 2,2,4;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into test(a,b,dummy) select 1,2,5;
ERROR 1062 (23000): Duplicate entry '1-2' for key 'PRIMARY'
回答by Bohemian
You are over thinking this:
你想多了：
- primary keys (no matter how many columns are involved) must be unique
- your primary key is the two columns a, b
- 主键（无论涉及多少列）必须是唯一的
- 你的主键是两列 a, b
Therefore a and b togethermust be unique.
因此 a 和 b一起必须是唯一的。
Individual value of a and b are irrelevant.
a 和 b 的个别值无关紧要。
回答by DWright
Yes, this is a normal thing to do in SQL and it works (having a composite primary key, in which multiple fields together constitute a unique value).
是的，这在 SQL 中是很正常的事情并且它可以工作（具有复合主键，其中多个字段共同构成一个唯一值）。
Two notes:
两个注意事项：
Make sure it's necessary. It often is and then it's fine. But sometimes it's a sign that you further need to normalize your data model.
I'm thinking that you don't want to make and a b be foreign keys from another table and then make them the composite primary key of your table. What happens if you set up a cascading delete, in which one user id, but not the other is deleted? So the composite primary key is fine, but then you don't want come from "unrelated" foreign keys.
确保它是必要的。经常这样，然后就好了。但有时这表明您需要进一步规范化您的数据模型。
我在想你不想让和 ab 成为另一个表的外键，然后让它们成为你表的复合主键。如果您设置级联删除，其中删除一个用户 ID，而不删除另一个用户 ID，会发生什么情况？所以复合主键很好，但是你不希望来自“无关”的外键。
回答by Michael A. Kline
Your thinking is good. I use multi-field primary keys frequently, simply because it makes my database design more logical, managable and readable. You can think of multi-field primary keys like having a unique name. For example:
你的想法很好。我经常使用多字段主键，只是因为它使我的数据库设计更具逻辑性、可管理性和可读性。您可以将多字段主键视为具有唯一名称。例如：
Multi-Field Primary Keys:
多字段主键：
(First ,Middle, Last)
Example Values:
示例值：
('Michael', 'A.', 'Kline')
There can be many people with the 'First' name 'Michael' and/or the 'Middle' name 'A.' and/or the'Last' name 'Kline', but as far as your database is concerned, there can only be ONE 'Michael A. Kline'.
可能有很多人的“名字”是“迈克尔”和/或“中间人”的名字是“A”。和/或“姓氏”名称“Kline”，但就您的数据库而言，只能有一个“Michael A. Kline”。
Usually, a multi-field primary key is a combination of other primary keys from other tables and the record contents describe content relavant to the specific key values. For example:
通常，多字段主键是其他表中其他主键的组合，记录内容描述与特定键值相关的内容。例如：
Table #1: Student Records (KEY: student_id)
Table #2: Course Records (KEY: course_id)
Table #3: Student Grades (KEY: student_id, course_id)
Hope this helps.
希望这可以帮助。
回答by mamdouh alramadan
Yes you have to be aware of duplicating your PRIMARY_KEYwhich is a combinedkey in your case not to be duplicated.
是的，您必须注意复制您的PRIMARY_KEY，它是您的情况下不可复制的组合键。
Anyway when you set two PK's this means that your signature is PK1+PK2so you can duplicate PK1 or PK2 but not the both of them.
无论如何，当您设置两个 PK 时，这意味着您的签名是PK1+PK2，因此您可以复制 PK1 或 PK2，但不能同时复制它们。
Hope that I helped
希望我有所帮助
回答by Krishna Shetty
回答by Devon Bernard
I believe what is happening is the paired columns together are a primary. For example you know you cannot have a duplicate primary column Ex: if col "a" is primary you cannot have two rows that have the same value for a.
我相信正在发生的事情是成对的列在一起是主要的。例如，您知道不能有重复的主列 Ex：如果 col "a" 是主列，则不能有两行具有相同的 a 值。
In this example you have two primaries; which means you can only have one unique value for each col pair. For example if col 'a' and 'b' are primary and 'c' is not: a|b|c 1,2,3 works 1,4,5 works and 5,1,6 works 9,1,10 works
在这个例子中，你有两个原色；这意味着每个 col 对只能有一个唯一值。例如，如果 col 'a' 和 'b' 是主要的，而 'c' 不是： a|b|c 1,2,3 工作 1,4,5 工作和 5,1,6 工作 9,1,10 工作
but you cannot have: 9,8,10 9,8,6 because for that (9,8) pair you can only have one unique value...
但你不能有： 9,8,10 9,8,6 因为对于那个 (9,8) 对你只能有一个唯一的值......
Does that make sense or would you like me to further elaborate?
这是否有意义，或者您希望我进一步详细说明吗？
回答by Bala
ALTER TABLE TableNameDROP PRIMARY KEY, ADD PRIMARY KEY( column1, column2);
if you have set primary key previously, then try this.
ALTER TABLE TableNameDROP PRIMARY KEY, ADD PRIMARY KEY( column1, column2); 如果您之前设置了主键，请尝试此操作。
回答by Vincent
To make it easier to explain, I will only use one table. Create a table with 2 int columns, and a PK on both of them together. As in the question.
为了便于解释，我将仅使用一张表。创建一个包含 2 个 int 列的表，并在这两个列上进行 PK。如问题中所述。
create table test(
a INT NOT NULL ,
b INT NOT NULL ,
PRIMARY KEY(a,b));
Now we can add rows, until we get an error
现在我们可以添加行，直到出现错误
insert into test values(1,1);
Query OK, 1 row affected (0,00 sec)
insert into test values(1,2);
Query OK, 1 row affected (0,00 sec)
insert into test values(1,1);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'PRIMARY'
Which is logical because the combined values of the 2 columns which make up the PK are not unique anymore when this last statement would be executed.
这是合乎逻辑的，因为当执行最后一条语句时，构成 PK 的 2 列的组合值不再是唯一的。
It is allowed to store 2x the value 1 in a, because that is not the PK. The PK is the combined value of columns a and b.
允许在 a 中存储值 1 的 2 倍，因为那不是 PK。PK 是列 a 和 b 的组合值。
