How Do I Define and Optimize a Composite Primary Key in SQL?

SQL Composite Primary Keys: Definition and Optimization

A composite primary key in SQL uses multiple columns to uniquely identify each row within a table. This contrasts with a single-column primary key. To create one, list the key columns after the PRIMARY KEY keyword.

Illustrative Example:

Let's say we're building a voting table with QuestionID, MemberID, and vote fields. We'll use QuestionID and MemberID as a composite primary key, ensuring that a member can only vote once per question.

CREATE TABLE voting (
  QuestionID NUMERIC,
  MemberID NUMERIC,
  vote VARCHAR(255),
  PRIMARY KEY (QuestionID, MemberID)
);

The combination of QuestionID and MemberID uniquely identifies each record. Crucially, neither QuestionID nor MemberID can contain NULL values.

Index Impact on Performance:

• Query Optimization: A composite primary key, inherently indexed, boosts query speed when filtering by any of its constituent columns.
• Key Order Significance: The column order within the primary key definition directly affects index selection during queries. Careful consideration is needed.

Strategic Additional Indexing:

Frequently querying on columns outside the primary key? Creating separate indexes for these columns is a performance optimization strategy.

For instance, if MemberID is frequently used in queries on the voting table, an additional index could be beneficial:

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

This creates a unique index using MemberID and QuestionID. Note that the order might differ from the primary key order to suit query patterns. Choosing the optimal index order depends on the most frequent query types.

The above is the detailed content of How Do I Define and Optimize a Composite Primary Key in SQL?. For more information, please follow other related articles on the PHP Chinese website!