134

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?

Kevin Brown
  • 36,829
  • 37
  • 188
  • 225
Zeeshan Rang
  • 17,525
  • 27
  • 65
  • 96
  • 1
    "And the QuestionID and MemberID will be the primary keys." (QuestionID, MemberID) will be the (composite) primary *key*. There is only one key and it consists of two columns. – Draemon Jul 10 '09 at 16:22

4 Answers4

257

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);
cletus
  • 578,732
  • 155
  • 890
  • 933
  • 8
    Good answer. Just to clarify, QuestionID and MemberID are not separate primary keys but rather the combination of them form a unique pair/tuple. – Peter Jul 10 '09 at 15:47
  • 7
    Is there a benefit to adding index on `(MemberID, QuestionID)`, as opposed to just `MemberID`? As far as I understood, you get indexed lookup when selecting with `QuestionId`, and also `(QuestionId, MemeberId)`, so the only missing one would be `MemberId` only. – swalog Nov 26 '15 at 13:21
  • I know this answer is quite old but since it popped up during a google search... It seems there's an inconsistency in the text about which columns are used (or not) for the default index(es?). Furthermore, it assumes that every RDBMS will automatically create an index on the primary key, but since it is not required by any standard, there might be corner cases out there. – To마SE Oct 05 '16 at 09:04
  • 1
    Because both fields are simple (and probably primary) keys from other tables the example shows a compound key not a composite key – guymid Dec 08 '16 at 09:24
7
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`)
);
Justin
  • 4,789
  • 1
  • 20
  • 21
1

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));
Vivek Gupta
  • 11
  • 3
-1

In Oracle database we can achieve like this.

CREATE TABLE Student(
  StudentID Number(38, 0) not null,
  DepartmentID Number(38, 0) not null,
  PRIMARY KEY (StudentID, DepartmentID)
);
Simas Joneliunas
  • 2,522
  • 12
  • 20
  • 28
Min
  • 21
  • 4