How to Define and Use a Composite Primary Key in SQL?

Detailed explanation of SQL composite primary key

In SQL, a primary key is used to uniquely identify each row in a table. When a primary key consists of multiple columns, it is called a composite primary key.

How to create a composite primary key

To define a composite primary key for a table, use the following syntax:

<code class="language-sql">CREATE TABLE 表名 (
  列名1 数据类型,
  列名2 数据类型,
  PRIMARY KEY (列名1, 列名2)
);</code>

Example: Consider a table called "Vote" containing columns QuestionID, MemberID, and vote. To create a composite primary key consisting of QuestionID and MemberID, use the following SQL statement:

<code class="language-sql">CREATE TABLE voting (
  QuestionID NUMERIC,
  MemberID NUMERIC,
  vote NUMERIC,
  PRIMARY KEY (QuestionID, MemberID)
);</code>

Key Notes

• The columns specified in the primary key must be unique for each row.
• No column in the primary key can contain null values.
• Queries that filter based only on a single column in a composite primary key will not be able to use the primary key index.
• To optimize queries, consider creating additional indexes on the remaining columns in the composite primary key.

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