How can I define a composite primary key in SQL?

Solution 1:

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

Solution 2:

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

Solution 3:

QuestionID will be the primary key in your case. You can use MemberID as a candidate key (indexing will be on this) as one member may answer multiple Questions. The other way around will not make sense.

Syntax:

CREATE TABLE SAMPLE_TABLE  
(COL1 integer,  
COL2 integer,  
COL3 integer,  
PRIMARY KEY (COL1, COL2));