How can I define a composite primary key in SQL?

Zeeshan Rang picture Zeeshan Rang · Jul 10, 2009 · Viewed 270.9k times · Source

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

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.

How should I do this?

Answer

cletus picture cletus · Jul 10, 2009

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:

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.

If necessary, add an index on the other:

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