如何在 SQL 中定义复合主键? [英] How can I define a composite primary key in SQL?

问题描述

如何在 SQL 中定义由两个字段组成的复合主键?

How can I define a composite primary key consisting of two fields in SQL?

我正在使用 PHP 创建表格和所有内容.我想用字段 QuestionID、MemeberID 和 vote 制作一个表名 voting.复合主键由字段QuestionID 和MemberID 组成.

I am using PHP to create tables and everything. I want to make a table name voting with fields QuestionID, MemeberID, and vote. And the Composite primary key consists of the fields QuestionID and MemberID.

我该怎么做?

推荐答案

只是为了澄清:一个表最多可以有一个主键.主键由一列或多列(来自该表)组成.如果主键由两列或更多列组成，则称为复合主键.其定义如下:

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

该对 (QuestionID,MemberID) 必须是表的唯一值，并且两个值都不能为 NULL.如果你做这样的查询:

The pair (QuestionID,MemberID) must then be unique for the table and neither value can be NULL. If you do a query like this:

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

不会，因为使用复合索引需要使用左侧"的所有键.如果索引位于字段 (A,B,C) 上并且您的条件位于 B 和 C 上，那么该索引对该查询没有用处.因此，请从 (QuestionID,MemberID) 和 (MemberID,QuestionID) 中选择最适合您使用表格的方式.

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.

如有必要，在另一个上添加索引:

If necessary, add an index on the other:

CREATE UNIQUE INDEX idx1 ON voting (MemberID, QuestionID);

这篇关于如何在 SQL 中定义复合主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！