How can I define a composite primary key in SQL?

by

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

More Related Contents:

  1. Auto Increment after delete in MySQL
  2. Strings as Primary Keys in SQL Database [closed]
  3. Foreign key referring to primary keys across multiple tables?
  4. SQL – many-to-many table primary key
  5. ALTER TABLE to add a composite primary key
  6. Is there AUTO INCREMENT in SQLite?
  7. Improving performance of cluster index GUID primary key
  8. Does ‘Select’ always order by primary key?
  9. SQL – Inserting a row and returning primary key
  10. adding primary key to sql view
  11. sql swap primary key values
  12. Retrieve Oracle last inserted IDENTITY
  13. Store and reuse value returned by INSERT … RETURNING
  14. How to cast the DateTime to Time
  15. Find a string by searching all tables in SQL Server
  16. Unrecognized name: employees at [9:8]
  17. Is there a way to define a named constant in a PostgreSQL query?
  18. How to assign an exec result to a sql variable?
  19. SQL ROWNUM how to return rows between a specific range
  20. Delete sql rows where IDs do not have a match from another table
  21. Slow simple update query on PostgreSQL database with 3 million rows
  22. Given an RGB value what would be the best way to find the closest match in the database?
  23. How to export image field to file?
  24. Move SQL data from one table to another
  25. Get Start and End Date from Week Number SQL Server
  26. Nested select statement in SQL Server
  27. PostgreSQL: Give all permissions to a user on a PostgreSQL database
  28. How do you interpret a query’s explain plan?
  29. Conversion failed when converting the nvarchar value … to data type int
  30. How to call Oracle MD5 hash function?

Leave a Comment