How can I define a composite primary key in SQL?

sql primary-key composite-primary-key
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);

Related questions

ALTER TABLE to add a composite primary key

I have a table called provider. I have three columns called person, place, thing. There can be duplicate persons, duplicate places, and duplicate things, but there can never be a dupicate person-place-thing combination. How would I ALTER TABLE to add …

Read More
Referencing a composite primary key

I have two tables, with each table having a composite primary key. One attribute is in both composite primary keys. How am i supposed to reference the common attribute?? Do i just reference it as a FK in both tables …

Read More
Change Primary Key to Composite Key (Primary Key already exists)

I am trying to change the primary key of a table in my SQL database from the existing key to a composite key, which does not include the existing column. The following code is not working due to the following …

Read More