How can I define a composite primary key in SQL

Question

In SQL, how do I create a composite primary key with two fields?

To generate tables and everything else, I'm using PHP. I'd want to create a table called voting that has the values QuestionID, MemberID, and vote. The fields QuestionID and MemberID make up the Composite primary key.

How do I go about doing this?

Answer 1

A primary key is unique and it consists of one or more columns (from that table itself). If a primary key consists of two or more columns it is called a composite primary key. It is defined as follows:

CREATE TABLE vote (
  QuestionID INT,
  MemberID INT,
  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:

SELECT * FROM vote WHERE QuestionID = 2

it will use the primary key's index. If however you do this:

SELECT * FROM vote WHERE MemberID = 2