如何在 SQL 中定义复合主键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1110349/
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 can I define a composite primary key in SQL?
提问by Zeeshan Rang
How can I define a composite primary key consisting of two fields in SQL?
如何在 SQL 中定义由两个字段组成的复合主键？
I am using PHP to create tables and everything. I want to make a table name votingwith fields QuestionID, MemeberID, and vote. And the Composite primary key consists of the fields QuestionIDand MemberID.
我正在使用 PHP 创建表和所有内容。我想打一个表名voting与领域QuestionID，MemeberID和vote。复合主键由字段QuestionID和 组成MemberID。
How should I do this?
我该怎么做？
回答by cletus
Just for clarification: a table can have at most one primary key. A primary key consists of one or more columns (from that table). If a primary key consists of two or more columns it is called a composite primary key. It is defined as follows:
只是为了澄清：一张表最多可以有一个主键。主键由一列或多列（来自该表）组成。如果主键由两列或更多列组成，则称为复合主键。它的定义如下：
CREATE TABLE voting (
QuestionID NUMERIC,
MemberID NUMERIC,
PRIMARY KEY (QuestionID, MemberID)
);
The pair (QuestionID,MemberID) must then be unique for the table and neither value can be NULL. If you do a query like this:
然后，该对 (QuestionID,MemberID) 对于该表必须是唯一的，并且两个值都不能为 NULL。如果你做这样的查询：
SELECT * FROM voting WHERE QuestionID = 7
it will use the primary key's index. If however you do this:
它将使用主键的索引。但是，如果您这样做：
SELECT * FROM voting WHERE MemberID = 7
it won't because to use a composite index requires using all the keys from the "left". If an index is on fields (A,B,C) and your criteria is on B and C then that index is of no use to you for that query. So choose from (QuestionID,MemberID) and (MemberID,QuestionID) whichever is most appropriate for how you will use the table.
不会，因为使用复合索引需要使用“左侧”的所有键。如果索引位于字段 (A,B,C) 上并且您的条件位于 B 和 C 上，那么该索引对该查询没有用处。因此，从 (QuestionID,MemberID) 和 (MemberID,QuestionID) 中选择最适合您将如何使用表格的方式。
If necessary, add an index on the other:
如有必要，在另一个上添加索引：
CREATE UNIQUE INDEX idx1 ON voting (MemberID, QuestionID);
回答by Justin
CREATE TABLE `voting` (
`QuestionID` int(10) unsigned NOT NULL,
`MemberId` int(10) unsigned NOT NULL,
`vote` int(10) unsigned NOT NULL,
PRIMARY KEY (`QuestionID`,`MemberId`)
);
